Резервное копирование и восстановление баз данных PostgreSQL: полное руководство для пользователей AlexHost

Почему стратегия резервного копирования PostgreSQL важнее, чем вы думаете

Потеря данных — это не гипотетический риск, а операционная неизбежность, с которой рано или поздно столкнется каждый администратор баз данных. Отказы оборудования, случайные удаления, повреждённые транзакции и атаки программ-вымогателей могут вывести производственную среду из строя за считанные секунды. Для пользователей PostgreSQL наличие надёжной, протестированной и автоматизированной стратегии резервного копирования — это разница между незначительным инцидентом и катастрофическим отказом в работе.

Выделенные серверы AlexHost обеспечивают идеальную основу для размещения и защиты баз данных PostgreSQL. Благодаря хранилищу NVMe SSD корпоративного уровня с исключительной пропускной способностью ввода-вывода, полному доступу root для полного контроля конфигурации и встроенной защите от DDoS, AlexHost предоставляет производительность инфраструктуры и уровень безопасности, которые требуют серьёзные рабочие нагрузки баз данных.

Независимо от того, работаете ли вы с высоконагруженной платформой электронной коммерции, приложением SaaS, установкой WordPress с поддержкой реляционной базы данных или пользовательской корпоративной системой, это руководство проведёт вас через все основные методы резервного копирования и восстановления PostgreSQL — от простых SQL-дампов до продвинутого восстановления в определённый момент времени (PITR) — всё оптимизировано для производственных сред.

Содержание

1. Понимание вариантов резервного копирования PostgreSQL
2. Предварительные требования и привилегии
3. Метод 1 — SQL-дамп с pg_dump
4. Метод 2 — Резервное копирование всех баз данных с pg_dumpall
5. Метод 3 — Резервные копии в пользовательском формате для больших баз данных
6. Восстановление из SQL-дампов
7. Восстановление из дампов в пользовательском формате
8. Метод 4 — Непрерывное архивирование и восстановление в определённый момент времени (PITR)
9. Автоматизация резервного копирования с помощью Cron
10. Защита и хранение резервных копий вне сервера
11. Краткое резюме лучших практик

1. Понимание вариантов резервного копирования PostgreSQL

PostgreSQL поставляется с несколькими зрелыми, хорошо документированными механизмами резервного копирования. Выбор правильного зависит от размера вашей базы данных, целевого времени восстановления (RTO), целевой точки восстановления (RPO) и допустимой сложности операций.

Понимание этих компромиссов перед началом работы критически важно. Большинство производственных сред выигрывают от комбинирования как минимум двух подходов — например, ежедневные дампы в пользовательском формате наряду с непрерывным архивированием WAL для детальной возможности восстановления.

2. Предварительные требования и привилегии

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

Привилегии пользователя:

• Вы должны быть суперпользователем PostgreSQL или владельцем целевой базы данных для выполнения полного резервного копирования.
• Для pg_dumpall требуются привилегии суперпользователя.

Проверьте версию PostgreSQL:

psql --version

Проверьте доступное дисковое пространство перед резервным копированием:

df -h /var/lib/postgresql/

Убедитесь, что место назначения резервной копии имеет достаточно свободного места — минимум в 1,5 раза больше размера резервируемой базы данных, чтобы учесть временные файлы и накладные расходы на сжатие.

Подключитесь к серверу через SSH:

ssh root@your-server-ip

Если вы используете план VPS Hosting, у вас будет полный доступ SSH и возможность устанавливать, настраивать и управлять PostgreSQL без ограничений.

3. Метод 1 — SQL-дамп с pg_dump

Утилита pg_dump — это наиболее часто используемый инструмент резервного копирования PostgreSQL. Она создаёт согласованный снимок одной базы данных, даже если база данных активно используется. Результат — это простой текстовый SQL-скрипт, который можно просмотреть, отредактировать и воспроизвести на любой совместимой установке PostgreSQL.

Шаг 1: Откройте терминал и получите доступ к серверу

ssh root@your-alexhost-server-ip

Шаг 2: Выполните команду pg_dump

pg_dump -U username -W -F p database_name > /backups/backup_file.sql

Разбор параметров:

Практический пример:

pg_dump -U postgres -W -F p my_production_db > /backups/my_production_db_$(date +%Y%m%d_%H%M%S).sql

> Совет профессионала: Добавление временной метки с помощью $(date +%Y%m%d_%H%M%S) к имени файла резервной копии гарантирует, что вы никогда случайно не перезапишете предыдущую резервную копию и создадите естественный хронологический архив.

Шаг 3: Проверьте файл резервной копии

ls -lh /backups/
head -50 /backups/my_production_db_*.sql

Файл должен начинаться с комментариев заголовка PostgreSQL и SET утверждений, подтверждая создание действительного дампа.

4. Метод 2 — Резервное копирование всех баз данных с pg_dumpall

