PostgreSQL 11. Мастерство разработки (5000,00 руб.)

Стр.5

Стр.6

Стр.7

Стр.8

Стр.9

Стр.10

Стр.11

УДК 004.65 ББК 32.972.134 Ш47 Ш47 PostgreSQL 11. Мастерство разработки / пер. с анг. А. А. Слинкина. – М.: ДМК Пресс, 2019. – 352 с.: ил. Шениг Г.-Ю. ISBN 978-5-97060-671-1 Книга описывает последние возможности PostgreSQL 11 для построения эффективных и отказоустойчивых приложений. Подробно рассмотрены передовые аспекты PostgreSQL, включая логическую репликацию, кластеры баз данных, оптимизацию производительности, мониторинг и управление пользователями, процесс миграции с Oracle на PostgreSQL. Издание рекомендовано ведущими специалистами в области PostreSQL в России, будет полезно администраторам и разработчикам этой СУБД. УДК 004.65 ББК 32.972.134 Authorized Russian translation of the English edition of Mastering PostgreSQL 11, Second Edition ISBN 9781789537819 © 2018 Packt Publishing. This translation is published and sold by permission of Packt Publishing, which owns or controls all rights to publish and sell the same. Все права защищены. Любая часть этой книги не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. ISBN 978-1-78953-781-9 (анг.) ISBN 978-5-97060-671-1 (рус.) © 2018 Packt Publishing © Оформление, издание, перевод, ДМК Пресс, 2019

Стр.5

Содержание Об авторе ...................................................................................................................... 11 О рецензенте ............................................................................................................... 12 Предисловие ............................................................................................................... 13 Глава 1. Обзор PostgreSQL .................................................................................. 16 Что нового в PostgreSQL 11.0? ............................................................................................ 16 Новые средства администрирования базы данных .................................................... 17 Усовершенствования в индексировании и оптимизации ........................................... 18 Улучшенное управление кешем .................................................................................... 20 Улучшенные оконные функции .................................................................................... 20 Добавление JIT-компиляции ......................................................................................... 21 Улучшенное секционирование ..................................................................................... 21 Поддержка хранимых процедур ................................................................................... 22 Улучшение команды ALTER TABLE ............................................................................... 23 Резюме ................................................................................................................................. 24 Вопросы ............................................................................................................................... 24 Глава 2. Транзакции и блокировка ................................................................ 25 Работа с транзакциями в PostgreSQL ................................................................................ 25 Обработка ошибок внутри транзакции ........................................................................ 27 Использование команды SAVEPOINT ........................................................................... 28 Транзакционные DDL-команды .................................................................................... 29 Основы блокировки ............................................................................................................ 30 Предотвращение типичных ошибок и явная блокировка .......................................... 31 Использование фраз FOR SHARE и FOR UPDATE .............................................................. 33 Уровни изоляции транзакций ........................................................................................... 36 SSI-транзакции ............................................................................................................... 38 Взаимоблокировки и смежные вопросы ........................................................................... 38 Рекомендательные блокировки ......................................................................................... 40 Оптимизация хранилища и управление очисткой .......................................................... 41 Настройка VACUUM и autovacuum ................................................................................ 41 Наблюдение за работой VACUUM .................................................................................. 43 Ограничение длительности транзакций с помощью времени жизни снимка .......... 46 Резюме ................................................................................................................................. 47 Вопросы ............................................................................................................................... 47 Глава 3. Использование индексов.................................................................. 49 Простые запросы и стоимостная модель .......................................................................... 49 Использование команды EXPLAIN ............................................................................... 50

Стр.6

