pg_dump и pg_restore: практическое руководство по backup

Почти любой случай покрывают две команды. pg_dump --format=custom --file=app.dump app записывает один сжатый согласованный архив одной базы, пока она продолжает обслуживать трафик. pg_restore --dbname=app_new --jobs=4 app.dump возвращает его на место, параллельно, в пустую базу. Всё остальное в этом руководстве - подробности вокруг этих двух строк: чего в архиве нет, какой версией инструментов пользоваться, как восстановить одну таблицу, не трогая остальные, и почему первое, что нужно сделать после любого восстановления, - это ANALYZE.

Причина выучить это, а не полностью полагаться на кнопку backup у вашего хостера, - переносимость. Логический дамп - это описание ваших данных, которое сможет прочитать любой PostgreSQL той же или более новой основной версии, на любой машине и навсегда. Это backup, который переживает смену хостера, смену версии и случайное удаление сервера.

Что такое дамп и чем он не является#

pg_dump подключается как обычный клиент и читает ваши данные через SQL. Чтобы получить согласованную картину, он открывает единственную транзакцию с уровнем изоляции REPEATABLE READ и делает снимок в ней, так что каждая таблица в архиве отражает один и тот же момент, даже если дамп идёт час. Читателей и пишущих он не блокирует.

Зато он берёт блокировку ACCESS SHARE на каждую читаемую таблицу. Это самая слабая из всех блокировок, и она конфликтует ровно с одной: ACCESS EXCLUSIVE, которую берут DROP TABLE, большинство форм ALTER TABLE, TRUNCATE и VACUUM FULL. Поэтому дамп и миграция, идущие одновременно, заблокируют очередь друг друга, а поскольку запросы на блокировку упорядочены, миграция, ожидающая за дампом, блокирует каждый запрос, пришедший после неё. Используйте --lock-wait-timeout=10s, чтобы дамп сдавался, а не вставал в затор, и не планируйте дампы и миграции на одно и то же окно. Вторая половина этой проблемы разобрана в статье миграции без простоя.

Вторая цена долгого дампа - удерживаемый снимок. Пока дамп идёт, vacuum не может удалить ни одну версию строки, более новую, чем он, так что дамп, который на нагруженной таблице идёт четыре часа, оставляет за собой четыре часа мёртвых строк. Это нормально, и потом они вычищаются, но именно поэтому дамп большой, активно обновляемой базы лучше снимать, когда она свободна. Чего стоят эти мёртвые строки, объясняет статья vacuum и bloat в Postgres.

Чем дамп не является - системой восстановления на момент времени. Он фиксирует один миг. Если вам нужно «восстановиться на 14:32, прямо перед злополучным UPDATE», это pg_basebackup плюс архивные журналы предзаписи, а это другая и куда более тяжёлая конструкция. Для большинства приложений подходит ночной дамп с возможной потерей в час, и делать вид, что это не так, - способ остаться ни с чем.

Четыре формата вывода#

Используйте формат custom, если нет причин для другого. Это один файл, он сжат, восстанавливается выборочно, и pg_restore может собирать его несколькими рабочими процессами.

Формат directory берите, когда база настолько велика, что узким местом становится сам дамп, потому что --jobs работает при создании дампа только в этом формате. Он создаёт папку с одним файлом на таблицу плюс оглавление, а это значит, что можно увидеть, какая таблица огромна, просто посмотрев на содержимое папки.

Формат plain нужен, когда человеку нужно прочитать или отредактировать SQL, либо когда вы переносите небольшую базу туда, где есть только psql. Он не выборочный и не сжатый, так что plain-дамп чего-то крупного - плохой выбор по умолчанию. Формат tar существует по историческим причинам и не предлагает ничего сверх остальных двух.

В PostgreSQL 16 и новее --compress принимает не только уровень, но и метод, поэтому --compress=zstd:3 для directory-дампа заметно быстрее gzip при сопоставимом размере. В более ранних версиях -Z 0 - -Z 9 выбирает уровень zlib, а значение по умолчанию для архивных форматов уже умеренное.

Снятие дампа#

$ export PGHOST=db.example.net PGPORT=5432 PGUSER=appuser$ pg_dump --format=custom --no-owner --no-privileges \      --file=app-$(date +%F).dump app

