Бэкапы баз данных (pg_dump / mysqldump)

Файл базы данных, скопированный простым файловым бэкапом, — консистентный слепок только при удачном стечении обстоятельств: в момент копирования СУБД вполне может писать данные, и результат окажется повреждён. Логический дамп через pg_dump/pg_dumpall (PostgreSQL) или mysqldump (MySQL/MariaDB) устроен иначе — он снимает консистентный снимок средствами самой СУБД, а на выходе получается обычный SQL-файл, который можно сжать, перенести и восстановить на любом сервере с той же или более новой версией СУБД. В этой статье — как снять такой дамп для PostgreSQL и MySQL/MariaDB, сжать его, восстановить, поставить на автомат через cron и увезти копию за пределы сервера.

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

• VPS mHost на Ubuntu 22.04/24.04, Debian 11/12 или AlmaLinux/Rocky Linux 8/9.
• Доступ по SSH под root или пользователем с sudo.
• Установленный и запущенный PostgreSQL и/или MySQL/MariaDB. Если СУБД ещё не установлена — см. статьи «PostgreSQL: установка и настройка» или «MySQL/MariaDB: установка и настройка»; эта статья — только про сами бэкапы.
• Свободное место на диске под дампы, в идеале на отдельном от каталога данных СУБД разделе. Если места не хватит посреди дампа, файл окажется обрезан и непригоден для восстановления — держите в поле зрения df -h (а если место всё же кончилось — см. «Диск заполнен»).

Часть 1. PostgreSQL: pg_dump и pg_dumpall

pg_dump снимает дамп одной базы; роли, табличные пространства и прочие настройки уровня кластера в него не попадают. Для дампа всего кластера целиком, включая роли, нужен pg_dumpall.

Шаг 1. Дамп одной базы (pg_dump)

Создайте каталог под дампы и снимите первый дамп, сразу сжав его gzip:

mkdir -p /var/backups/postgresql
sudo -u postgres pg_dump mydb | gzip > /var/backups/postgresql/mydb_$(date +%F).sql.gz

sudo -u postgres запускает pg_dump от системного пользователя postgres — на стандартной установке PostgreSQL на всех перечисленных дистрибутивах для подключения от него через локальный сокет настроена peer-аутентификация, и пароль не требуется. Без флага -F/--format pg_dump выводит обычный SQL-скрипт в stdout, который тут же уходит в gzip и оседает на диске уже сжатым.

Проверьте, что файл не пустой и распаковывается:

gzip -t /var/backups/postgresql/mydb_$(date +%F).sql.gz
ls -lh /var/backups/postgresql/

gzip -t проверяет целостность архива, не распаковывая его на диск.

Если баз несколько, узнайте их список и снимите дамп каждой в отдельный файл — этот же список пригодится в шаге 1 части 3 для скрипта автоматизации:

pg_databases=$(sudo -u postgres psql -Atqc "SELECT datname FROM pg_database WHERE datistemplate = false;")
for db in $pg_databases; do
  sudo -u postgres pg_dump "$db" | gzip > "/var/backups/postgresql/${db}_$(date +%F).sql.gz"
done

Шаг 2. Дамп всего кластера — роли и все базы (pg_dumpall)

pg_dumpall полезен, когда нужна не одна база, а полная копия всего сервера СУБД целиком — например, перед миграцией на новый VPS или переустановкой ОС. В отличие от pg_dump, он включает роли (пользователей), выданные им права и табличные пространства — то, что pg_dump сознательно не дампит.

Дамп всего кластера одним файлом:

sudo -u postgres pg_dumpall | gzip > /var/backups/postgresql/cluster_$(date +%F).sql.gz

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

sudo -u postgres pg_dumpall --globals-only | gzip > /var/backups/postgresql/globals_$(date +%F).sql.gz

Шаг 3. Восстановление PostgreSQL

Создайте пустую базу с нужным владельцем и восстановите в неё дамп:

sudo -u postgres createdb -O myuser mydb_restored
gunzip -c /var/backups/postgresql/mydb_2026-07-10.sql.gz | sudo -u postgres psql mydb_restored

pg_dump без флага -C/--create не кладёт в дамп саму команду CREATE DATABASE — он рассчитывает, что вы уже подключены к существующей (пусть и пустой) базе, поэтому createdb нужно выполнить первым отдельным шагом.

Дамп всего кластера (pg_dumpall) восстанавливается через psql напрямую, без createdb, — скрипт сам содержит все нужные CREATE ROLE/CREATE DATABASE:

gunzip -c /var/backups/postgresql/cluster_2026-07-10.sql.gz | sudo -u postgres psql postgres

Если снимали дамп в формате -F c, восстанавливайте через pg_restore, а не psql:

sudo -u postgres createdb -O myuser mydb_restored
sudo -u postgres pg_restore -d mydb_restored mydb.dump

Часть 2. MySQL и MariaDB: mysqldump

