Резервное копирование и восстановление PostgreSQL

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Архитектору Инженеру

Классификация методов

Критерий	Логическое копирование	Физическое копирование
Уровень	Объекты БД (таблицы, функции)	Файлы кластера (страницы данных)
Утилиты	pg_dump, pg_dumpall	pg_basebackup, копия каталога данных
Восстановление	Совместимые мажорные версии	Та же мажорная версия и ОС
Размер	Меньше (без «пустых» страниц)	Больше
Гранулярность	Отдельная БД или объекты	Весь кластер
Скорость restore	Медленнее (выполнение SQL)	Быстрее (копирование файлов)

Данные нельзя «переписать заново» — без проверенных резервных копий потеря необратима. Резервная копия ценна только после тестового восстановления.

---

Логическое резервное копирование

pg_dump

Форматы: plain (SQL-скрипт), custom (-Fc, сжатие), directory (-Fd, параллельность), tar.

# Бинарный сжатый дамп одной базы
pg_dump -Fc -f backup.dump mydb

# Параллельный дамп в каталог (4 потока)
pg_dump -Fd -j 4 -f backup_dir mydb

# Только одна таблица
pg_dump -t public.products -Fc -f products.dump mydb

Просмотр содержимого без восстановления:

pg_restore -l backup.dump

Восстановление

createdb mydb_restored
pg_restore -d mydb_restored backup.dump

После логического восстановления обновите статистику планировщика (pg_statistic в дамп не входит):

ANALYZE;

pg_dumpall

Резервная копия всего кластера, включая роли и глобальные настройки:

pg_dumpall -f cluster.sql

---

Физическое копирование и PITR

pg_basebackup

Горячая копия каталога данных. Требует настройки WAL:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

pg_basebackup -D /backup/base -Fp -Xs -P

Восстановление на точку во времени (PITR)

1. Восстановить физическую копию каталога данных.
2. Создать postgresql.auto.conf / сигнал восстановления с параметрами:

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2024-02-12 14:30:00'
recovery_target_action = 'promote'

3. Запустить кластер в режиме восстановления до указанного момента.

---

Стратегия 3-2-1 и проверка

• 3 копии данных (рабочая + две резервные);
• 2 типа носителей;
• 1 копия вне площадки.

Проверка:

• логический дамп: pg_restore -l backup.dump;
• физическая копия: pg_controldata /path/to/data;
• регулярное тестовое восстановление в изолированной среде (не реже раза в квартал).

---

Ограничения и риски

Логическое:

• не переносит статистику — нужен ANALYZE;
• расширения из shared_preload_libraries ставятся вручную.

Физическое:

• нельзя восстановить одну базу отдельно от кластера;
• версия PostgreSQL и платформа должны совпадать.

Общее:

• непротестированная процедура восстановления;
• разрыв архива WAL;
• повреждение файлов при передаче без контрольных сумм.

---

Практический минимум

# Тестовая база и данные
createdb shop_backup_test
psql shop_backup_test -c "
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC
);
INSERT INTO products (name, price) VALUES ('Товар А', 100.00);
"

pg_dump -Fc -f shop_backup.dump shop_backup_test
createdb shop_restored
pg_restore -d shop_restored shop_backup.dump
psql shop_restored -c "SELECT * FROM products;"

---

Контрольные вопросы

1. Когда выбрать pg_dump, а когда pg_basebackup?
2. Почему после логического восстановления нужен ANALYZE?
3. Что такое PITR и зачем архивировать WAL?
4. Почему правило 3-2-1 не заменяет тестовое восстановление?

---

См. также

• Взаимодействие приложений с СУБД
• Шпаргалка по SQL — раздел резервного копирования
• Транзакции, изоляция и блокировки

---