Резервное копирование одной базы PostgreSQL через pg_dump

pg_dump создаёт логическую резервную копию одной базы данных PostgreSQL.

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

В этой инструкции рассматривается только ручное резервное копирование одной базы данных через pg_dump.

Проверено на: Ubuntu Server 24.04 LTS
Уровень сложности: начальный
Время выполнения: около 15–25 минут
Требуемый доступ: роль PostgreSQL с правом чтения объектов базы

Что будет настроено

После выполнения инструкции можно будет:

• создать SQL-дамп одной базы;
• создать архив в формате custom;
• создать directory backup;
• исключить отдельные таблицы;
• сохранить только схему или только данные;
• проверить созданный архив;
• безопасно хранить пароль через .pgpass;
• оценить размер и продолжительность backup;
• диагностировать типичные ошибки pg_dump.

Что копирует pg_dump

pg_dump сохраняет логическую структуру базы:

• схемы;
• таблицы;
• данные;
• последовательности;
• индексы;
• ограничения;
• представления;
• функции;
• триггеры;
• комментарии;
• права объектов.

pg_dump не создаёт полный backup всего PostgreSQL-кластера.

Он не включает автоматически:

• все базы сервера;
• глобальные роли;
• tablespace definitions;
• системную конфигурацию;
• postgresql.conf;
• pg_hba.conf;
• WAL-файлы;
• физическое состояние кластера.

Полный backup кластера рассматривается отдельно.

Исходные параметры

В примерах используются:

База данных: app_db
Роль backup: backup_user
Сервер: 127.0.0.1
Порт: 5432
Каталог backup: /var/backups/postgresql

Замените значения на параметры своей инфраструктуры.

Проверка версии клиента

pg_dump --version

Желательно, чтобы версия pg_dump была не старше версии сервера PostgreSQL.

Проверить сервер:

psql \
  -h 127.0.0.1 \
  -U app_user \
  -d app_db \
  -Atc 'SHOW server_version;'

Проверка подключения

psql \
  -h 127.0.0.1 \
  -U app_user \
  -d app_db \
  -c \
  'SELECT current_database(), current_user;'

До запуска backup подключение должно работать без ошибок.

Проверка размера базы

psql \
  -h 127.0.0.1 \
  -U app_user \
  -d app_db \
  -c \
  "SELECT pg_size_pretty(
       pg_database_size(current_database())
   );"

Размер базы помогает оценить:

• свободное место;
• длительность backup;
• размер архива;
• нагрузку на диск.

Создание каталога backup

sudo install \
  -d \
  -m 750 \
  -o postgres \
  -g postgres \
  /var/backups/postgresql

Проверьте:

sudo ls -ld \
  /var/backups/postgresql

Проверка свободного места

df -h /var/backups/postgresql

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

Создание имени файла с датой

BACKUP_DATE="$(date +%F-%H%M%S)"

Проверьте:

printf '%s\n' "$BACKUP_DATE"

Пример:

2026-07-27-143000

Форматы pg_dump

Основные форматы:

plain
custom
directory
tar

Plain

Обычный SQL-файл.

Расширение:

.sql

Custom

Архив PostgreSQL для pg_restore.

Расширение:

.dump

или:

.backup

Directory

Каталог с отдельными файлами объектов.

Поддерживает параллельный backup.

Tar

Архив tar для pg_restore.

Используется реже, чем custom.

Формат plain

Создание SQL-дампа:

sudo -u postgres \
  pg_dump \
  -d app_db \
  -f \
  /var/backups/postgresql/app_db-$(date +%F-%H%M%S).sql

Если backup выполняется удалённо:

pg_dump \
  -h 192.0.2.10 \
  -p 5432 \
  -U backup_user \
  -d app_db \
  -f app_db.sql

Проверка SQL-файла

sudo ls -lh \
  /var/backups/postgresql/app_db-*.sql

Начало файла:

sudo head -40 \
  /var/backups/postgresql/app_db-*.sql

Конец файла:

sudo tail -40 \
  /var/backups/postgresql/app_db-*.sql

Преимущества plain

• читаемый SQL;
• можно просматривать обычными утилитами;
• можно редактировать вручную;
• восстанавливается через psql;
• удобно для небольших баз.

Ограничения plain

• нет выборочного восстановления через pg_restore;
• нет параллельного восстановления;
• обычно больше размер;
• изменение большого файла неудобно;
• восстановление выполняется последовательно.