6  Содержание Стоимостная модель PostgreSQL ................................................................................... 52 Развертывание простых индексов ................................................................................ 54 Сортировка результатов ................................................................................................ 55 Эффективное использование просмотра по битовой карте ....................................... 57 Разумное использование индексов .............................................................................. 57 Повышение быстродействия с помощью кластеризованных таблиц............................. 59 Кластеризация таблиц ................................................................................................... 62 Просмотр только индекса .............................................................................................. 62 Дополнительные свойства B-деревьев.............................................................................. 63 Комбинированные индексы .......................................................................................... 63 Добавление функциональных индексов ...................................................................... 64 Уменьшение занятого места на диске .......................................................................... 65 Добавление данных во время индексирования ........................................................... 66 Введение в классы операторов .......................................................................................... 67 Создание класса операторов для B-дерева .................................................................. 68 Типы индексов в PostgreSQL .............................................................................................. 73 Хеш-индексы .................................................................................................................. 73 GiST-индексы .................................................................................................................. 73 GIN-индексы ................................................................................................................... 76 SP-GiST-индексы ............................................................................................................ 77 BRIN-индексы ................................................................................................................. 77 Добавление новых типов индексов .............................................................................. 79 Получение более точных ответов с помощью нечеткого поиска .................................... 80 Расширение pg_trgm и его достоинства ....................................................................... 80 Ускорение запросов с предикатом LIKE ....................................................................... 82 Регулярные выражения ................................................................................................. 83 Полнотекстовый поиск ....................................................................................................... 83 Сравнение строк ............................................................................................................. 84 Определение GIN-индексов .......................................................................................... 85 Отладка поиска............................................................................................................... 86 Сбор статистики по словам ........................................................................................... 87 О пользе операторов исключения ................................................................................ 87 Резюме ................................................................................................................................. 88 Вопросы ............................................................................................................................... 88 Глава 4. Передовые средства SQL ................................................................. 90 Введение в наборы группирования ................................................................................... 90 Загрузка тестовых данных ............................................................................................. 90 Применение наборов группирования .......................................................................... 91 Сочетание наборов группирования с фразой FILTER .................................................. 94 Использование упорядоченных наборов .......................................................................... 95 Гипотетические агрегаты ................................................................................................... 97 Оконные функции и аналитические средства ................................................................. 97 Разбиение данных .......................................................................................................... 98 Упорядочение данных внутри окна .............................................................................. 99 Скользящие окна .......................................................................................................... 100 Абстрагирование окон ................................................................................................. 102

Стр.7

Содержание  7 Использование встроенных оконных функций ......................................................... 103 Создание собственных агрегатов .................................................................................... 109 Создание простых агрегатов ....................................................................................... 109 Добавление поддержки параллельных запросов ....................................................... 112 Повышение эффективности ........................................................................................ 113 Написание гипотетических агрегатов ........................................................................ 114 Резюме ............................................................................................................................... 116 Глава 5. Журналы и статистика системы .................................................. 117 Сбор статистических данных о работе системы ............................................................ 117 Системные представления в PostgreSQL .................................................................... 117 Создание файлов журналов ............................................................................................. 134 Конфигурационный файл postgresql.conf .................................................................. 134 Резюме ............................................................................................................................... 139 Вопросы ............................................................................................................................. 139 Глава 6. Оптимизация запросов для достижения максимальной производительности .......................................................... 141 Что делает оптимизатор................................................................................................... 141 Оптимизация на примере ........................................................................................... 142 Разбираемся в планах выполнения ................................................................................. 151 Систематический подход к планам выполнения ...................................................... 151 Выявление проблем ..................................................................................................... 153 Соединения: осмысление и исправление ....................................................................... 157 Как соединять правильно ............................................................................................ 157 Обработка внешних соединений ................................................................................ 158 Параметр join_collapse_limit ........................................................................................ 159 Включение и выключение режимов оптимизатора ....................................................... 160 Генетическая оптимизация запросов ......................................................................... 163 Секционирование данных ............................................................................................... 164 Создание секций .......................................................................................................... 164 Применение табличных ограничений........................................................................ 166 Модификация наследуемой структуры ...................................................................... 167 Перемещение таблицы в наследуемую структуру и из нее....................................... 168 Очистка данных ........................................................................................................... 168 Секционирование в PostgreSQL 11.0 ........................................................................... 169 Настройка параметров для повышения производительности запросов ..................... 171 Ускорение сортировки ................................................................................................. 173 Ускорение административных задач ......................................................................... 175 Распараллеливание запросов .......................................................................................... 176 Что PostgreSQL умеет делать параллельно? ............................................................... 179 Распараллеливание на практике ................................................................................. 179 Введение в JIT-компиляцию ............................................................................................ 180 Настройка JIT ................................................................................................................ 181 Выполнение запросов .................................................................................................. 181 Резюме ............................................................................................................................... 183

