Postgres Pro: настройка и резервное копирование с pg_dump

Postgres Pro — промышленная версия PostgreSQL с расширенной производительностью и надёжностью. Для регулярного резервного копирования в большинстве случаев используется встроенный инструмент pg_dump.

1. Начальная настройка Postgres Pro

После установки Postgres Pro необходимо правильно настроить:

postgresql.conf

listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 2GB
work_mem = 64MB
maintenance_work_mem = 256MB
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = 1000

Совет: значения памяти (например, shared_buffers, work_mem) должны подбираться в зависимости от объёма ОЗУ и характера нагрузки (OLTP/OLAP).

pg_hba.conf

# Разрешить подключения по паролю с локальной сети
host all all 192.168.0.0/24 md5

Создание пользователя для бэкапов

CREATE ROLE backup_user WITH LOGIN PASSWORD 'secure_pass';

GRANT CONNECT ON DATABASE mydb TO backup_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

2. pg_dump: базовый инструмент резервного копирования

pg_dump — утилита командной строки для создания резервной копии одной базы данных.

Синтаксис:

pg_dump -U username -h host -p port dbname > dump.sql

Поддерживаемые форматы:

Формат	Ключ	Назначение
plain text	-F p	SQL-скрипт
custom	-F c	Сжатый архив для pg_restore
directory	-F d	Каталог с файлами (удобно для параллельной загрузки)
tar	-F t	Архив в формате TAR

3. Примеры использования pg_dump

Полный дамп одной базы:

pg_dump -U postgres -d mydb -F c -f /backup/mydb.backup

Резервная копия определённой схемы:

pg_dump -U postgres -n analytics -F c -f /backup/analytics.backup

Только структура без данных:

pg_dump -U postgres -s -F p -f structure.sql mydb

Только данные без структуры:

pg_dump -U postgres -a -F c -f data.backup mydb

Использование с фильтрацией таблиц:

pg_dump -t sales -t orders -F c -f part.backup mydb

4. Восстановление из резервной копии

Формат custom (-F c) — через pg_restore:

pg_restore -U postgres -d mydb_restored -C /backup/mydb.backup

-C — создать базу перед восстановлением
-c — удалить объекты перед созданием (если нужно)

Формат directory:

pg_restore -U postgres -d mydb_restored -j 4 -Fd /backup/mydir/

Формат plain text:

psql -U postgres -d mydb < dump.sql

5. Автоматизация резервного копирования

Пример bash-скрипта:

#!/bin/bash

DATE=$(date +%F)
BACKUP_DIR="/var/backups/postgres"
DB_NAME="mydb"
USER="postgres"

pg_dump -U $USER -F c -f $BACKUP_DIR/${DB_NAME}_$DATE.backup $DB_NAME
find $BACKUP_DIR -name "${DB_NAME}_*.backup" -mtime +7 -delete

Что делает скрипт:

сохраняет дамп с датой;
удаляет старые копии старше 7 дней.

Можно добавить в cron:

0 2 * * * /usr/local/bin/pg_backup.sh

6. Рекомендации по безопасности

Никогда не храните пароли в скриптах — используйте .pgpass
Убедитесь, что только нужные пользователи имеют доступ к бэкапам
Шифруйте резервные копии, если они хранятся вне защищённого сервера

Пример .pgpass:

localhost:5432:*:postgres:secret_password

Права файла должны быть chmod 600 ~/.pgpass

7. Особенности в Postgres Pro

Совместим с pg_dump PostgreSQL
Можно использовать в связке с pg_probackup для создания точных инкрементальных копий на реплике
При использовании репликации и логической публикации нужно согласовывать snapshot для консистентности (--snapshot)

Заключение

pg_dump — простой и надёжный инструмент для резервного копирования в Postgres Pro. Он подходит для:

создания дампов перед обновлениями;
регулярных ночных бэкапов;
миграции между серверами;
восстановления отдельных схем или таблиц.

Как организовать полную схему бэкапа и восстановления в Postgres Pro