Формат custom

Рекомендуемый универсальный вариант:

sudo -u postgres \
  pg_dump \
  -Fc \
  -d app_db \
  -f \
  /var/backups/postgresql/app_db-$(date +%F-%H%M%S).dump

Параметр:

-Fc

означает custom format.

Проверка custom-архива

sudo -u postgres \
  pg_restore \
  -l \
  /var/backups/postgresql/app_db-*.dump |
head -40

Если архив читается, pg_restore выведет table of contents.

Преимущества custom

• встроенное сжатие;
• выборочное восстановление;
• восстановление отдельных схем и таблиц;
• изменение владельцев при restore;
• параллельное восстановление;
• удобен для большинства задач.

Формат directory

Создание каталога:

sudo -u postgres \
  pg_dump \
  -Fd \
  -d app_db \
  -f \
  /var/backups/postgresql/app_db-directory

Параметр:

-Fd

означает directory format.

Параллельный directory backup

sudo -u postgres \
  pg_dump \
  -Fd \
  -j 4 \
  -d app_db \
  -f \
  /var/backups/postgresql/app_db-$(date +%F-%H%M%S).dir

Параметр:

-j 4

запускает до четырёх параллельных jobs.

Когда использовать directory format

Directory format подходит для:

• крупных баз;
• нескольких CPU;
• быстрого диска;
• параллельного backup;
• параллельного restore.

Для маленькой базы custom format обычно проще.

Формат tar

sudo -u postgres \
  pg_dump \
  -Ft \
  -d app_db \
  -f \
  /var/backups/postgresql/app_db-$(date +%F-%H%M%S).tar

Параметр:

-Ft

означает tar format.

Сжатие custom backup

Пример:

sudo -u postgres \
  pg_dump \
  -Fc \
  -Z 6 \
  -d app_db \
  -f \
  /var/backups/postgresql/app_db-$(date +%F-%H%M%S).dump

Параметр:

-Z 6

задаёт уровень сжатия.

Более высокий уровень:

• сильнее нагружает CPU;
• может уменьшить файл;
• может увеличить время backup.

Сжатие plain через gzip

sudo -u postgres \
  pg_dump \
  -d app_db |
gzip > \
  /var/backups/postgresql/app_db-$(date +%F-%H%M%S).sql.gz

Проверьте:

gzip -t \
  /var/backups/postgresql/app_db-*.sql.gz

Сжатие plain через zstd

Если установлен zstd:

sudo -u postgres \
  pg_dump \
  -d app_db |
zstd -T0 -6 -o \
  /var/backups/postgresql/app_db-$(date +%F-%H%M%S).sql.zst

Проверка:

zstd -t \
  /var/backups/postgresql/app_db-*.sql.zst

Backup с указанием host и пользователя

pg_dump \
  -h 192.0.2.10 \
  -p 5432 \
  -U backup_user \
  -Fc \
  -d app_db \
  -f app_db.dump

Использование connection string

pg_dump \
  -Fc \
  -d \
  'host=192.0.2.10 port=5432 dbname=app_db user=backup_user sslmode=require' \
  -f app_db.dump

Безопасное хранение пароля через .pgpass

Создайте:

nano ~/.pgpass

Добавьте:

192.0.2.10:5432:app_db:backup_user:STRONG_PASSWORD

Назначьте права:

chmod 600 ~/.pgpass

Проверьте:

pg_dump \
  -h 192.0.2.10 \
  -U backup_user \
  -Fc \
  -d app_db \
  -f app_db.dump

Если права .pgpass слишком широкие, PostgreSQL-клиент проигнорирует файл.

Почему не стоит использовать PGPASSWORD постоянно

Пример:

export PGPASSWORD='STRONG_PASSWORD'

может оставить секрет:

• в окружении процесса;
• в shell-сессии;
• в диагностических выводах;
• в скриптах.

Для разовой проверки допустимо:

PGPASSWORD='STRONG_PASSWORD' \
pg_dump ...

Но для автоматизации лучше использовать .pgpass или secret storage.

Создание отдельной роли backup

Подключитесь как postgres:

sudo -u postgres psql -d app_db

Создайте роль:

CREATE ROLE backup_user
WITH LOGIN;

Задайте пароль:

\password backup_user

Минимальные права на подключение

GRANT CONNECT
ON DATABASE app_db
TO backup_user;

Права на схемы

GRANT USAGE
ON SCHEMA public
TO backup_user;

