УДК 004.6PostgreSQL 9
ББК 32.973-018.2
P49
Саймон Ригс, Ханну Кросинг
P49 Администрирование PostgreSQL 9. Книга рецептов. Пер. с англ.: Самохвалова
Е. В. – М.: ДМК Пресс, 2018. – 364 с.: ил.
ISBN 978-5-97060-609-4
Эта книга поможет вам освоить систему управления базами данных
PostgreSQL. Здесь вы найдете рекомендации ведущего разработчика функционала
репликации и восстановления PostgreSQL и системного архитектора
Skype – одного из самых успешных проектов, созданных с помощью
PostgreSQL. Данное руководство должно стать настольным для разработчиков,
имеющих дело с «боевыми» базами данных и использующих PHP, Java,
Python, Ruby, .Net в любой среде разработки.
В книге представлены ответы на общие вопросы и решения проблем,
почерпнутые авторами из личного опыта работы с СУБД PostgreSQL – в качестве
пользователей, разработчиков и преподавателей.
Каждая задача разбивается на простые части, или рецепты, проиллюстрированные
кодом и сопровождаемые подробными объяснениями, как и почему
это работает. Таким образом, руководство будет полезно как новичкам,
так и специалистам. Кроме того, здесь отражены все новые возможности
PostgreSQL 9.
All rights reserved. No part of this book may be reproduced, stored in a retrieval system,
or transmitted in any form or by any means, without the prior written permission of the
publisher, except in the case of brief quotations embedded in critical articles or reviews.
Все права защищены. Любая часть этой книги не может быть воспроизведена
в какой бы то ни было форме и какими бы то ни было средствами без письменного
разрешения владельцев авторских прав.
Материал, изложенный в данной книге, многократно проверен. Но, поскольку
вероятность технических ошибок все равно существует, издательство не может гарантировать
абсолютную точность и правильность приводимых сведений. В связи с
этим издательство не несет ответственности за возможные ошибки, связанные с использованием
книги.
ISBN 978-1-849510-28-8 (англ.)
ISBN 978-5-97060-609-4 (рус.)
© Published by Packt Publishing Ltd.
© Оформление, перевод на русский язык
ДМК Пресс
Стр.5
6
Содержание
Содержание
Об авторах............................................................................... 12
О рецензентах ......................................................................... 13
Предисловие ................................................... 14
О чем эта книга ........................................................................ 14
Что вам понадобится ............................................................... 17
Для кого написана книга .......................................................... 17
Условные обозначения ............................................................ 18
Сообщение об ошибках ........................................................... 18
Незаконное воспроизведение содержимого книги .................. 19
Вопросы .................................................................................. 19
ГЛАВА 1.
Первые шаги .................................................... 20
Введение ................................................................................. 20
Представляем PostgreSQL 9 .................................................... 20
Где взять PostgreSQL ............................................................... 23
Соединение с сервером PostgreSQL ....................................... 25
Открытие доступа для сетевых/удаленных
пользователей ......................................................................... 28
Использование графических инструментов
администрирования ................................................................ 30
Использование инструмента для написания запросов
и скриптов psql ........................................................................ 34
Безопасная смена пароля ....................................................... 38
Как избежать хранения пароля в коде ...................................... 39
Использование сервисного файла соединений ....................... 40
Если установить соединение не удается .................................. 41
ГЛАВА 2.
Исследование базы данных ................................ 44
Введение ................................................................................. 44
Как узнать версию сервера ...................................................... 44
Определение времени работы сервера ................................... 46
Где находятся файлы сервера БД ............................................ 47
Стр.7
Содержание
7
Где находится журнал сообщений сервера .............................. 49
Где находится системный идентификатор БД .......................... 52
Список баз данных на сервере ................................................ 53
Сколько таблиц в базе данных ................................................. 56
Сколько места на диске занимает база данных ........................ 59
Сколько места на диске занимает таблица .............................. 59
Какие таблицы являются самыми большими ........................... 60
Сколько строк в таблице .......................................................... 61
Быстрый подсчет строк в таблице............................................ 62
Понятие объектных зависимостей ........................................... 67
ГЛАВА 3.
Конфигурация .................................................. 70
Введение ................................................................................. 70
«Правильная» документация (RTFM) ........................................ 70
Планирование новой базы данных ........................................... 72
Изменение параметров в программах ..................................... 73
Текущие конфигурационные настройки ................................... 75
Параметры, не определяемые по умолчанию .......................... 76
Обновление файла параметров ............................................... 78
Настройка параметров для определенных
групп пользователей ............................................................... 79
Основной список параметров для проверки конфигурации
сервера ................................................................................... 80
Добавление внешних модулей в PostgreSQL ............................ 82
Запуск сервера в энергосберегающем режиме ....................... 84
ГЛАВА 4.
Контроль над сервером ..................................... 86
Введение ................................................................................. 86
Запуск сервера БД вручную ..................................................... 87
Быстрая и безопасная остановка сервера ............................... 88
Экстренная остановка сервера ................................................ 89
Перезагрузка конфигурационных файлов ................................ 89
Быстрый перезапуск сервера .................................................. 91
Запрет на создание новых соединений.................................... 93
Создание ограничения по одной сессии на пользователя ........ 94
Принудительное отключение пользователей ........................... 96
Проектирование с учетом multi-tenancy ................................... 97
Использование множественных схем ...................................... 99
Выделение для пользователя собственной базы данных ....... 101
Запуск нескольких серверов на одной системе ..................... 103
Настройка пула соединений .................................................. 104
Стр.8
8
Содержание
ГЛАВА 5.
Таблицы и данные ........................................... 108
Введение ............................................................................... 108
Выбор имени для объекта БД ................................................ 108
Управление объектами с именами,
заключенными в кавычки ....................................................... 110
Принудительная установка единого имени,
единого определения столбцов ............................................. 112
Обнаружение и удаление дубликатов .................................... 116
Предотвращение появления строк-дубликатов ..................... 119
Поиск уникального ключа для выборки данных ...................... 124
Генерирование тестовых данных ........................................... 126
Случайные данные по примеру .............................................. 129
Подгрузка данных из электронных таблиц ............................. 132
Подгрузка данных из файлов ................................................. 135
ГЛАВА 6.
Безопасность................................................. 139
Введение ............................................................................... 139
Как забрать у пользователя права доступа к таблице ............. 140
Как предоставить пользователю доступ к таблице ................. 142
Создание нового пользователя ............................................. 144
Временный запрет соединений для пользователя ................. 145
Удаление пользователя без потери его данных ...................... 146
Проверка безопасности паролей пользователей ................... 148
Назначение определенным пользователям
ограниченных прав суперпользователя ................................. 150
Аудит изменений DDL ............................................................ 153
Аудит изменений данных ....................................................... 154
Интеграция с LDAP ................................................................ 158
Соединение с использованием SSL ....................................... 159
Шифрование секретных данных ............................................ 162
ГЛАВА 7.
Администрирование баз данных ....................... 166
Введение ............................................................................... 166
Написание скрипта, который срабатывает полностью
или не работает вообще ........................................................ 167
Написание скрипта psql, который прерывается
после первой ошибки ............................................................ 170
Действия с несколькими таблицами ...................................... 171
Добавление и удаление столбца ........................................... 176
Стр.9
Содержание
9
Изменение типа данных столбца ........................................... 178
Добавление и удаление схем ................................................. 181
Перемещение объектов между схемами ................................ 183
Добавление и удаление табличных пространств .................... 184
Перемещение объектов между табличными пространствами .... 188
Доступ к объектам, находящимся в других БД PostgreSQL ..... 191
Обновление представления .................................................. 196
ГЛАВА 8.
Мониторинг и диагностика ............................... 202
Введение ............................................................................... 202
Подключен ли пользователь .................................................. 205
Что выполняет пользователь ................................................. 207
Активен или заблокирован запрос ......................................... 209
Почему запрос блокирован .................................................... 211
Как прервать сессию ............................................................. 212
Принятие решения о сомнительной подготовленной
транзакции ............................................................................ 215
Использует ли кто-либо определенную таблицу .................... 215
Когда в последний раз использовали таблицу ....................... 217
Сколько места на диске будут занимать временные данные ...... 220
Почему запросы выполняются медленно ............................... 222
Обнаружение ошибок и сообщение о них .............................. 225
Подведение итога записей в журнале об ошибках за день ..... 227
ГЛАВА 9.
Регулярное обслуживание ............................... 230
Введение ............................................................................... 230
Управление автоматическим обслуживанием базы данных ... 231
Предотвращение «автозаморозки» и повреждения страниц ...... 236
Предупреждение «свертывания» счетчика транзакций .......... 238
Удаление ранее подготовленных транзакций ......................... 240
Для любителей временных таблиц ......................................... 243
Обнаружение и исправление раздутых таблиц и индексов ..... 244
Обслуживание индексов ........................................................ 249
Обнаружение неиспользуемых индексов ............................... 252
Безопасное удаление ненужных индексов ............................. 254
Планирование обслуживания ................................................ 255
ГЛАВА 10.
Производительность и согласованность ............. 258
Введение ............................................................................... 258
Как найти медленные запросы SQL ....................................... 259
Стр.10
10
Содержание
Как получить регулярную статистику
из представлений pg_stat* ..................................................... 262
Как установить причины замедления работы SQL .................. 263
Как уменьшить число возвращаемых строк ........................... 268
Как упростить сложный SQL ................................................... 269
Как ускорить выполнение запросов, не переписывая их ........ 275
Почему запрос не использует индекс .................................... 279
Как заставить запрос использовать индекс ........................... 280
Использование оптимистичных блокировок .......................... 282
Сообщения о проблемах с производительностью .................. 284
ГЛАВА 11.
Резервное копирование и восстановление ......... 285
Введение ............................................................................... 285
Что такое восстановление после сбоя
и как его контролировать ....................................................... 286
Планирование резервного копирования ................................ 288
Горячее логическое резервное копирование
одной базы данных ................................................................ 291
Горячее логическое резервное копирование
всех баз данных ..................................................................... 293
Горячее логическое резервное копирование всех таблиц
из табличного пространства .................................................. 294
Резервное копирование определений
объектов базы данных ........................................................... 296
Автономное горячее физическое резервное копирование
базы данных .......................................................................... 297
Горячее физическое резервное копирование
и непрерывное архивирование .............................................. 300
Восстановление всех баз данных .......................................... 303
Восстановление до определенной временной точки ............. 307
Восстановление удаленной или поврежденной таблицы ....... 310
Восстановление удаленного или
поврежденного табличного пространства ............................. 312
Восстановление удаленной
или поврежденной базы данных ............................................ 314
Улучшение производительности резервного копирования
и восстановления .................................................................. 315
Инкрементальное и дифференциальное
резервное копирование и восстановление ............................ 318
Стр.11
Содержание
ГЛАВА 12.
11
Репликация и обновления ................................ 321
Репликация: основные термины ............................................ 322
Лучшие способы репликации ................................................. 326
Файловая репликация с передачей журналов ........................ 328
Настройка потоковой журнальной репликации ...................... 332
Управление репликацией с передачей журналов ................... 337
Управление режимом горячего резервирования ................... 341
Выборочная репликация с помощью Londiste 3.0 ................... 346
Выборочная репликация с помощью Slony 2.0 ....................... 351
Балансировка нагрузки с помощью pgpool-II 3.0 .................... 356
Минорное обновление ........................................................... 359
Мажорные обновления на месте ............................................ 360
Мажорные онлайн-обновления с помощью инструментов
репликации ........................................................................... 362
Стр.12