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

Содержание

  1. Содержание
  2. Команды для резервного копирования и восстановления
  3. Скрипт для периодического логического резервного копирования
  4. Скрипт для периодического физического резервного копирования
  5. Использованные материалы

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

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

pg_dumpall > savedfile.sql

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

psql postgres -f savedfile.sql

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

pg_dump -d dname > savedfile.sql

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

psql dbname -f savedfile.sql

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

pg_dump -d dname -t table1 -t table2 -t table3 > savedfile.sql

Резервное копирование только пользователей:

pg_dumpall -g > users.sql

Резервное копирование и восстановление базы данных под другим владельцем:

pg_dump -d database --no-owner > savedfile.sql

psql -U newowner dbname -f savedfile.sql

Физическая резервная копия в каталог:

pg_basebackup -D /destination/path -Pv --checkpoint=fast

Восстановление физической резервной копии из каталога:

chown postgres:postgres -R /destination/path
chmod 700 -R /destination/path

Физическая резервная копия в tar-файлы:

pg_basebackup -D /destination/path -Pv --checkpoint=fast -F t

Будут сформированы файлы base.tar и pg_wal.tar

Восстановление из физической резервной копии:

Распаковываем файл base.tar в каталог с файлами баз данных, затем распаковываем файл pg_wal.tar в подкаталог pg_wal.

Физическая резервная копия в один сжатый tar-файл:

pg_basebackup -D /destination/path -Pv --checkpoint=fast -F t -X stream -z

Скрипт для периодического логического резервного копирования

Для автоматизации резервного копирования всех баз данных я написал скрипт pgbackup.sh. Скрипт формирует в каталоге для резервных копий файлы с именами вида dbname.YYYYmmdd.sql.bz2, где dbname - имя базы данных, YYYYmmdd - текущая дата. Кроме резервных копий баз данных скрипт также формирует резервные копии файлов конфигурации /etc/postgresql/15/main/postgresql.conf и /etc/postgresql/15/main/pg_hba.conf. Резервные копии этих файлов сохраняются вместе с файлами резервных копий баз данных под именами postgresql.YYYYmmdd.conf и pg_hba.YYYYmmdd.conf. Дополнительно в файл global.YYYYmmdd.sql выгружаются команды для создания пользователей. Также имеется возможность заливать только что сделанные резервные копии на удалённый сервер по SSH.

Настроить скрипт можно с помощью файла конфигурации /etc/pgbackup.conf, пример которого приведён ниже:

BACKUPS_PATH=/backups/
DAYS=1

RUSER=archive
RSERVER=archive.server.tld
RPATH=/archive/postgresql.core/
RKEY=/root/.ssh/id_ed25519

Где:

  • BACKUP_PATH - каталог для резервных копий баз данных,
  • RUSER - имя пользователя на удалённом сервере,
  • RSERVER - доменное имя или IP-адрес удалённого сервера, на который нужно скопировать архив с резервной копией,
  • RPATH - путь к файлу архива на удалённом сервере,
  • RKEY - путь к приватному ключу пользователя на удалённом сервере для подключения по SSH,
  • DAYS - сколько архивов с резервной копией необходимо сохранять локально.

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

# crontab -e

И добавить в список задач время запуска скрипта и путь к скрипту, в данном случае скрипт /usr/local/bin/pgbackup.sh будет запускаться ежесуточно ровно в 4 часа утра:

20       4       *       *       *       /usr/local/bin/pgbackup.sh

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

Скрипт для периодического физического резервного копирования

Для автоматизации резервного копирования с помощью утилиты pg_basebackup я написал скрипт pgbasebackup.sh. Для его работы понадобится установить в систему пакет с утилитой pigz для сжатия архива в несколько параллельных потоков:

# apt-get install pigz

Настроить скрипт можно с помощью файла конфигурации /etc/pgbasebackup.conf, пример которого приведён ниже:

BACKUP_PATH=/backup/

PIGZ_OPTIONS="-p8"

RUSER=archive
RSERVER=archive.server.tld
RPATH=/archive/postgresql.core.tgz
RKEY=/var/lib/postgresql/.ssh/id_ed25519

DAYS=0

Как можно заметить, настройки практически полностью совпадают с настройками скрипта для логического резервного копирования. По сравнению с ним, в этом файле конфигурации появилась только одна новая опция:

  • PIGZ_OPTIONS - опции командной строки для утилиты сжатия pigz.

Внутри каталога, заданного переменной BACKUP_PATH, создаётся подкаталог db с резервной копией базы данных. Архив с резервной копией отправляется на удалённый сервер только если определены все четыре опции RSERVER, RPATH, RUSER, RKEY и только с сервера-реплики.

Опция DAYS используется только в том случае, если архив с резервной копией не отправляется на удалённый сервер. В этом случае в каталоге BACKUP_PATH поддерживается указанное в переменной количество архивов с содержимым каталога db, имеющих имена вида db_YYYYMMDD.tbz, где символы YYYY соответствуют году, MM - номеру месяца, DD - числу месяца создания архива. Впрочем, логику работы скрипта можно поменять, отредактировав его.

Использованные материалы