Резервное копирование PostgreSQL и автоматическая проверка целостности бэкапов

Проблема

База данных PostgreSQL это центральный элемент множества корпоративных систем: от 1С до веб-приложений. Администраторы часто ограничиваются созданием дампа с помощью pg_dump, складывая файл на локальный диск и надеясь, что копия «работает». Проблема в том, что резервная копия может быть повреждена, неполной или несовместимой с текущей версией сервера, и выясняется это только в момент восстановления, когда простой уже идёт на минуты и часы. Кроме того, pg_dump не всегда подходит для очень крупных баз: он создаёт логическую копию, которая может занять много времени, и не позволяет выполнять Point-in-Time Recovery (PITR). PostgreSQL резервное копирование должно включать как минимум два метода логический и физический и обязательно сопровождаться автоматизированной проверкой того, что из бэкапа действительно можно восстановить данные.

Решение

Настроим два взаимодополняющих механизма: регулярный логический дамп утилитой pg_dump для удобства переноса и анализа, а также физическое копирование с помощью pg_basebackup и архивирование WAL для непрерывного восстановления на любой момент времени. Затем напишем скрипт, который автоматически разворачивает свежий дамп в тестовую базу и проверяет целостность: наличие ключевых таблиц, количество строк, успешность pg_restore. Все инструменты входят в стандартную поставку PostgreSQL и описаны в официальной документации: Backup and Restore, pg_dump, Continuous Archiving and Point-in-Time Recovery (PITR). Дополнительно задействуем планировщик задач (cron) для автоматизации.

Пошаговая инструкция

Шаг 1. Настройка логического резервного копирования (pg_dump)

pg_dump создаёт копию отдельной базы данных в виде SQL-скрипта или бинарного архива.

1. Создайте директорию для хранения бэкапов:

bash

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql

2. Напишите скрипт /usr/local/bin/pg_backup.sh:

bash

#!/bin/bash
BACKUP_DIR="/var/backups/postgresql"
DB_NAME="mydb"
DATE=$(date +%Y%m%d_%H%M%S)
DUMP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# Создание резервной копии в формате custom (сжатый, гибкий)
pg_dump -U postgres -Fc -v -f "$DUMP_FILE" "$DB_NAME"

# Оставляем только последние 7 копий
find "$BACKUP_DIR" -name "${DB_NAME}_*.dump" -mtime +7 -delete

3. Сделайте скрипт исполняемым и протестируйте вручную:

bash

sudo chmod +x /usr/local/bin/pg_backup.sh
sudo -u postgres /usr/local/bin/pg_backup.sh

Убедитесь, что файл дампа создан и его размер соответствует ожидаемому.

4. Добавьте задачу в cron (например, ежедневно в 2:00 ночи):

bash

sudo crontab -e -u postgres
# Добавьте строку:
0 2 * * * /usr/local/bin/pg_backup.sh

Подробное описание опций pg_dump и формата custom см. в pg_dump documentation.

Шаг 2. Настройка физического резервного копирования (pg_basebackup) и архивирования WAL

Для больших баз и возможности PITR используем pg_basebackup в сочетании с архивацией WAL (Write-Ahead Logs).

1. Включите архивирование WAL. В файле postgresql.conf (обычно /etc/postgresql/16/main/postgresql.conf) установите:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/backups/postgresql/wal/%f && cp %p /var/backups/postgresql/wal/%f'

2. Создайте директорию для WAL и установите права:

bash

sudo mkdir -p /var/backups/postgresql/wal
sudo chown postgres:postgres /var/backups/postgresql/wal

3. Перезапустите PostgreSQL:

bash

sudo systemctl restart postgresql

4. Выполните базовый бэкап с помощью pg_basebackup:

bash

sudo -u postgres pg_basebackup -D /var/backups/postgresql/base_backup -Ft -z -P -X fetch

Ключи:

• -D  целевая директория;
• -Ft  tar-формат;
• -z  сжатие gzip;
• -P  отображение прогресса;
• -X fetch  включение WAL-файлов, необходимых для восстановления.

В результате в /var/backups/postgresql/base_backup появятся tar.gz архивы: base.tar.gz (данные) и pg_wal.tar.gz (логи).

5. Для автоматизации физического копирования также можно добавить скрипт в cron (например, еженедельно).

Информация о политике удержания WAL и настройке archive_cleanup_command доступна в разделе Continuous Archiving.

Шаг 3. Создание скрипта для автоматической проверки целостности бэкапов

Самый надёжный способ убедиться, что резервная копия работоспособна, — развернуть её в тестовую базу и выполнить несколько проверок.

1. Установите отдельный экземпляр PostgreSQL (можно на том же сервере, но на другом порту, или в Docker-контейнере) для тестовых восстановлений. Пример с Docker:

bash

sudo docker run -d --name pg_test -p 5433:5432 -e POSTGRES_PASSWORD=test123 postgres:16

2. Напишите скрипт /usr/local/bin/pg_verify_backup.sh:

