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

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

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

pg_dump

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

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

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

psql [имя БД] < [SQL скрипт postgresql dump database]

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

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

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

Синтаксис

pg_dump [параметры для подключения] [параметры дампа] [имя БД*] > [каталог, куда необходимо сохранить backup postgres database]

* Если не задать имя БД, то вместо него будет использоваться значение переменной окружения PGDATABASE. А если PGDATABASE не присвоено какое-либо значение, то pg_dump воспользуется именем пользователя, инициирующего утилиту.

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

• -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 может принимать следующие значения:
  • p или plain: SQL скрипт. Значение по умолчанию.
  • c или custom: архив.
  • d или directory: каталог.
  • t или tar: формат .tar
• -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 - максимальный).

pg_dumpall

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

Синтаксис

pg_dumpall [параметры для подключения] [параметры дампа] > [каталог, куда необходимо сохранить дамп]

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

• -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

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

Синтаксис

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

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

• -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]: формат дампа. Может иметь следующие значения:
  • p или plain: обычные файлы;
  • t или tar: формат .tar;
• -r [speed]или --max-rate=[speed]: задаем максимальную скорость передачи данных в Кб/с
• -Z [0..9] или --compress=[0..9]: выбираем уровня сжатия (0 - не сжимать, 9 - максимальный).

dbaas

pg_restore

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

Синтаксис

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

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

• -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

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

Загрузка и установка

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

wget https://github.com/wal-g/wal-g/releases/download/v1.1/wal-g-pg-ubuntu-20.04-amd64.tar.gz
tar -zxvf wal-g-pg-ubuntu-20.04-amd64.tar.gz -C /usr/local/bin/wal-g

Настройка

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

cat > /var/lib/postgresql/.walg.json << EOF
{
    "AWS_ENDPOINT": "https://s3.twcstorage.ru",
    "WALG_S3_PREFIX": "s3://имя_бакета",
    "AWS_ACCESS_KEY_ID": "Ключ доступа к хранилищу (логин)",
    "AWS_SECRET_ACCESS_KEY": "Секретный ключ",
    "WALG_COMPRESSION_METHOD": "Алгоритм сжатия:brotli, LZ4 или LZMA.",
    "WALG_DELTA_MAX_STEPS": "количество дельт*",
    "PGDATA": "путь к данным БД",
    "PGHOST": "имя хоста"
}
EOF

chown postgres: /var/lib/postgresql/.walg.json #меняем имя владельца скрипта на postgres

*Дельта-бэкап - это бэкап разницы между текущим состоянием системы и последним полным бэкапом. Такой подход позволяет экономить пространство в хранилище. Количество дельт - это количество таких бэкапов. 

Использование

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

su postgres -c ’/usr/local/bin/wal-g/wal-g backup_push *путь к данным БД или PGDATA*’

pgAdmin

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

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

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

Заключение

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