Параметры подключения берутся из флагов (-h, -p, -U, -d) или из переменных окружения PG*. Никогда не пишите пароль в командной строке, где он попадает в историю оболочки и в вывод ps. Используйте ~/.pgpass, по одной строке на сервер, права 0600:

db.example.net:5432:*:appuser:the-generated-password

Флаги, которые стоит знать:

• --no-owner (-O) - пропускает выражения ALTER ... OWNER TO. Необходим, когда имена ролей на целевой стороне отличаются, и безвреден, когда не отличаются.
• --no-privileges (-x) - пропускает GRANT и REVOKE. Используйте, когда восстанавливаете в базу, права в которой вы настраиваете отдельно.
• --schema-only и --data-only - одно без другого. Дамп только схемы полезно хранить в системе контроля версий.
• -t, -T, -n, -N - включают или исключают таблицы и схемы по имени или шаблону. Учтите, что -t orders не тянет за собой таблицы, на которые ссылается orders, так что построенный так дамп обычно сам по себе не восстановится в пустую базу.
• --exclude-table-data='audit_log*' - оставляет определение таблицы, отбрасывает строки. Так из базы в 40 ГБ получают дамп в 200 МБ для staging-копии.
• --jobs=4 (-j) - только формат directory, по одному соединению на рабочий процесс. Не ставьте больше числа ядер, которое готовы отдать базе.
• --verbose - выводит каждый объект по ходу дела, превращая молчаливый час в то, за чем можно следить.

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

$ pg_dumpall --globals-only --file=globals.sql

Роли, глобальные объекты и что дамп не включает#

Именно здесь ломаются восстановления, так что прочитайте список. pg_dump одной базы не включает:

• Роли и их пароли. Они общие для всего кластера. pg_dumpall --globals-only захватывает их вместе с хешами паролей, а восстанавливаются они командой psql -f globals.sql до дампа базы. Как эти роли должны выглядеть изначально, см. в статье роли и права в PostgreSQL.
• Табличные пространства. Тоже общие для кластера и тоже лежат в файле глобальных объектов. Каталоги, на которые они указывают, должны заранее существовать на целевой машине.
• Выражение `CREATE DATABASE`, если не указан -C. Обычно пустую базу вы создаёте сами, с нужным владельцем, кодировкой и collation, а затем восстанавливаете в неё.
• Конфигурацию сервера. postgresql.conf и pg_hba.conf - это файлы на диске, а не объекты базы. Копируйте их отдельно.
• Код расширений. В дампе есть CREATE EXTENSION postgis;, но нет самого PostGIS. Если пакет не установлен на целевой машине, восстановление останавливается на этом месте.
• Статистику планировщика. Каждая восстановленная таблица приходит вообще без статистики, поэтому свежевосстановленная база может быть заметно медленнее исходной, пока вы не выполните ANALYZE. Некоторые совсем свежие версии умеют переносить статистику, но ANALYZE после этого стоит недорого и снимает сомнения. Что делает эта статистика, объясняет статья как читать план запроса Postgres.

Большие объекты по умолчанию включаются при дампе всей базы и исключаются, если вы ограничили дамп через -t или -n, пока не добавите -b. Значения последовательностей включаются в виде вызовов setval, так что столбцы identity продолжают с того же места, а не сталкиваются на первой же вставке.

Восстановление и важные флаги#

$ createdb -T template0 app_restore$ pg_restore --dbname=app_restore --jobs=4 --no-owner \      --verbose app-2026-09-21.dump

-T template0 важнее, чем кажется: он даёт базу, в которой нет ничего, так что создаваемые дампом объекты не столкнутся с объектами, унаследованными от template1.