Для отдельной схемы:

GRANT USAGE
ON SCHEMA app
TO backup_user;

Права на таблицы

GRANT SELECT
ON ALL TABLES
IN SCHEMA public
TO backup_user;

Для схемы app:

GRANT SELECT
ON ALL TABLES
IN SCHEMA app
TO backup_user;

Права на последовательности

GRANT SELECT
ON ALL SEQUENCES
IN SCHEMA public
TO backup_user;

Права по умолчанию

Чтобы роль backup получала доступ к новым таблицам:

ALTER DEFAULT PRIVILEGES
FOR ROLE app_user
IN SCHEMA public
GRANT SELECT
ON TABLES
TO backup_user;

Для последовательностей:

ALTER DEFAULT PRIVILEGES
FOR ROLE app_user
IN SCHEMA public
GRANT SELECT
ON SEQUENCES
TO backup_user;

FOR ROLE должен соответствовать роли, которая создаёт новые объекты.

Проверка прав backup_user

psql \
  -h 127.0.0.1 \
  -U backup_user \
  -d app_db \
  -c \
  'SELECT count(*) FROM public.TABLE_NAME;'

Замените TABLE_NAME существующей таблицей.

Backup только схемы

pg_dump \
  -h 127.0.0.1 \
  -U backup_user \
  -d app_db \
  --schema-only \
  -f app_db-schema.sql

Краткий параметр:

-s

Пример:

pg_dump \
  -s \
  -d app_db \
  -f app_db-schema.sql

Backup только данных

pg_dump \
  -a \
  -d app_db \
  -f app_db-data.sql

Параметр:

-a

означает data only.

Backup одной схемы

pg_dump \
  -Fc \
  -d app_db \
  -n app \
  -f app-schema.dump

Параметр:

-n

выбирает схему.

Исключение схемы

pg_dump \
  -Fc \
  -d app_db \
  -N audit \
  -f app_db-without-audit.dump

Параметр:

-N

исключает схему.

Backup одной таблицы

pg_dump \
  -Fc \
  -d app_db \
  -t public.orders \
  -f orders.dump

Backup нескольких таблиц

pg_dump \
  -Fc \
  -d app_db \
  -t public.orders \
  -t public.customers \
  -f sales-tables.dump

Исключение таблицы

pg_dump \
  -Fc \
  -d app_db \
  -T public.audit_log \
  -f app_db-without-audit.dump

Исключение данных таблицы

Сохранить структуру, но не содержимое:

pg_dump \
  -Fc \
  -d app_db \
  --exclude-table-data=public.audit_log \
  -f app_db.dump

Это удобно для:

• крупных лог-таблиц;
• временных данных;
• кэша;
• очередей, которые не требуется переносить.

Паттерны имён

pg_dump \
  -Fc \
  -d app_db \
  -t 'public.report_*' \
  -f reports.dump

Заключайте wildcard-паттерн в кавычки, чтобы shell не раскрыл его раньше pg_dump.

Не сохранять владельцев

pg_dump \
  -Fc \
  --no-owner \
  -d app_db \
  -f app_db-no-owner.dump

Это удобно при восстановлении в среду с другими именами ролей.

Не сохранять ACL

pg_dump \
  -Fc \
  --no-acl \
  -d app_db \
  -f app_db-no-acl.dump

Краткий параметр:

-x

Backup для переноса между средами

Часто используют:

pg_dump \
  -Fc \
  --no-owner \
  --no-acl \
  -d app_db \
  -f app_db-portable.dump

Консистентность backup

pg_dump создаёт согласованный snapshot базы.

Во время backup пользователи могут продолжать:

• читать данные;
• добавлять записи;
• изменять строки;
• удалять данные.

Дамп отражает состояние базы на согласованный момент времени.

Блокировки

pg_dump не блокирует обычную работу таблиц на всё время backup.

Однако операции изменения структуры могут конфликтовать с его блокировками.

Во время backup лучше избегать:

• DROP TABLE;
• ALTER TABLE;
• крупных миграций;
• удаления схем;
• изменения объектов, которые дампируются.

Использование snapshot

Для сложных согласованных процедур можно использовать exported snapshot, но для обычного backup одной базы это не требуется.

Проверка кода завершения

pg_dump \
  -Fc \
  -d app_db \
  -f app_db.dump

Проверьте:

echo $?

Успешный код:

0

Запрет пустого или частичного файла

