PostgreSQL — одна из самых популярных СУБД в Linux-инфраструктуре. Но даже самая стабильная база данных не прощает отсутствие резервных копий. Ошибочное удаление таблицы, сбой диска, неудачное обновление или человеческий фактор — все это легко превращается в аварию, если нет актуального backup.
В этой статье рассмотрим создание резервных копий PostgreSQL с помощью pg_dump, восстановление через psql и pg_restore, а также автоматизацию бэкапов в Linux и Windows. Отдельно разберем частые ошибки, с которыми сталкиваются администраторы при работе с дампами PostgreSQL.
Что такое pg_dump
Утилита pg_dump входит в состав PostgreSQL и предназначена для создания резервных копий баз данных.
Поддерживаются:
- полный дамп базы;
- резервирование отдельных таблиц;
- экспорт только структуры;
- резервирование только данных;
- создание архивов в бинарном формате;
- сжатие backup-файлов;
- выгрузка удаленных баз.
Базовый синтаксис:
pg_dump <параметры> <имя_базы> > <файл>
Пример:
pg_dump users > /tmp/users.dump
Также можно использовать параметр -f:
pg_dump users -f /tmp/users.dump
Создание резервной копии PostgreSQL
Backup с авторизацией
Если подключение выполняется не от пользователя postgres, указываем логин и пароль:
pg_dump -U backup_user -W users > /tmp/users.dump
Где:
backup_user— пользователь PostgreSQL;-W— запрос пароля;users— имя базы данных.
Сжатие дампа
При больших объемах данных дампы быстро занимают много места. Проще сразу сохранять backup в сжатом виде.
Через gzip
pg_dump users | gzip > users.sql.gz
Через встроенную компрессию
pg_dump -Z9 users > users.sql.gz
Уровень сжатия задается от 0 до 9.
Резервное копирование отдельной таблицы
Иногда нужно выгрузить только одну таблицу:
pg_dump -t customers users > /tmp/customers.dump
Если таблица находится в определенной схеме:
pg_dump -t public.customers users > /tmp/customers.dump
Backup только структуры базы
Полезно при миграциях или переносе схемы:
pg_dump --schema-only users > users_schema.sql
Только данные:
pg_dump --data-only users > users_data.sql
Дамп определенной схемы
pg_dump -n public users > public_schema.sql
Только структура схемы:
pg_dump --schema-only -n public users > public_only_schema.sql
Бинарные форматы резервных копий
Текстовый SQL-дамп удобен для чтения, но для production-сред чаще используют бинарный формат.
Custom format
pg_dump -Fc users > users.bak
Tar format
pg_dump -Ft users > users.tar
Directory format
pg_dump -Fd users > users.dir
Преимущества:
- частичное восстановление;
- поддержка многопоточности;
- более удобная работа с крупными базами.
Резервное копирование всех баз PostgreSQL
Для выгрузки всего кластера используется pg_dumpall:
pg_dumpall > cluster.sql
Со сжатием:
pg_dumpall | gzip > cluster.tar.gz
Только глобальные объекты:
pg_dumpall -g > globals.sql
Это особенно полезно для сохранения:
- ролей;
- пользователей;
- tablespace;
- системных объектов.
Восстановление базы PostgreSQL
Восстановление SQL-дампа
Базовая команда:
psql users < /tmp/users.dump
С авторизацией:
psql -U backup_user -W users < /tmp/users.dump
Восстановление из gzip
gunzip users.sql.gzpsql users < users.sql
Или одной командой:
zcat users.sql.gz | psql users
Восстановление через pg_restore
Для бинарных backup-файлов используется pg_restore.
Восстановление custom backup
pg_restore -Fc users.bak
Восстановление tar backup
pg_restore -Ft users.tar
Создание базы автоматически
pg_restore -Ft -C users.tar
Подключение к удаленному серверу
pg_restore -d "postgresql://backup_user:password@localhost/users" -Fc users.bak
Автоматизация резервного копирования PostgreSQL в Linux
Пример bash-скрипта:
#!/bin/bashPGPASSWORD=passwordexport PGPASSWORDBACKUP_DIR=/backupDB_NAME=usersDB_USER=backup_userpg_dump -U $DB_USER $DB_NAME | gzip > $BACKUP_DIR/pgsql_$(date "+%Y-%m-%d").sql.gzunset PGPASSWORD
Даем права:
chmod +x /scripts/postgresql_dump.sh
Добавляем в cron:
crontab -e
Пример ежедневного запуска в 03:00:
0 3 * * * /scripts/postgresql_dump.sh
Резервное копирование PostgreSQL в Windows
Пример PowerShell-скрипта:
$Env:PGPASSWORD = 'password';$DateStr = (Get-Date).ToString("yyyy-MM-dd")$BackupPath = 'C:\Backup'pg_dump -U postgres users > $BackupPath\users.$DateStr.sqlЧастые ошибки pg_dump и pg_restore
Input file appears to be a text format dump
Причина:
- попытка восстановить SQL-дамп через
pg_restore.
Решение:
psql users < users.sql
Aborting because of server version mismatch
Ошибка возникает при несовпадении версий PostgreSQL.
Например:
- сервер PostgreSQL 16;
pg_dumpот PostgreSQL 12.
Решение — использовать утилиты той же версии, что и сервер.
В Linux они обычно находятся здесь:
/usr/lib/postgresql/<version>/bin/
No password supplied
Причина:
- не задан
PGPASSWORD; - отсутствует доступ через
pg_hba.conf.
Временное решение:
export PGPASSWORD=password
Неверная команда \
Часто возникает при поврежденном дампе или ошибке SQL во время восстановления.
Для детального вывода:
psql -v ON_ERROR_STOP=1 users < users.dump
pg_dump или pg_basebackup — что выбрать
pg_dump подходит для:
- логических backup;
- отдельных баз;
- таблиц;
- миграций;
- selective restore.
pg_basebackup используется для:
- резервирования всего кластера PostgreSQL;
- streaming replication;
- HA-инфраструктуры;
- standby-серверов.
Если нужен обычный backup базы — чаще всего достаточно pg_dump.
Заключение
pg_dump остается основным инструментом резервного копирования PostgreSQL благодаря простоте, гибкости и стабильности. Для большинства задач достаточно:
- регулярного автоматического backup;
- хранения нескольких поколений дампов;
- периодической проверки восстановления.
Потому что backup, который ни разу не тестировали на restore — это не backup. Это надежда. А надежда RAID не заменяет.
Источник с примерами команд и сценариев: DMOSK — Резервное копирование PostgreSQL
FAQ
Как сделать резервную копию PostgreSQL?
Используйте утилиту pg_dump:
pg_dump database_name > backup.sql
Чем отличается pg_dump от pg_basebackup?
pg_dump создает логический backup отдельных баз и таблиц, а pg_basebackup резервирует весь кластер PostgreSQL на уровне файловой системы.
Как восстановить backup PostgreSQL?
Для SQL-дампа используется:
psql database_name < backup.sql
Для бинарного backup:
pg_restore -d database_name backup.bak
Можно ли автоматизировать backup PostgreSQL?
Да. В Linux обычно используют cron, а в Windows — PowerShell и Task Scheduler.