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

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

Примеры создания резервной копии базы данных с помощью pg_dump

Для создания архива с бекапом базы данных можем воспользоваться следующей командой:

pg_dump --username "user_name" -d "database_name" -h "127.0.0.1" --port "5432" --password --format=c --blobs > "/path/to/backup/filename.dump"

• --username или -U – имя пользователя, под которым производится подключение.
• -d или --dbname – указывает имя базы данных для подключения. Равнозначно указанию имя_бд в первом аргументе, не являющемся ключом, в командной строке.
• -h или --host – указывает имя компьютера, на котором работает сервер. Если значение начинается с косой черты, оно определяет каталог Unix-сокета. Значение по умолчанию берётся из переменной окружения PGHOST, если она установлена. В противном случае выполняется подключение к Unix-сокету.
• -p или --port – указывает TCP-порт или расширение файла локального Unix-сокета, через который сервер принимает подключения. Значение по умолчанию определяется переменной окружения PGPORT, если она установлена, либо числом, заданным при компиляции.
• --password или -W – принудительно запрашивать пароль перед подключением к базе данных.
• --format или -F – указывает формат вывода копии. c или custom - выгрузить данные в специальном архивном формате, пригодном для дальнейшего использования утилитой pg_restore. p или plain - сформировать текстовый SQL-скрипт. Это поведение по умолчанию. Кроме того, есть еще 2 формата directory и tar
• --blobs или -b – включить большие объекты в выгрузку. Это поведение по умолчанию при отсутствии ключей --schema, --table или --schema-only. Таким образом, ключ -b полезен, лишь когда нужно добавить большие объекты при выгрузке только избранной схемы или таблицы. Заметьте, что большие объекты относятся к данным, и поэтому будут выгружаться, когда используется ключ --data-only, но не ключ --schema-only.

Если требуется создавать резервные копии в автоматическом режиме, например через cron, можно указать флаг --no-password например вот так:

pg_dump --username "user_name" -d "database_name" -h "127.0.0.1" --port "5432" --no-password --format=p --verbose > "/path/to/backup/filename.sql"

• --no-password или -w – не выдавать запрос на ввод пароля. Если сервер требует аутентификацию по паролю и пароль не доступен с помощью других средств, таких как файл .pgpass, попытка соединения не удастся. Этот параметр может быть полезен в пакетных заданиях и скриптах, где нет пользователя, который вводит пароль.
• --verbose или -v – включить подробный режим.

Для восстановления базы данных при выгрузке в текстовом формате утилита pg_restore не потребуется.

Примеры восстановления базы данных из резервной копии с помощью pg_restore

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

pg_restore --username "user_name" -d "database_name" -h "127.0.0.1" --port "5432" --password --format=c "/path/to/backup/filename.dump"

• --username или -U – имя пользователя, под которым производится подключение.
• -d или --dbname – подключиться к базе данных имя_базы и восстановить данные непосредственно в неё.
• -h или --host – указывает имя компьютера, на котором работает сервер. Если значение начинается с косой черты, оно определяет каталог Unix-сокета. Значение по умолчанию берётся из переменной окружения PGHOST, если она установлена. В противном случае выполняется подключение к Unix-сокету.
• -p или --port – указывает TCP-порт или расширение файла локального Unix-сокета, через который сервер принимает подключения. Значение по умолчанию определяется переменной окружения PGPORT, если она установлена, либо числом, заданным при компиляции.
• --password или -W – принудительно запрашивать пароль перед подключением к базе данных.
• --format или -F – задаёт формат архива. Указывать формат необязательно, так как pg_restore определяет формат автоматически.

Восстановление базы без указания владельца в несколько потоков:

pg_restore -d "database_name" --format=c --jobs=2 --no-owner --verbose "/path/to/backup/filename.dump"

• --jobs или -j – Запустить наиболее длительные операции pg_restore (в частности, загрузку данных, создание индексов или ограничений) в нескольких параллельных заданиях. Это позволяет кардинально сократить время восстановления большой базы данных, когда сервер работает на многопроцессорном компьютере.
• --no-owner или -O – не генерировать команды, устанавливающие владение объектами, как в исходной базе данных. По умолчанию, pg_restore генерирует команды ALTER OWNER или SET SESSION AUTHORIZATION, восстанавливающие исходных владельцев создаваемых элементов схемы. Однако эти команды можно будет выполнить, только если к базе данных первоначально подключается суперпользователь (или пользователь, владеющими всеми объектами в скрипте). Чтобы получить скрипт, который сможет восстановить любой подключающийся пользователь (но при этом он станет владельцем всех созданных объектов), используется -O.
• --verbose или -v – включить подробный режим.

Более подробно со вписком всех аргументов можно ознакомиться с помощью флага --help или на сайте postgrespro.ru