Резервное копирование PostgreSQL силами pg_dump и pg_basebackup со скриптами ротации копий и уведомлениями о сбоях

Потеря базы PostgreSQL почти никогда не история про саму СУБД. Это человек, запустивший UPDATE без WHERE, диск, отказавший под утро, или релиз приложения, который откатили позже, чем заметили проблему. Спасает только копия, сделанная до аварии, лежащая не рядом с данными и проверенная восстановлением. Стандартный арсенал умещается в две утилиты, которые ставятся вместе с сервером: pg_dump снимает логический дамп отдельной базы, pg_basebackup делает физическую копию всего кластера.

Само наличие копий ещё не система: без ротации они съедают диск за пару недель, без уведомлений тихо ломаются в ночь на пятницу, без контрольного восстановления дарят ложное чувство защищённости. Ниже собрана рабочая схема целиком: подготовка роли, два скрипта, ротация по возрастам, письма о каждом запуске и проверка того, что из архива вырастает живая база.

Чем логический дамп отличается от физической копии кластера

pg_dump подключается обычным клиентом и собирает содержимое базы в один файл: либо читаемый SQL-текст, либо внутренний "custom" формат со сжатием и оглавлением. Утилита читает данные через снимок транзакций, поэтому запись не блокируется, а дамп остаётся согласованным. Логический дамп переносится на сервер другой версии или платформы, из него можно вытащить одну таблицу. Плата за гибкость - время: загрузка больших баз обратно идёт часами.

pg_basebackup работает иначе: подключается по репликационному протоколу и переносит файлы кластера как есть, включая все базы, роли, табличные пространства и настройки. На выходе не текст, а готовый каталог с данными, из которого стартует сервер. Такая копия - отправная точка для отката на произвольный момент времени, потому что поверх неё накатываются журналы WAL. Физическую копию нельзя перенести на другой мажорный релиз, зато возврат гигабайт на место занимает минуты.

Практичный вывод прост. Для малых и средних баз хватает связки "дамп каждую ночь, базовая копия раз в неделю", для крупных кластеров центр тяжести смещается к pg_basebackup плюс архивация WAL. Ориентир по версиям: мажорные релизы выходят примерно раз в год, минорные - не реже раза в квартал, девятнадцатая ветка сейчас в бете, а старейшая из поддерживаемых, четырнадцатая, перестаёт получать исправления 12 ноября 2026 года.

Подготовка сервера и служебной роли для запуска резервного копирования

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

CREATE ROLE pgbackup WITH LOGIN REPLICATION PASSWORD 'S0me-Long-Passphrase';
GRANT CONNECT ON DATABASE app TO pgbackup;
GRANT pg_read_all_data TO pgbackup;

Роль pg_read_all_data появилась в 14-й ветке, на более старых серверах вместо неё выдают SELECT на таблицы всех схем. Репликационное подключение разрешается отдельной строкой с типом replication в конфиге доступа.

# pg_hba.conf
host replication pgbackup 127.0.0.1/32 scram-sha-256

Пароль не хранят в скрипте, а кладут в ~/.pgpass со строкой вида "хост:порт:база:пользователь:пароль" и правами 600: клиентские утилиты подхватывают его молча.

# ~/.pgpass
127.0.0.1:5432:*:pgbackup:S0me-Long-Passphrase

Для pg_basebackup сервер должен уметь отдавать WAL: wal_level в значении replica идёт по умолчанию, а слотов в max_wal_senders лучше оставить не меньше трёх, что проверяется одним запросом.

psql -U postgres -Atc "show wal_level; show max_wal_senders;"

Под копии создаются два каталога с правами 700 и владельцем postgres: /var/backups/postgres/dump и /var/backups/postgres/base, от его же имени работают скрипты.

Скрипт логического дампа с параллельным сжатием и метками даты

Логика ночного скрипта: выгрузить все рабочие базы в формате custom, отдельно сохранить глобальные объекты, подчистить старые файлы, записать итог в журнал.

#!/bin/bash
set -euo pipefail

BASE=/var/backups/postgres/dump
HOST=127.0.0.1
PORT=5432
USER=pgbackup
STAMP=$(date +%F_%H%M)
KEEP_DAYS=14
LOG=/var/log/pg-dump.log

exec >>"$LOG" 2>&1