Для shell-скрипта используйте временный файл:

TMP_FILE="/var/backups/postgresql/app_db.tmp"
FINAL_FILE="/var/backups/postgresql/app_db.dump"

if sudo -u postgres \
  pg_dump \
  -Fc \
  -d app_db \
  -f "$TMP_FILE"
then
  sudo mv \
    "$TMP_FILE" \
    "$FINAL_FILE"
else
  sudo rm -f \
    "$TMP_FILE"
  exit 1
fi

Так файл с финальным именем появляется только после успешного завершения.

Проверка размера backup

sudo ls -lh \
  /var/backups/postgresql/app_db-*.dump

Точный размер:

sudo stat \
  /var/backups/postgresql/app_db-*.dump

Контрольная сумма

sudo sha256sum \
  /var/backups/postgresql/app_db-*.dump

Сохранить checksum:

sudo sha256sum \
  /var/backups/postgresql/app_db-*.dump \
  > \
  /var/backups/postgresql/SHA256SUMS

Проверка:

cd /var/backups/postgresql &&
sudo sha256sum -c SHA256SUMS

Проверка custom archive через pg_restore

sudo -u postgres \
  pg_restore \
  -l \
  /var/backups/postgresql/app_db-*.dump \
  >/tmp/app_db-restore-list.txt

Проверьте:

head -40 \
  /tmp/app_db-restore-list.txt

Проверка plain SQL

grep -E \
  '^CREATE TABLE|^COPY |^INSERT INTO' \
  app_db.sql |
head

Для сжатого файла:

zgrep -E \
  '^CREATE TABLE|^COPY |^INSERT INTO' \
  app_db.sql.gz |
head

Проверка directory backup

sudo -u postgres \
  pg_restore \
  -l \
  /var/backups/postgresql/app_db-*.dir |
head

Ограничение времени backup

Можно установить timeout соединения:

pg_dump \
  -d \
  'host=192.0.2.10
   dbname=app_db
   user=backup_user
   connect_timeout=10' \
  -Fc \
  -f app_db.dump

connect_timeout ограничивает только время установки соединения.

statement_timeout

Обычный statement_timeout может прервать backup.

Перед запуском проверьте:

psql \
  -d app_db \
  -Atc 'SHOW statement_timeout;'

Для backup-сессии можно передать:

PGOPTIONS='-c statement_timeout=0' \
pg_dump \
  -Fc \
  -d app_db \
  -f app_db.dump

Используйте это только при понимании серверной политики.

Мониторинг процесса backup

На сервере:

ps aux |
grep '[p]g_dump'

В PostgreSQL:

SELECT
    pid,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    query_start
FROM pg_stat_activity
WHERE application_name = 'pg_dump';

Проверка нагрузки на диск

iostat -xz 1

Если iostat отсутствует:

sudo apt install -y sysstat

Проверка CPU и памяти

top

Или:

ps -C pg_dump \
  -o pid,%cpu,%mem,etime,cmd

Backup через SSH

Можно запускать pg_dump на PostgreSQL-сервере и передавать поток:

ssh ADMIN_USER@DB_SERVER \
  "sudo -u postgres pg_dump -Fc -d app_db" \
  > app_db.dump

Проверьте код завершения SSH-команды.

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

Backup на удалённый сервер

pg_dump \
  -h 192.0.2.10 \
  -U backup_user \
  -Fc \
  -d app_db |
ssh BACKUP_USER@BACKUP_SERVER \
  'cat > /srv/backups/app_db.dump'

Такой pipeline требует отдельной проверки кодов завершения обеих команд.

pipefail

Перед pipeline:

set -o pipefail

Пример:

set -o pipefail

pg_dump \
  -Fc \
  -d app_db |
gzip > app_db.dump.gz

Проверьте:

echo $?

Без pipefail ошибка pg_dump может быть скрыта успешным завершением последней команды.

Шифрование backup

Для передачи и хранения чувствительных данных архив можно шифровать.

Пример через age:

pg_dump \
  -Fc \
  -d app_db |
age \
  -r AGE_PUBLIC_KEY \
  -o app_db.dump.age

Шифрование требует отдельного безопасного управления ключами.

Не храните backup и ключ расшифровки в одном месте.

Права на файлы backup

sudo chmod 640 \
  /var/backups/postgresql/app_db-*.dump

Проверьте:

sudo ls -lh \
  /var/backups/postgresql/

Backup может содержать:

