Дампы в PostgreSQL: резервное копирование и восстановление

Материал подготовлен командой Simple-Server для администраторов VPS и выделенных серверов. Команды и пути проверяйте на тестовой машине перед production.

Кратко о задаче

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

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

• выполнение скрипта в консольном клиенте PostgreSQL;
• выполнение такой psql-команды:

Восстановления БД с помощью архива реализуется утилитой pg_restore.

Что выбрать: скрипт или архив? Зависит от вашей БД и цели резервного копирования. Если вы хотите перенести БД на другую машину в PostgreSQL, то подойдет скрипт. Архивы же устроены таким образом, что их можно переносить на другие платформы. Помимо прочего, восстановление с помощью pg_restore предоставит вам возможность настраивать сам процесс за счет параметров утилиты.

Важно не забывать: pg_dump создает дамп только одного экземпляра БД. При наличии глобальных объектов PostgreSQL необходимо использовать утилиту pg_dumpall, речь о которой пойдет дальше.

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

• -a или --data-only: сохраняем только данные. Например, при использовании этого параметра связи между таблицами не сохраняются.
• -b или --blobs: добавляем в дамп большие объекты. Этот параметр используется по умолчанию.
• -B или --no-blobs: не сохраняем большие объекты.
• -c или --clean: добавляем в скрипт команды DROP. Может понадобится при наличии объектов с одинаковыми именами. Применим только к SQL скриптам.
• -C или --create: добавляем в скрипт команды для создания БД и подключения к ней. Применимо только к SQL скриптам.
• -E кодировка или --encoding=кодировка: устанавливаем определенную кодировку дампа.
• -f [catalog] или --file=[catalog]: каталог, куда сохраняем дамп. Параметр равнозначен указанному в синтаксисе [каталог, куда необходимо сохранить дамп БД]
• -F [format] или --format=[format]: формат дампа. В postgresql format может принимать следующие значения:

• -j [count] или --jobs=[count]: выполняем утилиту в многопоточном формате (количество потоков = [count]).

• -n [schema] или --schema=[schema]: сохраняем схемы, удовлетворяющие шаблону.

• -N [schema] или --exclude-schema=[schema]: не сохраняем схемы, удовлетворяющие шаблону.

• -o или --oids: сохраняем OID.

• -O или --no-owner: не добавляем в скрипт команды, связанные с установкой владельцев.

• -s или --schema-only: сохраняем только схемы.

• -t [schema] или --table=[schema]: сохранить таблицы, удовлетворяющие шаблону.

• -T [schema] или --exclude-table=[schema]: не сохраняем таблицы, удовлетворяющие шаблону.

• -x или --no-privileges или --no-acl: не сохраняем права доступа.

• -Z [0..9] или --compress=[0..9]: выбираем уровня сжатия (0 - не сжимать, 9 - максимальный).

• p или plain: SQL скрипт. Значение по умолчанию.

• c или custom: архив.

• d или directory: каталог.

• t или tar: формат .tar

Pg_dumpall создает бэкап целого кластера или инстанса. Результат работы утилиты - SQL скрипт. Во многих аспектах эта утилита похожа на pg_dump.

• -d [connection string] или --dbname=[connection string]: задаем строку подключения.
• -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.
• -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.
• -U [name] или --username=[name]: имя пользователя.
• -l [name] или --database=[name]: выбираем БД, через которую загрузим глобальные объекты.

• -a или --data-only: сохраняем только данные. Например, при использовании этого параметра связи между таблицами не сохраняются.
• -c или --clean: добавляем в дамп команды DROP перед CREATE. Может понадобится при наличии объектов с одинаковыми именами.
• -f [catalog] или --file=[catalog]: каталог, куда сохраняем дамп. Параметр равнозначен указанному в синтаксисе [каталог, куда необходимо сохранить дамп]
• -g или --globals-only: сохраняем только глобальные объекты.
• -o или --oids: сохранять OID.
• -O или --no-owner: не добавляем в скрипт команды, связанные с установкой владельцев.
• -r или --roles-only: сохраняем только роли.
• -s или --schema-only: сохраняем только схемы.
• -t или --tablespaces-only: сохраняем только табличные пространства.
• -x или --no-privileges или --no-acl: не сохраняем права доступа.