DBS=$(psql -h "$HOST" -p "$PORT" -U "$USER" -At -c "select datname from pg_database where datallowconn and not datistemplate")

for DB in $DBS; do
  pg_dump -h "$HOST" -p "$PORT" -U "$USER" -Fc -Z6 -d "$DB" -f "$BASE/$DB-$STAMP.dump"
done

pg_dumpall -h "$HOST" -p "$PORT" -U "$USER" --globals-only -f "$BASE/globals-$STAMP.sql"

find "$BASE" -name '*.dump' -mtime +"$KEEP_DAYS" -delete
find "$BASE" -name 'globals-*.sql' -mtime +"$KEEP_DAYS" -delete

echo "$(date +%F_%T) dump ok: $(du -sm "$BASE" | awk '{print $1}') MiB"

Формат custom сжимается на лету и восстанавливается многопоточно, ключ -Z6 задаёт уровень сжатия от 0 до 9, дублировать его сжатием gzip поверх смысла нет. Ключ --globals-only (синоним -g) утилиты pg_dumpall выгружает роли и табличные пространства: забыть про него - классическая ошибка, после которой базы восстановлены, а паролей и прав нет. Фильтр datallowconn отсекает шаблоны. Для тяжёлых баз есть формат каталога с параллельной выгрузкой: "pg_dump -Fd -j4 -d app -f $BASE/app" пишет дамп четырьмя потоками и на многоядерном сервере сокращает время в разы.

Итоговая строка с du -sm не украшение: размер уходит в письмо и журнал, а копия, внезапно похудевшая втрое, сигнализирует о пустеющей базе или сорвавшемся ключе.

Физическая копия кластера через pg_basebackup с потоковой передачей WAL

Второй скрипт снимает копию кластера целиком с потоковой передачей журналов через ключ -X stream, такая копия самодостаточна и не требует докачивать архив. Слот удерживает WAL на случай обрыва, а ключ -R создаёт в каталоге файл standby.signal и дописывает строку подключения в postgresql.auto.conf, так что восстановленный каталог превращается в запасной сервер почти без ручной работы.

Слот basebackup создаётся вручную один раз командой "select pg_create_physical_replication_slot('basebackup')", дальше скрипт только ссылается на него ключом --slot: повторное создание слота заканчивается ошибкой.

#!/bin/bash
set -euo pipefail

BASE=/var/backups/postgres/base
STAMP=$(date +%F_%H%M)
DIR="$BASE/cluster-$STAMP"
SLOT=basebackup
LOG=/var/log/pg-basebackup.log

mkdir -p "$DIR"
exec >>"$LOG" 2>&1

pg_basebackup -h 127.0.0.1 -p 5432 -U pgbackup -D "$DIR" -Fp -X stream --checkpoint=fast --slot "$SLOT" --max-rate=32M -l "nightly-$STAMP" -R

ls -1dt "$BASE"/cluster-* | tail -n +4 | xargs -r rm -rf

echo "$(date +%F_%T) basebackup ok: $(du -sm "$DIR" | awk '{print $1}') MiB"

Режим контрольной точки задаётся ключом --checkpoint: значение spread идёт по умолчанию и размазывает нагрузку, значение fast завершает копирование быстрее ценой короткого всплеска записи. Ограничитель --max-rate бережёт производительность рабочего сервера. Слоты коварны: пока копия не снята, сервер держит WAL и не даёт его удалять, застрявший слот способен забить раздел. Страховка - параметр max_slot_wal_keep_size, ограничивающий удерживаемый хвост журнала.

В 17-й ветке и новее утилита научилась добавочным копиям: ключ --incremental принимает манифест опорной копии, сервер сверяет его со своим и отдаёт только изменённые файлы, что сокращает трафик в разы. Целостность снятого каталога проверяет утилита pg_verifybackup, сверяющая файлы с манифестом копии.

Автоматическая ротация копий и контроль свободного места на диске

Экономика хранения решает не меньше самого копирования. Прикидка на салфетке: база на 40 ГБ в custom-формате сжимается до 6-8 ГБ, базовая копия занимает полные 40 ГБ, и суточные дампы двухнедельной глубины плюс недельные копии за месяц съедают порядка 260-300 ГБ.

Классическая ротация по возрастам держит три уровня хранения:

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

