Как сделать бэкап и восстановление PostgreSQL: пошаговое руководство

Введение

Резервное копирование — критически важная задача для любой СУБД. PostgreSQL предлагает несколько методов: логическое копирование (pg_dump, pg_dumpall), физическое (pg_basebackup) и непрерывное архивирование WAL для восстановления на момент времени (PITR). В этом руководстве мы разберём, как правильно делать бэкапы и восстанавливать базы, чтобы избежать потери данных.

Требования и подготовка

• Установленный PostgreSQL (версии 10+).

• Доступ к серверу с правами суперпользователя или роли с правами на чтение всех баз.

• Достаточно места на диске для хранения дампов.

• Для физического бэкапа и PITR потребуется настроить архивирование WAL.

• Базовые знания командной строки и SQL.

Шаг 1: Логическое резервное копирование с pg_dump

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

Пример команды:

pg_dump -U postgres -Fc -f /backups/mydb.dump mydb

• -Fc — формат custom (сжатый, позволяет выборочное восстановление).

• -f — файл для сохранения.

• mydb — имя базы.

Для создания SQL-текстового дампа используйте -Fp. Для параллельного дампа больших баз добавьте -j N (количество потоков).

Совет: Для консистентности используйте --serializable-deferrable или делайте дамп в момент низкой нагрузки.

Шаг 2: Резервное копирование всех баз с pg_dumpall

pg_dumpall сохраняет все базы данных и глобальные объекты (роли, табличные пространства).

pg_dumpall -U postgres > /backups/all_databases.sql

Для сжатия можно сразу архивировать: pg_dumpall -U postgres | gzip > /backups/all_databases.sql.gz.

Важно: pg_dumpall не поддерживает параллельный дамп и может быть медленным на больших объёмах.

Шаг 3: Физическое резервное копирование с pg_basebackup

pg_basebackup создаёт побитовую копию всего кластера PostgreSQL. Это основа для потоковой репликации и PITR.

pg_basebackup -U replicator -D /backups/base -Ft -z -P -X stream

• -D — каталог для бэкапа.

• -Ft — формат tar.

• -z — сжатие gzip.

• -P — прогресс.

• -X stream — включает необходимые WAL-файлы в бэкап.

Для выполнения команды требуется роль с правами репликации или суперпользователь. Настройте pg_hba.conf для доступа.

Шаг 4: Настройка непрерывного архивирования WAL (PITR)

Чтобы иметь возможность восстановить базу на любой момент времени, нужно включить архивирование WAL.

1. В postgresql.conf установите:

   wal_level = replica
   archive_mode = on
   archive_command = 'cp %p /backups/wal/%f'

2. Перезапустите PostgreSQL. 3. Проверьте, что WAL-файлы копируются в /backups/wal.

Совет: Для надёжности используйте archive_command с проверкой целостности, например test ! -f /backups/wal/%f && cp %p /backups/wal/%f.

Шаг 5: Восстановление из логического дампа (pg_dump)

Для восстановления используйте pg_restore (для custom/tar) или psql (для SQL).

Из custom-дампа:

pg_restore -U postgres -d mydb_restored -C /backups/mydb.dump

• -C — создать базу, если её нет.

• -d — целевая база.

Из SQL-дампа:

psql -U postgres -d mydb_restored -f /backups/mydb.sql

Важно: Перед восстановлением убедитесь, что целевая база пуста или не существует.

Шаг 6: Восстановление из pg_dumpall

psql -U postgres -f /backups/all_databases.sql

Этот процесс пересоздаст все базы и роли. Будьте осторожны: если базы уже существуют, могут возникнуть конфликты. Рекомендуется восстанавливать на чистый кластер.

Шаг 7: Восстановление из физического бэкапа

1. Остановите PostgreSQL. 2. Очистите каталог данных (например, /var/lib/postgresql/14/main). 3. Распакуйте бэкап в каталог данных:

   tar -xzf /backups/base.tar.gz -C /var/lib/postgresql/14/main

4. Если использовался -X stream, WAL-файлы уже включены. Иначе скопируйте их из архива WAL. 5. Запустите PostgreSQL.

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

Шаг 8: Восстановление на момент времени (PITR)

1. Восстановите физический бэкап (шаг 7), но не запускайте PostgreSQL. 2. Создайте файл recovery.signal в каталоге данных (для PostgreSQL 12+). 3. В postgresql.conf укажите:

   restore_command = 'cp /backups/wal/%f %p'
   recovery_target_time = '2025-01-01 12:00:00'

4. Запустите PostgreSQL. Он применит WAL-файлы до указанного времени. 5. После завершения восстановления файл recovery.signal будет удалён, и сервер перейдёт в обычный режим.

Совет: Для точного восстановления используйте recovery_target_lsn или recovery_target_name.

Шаг 9: Автоматизация резервного копирования

Создайте скрипт и добавьте его в cron.

Пример скрипта бэкапа:

#!/bin/bash
BACKUP_DIR=/backups
DATE=$(date +%Y-%m-%d_%H-%M)
pg_dump -U postgres -Fc -f $BACKUP_DIR/mydb_$DATE.dump mydb
find $BACKUP_DIR -name '*.dump' -mtime +7 -delete

Добавьте в cron: 0 2 * * * /path/to/backup.sh.

Для физического бэкапа используйте pg_basebackup с ротацией.

Шаг 10: Проверка и тестирование бэкапов

Регулярно проверяйте целостность бэкапов:

• Для логических дампов: pg_restore --list /backups/mydb.dump.

• Для физических:尝试 восстановить на тестовом сервере.

Важно: Непроверенный бэкап — это не бэкап.

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

• Отсутствие проверки бэкапов — приводит к обнаружению проблем только при аварии.

• Игнорирование WAL — невозможность восстановления на момент времени.

• Недостаточно места — бэкапы могут занимать много места, особенно физические.

• Неправильные права доступа — ошибки при восстановлении.

• Восстановление поверх работающей базы — может привести к конфликтам и потере данных.

• Отсутствие шифрования — бэкапы могут содержать конфиденциальные данные.

FAQ

В: Как часто делать бэкапы? О: Зависит от критичности данных. Для продакшена — ежедневно, а для критичных систем — чаще, с использованием PITR.

В: Можно ли делать бэкап на лету? О: Да, pg_dump и pg_basebackup работают без остановки сервера.

В: Как восстановить только одну таблицу? О: Используйте pg_restore -t table_name для custom-дампа.

В: Что лучше: логический или физический бэкап? О: Логический удобен для переноса и выборочного восстановления, физический — для полного восстановления и PITR.

В: Как защитить бэкапы? О: Шифруйте их (например, GPG) и храните в защищённом месте.

Итог

Мы рассмотрели основные методы резервного копирования и восстановления PostgreSQL: от простого pg_dump до продвинутого PITR. Регулярно делайте бэкапы, проверяйте их и автоматизируйте процесс — это залог сохранности ваших данных.