• персональные данные;
• пароли приложений;
• токены;
• ключи API;
• внутренние адреса;
• коммерческие данные.

Ограничивайте доступ.

Резервная копия вне сервера базы

Локальный dump защищает от логической ошибки, но не от:

• отказа диска;
• удаления VM;
• компрометации сервера;
• ransomware;
• потери площадки.

Копируйте проверенный backup на отдельный сервер или storage.

Именование файлов

Рекомендуемый формат:

DATABASE-DATE-TIME.FORMAT

Пример:

app_db-2026-07-27-143000.dump

Не используйте пробелы и локализованные даты в именах файлов.

Метаданные рядом с backup

Создайте файл:

sudo tee \
  /var/backups/postgresql/app_db-2026-07-27.meta \
  >/dev/null <<'EOF'
Database: app_db
Format: custom
Created by: pg_dump
Restore tool: pg_restore
EOF

Не записывайте туда пароль.

Проверка возраста последнего backup

find \
  /var/backups/postgresql \
  -maxdepth 1 \
  -type f \
  -name 'app_db-*.dump' \
  -printf '%TY-%Tm-%Td %TH:%TM %p\n' |
sort |
tail -1

Удаление тестового backup

Сначала покажите файлы:

sudo find \
  /var/backups/postgresql \
  -maxdepth 1 \
  -type f \
  -name 'app_db-*' \
  -ls

Удалите конкретный файл:

sudo rm \
  /var/backups/postgresql/app_db-DATE.dump

Не используйте широкие wildcard-шаблоны без предварительного просмотра.

Типичные проблемы

pg_dump: command not found

Установите клиент:

sudo apt update
sudo apt install -y postgresql-client

Server version mismatch

Используйте pg_dump, совместимый с версией сервера.

Слишком старый клиент может отказаться работать с более новым сервером.

Permission denied for table

Назначьте роли backup право SELECT на таблицы и доступ к схемам.

Permission denied for sequence

Назначьте SELECT на последовательности.

Password authentication failed

Проверьте .pgpass, роль, пароль и pg_hba.conf.

No space left on device

Проверьте:

df -h

Удалите ненужные файлы или выберите другой storage.

Не оставляйте частичный backup с финальным именем.

pg_dump зависает

Проверьте:

• блокировки DDL;
• сеть;
• нагрузку диска;
• долгие транзакции;
• состояние сервера;
• журнал PostgreSQL.

Архив имеет нулевой размер

Проверьте код завершения команды и stderr.

Не считайте файл backup успешным только по факту его существования.

pg_restore -l не читает файл

Возможны:

• повреждение;
• незавершённый dump;
• неверный формат;
• файл был дополнительно сжат;
• используется несовместимая утилита.

Backup получился больше базы

Логический размер и размер файлов базы не обязаны совпадать.

На размер влияют:

• формат;
• сжатие;
• индексы;
• bloat;
• TOAST;
• исключённые объекты.

Безопасный порядок создания backup

1. Проверить версию pg_dump.
2. Проверить подключение.
3. Проверить размер базы.
4. Проверить свободное место.
5. Создать защищённый каталог.
6. Выбрать custom format.
7. Запустить backup во временный файл.
8. Проверить код завершения.
9. Проверить архив через pg_restore -l.
10. Рассчитать SHA-256.
11. Скопировать backup на отдельный storage.
12. Выполнить тестовое восстановление.

Быстрый вариант

sudo install \
  -d \
  -m 750 \
  -o postgres \
  -g postgres \
  /var/backups/postgresql

sudo -u postgres \
  pg_dump \
  -Fc \
  -d app_db \
  -f \
  /var/backups/postgresql/app_db-$(date +%F-%H%M%S).dump

Проверка:

sudo -u postgres \
  pg_restore \
  -l \
  /var/backups/postgresql/app_db-*.dump |
head

Контрольная сумма:

sudo sha256sum \
  /var/backups/postgresql/app_db-*.dump

Итог

После выполнения инструкции:

• создан логический backup одной базы;
• рассмотрены форматы plain, custom, directory и tar;
• настроено сжатие;
• показан backup отдельных схем и таблиц;
• рассмотрено исключение данных;
• настроена отдельная роль для чтения;
• проверен архив через pg_restore;
• рассчитана контрольная сумма;
• разобраны права и типичные ошибки.

Следующим этапом нужно выполнить тестовое восстановление базы через pg_restore или psql.