Автоматические бэкапы PostgreSQL: настройка без ловушек

За час можно настроить ежедневный бэкап одной базы PostgreSQL 16 на Linux-сервере: дамп будет создаваться в отдельном каталоге, а старые копии — удаляться автоматически. Но расписание само по себе не спасает данные: сначала нужно убедиться, что архив создаётся без ошибок и его можно восстановить.

Что понадобится

Инструкция рассчитана на PostgreSQL 16 и систему с systemd, например Debian или Ubuntu. Понадобятся права администратора на сервере, имя базы, адрес PostgreSQL и роль, у которой достаточно прав на чтение данных для дампа. Установите клиентские утилиты PostgreSQL 16: в частности, pg_dump и pg_restore. Ниже для примера используются база appdb, пользователь app_backup и локальный адрес 127.0.0.1 — замените их на свои.

Термин. Дамп — логическая копия базы: набор данных и объектов, который PostgreSQL умеет восстановить. Это не копия всего сервера: список ролей и другие общие для кластера объекты таким дампом не сохраняются.

Настройка по шагам

1. Создайте отдельного системного пользователя для задания и каталог бэкапов. Это не роль PostgreSQL: пользователь запускает скрипт в Linux.

   sudo useradd --system --home-dir /var/lib/pg-backup --create-home --shell /usr/sbin/nologin backup
   sudo install -d -o backup -g backup -m 0700 /var/backups/postgresql

   Проверка: команда getent passwd backup показывает пользователя, а у каталога /var/backups/postgresql владелец backup и права доступа только для владельца.

2. Настройте пароль для подключения. Создайте файл паролей от имени администратора, откройте его редактором и добавьте строку. Замените примерные имя базы, пользователя и заглушку пароля на свои значения.

   sudo install -o backup -g backup -m 0600 /dev/null /var/lib/pg-backup/.pgpass
   sudoedit /var/lib/pg-backup/.pgpass

   Строка внутри файла:

   127.0.0.1:5432:appdb:app_backup:YOUR_DB_PASSWORD

   Проверьте, что в файле нет лишних пробелов, а права остались 0600: PostgreSQL отказывается использовать файл паролей, если доступ к нему открыт другим пользователям. Если пароль содержит двоеточие или обратную косую черту, эти символы нужно экранировать по правилам файла .pgpass. Проверка: sudo stat -c '%a %U' /var/lib/pg-backup/.pgpass выводит 600 backup.

3. Создайте скрипт бэкапа. Он вызывает pg_dump в пользовательском формате, сначала пишет временный файл и только после успешного завершения переименовывает его в готовый архив. Пароль в команду не передаётся.

   sudo tee /usr/local/sbin/pg-backup >/dev/null <<'EOF'
   #!/usr/bin/env bash
   set -Eeuo pipefail
   umask 077
   
   BACKUP_DIR="/var/backups/postgresql"
   DB_NAME="appdb"
   DB_HOST="127.0.0.1"
   DB_PORT="5432"
   DB_USER="app_backup"
   RETENTION_DAYS=14
   
   mkdir -p "$BACKUP_DIR"
   timestamp=$(date -u +%Y%m%dT%H%M%SZ)
   tmp="$BACKUP_DIR/.${DB_NAME}_${timestamp}.dump.tmp"
   final="$BACKUP_DIR/${DB_NAME}_${timestamp}.dump"
   trap 'rm -f "$tmp"' EXIT
   
   pg_dump \
     --host="$DB_HOST" \
     --port="$DB_PORT" \
     --username="$DB_USER" \
     --no-password \
     --format=custom \
     --file="$tmp" \
     "$DB_NAME"
   
   test -s "$tmp"
   mv -- "$tmp" "$final"
   find "$BACKUP_DIR" -type f -name "${DB_NAME}_*.dump" -mtime +13 -delete
   trap - EXIT
   EOF
   sudo chown root:root /usr/local/sbin/pg-backup
   sudo chmod 0755 /usr/local/sbin/pg-backup

   Укажите в начале скрипта те же параметры, что и в строке .pgpass. Проверка: sudo -u backup /usr/local/sbin/pg-backup завершается без ошибки, а в каталоге появляется непустой файл с расширением .dump. Удаление по возрасту использует интервалы в целых днях, поэтому фактический срок хранения будет приблизительным — около двух недель.