Pg_basebackup - это утилита для создания бэкапа всего инстанса или кластера. Результат работы - дамп в бинарном формате. Сам процесс нельзя настроить: вы сохраняете кластер (инстанс) целиком. В postgresql список пользователей, обладающих правом создания дампа с помощью pg_basebackup, ограничен. Для этого необходимо быть суперпользователем или обладать правом REPLICATION.

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

• -D [catalog] или --pgdata=[catalog]: каталог, каталог, куда сохраняем дамп.
• -F [format] или --format=[format]: формат дампа. Может иметь следующие значения:

• -r [speed]или --max-rate=[speed]: задаем максимальную скорость передачи данных в Кб/с

• -Z [0..9] или --compress=[0..9]: выбираем уровня сжатия (0 - не сжимать, 9 - максимальный).

• p или plain: обычные файлы;

• t или tar: формат .tar;

Pg_restore работает в связке с pg_dump. На вход поступает дамп в архивном формате, созданный с помощью pg_dump. При восстановлении вы можете настраивать различные параметры процесса.

• -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.

• -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.

• -U [name] или --username=[name]: имя пользователя.

• -w или --no-password: отключаем запрос пароля.

• -W или --password: принудительно включаем запрос пароля.

• --role=[name]: задаем имя роли.

• -a или --data-only: восстанавливаем только данные.

• -c или --clean: удаляем одноименные объекты перед восстановлением.

• -C или --create: создаем БД перед восстановлением.

• -d [name] или --dbname=[name]: подключаемся к [name] БД и восстанавливаем данные в неё.

• -e или --exit-on-error: завершаем восстановление в случае ошибки.

• -j [count] или --jobs=[count]: осуществляем восстановление в многопоточном режиме ([count]=количество потоков)

• -n [schema] или --schema=[schema]: восстанавливаем объекты только этой схемы.

• -N [schema] или --exclude-schema=[schema]: не восстанавливаем объекты этой схемы.

• -O или --no-owner: не восстанавливаем права на владение объектами.

• -s или --schema-only: восстанавливаем только схему

• -t таблица или --table=таблица: восстанавливаем только указанную таблицу.

• -x или --no-privileges или --no-acl: не восстанавливаем права доступа.

Wal-g - это сторонняя утилита для выгрузки дампов в хранилища и восстановления БД. Её действие распространяется не только на PostgreSQL, но и на другие СУБД. Wal-g поддерживает работу с несколькими типами хранилищ. Мы сосредоточимся на работе с S3.

Wal-g работает в linux-системах, для работы на Win 10 необходимо использовать сервисы наподобие WSL. Работа с wal-g начинается с github, где расположены файлы утилиты. На вкладке releases размещены версии для различных систем и СУБД. Для установки последней версии wal-g на Ubuntu 20.04 выполняем эти команды:

Для настройки wal-g можно воспользоваться как переменными окружения, так и конфигурационным файлом. Мы создадим конфигурационный файл для хранилища S3 по такому шаблону bash:

За создание бэкапов в wal-g отвечает функция backup_push. Её можно использовать прямо в консоли следующим образом:

pgAdmin — это утилита с графическим интерфейсом для создания дампов. Для начала работы переходим на сайт pgAdmin и загружаем подходящую версию программы:

После загрузки и установки необходимо подключиться к серверу:

Выгодные тарифы на облачные базы данных

Мы рассмотрели несколько инструментов для создания резервных копий базы данных PostgreSQL и восстановления данных из них.

Нужен сервер для практики? Арендуйте VPS/VDS в России — root-доступ, NVMe, DDoS-защита и поддержка 24/7.