mysqldump работает одинаково в MySQL и MariaDB, включая современные версии MariaDB (10.11+/11.x), где основной командой стала mariadb-dump, — mysqldump там оставлена рабочим alias'ом для совместимости, и все команды и флаги ниже подходят без изменений.

В отличие от PostgreSQL, где для локального подключения от postgres действует peer-аутентификация без пароля, для MySQL/MariaDB надёжнее сразу завести отдельного пользователя специально для бэкапов — так автоматизация не будет зависеть от того, как именно на конкретном сервере настроен вход под root.

Шаг 1. Отдельная учётная запись для бэкапов

Подключитесь под администратором СУБД — например, sudo mysql (если root входит через сокет без пароля) или mysql -u root -p — и создайте пользователя только на чтение:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'CHANGE_ME_STRONG_PASSWORD';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, PROCESS, LOCK TABLES ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

SELECT покрывает сами данные — и его же, по документации MySQL, достаточно, чтобы mysqldump мог прочитать тела процедур и функций для флага --routines, даже если backup не является их автором. SHOW VIEW нужен для представлений, TRIGGER — для триггеров, EVENT — для событий планировщика. PROCESS требуется, пока не указан флаг --no-tablespaces (в командах ниже он не используется), а LOCK TABLES — если не используется --single-transaction; в примерах ниже он есть, так что по факту LOCK TABLES не обязателен, но пригодится, если когда-нибудь понадобится дампить таблицы не в InnoDB без этого флага.

Сохраните учётные данные в отдельный файл настроек клиента — это позволит запускать mysqldump без пароля прямо в команде (он был бы виден в выводе ps и истории команд) и без интерактивного запроса пароля из cron:

mkdir -p /etc/mysql
cat > /etc/mysql/backup.cnf <<'EOF'
[client]
user=backup
password=CHANGE_ME_STRONG_PASSWORD
EOF
chmod 600 /etc/mysql/backup.cnf

Проверьте подключение:

mysql --defaults-file=/etc/mysql/backup.cnf -e "SELECT 1;"

Шаг 2. Дамп базы (mysqldump)

Одна база, со сжатием на лету:

mkdir -p /var/backups/mysql
mysqldump --defaults-file=/etc/mysql/backup.cnf \
  --single-transaction --routines --triggers --events \
  mydb | gzip > /var/backups/mysql/mydb_$(date +%F).sql.gz

• --single-transaction — снимает консистентный снимок таблиц InnoDB одной транзакцией, не блокируя запись в базу другими клиентами. Для таблиц не в InnoDB (устаревший MyISAM) на время снятия дампа этот флаг консистентность не гарантирует.
• --routines, --triggers, --events — включить в дамп хранимые процедуры и функции, триггеры и события планировщика; без этих флагов mysqldump их пропускает.

Все базы разом, каждая — в отдельный файл (этот же список пригодится в шаге 1 части 3 для скрипта автоматизации):

mysql_databases=$(mysql --defaults-file=/etc/mysql/backup.cnf -N -e "SHOW DATABASES;" | grep -Ev "^(information_schema|performance_schema|mysql|sys)$")
for db in $mysql_databases; do
  mysqldump --defaults-file=/etc/mysql/backup.cnf \
    --single-transaction --routines --triggers --events \
    "$db" | gzip > "/var/backups/mysql/${db}_$(date +%F).sql.gz"
done

grep -Ev отсекает служебные базы MySQL/MariaDB — логическим дампом их бэкапить не нужно.

Шаг 3. Восстановление MySQL/MariaDB

Дамп одной базы (снятый без --databases/--all-databases) не содержит CREATE DATABASE — создайте базу заранее сами и восстанавливайте прямо в неё:

mysql --defaults-file=/etc/mysql/backup.cnf -e "CREATE DATABASE mydb_restored CHARACTER SET utf8mb4;"
gunzip -c /var/backups/mysql/mydb_2026-07-10.sql.gz | mysql --defaults-file=/etc/mysql/backup.cnf mydb_restored

Часть 3. Автоматизация и хранение бэкапов

Шаг 1. Ночной бэкап через cron

Соберите команды из шагов выше в один скрипт с логом и защитой от повторного запуска. Возьмите тот блок (PostgreSQL, MySQL/MariaDB или оба), который соответствует вашему серверу:

sudo nano /usr/local/bin/db-backup.sh

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR_PG="/var/backups/postgresql"
BACKUP_DIR_MYSQL="/var/backups/mysql"
MYSQL_DEFAULTS="/etc/mysql/backup.cnf"
KEEP_DAYS=7
DATE=$(date +%F)
LOG_FILE="/var/log/db-backup.log"
LOCK_FILE="/var/lock/db-backup.lock"

exec 9>"$LOCK_FILE"
if ! flock -n 9; then
  echo "$(date '+%F %T') Previous run still in progress, exiting" >> "$LOG_FILE"
  exit 1
fi

echo "$(date '+%F %T') Backup started" >> "$LOG_FILE"

mkdir -p "$BACKUP_DIR_PG" "$BACKUP_DIR_MYSQL"