• --clean --if-exists - удаляет каждый объект перед пересозданием. Используйте оба флага вместе, иначе --clean будет шумно падать на первом же отсутствующем объекте.
• --single-transaction (-1) - всё или ничего. Если что-то не удалось, база остаётся ровно такой, какой была. Совмещать с --jobs нельзя, так что выбирать приходится между скоростью и атомарностью.
• --jobs=N - восстанавливает данные таблиц и строит индексы одновременно. На восстановлении любого заметного размера это разница между двадцатью минутами и двумя часами. Только форматы custom и directory.
• --no-owner --role=appuser - восстанавливает всё от имени одной роли, кем бы ни был исходный владелец.
• --section=pre-data|data|post-data - схема, затем строки, затем индексы, ограничения и триггеры. Полезно, когда нужно загрузить данные, отложив построение индексов.
• -t, -n, -L - выборочное восстановление, разобрано ниже.

Дамп в формате plain вообще не восстанавливается через pg_restore. Это SQL, так что:

$ psql --dbname=app_restore --set ON_ERROR_STOP=1 --file=app.sql

Без ON_ERROR_STOP=1 psql печатает ошибки, идёт дальше и завершается с нулевым статусом и наполовину построенной базой. Это значение по умолчанию испортило больше восстановлений, чем что-либо другое.

Чтобы восстановить одну таблицу из архива, сначала выведите оглавление, оставьте нужные строки и подайте список обратно:

$ pg_restore --list app.dump > toc.list$ grep -E 'TABLE DATA public (orders|order_items)' toc.list > wanted.list$ pg_restore --dbname=app_restore --data-only --use-list=wanted.list app.dump

Это надёжнее, чем -t orders, потому что файл списка позволяет сохранить последовательность, индексы и ограничения, относящиеся к таблице.

Правила версий при переезде между серверами#

Всё укладывается в три правила:

1. Делайте дамп самым новым из имеющихся `pg_dump`. pg_dump версии 17 прекрасно снимает дамп с сервера версии 13. Наоборот нельзя: старый pg_dump против более нового сервера прерывается с несоответствием версий, потому что не может знать, что содержит более новый каталог.
2. Восстанавливайте в ту же основную версию или в более новую. Движение назад, с 17 на 16, не поддерживается. Иногда кажется, что работает, а потом падает на синтаксисе, о котором старый сервер не слышал.
3. `pg_restore` должен быть не старше `pg_dump`, записавшего архив. Более старый pg_restore отвергает более новый архив с сообщением unsupported version ... in file header.

Практический итог всех трёх: при обновлении или миграции запускайте pg_dump и pg_restore от нового сервера против старого. Дополнительные версии (с 17.2 до 17.6) здесь значения не имеют.

Как ускорить большое восстановление#

Восстановление - это очень длинная серия вставок, за которой следует очень длинная серия построений индексов, а конфигурация по умолчанию настроена на сервер, отвечающий на запросы, а не на это. На целевом сервере, только на время восстановления:

ALTER SYSTEM SET maintenance_work_mem = '1GB';ALTER SYSTEM SET max_wal_size = '8GB';ALTER SYSTEM SET synchronous_commit = 'off';SELECT pg_reload_conf();

maintenance_work_mem используют построение индексов и сортировки, и это самый мощный рычаг. max_wal_size не даёт восстановлению вызывать checkpoint каждые несколько секунд. synchronous_commit = off означает, что сбой посреди восстановления мог потерять недавние коммиты, что не важно, потому что вы всё равно начнёте восстановление заново. Верните значения обратно и выполните ANALYZE для всей базы:

$ vacuumdb --analyze-only --jobs=4 --dbname=app_restore

Размер цели тоже оценивайте реалистично. Восстановлению нужно место под данные, индексы и журнал предзаписи, создаваемый при их построении, а это может быть в два-три раза больше несжатого размера дампа. На тарифе с 20 ГБ именно это ограничение вы встретите первым, а сервер базы данных с заполненным диском перестаёт принимать записи.

Расписание дампов и доказательство, что дамп восстанавливается#

Дамп, который никто не восстанавливал, - гипотеза. Порядок действий, превращающий его в backup:

1. Снимайте дамп каждую ночь, в час, когда приложение свободно, с датой в имени файла.
2. Копируйте его с машины, на которой он был снят. Дамп, лежащий на том же диске, что и база, защищает от DROP TABLE и ни от чего больше.
3. Храните семь ежедневных, четыре еженедельных и столько ежемесячных, сколько требуют ваши обязательства. Очищайте автоматически, потому что ручная очистка заканчивается полным диском.
4. Раз в квартал восстанавливайте самый свежий в тестовую базу, считайте строки в трёх важных для вас таблицах, направьте на неё копию приложения и удалите базу.

