Бэкап PostgreSQL: pg_dump по cron в S3 (Yandex, Selectel)

Если у вас одна-две базы PostgreSQL на VPS, надёжный бэкап собирается из стандартных инструментов: pg_dump, cron или systemd timer и S3-хранилище в другом дата-центре. Ниже — рабочий скрипт, который не оставляет «пустых» дампов при ошибке, грузит копии в Yandex Object Storage или Selectel и сам удаляет старые файлы. В конце — когда логического дампа становится мало и пора переходить на физические бэкапы.

Какой формат pg_dump выбрать

У pg_dump четыре формата, и от выбора зависит, чем вы будете восстанавливать базу.

| Формат | Флаг | Плюсы | Минусы | |---|---|---|---| | plain | -Fp (по умолчанию) | обычный SQL: читается глазами, сравнивается diff, восстанавливается psql | нельзя выборочно восстановить одну таблицу, нет параллельного восстановления | | custom | -Fc | сжат по умолчанию, pg_restore умеет восстанавливать отдельные таблицы и работать в несколько потоков (-j) | бинарный файл, нужен pg_restore той же или более новой версии | | directory | -Fd | единственный формат, где параллелен сам дамп (-j N) | каталог из множества файлов, его надо упаковывать перед отправкой | | tar | -Ft | один файл, читается pg_restore | без сжатия и без параллельности, на практике выбирают редко |

Практическое правило: для небольших и средних баз берите plain и сжимайте через gzip — такой дамп восстанавливается чем угодно и читается любым инструментом. Когда восстановление plain-дампа начинает занимать неприемлемо долго, переходите на -Fc (параллельное восстановление) или -Fd -j 4 (параллельный дамп). pg_restore читает только custom, directory и tar; plain-файл восстанавливается через psql -f.

Пароль: .pgpass вместо строки подключения

