Бэкап нужен не для галочки, а чтобы пережить удалённую по ошибке таблицу, сгоревший диск и кривую миграцию в проде. В этом уроке разберём, какие вообще бывают копии в postgresql, чем логический дамп отличается от физического, как настроить непрерывную архивацию WAL и сделать восстановление на точный момент времени (PITR). Главная мысль урока: бэкап, который ни разу не восстанавливали, считается несуществующим, поэтому проверку восстановления мы выносим в отдельный пункт.
Как это работает
Есть два мира резервных копий, и их полезно не путать. Логическая копия - это, грубо говоря, набор команд (CREATE TABLE, COPY, INSERT), который заново собирает данные с нуля. Физическая копия - это побайтовый слепок самих файлов кластера плюс журнал предзаписи (WAL), из которого СУБД доигрывает изменения.
Логический бэкап делает pg_dump (одна база) и pg_dumpall (весь кластер целиком, включая роли и табличные пространства). Он переносим между версиями PostgreSQL и даже между архитектурами, легко достать одну таблицу, удобно хранить в git как схему. Платите за это скоростью: на большой базе дамп и особенно последующая загрузка идут долго, потому что заново строятся индексы и проверяются ограничения.
Физический бэкап делает pg_basebackup. Он снимает копию каталога данных на уровне файлов, работает быстрее на крупных базах и является фундаментом для реплик и для PITR. Минус - копия привязана к мажорной версии и платформе, отдельную таблицу из неё так просто не вынуть.
Теперь про сердце физического подхода - WAL. Любое изменение сначала пишется в журнал, а уже потом попадает в файлы данных. Если мы сохраняем базовую копию плюс непрерывный поток WAL-сегментов, то можем восстановить кластер на любой момент между базовой копией и последним заархивированным журналом. Это и есть PITR (point-in-time recovery): откатиться ровно к мгновению перед роковым DELETE.
Связка простая. Параметр archive_command говорит сервью, куда складывать заполненные WAL-сегменты (archive_mode при этом должен быть on). При восстановлении restore_command говорит, откуда их брать. В PostgreSQL 12 настройки восстановления переехали из recovery.conf в обычный postgresql.conf, а сигналом к восстановлению служит пустой файл recovery.signal в каталоге данных - это актуально для PG 15/16/17.
SQL и примеры
Логический дамп демобазы bookings в сжатом custom-формате (это не plain SQL, а контейнер, из которого pg_restore умеет доставать объекты выборочно и параллельно):
Код: Выделить всё
pg_dump -h localhost -U postgres -d demo \
-F c -Z 6 -f demo.dump
Код: Выделить всё
createdb -U postgres demo_restored
pg_restore -U postgres -d demo_restored -j 4 demo.dump
Код: Выделить всё
pg_dump -U postgres -d demo -F c -t bookings.flights -f flights.dump
Код: Выделить всё
pg_dumpall -U postgres --roles-only -f roles.sql
Код: Выделить всё
SELECT relname AS table,
to_char(reltuples, 'FM999G999G999') AS approx_rows,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'bookings' AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC;
Физическая базовая копия для PITR или для будущей реплики:
Код: Выделить всё
pg_basebackup -h localhost -U replicator \
-D /backup/base -F t -z -X stream -P
Настройка непрерывной архивации в postgresql.conf:
Код: Выделить всё
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /arch/%f && cp %p /arch/%f'
Восстановление на момент времени. Распаковываем базовую копию в пустой каталог, кладём recovery.signal и в postgresql.conf задаём:
Код: Выделить всё
restore_command = 'cp /arch/%f %p'
recovery_target_time = '2026-06-13 18:30:00+03'
recovery_target_action = 'promote'
Частые грабли
- archive_command вернул успех, хотя копирование не прошло - сервер считает сегмент заархивированным и может его удалить. Команда обязана падать с ненулевым кодом при любой ошибке.
- Один pg_dump в проде нагружает диск и держит снапшот транзакции долго - на горячей базе это конфликтует с агрессивным VACUUM и раздувает таблицы.
- pg_dump не переносит глобальные объекты - роли, права на уровне кластера и табличные пространства остаются за бортом, их забирает только pg_dumpall.
- Параллельный pg_restore (-j) ускоряет, но требует формата custom или directory; на plain SQL-дампе флаг -j молча игнорируется.
- Забыли -X у pg_basebackup или не настроили архив - в копии не хватит WAL, и кластер не стартует.
- recovery_target_time без таймзоны трактуется в локали сервера - легко промахнуться на пару часов и недо- или перевосстановить.
- Версии не совпадают: физическую копию с PG 15 нельзя поднять на PG 16. Логический дамп между версиями переносится, обратное - нет.
- Создайте тестовую базу: createdb lab42 и наполните таблицу заметным набором строк через generate_series.
- Сделайте custom-дамп: pg_dump -d lab42 -F c -f lab42.dump.
- Уроните таблицу (DROP TABLE) и восстановите её из дампа в новую базу через pg_restore -j 2.
- Включите archive_mode и archive_command, перезапустите сервер, сделайте pg_basebackup в отдельный каталог.
- Запишите точное время (SELECT now()), затем вставьте мусорные строки и снова запомните время.
- Поднимите вторую копию кластера с recovery_target_time перед вставкой мусора и убедитесь, что мусорных строк нет.
- Сравните число строк до и после - так вы проверили, что PITR реально работает.
- Чем логический бэкап pg_dump принципиально отличается от физического pg_basebackup и когда какой выбирать?
- Почему archive_command обязан возвращать ошибку при неудаче и что случится, если этого не сделать?
- Какие объекты сохраняет pg_dumpall, но не сохраняет pg_dump?
- Что должно быть в наличии, кроме базовой копии, чтобы выполнить восстановление на момент времени?
- Зачем нужен файл recovery.signal и какие параметры управляют целевой точкой PITR?
- Почему бэкап без регулярной проверки восстановления нельзя считать рабочим?
