Резервное копирование PostgreSQL по-взрослому

Базы данных — замечательный инструмент, без которого сложно представить современное приложение. И как бы сильно я ни любил использовать БД, я просто ненавижу писать SQL-запросы. Поэтому однажды задался вопросом, кто мог бы делать это за меня, при этом несильно теряя в качестве. И, конечно же, на ум пришёл мой AI-друг. Тогда остаётся одна проблема, как скормить ему мою БД. Тут на помощь приходит резервное копирование! Выполнив все необходимые задачи, я решил углубиться в тему и поделиться с Хабром, какие вообще есть виды и, конечно, сравнить их между собой.

▍ Инструменты для работы с резервным копированием PostgreSQL

Для начала рассмотрим несколько CLI-инструментов, которые позволят нам работать с резервными копиями

▍ pg_dump

pg_dump <название БД>

Стандартная утилита для создания копий. Для лучшего понимания предлагаю вкратце ознакомиться с тем, как она реализована. Рассмотрим шаги внутренней работы:

• Подключение к БД: авторизация такая же, как и у обычного клиента, используется библиотека С — libpq.
• Чтение схемы: посредством запросов SELECT * FROM ...
• Создание структуры: строится дерево объектов, которое затем преобразуется в соответствующий формат.
• Сохранение данных: отдельно сохраняются данные из БД.
• Запись в конечный файл: в зависимости от типа резервной копии сохраняется либо в бинарник, либо в текстовый файл всю копию, включающую структуру и данные.

Ключи:

• -U имя пользователя
• -h хост
• -p порт
• -F формат дампа: p (plain), c (custom), d (directory), t (tar)
• -f путь к файлу вывода
• -d название базы данных
• --table=имя снять дамп только одной таблицы
• --schema=имя только определённая схема
• -v подробный вывод
• --data-only только данные, без схемы
• --schema-only только схема, без данных
• --inserts использовать INSERT вместо COPY

▍ pg_dumpall

pg_dumpall

По сути тот же pg_dump, но делает дамп сразу всего кластера. Однако отметим сразу, что данная утилита не поддерживает указание форматов, копия создаётся только в формате SQL. Давайте начнём также с реализации:

• Подключение к БД: аналогично pg_dump.
• Получение имён всех БД: посредством запросов SELECT datname FROM pg_database WHERE datallowconn.
• Сохранение всех глобальных объектов.
• Получение списка БД: вызывается команда pg_dump для каждой базы.
• Создание общего файла дампа: все дампы объединяются в один поток вывода и записываются в файл аналогично pg_dump.

Ключи:

• -U имя пользователя
• -h хост
• -p порт
• -f файл вывода
• -v подробный вывод
• --globals-only только роли и настройки
• --roles-only только роли
• --data-only только данные

▍ pg_restore

pg_restore <путь к дампу>

Инструмент для восстановления БД из дампа. Под капотом всё просто:

• Чтение копии: считывается TOC (table of contents), определяется, в каком порядке необходимо восстанавливать данные.
• Фильтрация: происходит по определённым параметрам, которые задаются при запуске утилиты.
• Соединение с БД: посредством знакомой нам уже libpq. Также, если выбран многопоточный режим, то для каждого потока будет отдельное соединение.
• Восстановление: исполнение SQL-команд.

Ключи:

• -U имя пользователя
• -h хост
• -p порт
• -d целевая база данных
• -F формат (обычно не нужен — определяется автоматически)
• -v подробный вывод
• -c удалить объекты перед созданием
• -C создать базу перед восстановлением
• -j N параллельное восстановление (N — число потоков)
• --list показать содержимое дампа
• --schema восстановить только указанную схему
• --table восстановить только указанную таблицу

Отлично! С инструментами мы познакомились, теперь перейдём к самому интересному.

▍ Форматы дампов

Ради этого мы тут все и собрались. Давайте погрузимся в возможности резервных копий. До этого вы могли наблюдать непонятный ключ -F, который позволяет указать формат. Всего в PostgreSQL есть 4 формата:

• plain (стандартный)
• custom
• tar
• dir

Теперь хочется познакомиться с каждым поближе, но перед этим обсудим одну очень важную для понимания разницы между форматами вещь — TOC.