Общий подход

Резервное копирование — это не просто «сделать дамп». Это регламент, автоматизация и проверка:

Как, когда и где создаются копии?
Кто за них отвечает?
Как быстро можно восстановиться?
Как понять, что копия рабочая?

Этапы организации надёжной схемы бэкапа

1. Классификация систем по критичности

Уровень	Пример	Подход к резервному копированию
Высокий	ERP, DWH, 1С, банковские БД	Инкрементальные и полные копии, репликация, PITR
Средний	CRM, аналитика, API	Ежедневный `pg_dump`, периодическая проверка
Низкий	dev-серверы, sandbox	Недельный `pg_dump`, реже `pg_basebackup`

2. Инструменты резервного копирования

Инструмент	Подходит для	Формат	Поддержка инкремента
`pg_dump`	Малые/средние базы, миграции	SQL / Custom	×
`pg_basebackup`	Физическое копирование	Директория	×
`pg_probackup`	Большие базы, прод-среда	Custom с метаданными	✓ (DELTA, PTRACK)

3. Стратегия резервного копирования

Пример на `pg_dump`:

Каждый день в 03:00 — pg_dump -Fc полной базы
Хранение 7 копий
Проверка логов (exit code, размер файла)

Пример на `pg_probackup`:

Пн: FULL
Вт–Сб: DELTA / PTRACK
Вс: VALIDATE + DELETE EXPIRED

4. Структура хранения резервных копий

/backups/
├── mydb/
│   ├── full/
│   ├── delta/
│   ├── logs/
│   └── verify/

Лучше хранить копии на другом сервере или в облаке (S3/MinIO)

5. Регулярное восстановление

Выберите базу и дату
Разверните dev-кластер
Выполните pg_restore или pg_probackup restore
Сравните контрольные суммы (md5, rowcount, audit)

Когда перейти с `pg_dump` на `pg_probackup`

Оставайтесь на `pg_dump`, если:

размер базы < 30 ГБ;
достаточно резервной копии раз в сутки;
не требуется восстановление до точки во времени (PITR);
важно быстро развернуть копию на другом сервере.

Пора переходить на `pg_probackup`, если:

Признак	Почему `pg_dump` не справляется
База > 50–100 ГБ	`pg_dump` занимает часы, замедляет прод
Нужна инкрементальность (только изменения)	`pg_dump` всегда создаёт полный дамп
Нужно хранить 30+ дней архивов без лишнего места	`pg_probackup` хранит разницу, экономит пространство
Требуется резервное копирование с реплики	`pg_dump` не знает о WAL, возможна несогласованность
Нужна проверка целостности (`validate`)	`pg_dump` не даёт встроенного механизма
Требуется PITR (восстановление «на 14:03:20»)	Только `pg_probackup` и WAL позволяют это

Резюме: рекомендация по внедрению

Этап	Что использовать
Базовая защита	`pg_dump` с расписанием
Продакшн DWH/ERP/1С	`pg_probackup + cron`
BI и аналитика на реплике	`pg_probackup` на standby
Сценарий георезерва	`pg_probackup` + WAL archive
Проверка бэкапов	`pg_probackup validate`

Практика использования pg_dump в Postgres Pro: кейсы, ошибки, чек-лист

Кейс 1: Ежедневные ночные дампы в формате .backup

Ситуация:
Небольшая компания с одной БД объёмом ~20 ГБ делает дамп каждую ночь.
Используется cron-задача с pg_dump -Fc.

Проблема:
В один день размер дампа резко вырос до 60 ГБ, и скрипт завершился с ошибкой из-за переполнения диска.

Причина:
— Большая транзакция была открыта во время бэкапа, что затянуло весь WAL.
— Не было контроля объёма логов и места.

Решение:

Добавить проверку свободного места перед запуском
Ввести pg_stat_activity-проверку активных транзакций
Перевести пользователей на read-only режим на время бэкапа (если допустимо)