Стр.8

8  Содержание Глава 7. Написание хранимых процедур ................................................. 184 Языки хранимых процедур .............................................................................................. 184 Фундаментальные основы – хранимые процедуры и функции ............................... 185 Анатомия функции ...................................................................................................... 186 Языки хранимых процедур .............................................................................................. 189 Введение в PL/pgSQL .................................................................................................... 190 Создание хранимых процедур на PL/pgSQL............................................................... 204 Введение в PL/Perl ........................................................................................................ 205 Введение в PL/Python ................................................................................................... 211 Улучшение функций ......................................................................................................... 214 Уменьшение числа вызовов функций ........................................................................ 214 Резюме ............................................................................................................................... 218 Вопросы ............................................................................................................................. 218 Глава 8. Безопасность в PostgreSQL............................................................ 220 Управление сетевой безопасностью ............................................................................... 220 Подключения и адреса привязки ................................................................................ 221 Файл pg_hba.conf .......................................................................................................... 224 Безопасность на уровне экземпляра .......................................................................... 228 Задание безопасности на уровне базы данных ......................................................... 232 Задание прав на уровне схемы.................................................................................... 233 Работа с таблицами ...................................................................................................... 235 Задание прав на уровне столбцов ............................................................................... 236 Задание привилегий по умолчанию ........................................................................... 237 Безопасность на уровне строк ......................................................................................... 238 Просмотр прав .................................................................................................................. 242 Передача объектов и удаление пользователей ............................................................... 243 Резюме ............................................................................................................................... 245 Вопросы ............................................................................................................................. 245 Глава 9. Резервное копирование и восстановление ........................ 246 Простая выгрузка .............................................................................................................. 246 Запуск pg_dump ............................................................................................................ 247 Задание пароля и информации о подключении ........................................................ 248 Извлечение подмножества данных ............................................................................ 250 Форматы резервной копии .............................................................................................. 250 Восстановление из резервной копии .............................................................................. 252 Сохранение глобальных данных ..................................................................................... 253 Резюме ............................................................................................................................... 253 Вопросы ............................................................................................................................. 254 Глава 10. Резервное копирование и репликация .............................. 255 Что такое журнал транзакций .......................................................................................... 255 Знакомство с журналом транзакций .......................................................................... 256 Контрольные точки ...................................................................................................... 257 Оптимизация журнала транзакций ............................................................................ 257

Стр.9