Реализуется это правилами удаления с масками имён: скрипт дампа уже чистит по -mtime, для трёхуровневой схемы достаточно разнести маски и сроки.

find "$BASE" -name 'daily-*.dump' -mtime +7 -delete
find "$BASE" -name 'weekly-*.dump' -mtime +60 -delete
find "$BASE" -name 'monthly-*.dump' -mtime +400 -delete

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

FREE=$(df -Pm "$BASE" | awk 'NR==2{print $4}')
if [ "$FREE" -lt 20480 ]; then
  echo "pg_backup: less than 20 GiB free ($FREE MiB)" >&2
  exit 3
fi

Копия на той же машине - это половина копии. Ночной перенос на второй хост закрывает сценарий с отказом диска целиком.

rsync -az --delete /var/backups/postgres/ Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.:/srv/pg/

Уведомления о результатах копирования через почту и вебхук

Мониторить надо успех, а не факт запуска. Письмо "задача выполнена" без проверки кода возврата успокаивает лучше, чем работает. Фундамент - режим set -euo pipefail и перехват ошибок, а поверх него два канала: почта для человека и вебхук для системы учёта событий.

notify() {
  curl -sS --url "smtp://mail.example.com:587" --ssl-reqd --mail-from Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript. --mail-rcpt Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript. --user Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.:app-password' -T - <<EOF
From: Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.
To: Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.
Subject: postgres backup on $(hostname)

$1
EOF
}

trap 'notify "pg_dump FAILED, details in $LOG"' ERR
notify "pg_dump ok, $STAMP, size $SIZE MiB"

Для систем мониторинга удобнее короткий вебхук.

SIZE=$(du -sm "$BASE" | awk '{print $1}')
curl -fsS -X POST "https://hooks.example.com/postgres-backup" --data-urlencode "host=$(hostname)" --data-urlencode "stamp=$STAMP" --data-urlencode "size=$SIZE"

Запуск по расписанию логично отдать systemd-таймеру: он размазывает время старта, доигрывает пропущенные ночью запуски и шлёт уведомление при падении юнита.

# /etc/systemd/system/pg-dump.service
[Unit]
Description=Nightly PostgreSQL dumps
OnFailure=backup-notify@%n.service

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/bin/pg-dump-nightly.sh

# /etc/systemd/system/pg-dump.timer
[Timer]
OnCalendar=*-*-* 02:30:00
RandomizedDelaySec=20m
Persistent=true

[Install]
WantedBy=timers.target

Persistent=true гарантирует запуск после простоя, а OnFailure срабатывает только при ненулевом коде возврата, поэтому set -e в скрипте не просто украшение.

Проверка восстановления и типичные ошибки администраторов

Копия без репетиции - талисман, а не страховка. Быстрая проверка читается одной командой: оглавление показывает, что файл не битый.

pg_restore --list app-2026-10-09_0230.dump | head -n 20

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

initdb -D /tmp/check_cluster
echo "port = 5556" >> /tmp/check_cluster/postgresql.conf
pg_ctl -D /tmp/check_cluster -l /tmp/check.log start
createdb -p 5556 app_restore
pg_restore -p 5556 -j4 -d app_restore app-2026-10-09_0230.dump
psql -p 5556 -Atc "select count(*) from orders"

Базовая копия проверяется ещё проще: содержимое каталога переносится на место опустевшего PGDATA, и сервер стартует. Для отката на момент времени в конфиг копии добавляется команда подачи архива и целевая точка, а файл recovery.signal переводит сервер в режим восстановления.

# на живом сервере
archive_command = 'test ! -f /var/backups/postgres/wal/%f && cp %p /var/backups/postgres/wal/%f'
# в каталоге восстановленной копии
restore_command = 'cp /var/backups/postgres/wal/%f %p'
recovery_target_time = '2026-10-09 03:00:00+03'

Типичные провалы повторяются годами: копии лежат на том же диске, что и данные, и уезжают вместе с ним; роли сохранены, а пароли остались за кадром; слот без ограничителя остановил удаление WAL и заполнил раздел; мониторинг следит за фактом запуска, а не успеха; восстановление проверяется утилитой версии постарше, и чужие форматы не читаются. Всё лечится скриптом выше и одной репетицией в месяц.

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