TOC — Table of Contents, оглавление резервной копии, которое отражает, какой контент содержится в дампе, в каком порядке его нужно восстанавливать и какие есть зависимости между объектами.

▍ plain

Дамп представляет собой один текстовый SQL-файл. Данный формат позволяет редактировать копию, а также удобно переходить между СУБД. Реализация представляет собой банальное генерирование SQL-команд.

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

• Отличный вариант для миграции
• Прозрачность дампа

Недостатки:

• Невозможно выборочное восстановление
• Не поддерживает параллельное восстановление
• Отсутствует сжатие

Пример использования:

pg_dump -Fp demo -f demo-plain

Здесь ключ -Fp указывает формат plain. Также можно было вообще не указывать ключ формата, т. к. дефолтно используется plain, но для наглядности прописан.

Давайте посмотрим на то, как отработает наш дамп. Здесь и далее для примера взята БД demo, последняя версия размером 2.5гб:

time pg_dump -Fp demo -f demo-plain

Executed in    8.42 secs        fish                  external
      usr time    1.33 secs    231.00 micros   1.33 secs
      sys time    1.63 secs    120.00 micros   1.63 secs

ls -lh | grep "demo-plain"

-rw-r--r--. 1 net0pyr net0pyr 888M апр 1 21:03 demo-plain

Если посмотреть на формат файла, то это будет обычный текстовый файл:

file demo-plain

demo-plain: Unicode text, UTF-8 text, with very long lines (307)

P.S. Именно этот формат помог мне скормить моему AI-другу БД.

▍ custom

Резервная копия сохраняется в бинарном формате. Дамп содержит TOC.

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

• Выборочное восстановление, можно использовать фильтры для восстановления только частей дампа
• Поддержка параллельного восстановления
• Сжатие

Недостатки:

• Невозможность читать дамп, пока не восстановишь его

Для использования необходимо прописать ключ -Fc. Пример:

pg_dump -Fc demo -f demo.dump

Также давайте аналогично предыдущему варианту соберём информацию по требуемым для создания дампа ресурсам:

time pg_dump -Fc demo -f demo.dump

Executed in    37.00 secs      fish                  external
      usr time    36.40 secs   360.00 micros   36.40 secs
      sys time    0.44 secs     187.00 micros   0.44 secs

ls -lh | grep "demo.dump"

-rw-r--r--. 1 net0pyr net0pyr 233M апр 1 20:50 demo.dump

В этот раз формат файла будет бинарный дамп PostgreSQL:

file demo-plain

demo-plain: Unicode text, UTF-8 text, with very long lines (307)

Вы можете отобразить TOC, воспользовавшись командой pg_restore:

pg_restore --list demo.dump

▍ tar

Дамп сохраняется как tar-архив, который содержит файлы с данными, SQL-файл со структурой и TOC.

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

• Удобство транспортировки
• Есть возможность восстановиться с помощью tar -xvf без использования pg_restore
• Выборочное восстановление

Недостатки:

• Отсутствует сжатие
• Не поддерживает параллельное восстановление

Аналогично предыдущим форматам схема использования:

pg_dump -Ft demo -f demo-tar

И по классике взглянем на результаты создания дампа в tar-формате:

time pg_dump -Ft demo -f demo-tar

Executed in    10.77 secs      fish                  external
      usr time     1.57 secs     77.00 micros    1.57 secs
      sys time     1.53 secs     982.00 micros  1.53 secs

ls -lh | grep "demo-tar"

-rw-r--r--. 1 net0pyr net0pyr 888M апр 1 20:59 demo-tar

Заинтересованный читатель, скорее всего, задался вопросом, что именно лежит в этом архиве. Давайте посмотрим:

tar -xvf demo-tar && ls -lh

total 1.8G
-rw------- 1 net0pyr net0pyr  631 Apr 21 16:14 3508.dat
-rw------- 1 net0pyr net0pyr  17K Apr 21 16:14 3509.dat
-rw------- 1 net0pyr net0pyr 204M Apr 21 16:15 3510.dat
-rw------- 1 net0pyr net0pyr  79M Apr 21 16:15 3511.dat
-rw------- 1 net0pyr net0pyr  26M Apr 21 16:15 3512.dat
-rw------- 1 net0pyr net0pyr  21K Apr 21 16:15 3514.dat
-rw------- 1 net0pyr net0pyr 297M Apr 21 16:15 3515.dat
-rw------- 1 net0pyr net0pyr 284M Apr 21 16:15 3516.dat
-rw-r--r-- 1 net0pyr net0pyr 888M Apr 21 16:15 demo-tar
-rw------- 1 net0pyr net0pyr  33K Apr 21 16:15 restore.sql
-rw------- 1 net0pyr net0pyr  41K Apr 21 16:14 toc.dat