Содержание  9 Архивация и восстановление журнала транзакций ....................................................... 259 Настройка архивации .................................................................................................. 259 Конфигурирование файла pg_hba.conf ....................................................................... 260 Создание базовой резервной копии ........................................................................... 261 Воспроизведение журнала транзакций ...................................................................... 263 Очистка архива журналов транзакций ....................................................................... 267 Настройка асинхронной репликации ............................................................................. 268 Базовая настройка ........................................................................................................ 268 Остановка и возобновление репликации ................................................................... 270 Проверка состояния репликации для обеспечения доступности ............................. 271 Отработка отказов и линии времени ......................................................................... 274 Управление конфликтами ........................................................................................... 275 Повышение надежности репликации ......................................................................... 276 Переход на синхронную репликацию ............................................................................. 277 Настройка долговечности ............................................................................................ 279 Слоты репликации ............................................................................................................ 280 Работа с физическими слотами репликации ............................................................. 281 Работа с логическими слотами репликации .............................................................. 283 Использование команд CREATE PUBLICATION и CREATE SUBSCRIPTION ................... 285 Резюме ............................................................................................................................... 287 Вопросы ............................................................................................................................. 287 Глава 11. Полезные расширения .................................................................. 289 Как работают расширения ............................................................................................... 289 Проверка доступных расширений .............................................................................. 290 Использование модулей из подборки contrib ................................................................ 293 Модуль adminpack ........................................................................................................ 293 Применение фильтра Блума ....................................................................................... 294 Установка btree_gist и btree_gin ................................................................................... 296 Dblink – пора расстаться .............................................................................................. 297 Доступ к файлам с помощью file_fdw.......................................................................... 298 Анализ хранилища с помощью pageinspect ............................................................... 299 Анализ кеша с помощью pg_buffercache ..................................................................... 301 Шифрование данных с помощью pgcrypto ................................................................. 302 Прогрев кеша с помощью pg_prewarm ........................................................................ 302 Анализ производительности с помощью pg_stat_statements ................................... 304 Анализ хранилища с помощью pgstattuple ................................................................ 304 Нечеткий поиск с помощью pg_trgm .......................................................................... 305 Подключение к удаленному серверу с помощью postgres_fdw ................................. 305 Глава 12. Поиск и устранение неполадок ............................................... 311 Первоначальное изучение незнакомой базы данных ................................................... 311 Анализ результатов pg_stat_activity ................................................................................. 311 Опрос pg_stat_activity ................................................................................................... 312 Выявление медленных запросов ..................................................................................... 314 Другие полезные расширения ......................................................................................... 309 Резюме ............................................................................................................................... 310

Стр.10

10  Содержание Анализ отдельных запросов ........................................................................................ 315 Углубленный анализ с помощью perf ......................................................................... 316 Анализ журнала ................................................................................................................ 317 Анализ наличия индексов ................................................................................................ 318 Анализ памяти и ввода-вывода ....................................................................................... 318 О конкретных ошибочных ситуациях ............................................................................. 320 Повреждение clog ......................................................................................................... 320 Что означают сообщения о контрольной точке ......................................................... 321 Что делать с поврежденными страницами данных ................................................... 322 Беззаботное управление подключениями ................................................................. 323 Борьба с разбуханием таблиц ...................................................................................... 323 Глава 13. Переход на PostgreSQL ................................................................. 325 Перенос команд SQL в PostgreSQL................................................................................... 325 Латеральные соединения ............................................................................................ 325 Наборы группирования ............................................................................................... 326 Фраза WITH – общие табличные выражения ............................................................. 327 Фраза WITH RECURSIVE ............................................................................................... 328 Фраза FILTER ................................................................................................................. 328 Оконные функции ........................................................................................................ 329 Упорядоченные наборы – фраза WITHIN GROUP ...................................................... 329 Фраза TABLESAMPLE .................................................................................................... 330 Ограничение выборки и смещение ............................................................................ 331 Фраза OFFSET ............................................................................................................... 331 Темпоральные таблицы ............................................................................................... 332 Сопоставление с образцом во временных рядах ....................................................... 332 Резюме ............................................................................................................................... 324 Вопросы ............................................................................................................................. 324 Переход с Oracle на PostgreSQL ........................................................................................ 332 Использование расширения oracle_fdw для переноса данных ................................. 333 Использование ora2pg для перехода с Oracle ............................................................. 334 Распространенные подводные камни ........................................................................ 336 ora_migrator – быстрая миграция Oracle в PostgreSQL .............................................. 337 Резюме ............................................................................................................................... 345 Предметный указатель ....................................................................................... 346 Переход из MySQL или MariaDB на PostgreSQL .............................................................. 338 Обработка данных в MySQL и MariaDB ....................................................................... 339 Перенос данных и схемы ............................................................................................. 343

Стр.11