В первой части статьи «Ускоряем восстановление бэкапов в Postgres» я рассказал о предпринятых шагах по уменьшению времени восстановления в локальном окружении. Мы начали с простого: pg_dump-пили (а есть ли такое слово?), паковали gzip-ом, распаковывали и направляли вывод в psql < file.sql
. На восстановление уходило около 30 минут. В итоге мы остановились на настраиваемом (custom) формате Postgres и применили аргумент -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
я нашел именно то, что было нужно.
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, отмечу, что есть еще несколько способов ускорить процедуру восстановления. Например, стоит взглянуть на параметр -j
команды pg_dump
, который может сократить время создания бэкапа (доступно начиная с PostgreSQL 9.3). Также могут помочь: отключение autocommit
, значительное увеличение maintenance_work_mem
и установка более высокого значения max_wal_size
.
На данный момент время восстановления резервной копии нашей базы в локальном dev-окружении меня вполне устраивает.
Ссылки:
- Оригинал: Speeding up Postgres Restores Part 2 (Because cutting the time to restore in half just wasn’t enough).
- Ускоряем восстановление бэкапов в Postgres. Часть первая.
Автор: Southbridge