Кейс 2: Не запускается pg_dump по cron

Симптом:
Вручную pg_dump работает, по cron — нет.

Причина:
— Переменные окружения (PATH, PGPASSWORD) недоступны в cron
— pg_dump не находит бинарник или не может подключиться к БД

Решение:

Использовать файл ~/.pgpass с правами 600
Указывать полный путь к pg_dump в cron:

0 2 * * * /usr/pgpro/enterprise-14/bin/pg_dump -U backup_user -Fc -f /backup/mydb.backup mydb

Кейс 3: Бэкап весит 0 байт

Причина:

Ошибка подключения к БД (например, неверный порт или пользователь)
Скрипт не обрабатывает код возврата

Решение:

Проверка $? в скриптах или set -e
Проверка размера файла после дампа:

if [ ! -s "/backup/mydb.backup" ]; then

  echo "Backup failed or empty!"

fi

Кейс 4: Ошибка при восстановлении — конфликт имен или ключей

Причина:
pg_restore запускается в существующей базе без --clean

Решение:

Использовать:

pg_restore -C -d postgres mydb.backup

Или:

pg_restore -c -d mydb mydb.backup

Либо предварительно удалить БД вручную

Кейс 5: Проблема с дампом таблиц с внешними ключами

Симптом:
pg_restore не может восстановить таблицу, ссылающуюся на ещё не созданную таблицу.

Решение:

Добавить --section=pre-data --section=data --section=post-data
Или использовать --disable-triggers и восстановление в нужном порядке

Чек-лист: настройка надёжной схемы с pg_dump

1. Подготовка

Создан специальный пользователь backup_user с SELECT и CONNECT
Настроен файл .pgpass с правами 600 для беззвучного подключения
Установлен полный путь к pg_dump в скриптах
Выбран формат -Fc (рекомендуемый)
Путь сохранения имеет достаточно места (> x1.5 объёма базы)

2. Скрипт и логика

Скрипт использует переменные DATE и ротацию по дням
После бэкапа проверяется размер файла и код возврата
Старые копии удаляются по сроку хранения (например, -mtime +7)

3. Расписание и автоматизация

В crontab настроен запуск 1 раз в сутки (обычно в 02:00–04:00)
Лог бэкапа пишется в /var/log/pg_backup.log
При ошибке — отправка уведомления (email, Telegram, webhook)

4. Тест восстановления

Раз в месяц создаётся testdb и проверяется восстановление:

pg_restore -C -d postgres /backup/last.backup

Сравнение данных по контрольным точкам (row count, checksums)

Когда pg_dump — лучший выбор:

Условие	Подходит pg_dump
База данных до 50 ГБ	✓
Нужна гибкость восстановления (по таблице)	✓
Миграция между версиями Postgres	✓
Не требуется PITR или инкремент	✓
Минимальная инфраструктура	✓

Postgres Professional — это российская промышленная СУБД, созданная на базе открытого PostgreSQL, но значительно расширенная для корпоративного применения. В отличие от классического PostgreSQL, решения от Postgres Professional включают в себя поддержку российских ГОСТов и сертификацию ФСТЭК, повышенную надёжность, оптимизации под высоконагруженные системы (в том числе 1С и DWH), инструменты резервного копирования, мониторинга и отказоустойчивости. За платформой стоит команда ядра PostgreSQL в России, что гарантирует актуальность, стабильность и экспертную техническую поддержку 24/7.

Для компаний, которым важно не просто использовать PostgreSQL, а внедрить его на уровне корпоративных стандартов — с гарантией, сопровождением, документированными улучшениями и адаптацией под российское законодательство — Postgres Pro Enterprise становится логичным выбором. Это не просто бесплатная база данных, а полноценный продуктовый стек, совместимый с BI, аналитикой, ERP, 1С и другими системами, в том числе импортозамещёнными.

← Предыдущая статья

Postgres Pro на Windows: установка, настройка, особенности

Следующая статья →

Postgres Pro: обучение и сертификация