Мы можем наблюдать toc.dat — наш TOC, restore.sql — SQL-скрипт для восстановления, 35[08-16].dat — файлы с данными из БД. Таким образом мы можем восстановиться из дампа только с утилитой tar.

▍ dir

Вот мы и дошли до последнего формата. Я специально оставил его на десерт, потому что он позиционирует себя как «не такой как все», предлагая уникальные возможности. Давайте подробнее разберёмся. Сам дамп представляет собой директорию, содержащую сжатые данные и TOC.

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

• Есть поддержка параллельного дампа и восстановления
• Есть сжатие
• Быстро работает с большими объёмами данных

Недостатки:

• Неудобные хранение и транспортировка

Обычное использование:

pg_dump -Fd demo -f demo-dir-0

Но мы ведь уже знаем, что здесь есть возможность параллельного дампа, в этом нам поможет ключ -j. Для пяти потоков команда будет выглядеть следующим образом:

pg_dump -Fd demo -j 5 -f demo-dir-5

Перейдём к самому интересному — сравнению результатов отработки дампа:

time pg_dump -Fd demo -f demo-dir-0

      Executed in    36.94 secs     fish                  external
      usr time    36.23 secs  380.00 micros  36.22 secs
      sys time    0.36 secs    197.00 micros  0.36 secs

ls -lh demo-dir-0

итого 233M
-rw-r--r--. 1 net0pyr net0pyr 321 апр 1 20:56 4437.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 5,6K апр 1 20:56 4438.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 54M апр 1 20:56 4439.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 20M апр 1 20:56 4440.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 3,3M апр 1 20:56 4441.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 3,0K апр 1 20:56 4443.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 69M апр 1 20:56 4444.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 87M апр 1 20:56 4445.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 40K апр 1 20:56 toc.dat

Обратим внимание на время 37 секунд. Также хочется остановиться на структуре каталога. Мы видим `toc.dat`и 8 сжатых файлов с данными, что позволяет сохранить небольшой размер копии. Теперь для сравнения посмотрим на дамп с 5 потоками:

time pg_dump -Fd demo -j 5 -f demo-dir-5

     Executed in   17.69 secs      fish                  external
     usr time     53.16 secs   0.61 millis       53.16 secs
     sys time     0.57 secs     1.24 millis       0.56 secs

ls -lh demo-dir-5

итого 233M
-rw-r--r--. 1 net0pyr net0pyr 321 апр 1 20:55 4437.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 5,6K апр 1 20:55 4438.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 54M апр 1 20:55 4439.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 20M апр 1 20:55 4440.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 3,3M апр 1 20:55 4441.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 3,0K апр 1 20:55 4443.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 69M апр 1 20:55 4444.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 87M апр 1 20:55 4445.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 40K апр 1 20:55 toc.dat

Мы получили аналогичный результат, но уже за 18 секунд, т. е. в 2 раза быстрее.

▍ Выводы

Дорогой читатель, вот мы и познакомились с форматами резервных копий в PostgreSQL. Но мы не можем закончить на этом, в конце хочется структурировать все те измерения, что мы произвели, чтобы иметь под рукой шпаргалку, которая позволит выбрать самый подходящий формат для наших будущих задач.

Формат	Время	Размер	Многопоточный restore	Многопоточный dump	Выборочный restore	Транспортировка
plain	8.42	888Мб	❌	❌	❌	Один текстовый файл
custom	37.00	233Мб	✅	❌	✅	Один бинарный файл
tar	10.77	888Мб	❌	❌	✅	Один tar-архив
dir (1 поток)	36.94	233Мб	✅	✅	✅	Директория с файлами
dir (5 поток)	17.69	233Мб	✅	✅	✅	Директория с файлами

© 2025 ООО «МТ ФИНАНС»

Telegram-канал со скидками, розыгрышами призов и новостями IT 💻