Резервное копирование и восстановление баз данных PostgreSQL при помощи pg_dump на платформе Windows

Резервное копирование - одна из важнейших задач системного администратора. Хорошо если копии вам никогда не пригодятся, но они должны быть. Сегодня мы рассмотрим некоторые аспекты резервного копирования популярной СУБД PostgreSQL, в частности при ее применении совместно с 1С:Предприятие. Начнем с самого простого и понятного способа - использования утилиты pg_dump, которая, кстати, может использоваться не только для резервного копирования, но и для переноса баз между различными серверами.

Онлайн-курс по устройству компьютерных сетей
На углубленном курсе "Архитектура современных компьютерных сетей" вы с нуля научитесь работать с Wireshark и «под микроскопом» изучите работу сетевых протоколов. На протяжении курса надо будет выполнить более пятидесяти лабораторных работ в Wireshark.

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

Иные способы, позволяющие такое восстановление, работают на уровне инстанса и позволяют восстановить сразу весь кластер, т.е. все базы. Поэтому рекомендации по продуктовому применению PostgreSQL предусматривают основную схему: 1 база - 1 инстанс, что для небольших внедрений может быть избыточно как по ресурсам, так и по накладным расходам на администрирование.

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

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

Подготовка сервера

Чтобы удобно работать с утилитами PostgreSQL добавим путь к ним в переменную окружения PATH. Для этого перейдем в Свойства системы - Дополнительно - Переменные окружения.

Затем найдем в списке переменную PATH и изменим ее, добавив новой строкой путь к папке bin вашего экземпляра PostgreSQL, в нашем случае это C:\Program Files\PostgreSQL\15.5-10.1C\bin:

Теперь вы можете обращаться к утилитам PostgreSQL просто по имени. Однако при написании скриптов всегда указывайте полный путь, что позволит избежать ошибок, если у запустившего пользователя скрипт в переменной PATH не окажется указанного выше пути.

Следующий вопрос - пароль суперпользователя СУБД, либо другого пользователя, имеющего нужные права для выгрузки и загрузки базы. В подавляющем большинстве случаев с пользователями никто не заморачивается и все работают от суперпользователя postgres. Для этого можно пойти несколькими путями, один из них создание специального файла паролей, для этого создайте в указанные ниже директорию и файл:

%APPDATA%\postgresql\pgpass.conf

После чего внесите в него следующие строки:

#имя_узла:порт:база_данных:имя_пользователя:пароль

localhost:*:*:postgres:MyPa$$Word_1

Первая строка - это подсказка-комментарий, чтобы вам потом не приходилось вспоминать синтаксис. А ниже мы указали, что при подключении к инстансу расположенному на локальном узле localhost, использующему любой порт, для любой базы данных, при подключении пользователем postgres использовать указанный пароль. Первые четыре поля могут использовать подстановочный знак * указывающий на любое значение. Например, если у вас множество серверов с одинаковым паролем, то можете указать так:

*:*:*:postgres:MyPa$$Word_1

Данный способ является рекомендуемым, но у него есть недостаток в виде хранения пароля в открытом виде. Поэтому можно использовать альтернативу, разрешить локальные подключения без пароля. Для этого найдите файл pg_hba.conf который находится в директории кластера data, в нашем случае он расположен по умолчанию в C:\Program Files\PostgreSQL\15.5-10.1C\data. В данный файл добавьте строку:

host all all localhost trust

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

Создание резервной копии базы данных

Утилита pg_dump умеет создавать копии в разных форматах, каждый из которых имеет свои достоинства и недостатки.

plain - выгрузка в текстовом SQL формате. Наиболее универсальна, а при необходимости позволяет вручную откорректировать дамп или выполнить частичную загрузку или восстановление, например отдельной таблицы. Не сжимается, имеет большой размер.
custom - собственный формат pg_dump, предусматривает сжатие данных и возможность многопоточной загрузки, выгружается всегда однопоточно
directory - выгрузка в виде директории, на каждую таблицу выгружается отдельный сжатый файл, позволяет многопоточную выгрузку и загрузку.
tar - представляет, по сути, выгрузку в виде директории, но упакованной в tar-архив, сжатие не предусмотрено, поэтому размер выгрузки будет больше, чем у директории, однопоточен.

Таким образом, наиболее удобными с практической точки зрения является формат custom, либо directory - если вам требуется многопоточная выгрузка. При переносе между разными системами предпочтительно использовать plain, так как он представляет набор SQL команд и может быть легко отредактирован вручную.

Итак приступим. Прежде всего следует узнать какие базы данных есть на нашем сервере и как они называются, для этого выполним:

psql -h localhost -U postgres -l

В выводе мы увидим список баз и их параметры:

