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

Резервное копирование — критически важная задача. PostgreSQL предоставляет несколько инструментов: pg_dump для логических бэкапов и pg_basebackup для физических.

pg_dump: логический бэкап

# Базовый бэкап базы данных
pg_dump -h localhost -U postgres -d myapp > myapp_backup.sql

# С паролем через переменную окружения (не в командной строке!)
PGPASSWORD=mypassword pg_dump -h localhost -U postgres -d myapp > backup.sql

# Через .pgpass файл (рекомендуется)
# ~/.pgpass: hostname:port:database:username:password
echo "localhost:5432:myapp:postgres:mypassword" >> ~/.pgpass
chmod 600 ~/.pgpass

Форматы бэкапа

Plain SQL (текст)

pg_dump -Fp myapp > myapp.sql  # или без -F, по умолчанию
# Размер: большой, можно читать текстом
# Восстановление: psql

Custom format (рекомендуется)

pg_dump -Fc myapp > myapp.dump
# Сжатый, быстрое восстановление, поддерживает параллельность
# Восстановление: pg_restore

Directory format (параллельный)

pg_dump -Fd myapp -f /backup/myapp_dir/ -j 4  # 4 потока
# Каждая таблица — отдельный файл, можно восстанавливать параллельно

Gzip сжатие

pg_dump myapp | gzip > myapp_$(date +%Y%m%d).sql.gz
# Восстановление:
gunzip -c myapp_20260315.sql.gz | psql myapp

Опции pg_dump

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

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

# Конкретные таблицы
pg_dump -t users -t orders myapp > users_orders.sql

# Исключить таблицы
pg_dump -T logs -T events myapp > myapp_no_logs.sql

# С прогрессом
pg_dump -Fc -v myapp > myapp.dump 2>&1

# Сжатие (0-9, по умолчанию 6)
pg_dump -Fc -Z 9 myapp > myapp_max_compressed.dump

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

Из Plain SQL

# Создать базу
createdb -h localhost -U postgres myapp_restored

# Восстановить
psql -h localhost -U postgres -d myapp_restored < myapp.sql

# Или с прогрессом
pv myapp.sql | psql -h localhost -U postgres -d myapp_restored

Из Custom format (pg_restore)

# Восстановить всю базу
pg_restore -h localhost -U postgres -d myapp_restored myapp.dump

# Параллельное восстановление (намного быстрее!)
pg_restore -h localhost -U postgres -d myapp_restored -j 4 myapp.dump

# Только конкретные таблицы
pg_restore -h localhost -U postgres -d myapp_restored -t users -t orders myapp.dump

# Только схема
pg_restore --schema-only -d myapp_restored myapp.dump

# Список объектов в бэкапе
pg_restore -l myapp.dump | head -50

pg_dumpall: бэкап всего кластера

# Бэкап всех баз данных + роли + tablespaces
pg_dumpall -h localhost -U postgres > all_databases.sql

# Только глобальные объекты (роли, tablespaces)
pg_dumpall -g > globals.sql

# Восстановление
psql -h localhost -U postgres < all_databases.sql

Автоматизация через cron

#!/bin/bash
# /usr/local/bin/pg_backup.sh

BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="myapp"
RETAIN_DAYS=7

mkdir -p "$BACKUP_DIR"

# Создать бэкап
pg_dump -Fc -Z 6 "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# Проверить успешность
if [ $? -eq 0 ]; then
    echo "Backup successful: ${DB_NAME}_${DATE}.dump"
else
    echo "Backup FAILED!" | mail -s "PostgreSQL Backup Failed" admin@example.com
    exit 1
fi

# Удалить старые бэкапы
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETAIN_DAYS -delete

echo "Backup size: $(du -sh $BACKUP_DIR/${DB_NAME}_${DATE}.dump | cut -f1)"

# Добавить в cron (каждый день в 2:00)
crontab -e
0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

pg_basebackup: физический бэкап

# Полный физический бэкап (для Point-in-Time Recovery)
pg_basebackup \
  -h localhost \
  -U replicator \
  -D /backup/basebackup_$(date +%Y%m%d) \
  -Ft \          # tar формат
  -z \           # gzip
  -P \           # прогресс
  -Xs            # включить WAL

# Восстановление из базового бэкапа
tar xzf base.tar.gz -C /var/lib/postgresql/data
# + настроить recovery.conf для PITR

Point-in-Time Recovery (PITR)

# postgresql.conf — включить архивирование WAL
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'
# или в S3: aws s3 cp %p s3://my-bucket/wal/%f

# Восстановление на конкретный момент времени

# 1. Остановить PostgreSQL, очистить data directory
# 2. Восстановить базовый бэкап
tar xzf base.tar.gz -C /var/lib/postgresql/data

# 3. Создать файл recovery
cat > /var/lib/postgresql/data/postgresql.auto.conf << EOF
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2026-03-15 14:30:00'
recovery_target_action = 'promote'
EOF

# Создать файл-маркер
touch /var/lib/postgresql/data/recovery.signal

# 4. Запустить PostgreSQL — он восстановится до указанного момента
systemctl start postgresql

Docker: бэкап контейнера

# Бэкап из Docker-контейнера
docker exec postgres_container pg_dump -U postgres myapp > backup.sql

# С кастомным форматом
docker exec postgres_container pg_dump -Fc -U postgres myapp | gzip > backup.dump.gz

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

# Или через docker-compose
docker compose exec -T postgres pg_dump -U postgres myapp > backup.sql

Проверка бэкапа

# Всегда проверяйте бэкап! Нечитаемый бэкап = нет бэкапа

# Тест восстановления в отдельную БД
createdb myapp_test
pg_restore -d myapp_test myapp.dump

# Сравнить количество строк
psql -d myapp -c "SELECT COUNT(*) FROM users;"
psql -d myapp_test -c "SELECT COUNT(*) FROM users;"

# Удалить тестовую БД
dropdb myapp_test

Итог

Правило 3-2-1: 3 копии данных, 2 разных носителя, 1 за пределами офиса/дата-центра. Автоматизируйте бэкапы и регулярно проверяйте восстановление.