Резервное копирование и восстановление PostgreSQL

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

PostgreSQL доступен в качестве облачной базы данных, а также его можно развернуть своими силами на VPS. Для облачных баз данных мы автоматически создаем бесплатные бэкапы БД каждые несколько дней, восстановление из которых доступно в панели управления в разделе “BackUp”.

Описанные в этой статье утилиты подойдут для настройки собственной системы резервного копирования или переноса данных между серверами.

Создание резервной копии PostgreSQL

В этом разделе мы рассмотрим, как сделать дамп базы данных PostgreSQL с помощью консольных утилит. Мы разберем синтаксис и покажем примеры использования утилит pg_dump, pg_dumpall и pg_basebackup.

pg_dump

pg_dump – это утилита для создания резервных копий PostgreSQL. Она позволяет создавать целостные резервные копии отдельных баз данных, не препятствуя работе с базой данных.

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

Для восстановления из архивных форматов файлов используется утилита pg_restore. Эти форматы позволяют указывать pg_restore, какие объекты базы данных восстановить, а также позволяют изменить порядок восстанавливаемых объектов. Архивные форматы файлов спроектированы так, чтобы их можно было переносить на другие платформы с другой архитектурой.

Синтаксис

pg_dump [параметры подключения] [параметры создания резервной копии] [имя БД]

Параметры подключения

• -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.
• -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.
• -U [name] или --username=[name]: имя пользователя.

Полезные параметры создания резервной копии

• -a или --data-only: сохраняем только данные.
• -c или --clean: добавляем в скрипт команды DROP. Может понадобиться при наличии объектов с одинаковыми именами. Применим только к SQL-скриптам.
• -C или --create: добавляем в скрипт команды для создания БД и подключения к ней. Применимо только к SQL-скриптам.
• -E кодировка или --encoding=кодировка: устанавливаем определенную кодировку дампа.
• -f [file] или --file=[file]: файл, в котором будет сохранена резервная копия.
• -F [format] или --format=[format]: формат дампа. В базе данных PostgreSQL формат может принимать следующие значения:
  • p или plain: SQL-скрипт. Значение по умолчанию.
  • c или custom: архивный формат.
  • d или directory: формат каталога.
  • t или tar: формат .tar.
• -Z [0..9] или --compress=[0..9]: выбор уровня сжатия (0 – не сжимать, 9 – максимальный).

С полным списком доступных параметров утилиты pg_dump вы можете ознакомиться в документации.

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

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

pg_dump dbname > /tmp/dbname.sql

Создание резервной копии удаленной базы данных на примере PostgreSQL в облаке:

pg_dump -h example.beget.app -p 5432 -U cloud_user default_db > /tmp/default_db.sql

Создание резервной копии отдельной таблицы локальной базы данных:

pg_dump -t example_table dbname > /tmp/dbname_example_table.sql

Создание резервной копии локальной базы данных в архивном формате:

pg_dump -Fc dbname > /tmp/dbname.bak

pg_dumpall

pg_dumpall – это утилита для создания резервных копий PostgreSQL, очень похожая на pg_dump. Она позволяет создать резервную копию всех баз данных внутри одного инстанса PostgreSQL. pg_dumpall также выгружает глобальные объекты, общие для всех баз данных, такие как роли и табличные пространства.

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

pg_dumpall может создать резервную копию только в формате SQL-скрипта.

Синтаксис

pg_dumpall [параметры подключения] [параметры создания резервной копии]

Параметры подключения

• -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.
• -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.
• -U [name] или --username=[name]: имя пользователя.

Полезные параметры создания резервной копии

• -role=[имя роли] — роль, от имени которой генерируется резервная копия.
• -a, --data-only — создание резервной копии без схемы данных.
• -c, --clean — добавление операторов DROP перед операторами CREATE.
• -g, --globals-only — выгрузка глобальных объектов без баз данных.
• -r, --roles-only — выгрузка только ролей без баз данных и табличных пространств.
• -s, --schema-only — выгрузка только схемы без самих данных.

С полным списком доступных параметров утилиты pg_dumpall вы можете ознакомиться в документации.

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

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

pg_dumpall > /tmp/dump.sql

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

pg_dumpall -h IP -p 5432 -U superuser > /tmp/dump.sql

pg_basebackup

pg_basebackup – утилита для создания бинарной резервной копии экземпляра PostgreSQL.