В нашем случае мы будем бекапить базу данных bkp1 и местом хранения резервных копий определим D:\Backup. Начнем с текстового формата, он используется по умолчанию и отдельно указывать его не нужно:

pg_dump -h localhost -U postgres -f D:\Backup\bkp1.sql bkp1

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

-h сервер - указывает имя или адрес компьютера, на котором работает сервер СУБД
-U имя_пользователя - имя пользователя, под которым производится подключение
-f файл - файл, в который производится выгрузка

Также ниже мы будем использовать ключи:

-F формат - формат выгрузки
-j число_заданий - количество потоков

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

В некоторых источниках можно встретить команду в виде:

pg_dump -h localhost -U postgres bkp1 > D:\Backup\bkp1.sql

Важно! В среде Windows не используйте перенаправление для выгрузки и загрузки резервных копий!

Мы неоднократно сталкивались с тем, что выгруженные через перенаправление копии внешне выглядели вполне нормально, в том числе и копии в текстовом формате, но при восстановлении отказывались загружаться. Это происходит не всегда и не везде, но для исключения подобных ситуаций перенаправление в Windows использовать не следует.

Теперь создадим выгрузку в формате custom:

pg_dump -h localhost -U postgres -Fc -f D:\Backup\bkp1.dump bkp1

Здесь у нас добавился еще один ключ, указывающий на формат выгрузки, если вы хотите выгрузить в формате tar, просто замените -Fc на -Ft.

И, наконец, в формате директории:

pg_dump -h localhost -U postgres -Fd -f D:\Backup\bkp1_dir -j 4 bkp1

Для данного формата у нас появился еще один ключ, указывающий число потоков выгрузки. Число потоков не должно превышать количество ядер процессора, но не все так просто: pg_dump спокойно нагрузит каждый поток, создав 100% загрузку ядер процессора, но сможет ли его принять устройство хранения? Может получиться так, что скорость записи на накопитель окажется бутылочным горлышком и вместо ускорения вы получите замедление как выгрузки дампа, так и всей системы вообще.

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

Еще одна тонкость, что будет если указанный файл выгрузки существует? Форматы, custom и tar молча перезапишут его. При выгрузке в формате directory вы получите сообщение, что целевая директория не пуста и выгрузка выполнена не будет. Это следует учитывать при написании скриптов, потому как при ошибке вы либо останетесь без старых копий, либо не будут создаваться новые. Хотя визуально все будет нормально.

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

Восстановление базы данных из резервной копии

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

Итак, прежде всего удалим старую базу:

dropdb -h localhost -U postgres -i bkp1

В приведенной команде мы использовали ключ -i который запросит интерактивное подтверждение действия:

База данных "bkp1" будет удалена навсегда.
Продолжить? (y/n)

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

А теперь создадим новую:

createdb -h localhost -U postgres -T template0 bkp1

Ключ -T указывает использовать при создании базы полностью пустой шаблон template0. Если вы хотите выполнить восстановление в отдельную новую базу, то просто создайте ее приведенной выше командой.

Начнем восстановление с формата plain, так как это не дамп, а набор SQL команд, то для их исполнения мы будем использовать утилиту psql:

psql -h localhost -U postgres -d bkp1 D:\Backup\bkp1.sql

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

Для остальных форматов следует использовать утилиту pg_restore, например восстановим дамп формата custom, в два потока:

pg_restore -h localhost -U postgres -d bkp1 -j 2 D:\Backup\bkp1.dump

Синтаксис тот же самый, просто указываем базу и файл, с форматом утилита разберется самостоятельно. Если же вы попытаетесь подсунуть ей формат plain, то утилита откажется делать загрузку и любезно посоветует вам использовать для этого psql.

Что касается выбора количества потоков, то исходим из тех же соображений: один поток - одно ядро и обязательно тестируем, чтобы производительность диска не стала узким горлышком. Если вы выполняете восстановление в рабочее время, то учтите также влияние на работу пользователей, чтобы не вышло что вы запустили восстановление, а у всех остальных все стало.

При восстановлении из формата directory вместо файла укажите путь к папке:

pg_restore -h localhost -U postgres -d bkp1 -j 2 D:\Backup\bkp1_dir

Напоминаем, что формат tar многопоточную загрузку не поддерживает, но вы можете его распаковать и загрузить многопоточно в формате directory.

Также есть способ несколько упростить себе жизнь, чтобы не удалять и не создавать заново базу данных мы можем выполнить:

pg_restore -h localhost -U postgres -d postgres -C -c  D:\Backup\bkp1.dump

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

Однако использовать эту команду следует очень осторожно и только на том сервере, откуда был сделан дамп. Также убедитесь, что вы взяли именно тот дамп, что надо. Но мы не рекомендуем так делать, особенно на продуктовых серверах, лучше осознанно удалите базу руками.

Записки IT специалиста