PostgreSQL: pg_dump и pg_restore — бэкап и восстановление

Полное руководство по бэкапу и восстановлению PostgreSQL одной базы данных. Команды pg_dump, psql, pg_restore: форматы дампов, восстановление, проверка целостности и типичные ошибки.

Важно: pg_dump не блокирует других пользователей; дамп согласован на момент старта. Для одной БД — pg_dump; для всего кластера и ролей — pg_dumpall.

Форматы дампов

Формат	Команда	Восстановление	Преимущества
Plain SQL	`pg_dump db > backup.sql`	`psql -f backup.sql`	Универсальный, читаемый
Custom	`pg_dump -Fc db > backup.dump`	`pg_restore -d db backup.dump`	Сжатие, выборочное восстановление
Directory	`pg_dump -Fd db -f backup_dir`	`pg_restore -d db -j 4 backup_dir`	Параллельное восстановление
Tar	`pg_dump -Ft db > backup.tar`	`pg_restore -d db backup.tar`	Архив для переноса

1. Plain SQL (текстовый дамп)

Универсальный формат: обычный SQL. Восстанавливается через psql. Подходит для переноса между разными версиями PostgreSQL и для ручного просмотра.

# Дамп одной БД в файл
pg_dump -U postgres dbname > backup.sql

# Только схема (без данных)
pg_dump -U postgres -s dbname > schema.sql

# Только данные (без схемы)
pg_dump -U postgres -a dbname > data.sql

# Сжатие на лету
pg_dump -U postgres dbname | gzip > backup.sql.gz

# Дамп с указанием хоста и порта
pg_dump -h localhost -p 5432 -U postgres dbname > backup.sql

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

# Создай БД, если её нет
createdb -U postgres mydb

# Восстановление из plain SQL
psql -U postgres -d mydb -f backup.sql

# Из сжатого
gunzip -c backup.sql.gz | psql -U postgres -d mydb

# С игнорированием ошибок (для повторного импорта)
psql -U postgres -d mydb -f backup.sql --set ON_ERROR_STOP=off

2. Custom format (для pg_restore)

Формат -Fc — один файл .dump, можно выборочно восстанавливать объекты и использовать параллельность. Рекомендуется для регулярных бэкапов.

# Создание дампа
pg_dump -U postgres -Fc dbname > backup.dump

# Восстановление в существующую БД
pg_restore -U postgres -d mydb backup.dump

# Очистить БД перед восстановлением (⚠️ удалит существующие объекты)
pg_restore -U postgres -d mydb --clean --if-exists backup.dump

# Только схема
pg_restore -U postgres -s -d mydb backup.dump

# Только данные
pg_restore -U postgres -a -d mydb backup.dump

# Восстановление конкретной таблицы
pg_restore -U postgres -d mydb -t users backup.dump

# Список объектов в дампе
pg_restore -l backup.dump

3. Directory format (параллельное восстановление)

# Создание дампа в каталог
pg_dump -U postgres -Fd dbname -f backup_dir

# Восстановление с параллельными задачами
pg_restore -U postgres -d mydb -j 4 backup_dir

# Проверка содержимого
ls -la backup_dir/

4. Cron: автоматический бэкап

# /etc/cron.daily/pg-backup
#!/bin/bash

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/postgresql"
DB_NAME="myapp"
PG_USER="postgres"

# Создать директорию
mkdir -p $BACKUP_DIR

# Дамп в custom формате
pg_dump -U $PG_USER -Fc $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# Удалить дампы старше 14 дней
find $BACKUP_DIR -name "${DB_NAME}_*.dump" -mtime +14 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.dump"

Настройка crontab:

# Ежедневно в 03:00
0 3 * * * /etc/cron.daily/pg-backup

5. Docker: бэкап из контейнера

# Дамп из контейнера
docker exec -t postgres_container pg_dump -U postgres mydb > backup.sql

# Дамп в custom формате
docker exec -t postgres_container pg_dump -U postgres -Fc mydb > backup.dump

# Восстановление в контейнер
cat backup.sql | docker exec -i postgres_container psql -U postgres -d mydb

# Копирование дампа из контейнера
docker cp postgres_container:/backup/backup.dump ./backup.dump

Проверка целостности

1. Проверка дампа после создания

# Plain SQL: проверить начало файла
head -20 backup.sql

# Должно быть:
-- PostgreSQL database dump
-- Dumped from database version 15.0
-- Dumped by pg_dump version 15.0

--
-- PostgreSQL database dump complete
--

# Custom format: список объектов
pg_restore -l backup.dump | head -30

# Проверка размера файла
ls -lh backup.dump
du -sh backup_dir/

2. Тестовое восстановление

# Создать тестовую БД
createdb -U postgres mydb_test

# Восстановить дамп
pg_restore -U postgres -d mydb_test backup.dump

# Проверить количество записей
psql -U postgres -d mydb_test -c "SELECT COUNT(*) FROM users;"
psql -U postgres -d mydb_test -c "\dt"

# Удалить тестовую БД
dropdb -U postgres mydb_test

3. Сверка до и после

# До бэкапа: сохранить количество записей
psql -U postgres -d mydb -c "SELECT COUNT(*) FROM users;"

# После восстановления: сверить
psql -U postgres -d mydb -c "SELECT COUNT(*) FROM users;"

Типичные ошибки

❌ Восстановление в БД с существующими объектами без —clean — ошибки «already exists»
❌ Неверный формат — plain SQL через pg_restore или custom через psql -f
❌ Нет прав на БД — pg_dump требует прав на чтение, pg_restore на создание
❌ Путь к дампу и место на диске — проверьте df -h перед большим дампом
❌ Путаница с owner — при restore может потребоваться --no-owner и --no-privileges

Best practices

✅ Регулярные бэкапы — ежедневно через cron
✅ Тестовое восстановление — раз в месяц проверяйте дамп
✅ Хранение нескольких копий — минимум 3 последних дампа
✅ Проверка размера — резкое изменение размера = возможная проблема
✅ Бэкап в другое место — копируйте дамп на другой сервер/NAS

Где применять

Production: ежедневный custom format + хранение 14 дней
Migration: plain SQL для переноса между версиями PostgreSQL
Dev/Local: дампы для синхронизации между разработчиками
Docker: docker exec для бэкапа из контейнера

Связанные сниппеты:

Документация:

Как использовать