Когда вам нужно создать резервную копию каждой базы данных в экземпляре PostgreSQL — включая глобальные объекты, такие как роли и табличные пространства — pg_dumpall является правильным инструментом.

pg_dumpall -U postgres -W > /backups/all_databases_$(date +%Y%m%d).sql

Эта команда экспортирует:

• Все базы данных
• Все роли (пользователи и группы)
• Все табличные пространства
• Всю глобальную конфигурацию

Важно: Файл вывода из pg_dumpall может быть очень большим на занятых серверах. Убедитесь, что раздел резервной копии имеет достаточно места, и рассмотрите возможность немедленного сжатия вывода:

pg_dumpall -U postgres | gzip > /backups/all_databases_$(date +%Y%m%d).sql.gz

5. Метод 3 — Резервные копии в пользовательском формате для больших баз данных

Для производственных баз данных, превышающих несколько гигабайт, пользовательский формат (-F c) настоятельно рекомендуется вместо простых SQL-дампов. Резервные копии в пользовательском формате:

• Сжаты по умолчанию — значительно снижая требования к хранилищу
• Быстрее восстанавливаются — поддерживая параллельные операции восстановления с флагом -j
• Выборочно восстанавливаются — позволяя восстановить отдельные таблицы или схемы

Создание резервной копии в пользовательском формате

pg_dump -U postgres -W -F c my_production_db > /backups/my_production_db_$(date +%Y%m%d).dump

Создание сжатой резервной копии в формате каталога (поддерживает параллелизм)

pg_dump -U postgres -W -F d -j 4 -f /backups/my_production_db_dir my_production_db

Этот подход резко сокращает время резервного копирования на многоядерных серверах, что делает его идеальным для высокопроизводительных выделенных серверных сред, доступных в AlexHost.

6. Восстановление из SQL-дампов

Восстановление одной базы данных из простого SQL-дампа

Сначала убедитесь, что целевая база данных существует. Если её нет, создайте её:

psql -U postgres -c "CREATE DATABASE my_restored_db;"

Затем восстановите:

psql -U postgres -d my_restored_db -f /backups/my_production_db_backup.sql

Разбор параметров:

Мониторинг прогресса восстановления

Для больших SQL-файлов вы можете отслеживать прогресс с помощью pv:

pv /backups/my_production_db_backup.sql | psql -U postgres -d my_restored_db

7. Восстановление из дампов в пользовательском формате

Дампы в пользовательском формате требуют утилиты pg_restore вместо psql.

Базовое восстановление

pg_restore -U postgres -d my_restored_db /backups/my_production_db.dump

Восстановление и автоматическое создание базы данных

Используйте флаг -C для указания pg_restore создать базу данных перед её заполнением:

pg_restore -U postgres -C -d postgres /backups/my_production_db.dump

Параллельное восстановление для более быстрого восстановления

pg_restore -U postgres -d my_restored_db -j 4 /backups/my_production_db_dir/

Использование -j 4 с дампом в формате каталога может сократить время восстановления на 75% на четырёхядерном сервере — значительное преимущество при минимизации простоев во время восстановления после сбоя.

Восстановление только определённой таблицы

pg_restore -U postgres -d my_restored_db -t orders /backups/my_production_db.dump

Эта детальная возможность — одно из ключевых преимуществ пользовательского формата перед простыми SQL-дампами.

8. Метод 4 — Непрерывное архивирование и восстановление в определённый момент времени (PITR)

PITR — это золотой стандарт для критичных для бизнеса развёртываний PostgreSQL. Это позволяет восстановить вашу базу данных в любой конкретный момент времени — не только последнюю резервную копию — путём воспроизведения сегментов журнала упреждающей записи (WAL) поверх базовой резервной копии. Это необходимо для сценариев, когда вам нужно восстановиться после логической ошибки (такой как случайный DROP TABLE), которая произошла в известный момент времени.

Шаг 1: Включите архивирование WAL в postgresql.conf

Найдите и отредактируйте файл конфигурации PostgreSQL:

nano /etc/postgresql/15/main/postgresql.conf

Добавьте или измените следующие директивы:

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Объяснение параметров:

Создайте каталог архива и установите правильные разрешения:

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

Перезагрузите PostgreSQL для применения изменений:

systemctl restart postgresql

Шаг 2: Создайте базовую резервную копию с pg_basebackup

pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P -Xs

Шаг 3: Восстановление в определённый момент времени

Для восстановления из базовой резервной копии и архивов WAL:

1. Остановите PostgreSQL:

systemctl stop postgresql

1. Очистите существующий каталог данных:

rm -rf /var/lib/postgresql/15/main/*

1. Извлеките базовую резервную копию:

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

1. Создайте recovery.conf (PostgreSQL 11 и ранее) или настройте postgresql.conf и создайте файл recovery.signal (PostgreSQL 12+):

# For PostgreSQL 12+
touch /var/lib/postgresql/15/main/recovery.signal

Добавьте в postgresql.conf:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-01-15 14:30:00'
recovery_target_action = 'promote'

1. Установите