# PostgreSQL: one file per database, skip this block if PostgreSQL isn't installed
if command -v pg_dump >/dev/null; then
  pg_databases=$(sudo -u postgres psql -Atqc "SELECT datname FROM pg_database WHERE datistemplate = false;")
  for db in $pg_databases; do
    sudo -u postgres pg_dump "$db" | gzip > "$BACKUP_DIR_PG/${db}_${DATE}.sql.gz"
  done
  sudo -u postgres pg_dumpall --globals-only | gzip > "$BACKUP_DIR_PG/globals_${DATE}.sql.gz"
  find "$BACKUP_DIR_PG" -name "*.sql.gz" -mtime "+${KEEP_DAYS}" -delete
fi

# MySQL/MariaDB: one file per database, skip this block if it isn't installed
if command -v mysqldump >/dev/null; then
  mysql_databases=$(mysql --defaults-file="$MYSQL_DEFAULTS" -N -e "SHOW DATABASES;" | grep -Ev "^(information_schema|performance_schema|mysql|sys)$")
  for db in $mysql_databases; do
    mysqldump --defaults-file="$MYSQL_DEFAULTS" --single-transaction --routines --triggers --events "$db" \
      | gzip > "$BACKUP_DIR_MYSQL/${db}_${DATE}.sql.gz"
  done
  find "$BACKUP_DIR_MYSQL" -name "*.sql.gz" -mtime "+${KEEP_DAYS}" -delete
fi

echo "$(date '+%F %T') Backup finished OK" >> "$LOG_FILE"

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

sudo chmod 700 /usr/local/bin/db-backup.sh
sudo /usr/local/bin/db-backup.sh
tail -n 20 /var/log/db-backup.log
ls -lh /var/backups/postgresql /var/backups/mysql

set -euo pipefail — важная деталь именно для этого скрипта: без pipefail ошибка pg_dump/mysqldump внутри конвейера ... | gzip осталась бы незамеченной, потому что код возврата конвейера в bash по умолчанию берётся от последней команды (gzip), а она «успешно» отработает, даже получив на входе оборванные данные. find ... -mtime "+${KEEP_DAYS}" -delete убирает локальные дампы старше KEEP_DAYS дней — иначе диск рано или поздно закончится (см. «Диск заполнен»); за более долгое хранение отвечает копия за пределами сервера из шага 2.

Добавьте задание в crontab root — например, каждую ночь в 02:00:

sudo crontab -e

0 2 * * * /usr/local/bin/db-backup.sh

Скрипт сам ведёт лог в /var/log/db-backup.log, поэтому дополнительно перенаправлять вывод в crontab не обязательно. Подробный разбор синтаксиса cron, часовых поясов и типичных ошибок — в статье «Автобэкапы по расписанию (cron)».

Шаг 2. Скопируйте бэкапы за пределы сервера

Дампы в /var/backups/postgresql и /var/backups/mysql защищают от испорченных данных и человеческих ошибок, но не от аварии самого сервера или диска — если бэкап лежит там же, где и рабочая база, при потере VPS вы потеряете и то, и другое разом. Заберите эти каталоги на другой сервер так же, как и любые другие файлы:

• rsync — простое зеркалирование каталога с дампами на другой сервер по SSH, без версионирования. См. «Резервное копирование с rsync».
• restic — версионированные зашифрованные копии с дедупликацией, дольше хранят историю снимков. См. «Инкрементальные бэкапы с restic».

В обоих случаях источником для rsync/restic будет тот же каталог /var/backups/{postgresql,mysql}: эта статья готовит для них сжатые файлы, а сами инструменты уже переносят их за пределы сервера.

Шаг 3. Обязательно проверяйте восстановление

Дамп, который ни разу не восстанавливали, — это не бэкап, а предположение, что бэкап работает. Ломается это по частям, которые незаметны без проверки: pg_dump/mysqldump мог упасть на середине из-за нехватки места, cron мог не запуститься из-за опечатки в расписании, права на файл дампа могли не дать его прочитать, а синхронизация за пределы сервера — молча остановиться при первой сетевой ошибке.

• Раз в 1–2 месяца — или после любого значимого изменения схемы — по-настоящему восстановите последний дамп в отдельную базу (шаг 3 части 1 или части 2), в идеале на другом сервере, а не на боевом.
• Сверяйте не только код возврата команды восстановления, но и содержимое: количество строк в ключевых таблицах, наличие последних по времени записей, работоспособность приложения на восстановленной копии.
• Проверяйте не только локальный дамп в /var/backups, но и его копию за пределами сервера из шага 2 — именно она останется, если сам VPS будет потерян целиком.
• Держите под рукой лог db-backup.log и заглядывайте в него не только тогда, когда что-то уже пошло не так, — молчаливо «зависший» на несколько дней бэкап без алертов легко пропустить.

Что дальше

Детали самой автоматизации — синтаксис cron, часовые пояса, ротация логов — в статье «Автобэкапы по расписанию (cron)». Про перенос дампов за пределы сервера — в статьях «Резервное копирование с rsync» и «Инкрементальные бэкапы с restic».