Бинарная резервная копия может использоваться для репликации либо восстановления на момент создания копии.

pg_basebackup создает резервную копию всего экземпляра PostgreSQL и не позволяет выбирать отдельные сущности для резервного копирования.

Подключение pg_basebackup к базе данных PostgreSQL выполняется при помощи протокола репликации с полномочиями суперпользователя или с правом REPLICATION.

Синтаксис

pg_basebackup [параметры подключения] [параметры создания резервной копии]

Параметры подключения

• -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.
• -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.
• -U [name] или --username=[name]: имя пользователя.

Полезные параметры создания резервной копии

• -D каталог, --pgdata=каталог — директория записи данных.
• -F [формат], --format=формат — формат вывода. Допустимые варианты:
  • p, plain — значение для записи выводимых данных в текстовые файлы;
  • t, tar — значение, указывающее на необходимость записи в целевую директорию в формате tar.
• -r [скорость_передачи], --max-rate=скорость_передачи — предельная скорость передачи данных в Кб/с.
• -z, --gzip — активация gzip-сжатия результирующего tar-файла.
• -Z [уровень], --compress=уровень — определение уровня сжатия механизмом gzip.
• -P, --progress — активация в вывод отчета о прогрессе.

С полным списком доступных параметров утилиты pg_basebackup вы можете ознакомиться в документации.

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

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

pg_basebackup -D /tmp/postgre_bak

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

pg_basebackup -h IP -p 5432 -U superuser -D /tmp/postgre_bak

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

В этом разделе мы разберем, как восстановить базу данных PostgreSQL из резервной копии с помощью консольных утилит. Мы разберем синтаксис и покажем примеры использования утилит psql и pg_restore.

psql

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

С помощью psql можно произвести восстановление базы данных PostgreSQL из резервной копии в формате SQL-скрипта. Для этого достаточно передать файл резеверной копии в утилиту psql при подключении к базе данных.

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

psql dbname < /tmp/dbname.sql

Восстановление удаленной базы данных на примере облачного PostgreSQL:

psql -h example.beget.app -p 5432 -U cloud_user default_db < /tmp/default_db.sql

pg_restore

pg_restore – утилита, предназначенная для восстановления базы данных из файла резервной копии в одном из архивных форматов, который создан утилитой pg_dump. При наличии файлов архивов pg_restore может восстанавливать данные избирательно или даже переупорядочить объекты перед восстановлением.

Синтаксис

pg_restore [параметры подключения] [параметры восстановления резервной копии][файл резервной копии]

Параметры подключения

• -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.
• -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.
• -U [name] или --username=[name]: имя пользователя.
• -d [имя_бд], --dbname=имя_бд: имя целевой базы данных.

Параметры восстановления резервной копии

• -a, --data-only — восстановление данных PostgreSQL без схемы.
• -c, --clean — добавление операторов DROP перед операторами CREATE.
• -I [индекс], --index=индекс — восстановление только заданного индекса.
• -l, --list — активация вывода содержимого архива.
• -L [файл-список], --use-list=файл-список — восстановление из архива элементов, перечисленных в файле-списке в соответствующем порядке.
• -n [пространство_имен], --schema=схема — восстановление объектов в указанной схеме.
• -P [имя-функции(тип-аргумента[, …])], --function=имя-функции(тип-аргумента[, …]) — восстановление только указанной функции.
• -s, --schema-only — восстановление только схемы без самих данных.
• -t [таблица], --table=таблица — восстановление определенной таблицы.
• -T [триггер], --trigger=триггер — восстановление конкретного триггера.

С полным списком доступных параметров утилиты pg_basebackup вы можете ознакомиться в документации.

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

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

pg_restore -d dbname /tmp/dbname.bak

Восстановление удаленной базы данных на примере облачного PostgreSQL:

psql -h example.beget.app -p 5432 -U cloud_user -d default_db /tmp/default_db.bak

Восстановление отдельной таблицы локальной базы данных:

pg_restore -a -t example_table -d dbname /tmp/dbname.bak

Если возникнут вопросы, напишите нам, пожалуйста, тикет из панели управления аккаунта (раздел “Помощь и поддержка”), а если вы захотите обсудить эту статью или наши продукты с коллегами по цеху и сотрудниками Бегета – ждем вас в нашем сообществе в Telegram.