Пароль в URL (postgresql://user:secret@host/db) или в PGPASSWORD в теле скрипта попадает в историю shell, логи и иногда в вывод ps. Для бэкапов заведите отдельного пользователя и файл ~/.pgpass:

# формат: hostname:port:database:username:password
echo '127.0.0.1:5432:app:backup:очень-длинный-пароль' > ~/.pgpass
chmod 600 ~/.pgpass

Права 600 обязательны: если файл читается группой или всеми, libpq его проигнорирует и pg_dump попросит пароль. Если файл должен лежать в другом месте (например, скрипт запускается от сервисного пользователя), укажите путь через переменную PGPASSFILE. В скрипте добавьте --no-password, чтобы pg_dump при проблеме с паролем сразу завершался с ошибкой, а не висел в ожидании ввода.

Пользователю для бэкапа достаточно прав на чтение. В PostgreSQL 14+ для этого есть встроенная роль:

CREATE ROLE backup LOGIN PASSWORD 'очень-длинный-пароль';
GRANT pg_read_all_data TO backup;

Скрипт бэкапа с проверкой ошибок

Классическая ошибка — pg_dump app | gzip > app.sql.gz в cron без pipefail. Если pg_dump упадёт, код возврата конвейера возьмётся от gzip, который успешно сожмёт пустой поток. В итоге у вас неделями копятся файлы по 20 байт, а скрипт «работает».

#!/usr/bin/env bash
# /usr/local/bin/pg-backup.sh
set -euo pipefail

DB_NAME="app"
DB_HOST="127.0.0.1"
DB_USER="backup"
LOCAL_DIR="/var/backups/postgres"
BUCKET="s3://my-pg-backups"
ENDPOINT="https://storage.yandexcloud.net"
KEEP_DAYS=7

STAMP="$(date -u +%Y-%m-%dT%H%M%SZ)"
FILE="${LOCAL_DIR}/${DB_NAME}-$(hostname -s)-${STAMP}.sql.gz"
TMP="${FILE}.part"

mkdir -p "$LOCAL_DIR"
trap 'rm -f "$TMP"' EXIT

# pipefail: если pg_dump завершится с ошибкой, упадёт весь конвейер
pg_dump -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME" \
  --format=plain --no-password --lock-wait-timeout=60000 \
  | gzip -6 > "$TMP"

gzip -t "$TMP"          # архив читается целиком
mv "$TMP" "$FILE"       # «готовый» файл появляется только после успеха

aws s3 cp "$FILE" "${BUCKET}/postgres/${DB_NAME}/$(basename "$FILE")" \
  --endpoint-url "$ENDPOINT" --profile backup --only-show-errors

find "$LOCAL_DIR" -name "${DB_NAME}-*.sql.gz" -type f -mtime +"$KEEP_DAYS" -delete

Что здесь важно:

• Уникальные имена. Метка времени в UTC и имя хоста в ключе исключают перезапись: два запуска в один день или два сервера с одной базой не затрут друг друга.
• Временный файл .part. Пока дамп пишется, он не выглядит готовым — ни ротация, ни загрузка не возьмут недописанный файл.
• --lock-wait-timeout=60000. pg_dump берёт на таблицы блокировку ACCESS SHARE: обычным чтениям и записям она не мешает, конфликтует только с DDL (ALTER TABLE, DROP). Если в момент старта идёт долгая миграция, дамп через минуту завершится ошибкой, а не встанет в очередь за ней. Флага --no-lock у pg_dump нет.
• Согласованность. pg_dump работает в одном MVCC-снимке: дамп отражает состояние базы на момент старта, даже если в это время идут записи.

Загрузка в Yandex Object Storage и Selectel

Оба провайдера совместимы с S3 API, поэтому подходит стандартный AWS CLI. Создайте профиль с ключами доступа (в Yandex Cloud — статический ключ сервисного аккаунта, в Selectel — S3-ключи пользователя):

aws configure --profile backup
# AWS Access Key ID / Secret Access Key — ключи провайдера
# Default region name: ru-central1 (Yandex) или ru-1 (Selectel)

Эндпоинты:

# Yandex Object Storage
aws s3 cp app.sql.gz s3://my-pg-backups/postgres/app/ \
  --endpoint-url https://storage.yandexcloud.net --profile backup

# Selectel S3 (регион ru-1; для других регионов эндпоинт смотрите в панели)
aws s3 cp app.sql.gz s3://my-pg-backups/postgres/app/ \
  --endpoint-url https://s3.ru-1.storage.selcloud.ru --profile backup

Если новая версия AWS CLI v2 падает на загрузке с ошибкой контрольной суммы, проверьте документацию провайдера: часть S3-совместимых хранилищ требует добавить в ~/.aws/config для профиля строки request_checksum_calculation = when_required и response_checksum_validation = when_required.

Альтернатива — rclone. Конфиг ~/.config/rclone/rclone.conf:

[yos]
type = s3
provider = Other
access_key_id = YCAJE...
secret_access_key = YCM...
endpoint = https://storage.yandexcloud.net
region = ru-central1

[selectel]
type = s3
provider = Other
access_key_id = ...
secret_access_key = ...
endpoint = https://s3.ru-1.storage.selcloud.ru
region = ru-1

rclone copy "$FILE" yos:my-pg-backups/postgres/app/

Подробнее о том, чем отличаются облака Yandex Cloud, Selectel и Cloud.ru, — в обзоре российской облачной инфраструктуры.

Ротация: локально и в бакете

Локально старые файлы удаляет find -mtime +N -delete в конце скрипта. В бакете удобнее настроить правило жизненного цикла, чтобы хранилище само удаляло объекты старше N дней. Файл lifecycle.json:

{
  "Rules": [
    {
      "ID": "expire-postgres-dumps",
      "Status": "Enabled",
      "Filter": { "Prefix": "postgres/" },
      "Expiration": { "Days": 30 }
    }
  ]
}

aws s3api put-bucket-lifecycle-configuration \
  --bucket my-pg-backups \
  --lifecycle-configuration file://lifecycle.json \
  --endpoint-url https://storage.yandexcloud.net --profile backup

Поддержку lifecycle-правил и их ограничения уточните в документации вашего провайдера. С rclone то же самое делается командой rclone delete yos:my-pg-backups/postgres --min-age 30d, но её придётся запускать по расписанию самим.

Для ключей, которыми пишет сервер, по возможности выдайте права только на запись в бакет. Если злоумышленник получит доступ к серверу, он не сможет удалить архивные копии.

Расписание: cron с flock или systemd timer

Если дамп однажды затянется дольше суток, следующий запуск cron стартует поверх. flock -n не даст запустить второй экземпляр:

# crontab -e (от пользователя, у которого есть ~/.pgpass и профиль aws)
15 3 * * * flock -n /tmp/pg-backup.lock /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

На системах с systemd удобнее timer: логи попадают в journald, а Persistent=true запускает пропущенный бэкап после перезагрузки.

# /etc/systemd/system/pg-backup.service
[Unit]
Description=PostgreSQL logical backup
Wants=network-online.target
After=network-online.target

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

# /etc/systemd/system/pg-backup.timer
[Unit]
Description=Nightly PostgreSQL backup

[Timer]
OnCalendar=*-*-* 03:15:00
Persistent=true
RandomizedDelaySec=10min

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer
systemctl list-timers pg-backup.timer
journalctl -u pg-backup.service -n 50

Сервис типа oneshot не запустится повторно, пока работает предыдущий запуск, так что flock здесь не нужен. Общие принципы расписаний и хранения копий описаны в статье о настройке автоматических бэкапов.

Windows: pg_dump и планировщик заданий

На Windows файл паролей по умолчанию лежит в %APPDATA%\postgresql\pgpass.conf, но для задачи от имени SYSTEM надёжнее явно указать PGPASSFILE. Пишите дамп через --file, а не через перенаправление >: Windows PowerShell 5.1 перекодирует вывод в UTF-16, и дамп не восстановится.

# C:\Scripts\pg-backup.ps1
$ErrorActionPreference = 'Stop'
$env:PGPASSFILE = 'C:\Scripts\pgpass.conf'
$pgDump = 'C:\Program Files\PostgreSQL\17\bin\pg_dump.exe'
$dir    = 'D:\Backups\postgres'
$stamp  = (Get-Date).ToUniversalTime().ToString('yyyy-MM-ddTHHmmssZ')
$out    = Join-Path $dir "app-$stamp.sql"

& $pgDump -h 127.0.0.1 -U backup -d app --format=plain --no-password --file $out
if ($LASTEXITCODE -ne 0) { throw "pg_dump завершился с кодом $LASTEXITCODE" }

Get-ChildItem $dir -Filter 'app-*.sql' |
  Where-Object LastWriteTime -lt (Get-Date).AddDays(-7) |
  Remove-Item

$action    = New-ScheduledTaskAction -Execute 'powershell.exe' `
  -Argument '-NoProfile -ExecutionPolicy Bypass -File C:\Scripts\pg-backup.ps1'
$trigger   = New-ScheduledTaskTrigger -Daily -At 3:15
$principal = New-ScheduledTaskPrincipal -UserId 'SYSTEM' -LogonType ServiceAccount
Register-ScheduledTask -TaskName 'pg-backup' -Action $action -Trigger $trigger -Principal $principal

Закройте доступ к pgpass.conf через свойства файла: читать его должны только SYSTEM и администраторы.

Роли и версии: что pg_dump не сохраняет

pg_dump выгружает одну базу. Роли, их пароли и членство в группах хранятся на уровне кластера и в дамп не попадают. Без них восстановление на новый сервер упадёт на GRANT ... TO app_user. Выгружайте их отдельно:

pg_dumpall -h 127.0.0.1 -U postgres --globals-only | gzip > globals-$(date -u +%F).sql.gz

Для выгрузки ролей с паролями нужен суперпользователь (пароли читаются из pg_authid). Без него можно выгрузить роли без паролей, добавив --no-role-passwords. Поэтому эту команду удобно запускать отдельно от основного дампа.

Версии: pg_dump должен быть той же мажорной версии, что и сервер, или новее. Старый клиент откажется работать с новым сервером (server version mismatch). На Ubuntu клиент из стандартного репозитория часто отстаёт от сервера из репозитория PGDG — ставьте postgresql-client-17 той же версии, что и сервер. Обратная ситуация тоже не идеальна: дамп, снятый новым pg_dump, может не загрузиться в более старый сервер.

Восстановление и проверка

createdb -h 127.0.0.1 -U postgres app_restore_test
gunzip -c app-2026-09-11T001500Z.sql.gz \
  | psql -h 127.0.0.1 -U postgres -d app_restore_test -v ON_ERROR_STOP=1 --single-transaction

ON_ERROR_STOP=1 останавливает загрузку на первой ошибке, --single-transaction откатывает всё целиком, чтобы не осталась половина базы. Для custom-формата: pg_restore -d app_restore_test -j 4 app.dump. Подробно все варианты — в статье как восстановить базу из бэкапа, а сценарий регулярной проверки (тестовое восстановление, сверка числа строк) — в материале как проверить, что бэкап рабочий.

Когда pg_dump уже мало

Логический дамп хорош, пока база восстанавливается за приемлемое время, а потеря данных за сутки (между ночными бэкапами) вас устраивает. Если нет:

• pg_basebackup снимает физическую копию всего кластера — быстрее на больших объёмах, но восстанавливается только на ту же мажорную версию.
• WAL-архивирование (archive_mode + archive_command) вместе с базовой копией даёт восстановление на любой момент времени (PITR): можно откатиться к состоянию за минуту до DELETE без WHERE.
• pgBackRest и WAL-G автоматизируют это: инкрементальные копии, параллельность, загрузка в S3, проверки.

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

Как это сделать в dbsend

dbsend не подключается к вашему серверу и не запускает pg_dump — дамп по-прежнему делает ваш скрипт. Сервис принимает plain-дампы (--format=plain) и хранит их как версии. Для custom-, directory- и tar-форматов разбора нет: такие файлы можно сохранить только как generic_file, с размером и контрольной суммой.

Добавьте в скрипт после pg_dump загрузку несжатого .sql (CLI сжимает файл при передаче сам):

npm install --global @dbsend/sdk
export DBSEND_API_KEY='sqv_pk_…'

pg_dump -h 127.0.0.1 -U backup -d app --format=plain --no-password --file /var/backups/postgres/app.sql
dbsend backup /var/backups/postgres/app.sql -d "$DBSEND_DATABASE_ID" -e postgresql_dump -l "nightly-$(date +%F)"
dbsend log "$DBSEND_DATABASE_ID"

Что вы получаете: каждый дамп становится версией; если файл побайтно совпадает с предыдущим, новая версия не создаётся. Для каждой версии видны схема, таблицы и число строк, а между любыми двумя версиями доступен diff: изменения схемы, таблиц, количества строк и размера. Если бэкап не пришёл по расписанию, приходит уведомление в Telegram, на почту или в webhook. Данные хранятся в S3 в России с шифрованием AES-256-GCM. Diff и уведомления доступны на платных тарифах — подробности на странице тарифов. Команды CLI описаны в документации, а возможности для Postgres — на странице бэкапов PostgreSQL.

Если базы крутятся в контейнерах, посмотрите статью о бэкапе базы в Docker и Coolify. Для MySQL похожая схема описана в материале про автоматизацию mysqldump.

Частые вопросы

Блокирует ли pg_dump работу приложения?

Нет, обычные SELECT, INSERT, UPDATE и DELETE продолжают работать. pg_dump берёт блокировки ACCESS SHARE, которые конфликтуют только с DDL. Миграция с ALTER TABLE будет ждать окончания дампа, поэтому не ставьте бэкап и деплой на одно время. Нагрузка на диск и процессор при этом есть, поэтому запускайте дамп в часы с наименьшим трафиком.

Почему мой дамп весит 20 байт?

Почти всегда это pg_dump | gzip без set -o pipefail: pg_dump упал (пароль, права, версия), а gzip сжал пустой поток и вернул успех. Включите pipefail, проверяйте архив gzip -t и смотрите лог задания.

Какой формат лучше хранить в S3 — plain или custom?

Для небольших баз — plain + gzip: его можно открыть, сравнить и восстановить psql на любой машине. Custom (-Fc) выигрывает, когда нужно восстановить отдельную таблицу или ускорить восстановление в несколько потоков. Можно хранить оба, если позволяет место.

Нужен ли pg_dumpall вместо pg_dump?

Полный pg_dumpall выгружает все базы одним plain-файлом и не поддерживает custom-формат и параллельность. Обычно удобнее pg_dump для каждой базы плюс pg_dumpall --globals-only для ролей.

Как часто делать бэкап PostgreSQL?

Столько, сколько данных вы готовы потерять. Ночной дамп означает до суток потерь. Если это неприемлемо, добавляйте дампы чаще или переходите на WAL-архивирование с PITR через pgBackRest или WAL-G.