Ускоряем восстановление бэкапов в Postgres. Часть вторая (потому что сокращения времени вдвое недостаточно)

В первой части статьи «Ускоряем восстановление бэкапов в Postgres» ^[1] я рассказал о предпринятых шагах по уменьшению времени восстановления в локальном окружении. Мы начали с простого: pg_dump-пили (а есть ли такое слово?), паковали gzip-ом, распаковывали и направляли вывод в psql < file.sql. На восстановление уходило около 30 минут. В итоге мы остановились на настраиваемом (custom) формате Postgres ^[2] и применили аргумент -j, добившись уменьшения времени до 16 минут.

В этой статье я описал, как нам удалось уменьшить размер файла резервной копии, что дополнительно ускорило процедуру восстановления.

Исследуем размер бэкапа

Когда я начал писать о наших попытках ускорения процедуры восстановления, размер упакованного файла резервной копии составлял где-то 2 ГБ (неупакованного — 30 ГБ ). С тех пор наша база практически удвоилась (упакованная — 3,7 ГБ, неупакованная — 68 ГБ). Это не только значительно увеличило время восстановления, но и требовало больше времени на копирование/передачу файла с резервной копией.

Осознав, что файл бэкапа удвоился, я начал выяснять, почему это произошло и какие данные в этом виноваты.

Сначала я узнал размер базы:

# SELECT pg_size_pretty(pg_database_size('dbname'));
pg_size_pretty
----------------
 68 GB
(1 row)

Затем решил посмотреть размеры таблиц, чтобы выяснить, нет ли среди них очевидных виновников разрастания базы.

# SELECT table_name, pg_relation_size(table_name), pg_size_pretty(pg_relation_size(table_name))
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER by 2 DESC;
table_name         | pg_relation_size | pg_size_pretty
-------------------+------------------+---------------
 logging           |      19740196864 | 18 GB
 reversions        |      15719358464 | 15 GB
 webhook_logging   |       8780668928 | 8374 MB
 ...               |       1994645504 | 1902 MB
 ...               |        371900416 | 355 MB
 ...               |        304226304 | 290 MB

На листинге хорошо видно, что 3 верхние таблицы, занимающие более 60% всей базы, относятся к истории или логированию, которые для dev-окружения не нужны. Вместе со своими индексами (17 ГБ, 1 ГБ, 1,5 ГБ соответственно) эти таблицы занимают 89% базы. На этом я решил остановить исследование размеров таблиц (уменьшение на 89% считаю приемлемым) и посмотреть, могу ли я исключить таблицы из бэкапа.

Уменьшаем размер резервной копии

Начиная разбираться с какой-либо проблемой, я стараюсь первым делом ознакомиться с документацией. Проект PostgreSQL в этом плане проделал замечательную работу — после нескольких минут чтения раздела по pg_dump ^[2] я нашел именно то, что было нужно.

pg_dump dbname -Fc 
   --exclude-table-data 'logging*' 
   --exclude-table-data 'reversions*' 
   --exclude-table-data 'webhooks_logging*' > postgres.dev.sql

* (звездочка) здесь используется в качестве символа подстановки и помогает исключить также и индексы этих таблиц.

Указание исключаемых таблиц с помощью параметра --exclude-table-data позволило уменьшить размер файла бэкапа с 3.7 Гб (несжатый — 68 ГБ ) до 0.7 ГБ (несжатый — 5,4 ГБ ).

Посмотрев на листинги, можно убедиться, что результаты просто замечательные.

До:

$ pg_restore -d db -j 8 dumpfc.gz
real 16m49.539s
user 1m1.344s
sys 0m39.522s

После:

$ pg_restore -d db -j 8 devfc.gz
real 5m38.156s
user 0m24.574s
sys 0m13.325s

Исключение трех таблиц позволило уменьшить размер базы на 89% и ускорить восстановление на 66%! Если помните, в первой части мы начали с 32,5 минут. Получается, мы смогли сократить время восстановления на 26,9 минуты или 87%.

По итогам этой статьи мы добились ускорения восстановления с 16 до 5 минут. Это экономит нам 57 часов времени восстановления в год (6 разработчиков на 52 раза в год на 11 минут). В общей сложности мы сократили время ожидания восстановления на 130 часов.

Заключение

Возвращаясь к документации по PostgreSQL ^[3], отмечу, что есть еще несколько способов ускорить процедуру восстановления. Например, стоит взглянуть на параметр -j команды pg_dump, который может сократить время создания бэкапа (доступно начиная с PostgreSQL 9.3). Также могут помочь: отключение autocommit, значительное увеличение maintenance_work_mem и установка более высокого значения max_wal_size.

На данный момент время восстановления резервной копии нашей базы в локальном dev-окружении меня вполне устраивает.

Ссылки:

1. Оригинал: Speeding up Postgres Restores Part 2 (Because cutting the time to restore in half just wasn’t enough) ^[4].
2. Ускоряем восстановление бэкапов в Postgres. Часть первая ^[1].

Автор: Southbridge

Источник ^[5]