4. Добавьте systemd-службу. Она запускает скрипт от имени пользователя backup и не ждёт ввода пароля в терминале.

   sudo tee /etc/systemd/system/pg-backup.service >/dev/null <<'EOF'
   [Unit]
   Description=Backup PostgreSQL database
   
   [Service]
   Type=oneshot
   User=backup
   Group=backup
   ExecStart=/usr/local/sbin/pg-backup
   UMask=0077
   EOF

   Проверка: файл /etc/systemd/system/pg-backup.service содержит секции [Unit] и [Service], а имя скрипта в ExecStart совпадает с его путём.

5. Настройте ежедневный запуск. Таймер назначит его на 02:15 по местному времени сервера. Если сервер в это время был выключен, Persistent=true позволит выполнить пропущенный запуск после включения.

   sudo tee /etc/systemd/system/pg-backup.timer >/dev/null <<'EOF'
   [Unit]
   Description=Daily PostgreSQL backup
   
   [Timer]
   OnCalendar=*-*-* 02:15:00
   Persistent=true
   
   [Install]
   WantedBy=timers.target
   EOF
   sudo systemctl daemon-reload
   sudo systemctl enable --now pg-backup.timer

   Проверка: команда systemctl list-timers pg-backup.timer показывает следующий запуск. Чтобы проверить саму службу сразу, выполните sudo systemctl start pg-backup.service, а результат посмотрите командой sudo systemctl status pg-backup.service.

6. Проверьте не только создание, но и читаемость архива. Команда ниже показывает список объектов внутри выбранного дампа, не восстанавливая его:

   pg_restore --list /var/backups/postgresql/appdb_ГОДМЕСЯЦДЕНЬTЧАСМИНУТСЕКУНДZ.dump

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

Бэкап на том же сервере защищает от части ошибок, но не от потери самого сервера. Для аварийного восстановления копии нужны ещё где-то.

Если не получилось

• fe_sendauth: no password supplied — сервер не нашёл подходящую строку в .pgpass. Сверьте адрес, порт, имя базы и пользователя с параметрами скрипта; проверьте владельца файла и права 0600.

• FATAL: password authentication failed — пароль в файле не совпадает с паролем роли PostgreSQL или строка не подходит к подключению. Не добавляйте пароль в команду запуска: проверьте учётные данные и экранирование специальных символов в .pgpass.

• pg_dump: error: connection to server ... failed — PostgreSQL недоступен по заданным адресу и порту, либо клиент подключается не к тому экземпляру. Проверьте, на каком адресе слушает сервер, и согласуйте параметры скрипта с настройками PostgreSQL.

• Permission denied — пользователь backup не может прочитать файл паролей, записать в каталог бэкапов или выполнить скрипт. Проверьте владельца и права каждого из этих путей; не устраняйте ошибку выдачей прав на запись всем пользователям.

• Таймер виден, но архив не появился — расписание и успешная работа службы не одно и то же. Посмотрите журнал командой sudo journalctl -u pg-backup.service, исправьте первую ошибку и запустите службу вручную.

Что делать дальше

Перенесите готовые архивы на другой сервер или в отдельное хранилище с ограниченным доступом и предусмотрите защиту копий от случайного удаления. Продумайте отдельно сохранение ролей PostgreSQL: дамп одной базы не содержит их полного списка. Затем назначьте ответственного за регулярное тестовое восстановление и фиксируйте, сколько оно занимает. Бэкап, который никто не пробовал восстановить, остаётся предположением, а не проверенным планом восстановления.

Инструкция относится к PostgreSQL 16 на Linux с systemd. Для другой основной версии PostgreSQL сверяйте поведение и совместимость клиентских утилит с документацией именно этой версии. Отдельная тема — физические копии кластера и непрерывное архивирование WAL; здесь настроен логический дамп одной базы.