bash

#!/bin/bash
BACKUP_DIR="/var/backups/postgresql"
TEST_DB="verify_db"
TEST_HOST="localhost"
TEST_PORT="5433"
TEST_USER="postgres"
PGPASSWORD="test123"

# Находим самый свежий дамп
LATEST_DUMP=$(ls -t $BACKUP_DIR/*.dump | head -1)
if [ -z "$LATEST_DUMP" ]; then
    echo "No dump found!"
    exit 1
fi

echo "Verifying dump: $LATEST_DUMP"

# Удаляем тестовую базу, если она существует
PGPASSWORD=$PGPASSWORD psql -h $TEST_HOST -p $TEST_PORT -U $TEST_USER -c "DROP DATABASE IF EXISTS $TEST_DB;" 2>/dev/null

# Создаём тестовую базу
PGPASSWORD=$PGPASSWORD createdb -h $TEST_HOST -p $TEST_PORT -U $TEST_USER $TEST_DB

# Восстанавливаем дамп в тестовую базу
PGPASSWORD=$PGPASSWORD pg_restore -h $TEST_HOST -p $TEST_PORT -U $TEST_USER -d $TEST_DB -v "$LATEST_DUMP"

# Проверяем наличие данных
ROW_COUNT=$(PGPASSWORD=$PGPASSWORD psql -h $TEST_HOST -p $TEST_PORT -U $TEST_USER -d $TEST_DB -t -c "SELECT count(*) FROM information_schema.tables WHERE table_schema='public';")
if [ "$ROW_COUNT" -gt 0 ]; then
    echo "SUCCESS: $LATEST_DUMP restored successfully. $ROW_COUNT tables found."
else
    echo "FAILURE: No tables found in restored database!"
    exit 2
fi

3. Сделайте скрипт исполняемым:

bash

sudo chmod +x /usr/local/bin/pg_verify_backup.sh

4. Запустите проверку вручную:

bash

sudo -u postgres /usr/local/bin/pg_verify_backup.sh

5. Добавьте задачу в cron для автоматической проверки (например, раз в день после создания дампа):

bash

0 4 * * * /usr/local/bin/pg_verify_backup.sh >> /var/log/pg_backup_verify.log 2>&1

При желании скрипт можно расширить отправкой уведомлений в Telegram или email при обнаружении FAILURE.

Шаг 4. Тестовое восстановление из физического бэкапа (PITR)

Для проверки физического бэкапа выполните следующие шаги (вручную или также автоматизируйте):

1. Остановите тестовый экземпляр PostgreSQL.
2. Очистите директорию данных и разархивируйте базовый бэкап:

bash

sudo rm -rf /var/lib/postgresql/16/test_data/*
sudo tar -xzf /var/backups/postgresql/base_backup/base.tar.gz -C /var/lib/postgresql/16/test_data

3. Настройте recovery.conf (для версий <12) или postgresql.auto.conf с параметрами восстановления, указав точку остановки или «последнее доступное состояние».
4. Запустите сервер; он автоматически применит WAL-файлы и перейдёт в режим готовности.

Подробнее: Point-in-Time Recovery.

Устранение распространённых проблем

Симптом	Вероятная причина	Решение
pg_dump завершается с ошибкой «out of shared memory»	Слишком большая база или низкий shared_buffers	Увеличьте shared_buffers в postgresql.conf, либо выполняйте pg_dump в непиковые часы с ограничением параллельных подключений.
pg_basebackup сообщает «could not connect to server»	Сервер не слушает внешние подключения или требуется пароль	В pg_hba.conf разрешите локальные подключения без пароля для пользователя postgres, либо используйте -U postgres -w.
При восстановлении дампа ошибка «schema already exists»	Попытка восстановить в непустую базу	Всегда создавайте новую базу перед восстановлением. Используйте опцию --clean при создании дампа, чтобы он удалял объекты перед созданием.
WAL-файлы накапливаются и заполняют диск	archive_command неверна или архивная папка недоступна	Проверьте доступность директории WAL и права postgres на запись. Настройте archive_cleanup_command для автоматического удаления обработанных сегментов.
Скрипт проверки выдаёт «FAILURE», хотя дамп корректен	Тестовая база не запущена или изменились параметры подключения	Проверьте, что тестовый экземпляр слушает нужный порт (ss -tlnp), а пароль экспортирован корректно. Добавьте в скрипт export PGPASSWORD.

Итог

PostgreSQL резервное копирование перестаёт быть «рутиной в одну команду» и превращается в полноценную стратегию, когда помимо регулярных дампов и непрерывного WAL-архивирования настроена автоматическая проверка целостности. Развёртывание свежего бэкапа в тестовую базу и подтверждение наличия данных единственный реальный способ гарантировать, что в час X вы сможете восстановить бизнес-критичную информацию за минуты, а не часы. Оба метода логический и физический решают разные задачи, и грамотный администратор использует их вместе, подкрепляя мониторингом и логированием каждого шага.