Бэкап PostgreSQL — pg_dump, pg_restore, автоматизация

pg_dump — стандартный инструмент резервного копирования PostgreSQL. Создаёт консистентный снимок базы без остановки сервера. Разбираем форматы дампов, восстановление через pg_restore и автоматизацию через cron.

pg_dump — основные команды

# Базовый дамп в SQL-файл:
pg_dump -U postgres mydb > mydb_backup.sql

# С указанием хоста:
pg_dump -U postgres -h localhost -p 5432 mydb > backup.sql

# Только структура (без данных):
pg_dump -U postgres --schema-only mydb > schema.sql

# Только данные (без структуры):
pg_dump -U postgres --data-only mydb > data.sql

# Дамп конкретной таблицы:
pg_dump -U postgres -t users mydb > users_backup.sql

# Дамп нескольких таблиц:
pg_dump -U postgres -t users -t orders mydb > partial_backup.sql

# Исключить таблицу из дампа:
pg_dump -U postgres --exclude-table=logs mydb > backup_no_logs.sql

# Дамп с timestamp в имени файла (для скриптов):
pg_dump -U postgres mydb > "mydb_$(date +%Y%m%d_%H%M%S).sql"

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

# Custom формат — рекомендуется для больших баз:
pg_dump -U postgres -F c -f mydb_backup.dump mydb
# Автоматически сжимает, поддерживает выборочное восстановление

# Directory формат — параллельное создание дампа:
pg_dump -U postgres -F d -j 4 -f mydb_backup_dir mydb
# -j 4 = использовать 4 параллельных процесса (быстрее на многоядерных CPU)

# Tar формат:
pg_dump -U postgres -F t -f mydb_backup.tar mydb

# Сжатие SQL дампа через gzip:
pg_dump -U postgres mydb | gzip > mydb_backup.sql.gz
# Распаковать и восстановить:
gunzip -c mydb_backup.sql.gz | psql -U postgres mydb

pg_restore — восстановление из дампа

# Восстановить из SQL файла (plain format):
psql -U postgres -d mydb < backup.sql
# Или создать новую базу и восстановить:
createdb -U postgres mydb_restored
psql -U postgres mydb_restored < backup.sql

# Восстановить из custom/tar/directory формата:
pg_restore -U postgres -d mydb backup.dump

# Создать базу автоматически при восстановлении:
pg_restore -U postgres -C -d postgres backup.dump
# -C создаст базу из дампа (имя берётся из дампа)

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

# Восстановить только структуру (без данных):
pg_restore -U postgres -d mydb --schema-only backup.dump

# Параллельное восстановление (directory формат):
pg_restore -U postgres -d mydb -j 4 mydb_backup_dir/

# Посмотреть содержимое дампа без восстановления:
pg_restore -l backup.dump

pg_dumpall — дамп всего кластера

# Дамп всех баз + ролей + tablespaces:
pg_dumpall -U postgres > full_cluster_backup.sql

# Только глобальные объекты (роли, tablespaces — без данных баз):
pg_dumpall -U postgres --globals-only > globals.sql
# Нужно для переноса пользователей на новый сервер

# Восстановить всё:
psql -U postgres < full_cluster_backup.sql

# Восстановить только роли (на новом сервере перед pg_restore баз):
psql -U postgres < globals.sql

Автоматизация бэкапов через cron

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

BACKUP_DIR="/var/backups/postgresql"
DB_NAME="mydb"
DB_USER="postgres"
KEEP_DAYS=7  # хранить 7 дней

# Создать директорию если не существует:
mkdir -p "$BACKUP_DIR"

# Имя файла с датой и временем:
FILENAME="$BACKUP_DIR/${DB_NAME}_$(date +%Y%m%d_%H%M%S).dump"

# Создать дамп:
pg_dump -U "$DB_USER" -F c -f "$FILENAME" "$DB_NAME"

if [ $? -eq 0 ]; then
  echo "$(date): Backup created: $FILENAME" >> /var/log/pg_backup.log
else
  echo "$(date): BACKUP FAILED!" >> /var/log/pg_backup.log
fi

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

# Сделать скрипт исполняемым:
chmod +x /usr/local/bin/pg_backup.sh

# Добавить в cron (редактировать от пользователя postgres):
crontab -u postgres -e

# Запускать каждый день в 3:00:
0 3 * * * /usr/local/bin/pg_backup.sh

# Создать .pgpass для беспарольного подключения:
echo "localhost:5432:mydb:postgres:пароль" >> ~/.pgpass
chmod 600 ~/.pgpass

Часто задаваемые вопросы

Какой формат лучше для pg_dump?

Для большинства задач — Custom (-F c): автоматически сжимает (уменьшает размер в 5-10 раз), поддерживает выборочное восстановление отдельных таблиц, быстрее восстанавливается чем SQL. Недостаток: нечитаемый бинарный формат, нельзя открыть в текстовом редакторе. SQL-формат (по умолчанию) — для небольших баз и когда нужна читаемость. Directory (-F d) с -j N — для очень больших баз: параллельное создание и восстановление значительно быстрее.

Как восстановить только одну таблицу из бэкапа PostgreSQL?

Только если дамп в custom или directory формате: pg_restore -U postgres -d mydb -t users backup.dump. SQL-формат не поддерживает выборочное восстановление — придётся восстанавливать всю базу или вручную найти нужные INSERT в файле. Посмотреть список объектов в дампе: pg_restore -l backup.dump | grep TABLE. Можно восстановить в отдельную временную базу и перенести нужные данные через INSERT INTO … SELECT.

Как проверить что бэкап PostgreSQL не повреждён?

Для SQL файлов — восстановить в тестовую базу: createdb test_restore && psql test_restore < backup.sql. Для custom формата: pg_restore --list backup.dump — если выводит список объектов без ошибок, архив не повреждён. Для полной проверки: создать временную базу, восстановить и запросить количество строк в ключевых таблицах. В продакшн-окружениях рекомендуется автоматически тестировать восстановление еженедельно.