В RE:NODE вкладка Schedules принимает выражение cron и запускает упорядоченные задачи с паузами между ними - backup, действие с питанием, команду консоли, - так что ночная половина этого списка становится настройкой, а не скриптом, который приходится держать живым. Слоты backup входят в каждый тариф баз данных: backup делаются по запросу или по расписанию, хранятся вне защищаемой машины, восстанавливаются кнопкой, скачиваются и блокируются, чтобы ротация не удалила нужный. Стоит запомнить две вещи: удаление сервера удаляет его backup, в том числе заблокированные, а скачанный архив pg_dump - единственная копия, которую можно перенести к другому хостеру. Храните и то и другое. Подробнее об этом - в статьях backup, из которых действительно восстанавливаются и проверка восстановления до того, как оно понадобится, а руководства по панели показывают, где находятся кнопки.

Ошибки, которые вы действительно увидите#

`pg_dump: error: aborting because of server version mismatch` - ваш pg_dump старше сервера. Установите подходящие клиентские инструменты; не пытайтесь обойти это принудительно.

`pg_restore: error: input file appears to be a text format dump. Please use psql.` - вы сделали дамп в формате plain и взялись не за тот инструмент. Восстановите его через psql -f.

`role "someone" does not exist` - вы восстановили дамп, содержащий владельцев или права, в кластер без этих ролей. Сначала восстановите глобальные объекты либо снимите дамп заново с --no-owner --no-privileges.

`permission denied for schema public` - начиная с PostgreSQL 15 схема public больше не позволяет каждой роли создавать в ней объекты. Выдайте право явно восстанавливающей роли или сделайте эту роль владельцем базы.

`ERROR: relation "orders" already exists` - восстановление поверх базы, которая не пуста. Используйте свежую базу либо --clean --if-exists.

`could not execute query: ERROR: extension "pg_trgm" is not available` - файлов расширения нет на целевой машине. Установите пакет, затем начните восстановление заново.

Восстановление, которое завершилось успешно, но приложение сломано - почти всегда это отсутствующие последовательности или права, из-за дампа с -t, который взял таблицу и ничего из связанного с ней.

FAQ#

Блокирует ли pg_dump мои таблицы или отключает базу?

Нет. Он берёт блокировку ACCESS SHARE на каждую таблицу, которую читатели и пишущие игнорируют. Ждать приходится только выражениям, которым нужна блокировка ACCESS EXCLUSIVE, например ALTER TABLE или TRUNCATE. Всё это время база обслуживает трафик как обычно.

Можно ли восстановить дамп в другую версию PostgreSQL?

В ту же основную версию или в более новую - да, и это стандартный способ обновления. В более старую основную версию - нет. При переезде вперёд читайте старый сервер pg_dump от нового.

Чем отличается pg_dump от pg_dumpall?

pg_dump работает с одной базой и умеет писать сжатые архивы с выборочным восстановлением. pg_dumpall обходит все базы кластера и пишет только простой SQL. На практике вы используете pg_dumpall --globals-only для ролей и табличных пространств, а pg_dump - для каждой базы.

Сколько должен идти дамп?

Примерно столько, сколько занимает чтение всей базы с диска плюс сжатие. Несколько гигабайт на NVMe - это минута-две. Если он идёт заметно дольше, обычные причины - занятый CPU, формат plain через медленный компрессор или таблица, набитая большими объектами.

Что использовать: pg_dump или кнопку backup хостера?

Оба. Backup платформы делается быстро и быстро восстанавливается на месте, но исчезает вместе с удалённым сервером. Скачанный вами архив pg_dump медленнее, зато переносим, терпим к версиям и принадлежит вам. Ломаются они по-разному, и в этом весь довод за то, чтобы иметь оба.

Восстановленная база намного медленнее оригинала. Почему?

Потому что статистика в дамп не входит, и планировщик гадает по каждой таблице, пока вы не выполните ANALYZE. Запускайте vacuumdb --analyze-only для всей базы последним шагом любого восстановления, и разница обычно исчезает.