В моем блоге уже был цикл статей с конспектом по MySQL. Эта одна из публикаций цикла конспектов по PostgreSQL. Материал этих публикаций не особо структурирован и представляет собой просто мои заметки – конспектировал что-то новое и то, что я посчитал интересным и важным. Эта публикация некий конспект про резервное копирование и восстановление PostgreSQL.
Список литературы
При подготовке этого конспекта я использовал следующие материалы:
1. Книга PostgreSQL 14 Administration Cookbook.
2. Курс DBA1: Администрирование PostgreSQL 13. Базовый курс.
Резервное копирование и восстановление
В терминологии PostgreSQL журнал транзакций называется Write-Ahead Log (WAL). Туда сервер PostgreSQL записывает всю информацию по тем изменения, которое произошли с данными. Это позволяет выполнить восстановление в случае сбоев. Физически журналы транзакций представляют собой файлы в директории pg_wal, которая в свою очередь расположена в директории с данными PostgreSQL.
В WAL изменения попадают не мгновенно. Они записываются специальным фоновым процессом. В случае сбоя восстановление начнется с определенной точки восстановления (ckeckpoint). Именно эта точка и определяет – сколько транзакций необходимы выполнить для приведения базы данных в согласованное состояние.
При высоких нагрузках ckeckpoint может оказывать некоторое влияние на производительность сервера. Два параметра в конфигурационном файле определяют частоту работу механизма ckeckpoint: checkpoint_timeout и max_wal_size. Первый задает количество секунд, которые должны пройти для повторного запуска механизма ckeckpoint, второй параметр определяет объем данных, который должен быть записан в базу данных, чтобы инициировать механизм ckeckpoint. Каждый раз, когда какой-то из этих двух лимитов превышает свое значение запускается механизм ckeckpoint.
Создание горячей резервной копии базы данных
Команда для создания горячей резервной копии базы данных app:
sudo -u postgres pg_dump -F c -f /data/app.sql appИз особенностей отчмечу параметр, который задает формат резервной копии – параметр -F. Значение “c” задает произвольный формат, который, во-первых, сжимается, а во-вторых, может быть использован утилитой pg_restore для восстановления отдельных объектов.
Необходимо учитывать, что pg_dump не создает резервную копию ролей и групп, а также пространств имен. Чтобы включить в резервную копию роли, группы и пространства имен используйте утилиту pg_dumpall.
Создание горячей резервной копии всех баз данных
Для создания горячей резервной копии всех баз данных повторите шаги из предыдущего раздела для каждой из баз данных.
В дополнении выполните резервную копию ролей, групп и пространства имен утилитой pg_dumpall:
sudo -u postgres pg_dumpall -g -f /data/global.sqlРезервная копия определения данных
Пример создания резервной копии только схемы данных без непосредственно данных :
sudo -u postgres pg_dumpall --schema-only > /data/global.sqlРезервная копия ролей:
sudo -u postgres pg_dumpall --roles-only > /data/roles.sqlРезервная копия табличных пространств:
sudo -u postgres pg_dumpall --tablespaces-only > /data/tablespaces.sqlПример создания резервной копии ролей, групп и табличных пространств:
sudo -u postgres pg_dumpall --globals-only > /data/globals.sqlСоздание физической резервной копии
Все предыдущие варианты создания резервной копии были логическими, т.к. в виде набора инструкций. Но есть еще и другой вариант резервного копирования – физический, т.е. создание резервной копии непосредственно всех файлов из директории с данными PostgreSQL.
Пример создания физической резервной копии данных:
sudo -u postgres pg_basebackup -D /data/backup -c fast -P -R
Для полного восстановления сервера из резервной копии дополнительно не забудьте сделать резервную копию конфигурационных файлов. Для Ubuntu сервер они находятся в директории /etc/postgresql
Физическая резервная копия с помощью Barman
Установка пакета Barman на Ubuntu Server:
sudo apt install barmanУстановка на Barman RHEL (Rocky Linux 9.3):
dnf install epel-release
dnf install barmanПример создания физической резервной копии через Barman:
1. Включить компрессию в конфигурационном файле – /etc/barman.conf (/etc/barman/barman.conf для RHEL)
compression = gzip
2. Пример конфигурационного файла для резервного копирования локального или удаленного сервера PostgreSQL:
sudo nano /etc/barman.d/pg02.conf[PG02]
description = "PostgreSQL database on PG02"
active = off
backup_method = postgres
archiver = off
streaming_archiver = on
slot_name = "barman_backup1"
conninfo = "host=10.10.10.33 dbname=postgres user=roman"
streaming_conninfo = "host=10.10.10.33 dbname=postgres user=roman"
3. Проверить сервер в списке:
barman list-server
4. Инициализация директории:
barman check db1
Barman увидел новый сервер в списке, но есть ряд ошибок. Если код завершения команды выше равен нулю, то пока беспокоиться не о чем:
echo $?
5. Активируем сервер в файле pg02.conf:
active = on
6. Добавляем слот репликации:
barman receive-wal --create-slot PG02
7. Переключаемся на следующий WAL файл:
barman switch-wal PG02
8. Выполняем повторную проверку. Все должно быть ОК:
barman check PG02
Единственное исключение – это строка “WAL archive: FAILED (please make sure WAL shipping is setup)”. Подождите пару минут пока отработает задание в cron для Barman. После этого ошибки быть не должно:
9. Запуск полной резервной копии:
barman backup PG02root@pg01:/home/roman# barman backup PG02
Starting backup using postgres method for server PG02 in /var/lib/barman/PG02/base/20240229T215120
Backup start at LSN: 0/2000148 (000000010000000000000002, 00000148)
Starting backup copy via pg_basebackup for 20240229T215120
Copy done (time: 30 seconds)
Finalising the backup.
This is the first backup for server PG02
WAL segments preceding the current backup have been found:
000000010000000000000001 from server PG02 has been removed
Backup size: 23.5 MiB
Backup end at LSN: 0/4000060 (000000010000000000000004, 00000060)
Backup completed (start time: 2024-02-29 21:51:20.054168, elapsed time: 30 seconds)
Processing xlog segments from streaming for PG02
000000010000000000000002
000000010000000000000003
WARNING: IMPORTANT: this backup is classified as WAITING_FOR_WALS, meaning that Barman has not received yet all the required WAL files for the backup consistency.
This is a common behaviour in concurrent backup scenarios, and Barman automatically set the backup as DONE once all the required WAL files have been archived.
Hint: execute the backup command with '--wait'
root@pg01:/home/roman# 
Последнее предупреждение говорит о том, что некоторый WAL журналы еще не получены, но будут доставлены на сервер позднее. Вы можете добавить опцию –wait. Тогда команда создания физической копии завершится только после доставки на сервер всех журналов WAL.
Посмотреть список резервных копий:
barman list-backup db1
При необходимости можно посмотреть более детальную информацию по резервной копии:
barman show-backup PG02 20240229T215120root@pg01:/home/roman# barman show-backup PG02 20240229T215120
Backup 20240229T215120:
Server Name : PG02
System Id : 7341033371079429721
Status : DONE
PostgreSQL Version : 130014
PGDATA directory : /var/lib/pgsql/data
Base backup information:
Disk usage : 23.5 MiB (23.5 MiB with WALs)
Incremental size : 23.5 MiB (-0.00%)
Timeline : 1
Begin WAL : 000000010000000000000003
End WAL : 000000010000000000000004
WAL number : 2
WAL compression ratio: 99.90%
Begin time : 2024-02-29 21:51:20.039103+07:00
End time : 2024-02-29 21:51:50.584803+07:00
Copy time : 30 seconds
Estimated throughput : 788.1 KiB/s
Begin Offset : 40
End Offset : 96
Begin LSN : 0/3000028
End LSN : 0/4000060
WAL information:
No of files : 0
Disk usage : 0 B
WAL rate : 237.33/hour
Last available : 000000010000000000000004
Catalog information:
Retention Policy : not enforced
Previous Backup : - (this is the oldest base backup)
Next Backup : - (this is the latest base backup)
root@pg01:/home/roman#
Восстановление всех баз данных
Процесс восстановления различается в зависимости от того, какой тип резервного копирования вы использовали – логический или физический. В разделах ниже я приведу наиболее распространенные варианты восстановления из резервных копий.
Восстановление логической резервной копии, созданной через pg_dump -F c
Обновить список глобальных объектов:
sudo -u postgres psql -f /data/globals.sqlВосстановить базу данных:
sudo -u postgres pg_restore -C -d app -j 4 /data/app.sqlПараметр -j задает количество потоков, которые будут использоваться в процессе восстановления объекта базы данных.
Восстановление логической резервной копии, созданной через pg_dump -F p
Если выполнялась логическая резервная копия в текстовом формате (параметр -F p), то процесс восстановления будет немного отличаться.
Обновить список глобальных объектов:
sudo -u postgres psql -f /data/globals.sqlВосстановить базу данных:
sudo -u postgres psql -f /data/app_plain.sqlВосстановление логической резервной копии, созданной через pg_dumpall
Восстановить базу данных:
sudo -u postgres psql -f /data/global.sqlВосстановление физической резервной копии, созданной через pg_basebackup
При таком подходе восстанавливаются абсолютно все данные без возможности выбора отдельных объектов для восстановления.
1. Остановить службу сервера:
sudo systemctl stop postgresql2. Восстановить все файлы из директории с физической резервной копией на в директорию с данными.
sudo cp -r /data/backup/* /var/lib/postgresql/12/main/3. Убедиться, что у всех файлов установлен правильный владелец:
sudo chown -R postgres:postgres /var/lib/postgresql/12/main
sudo chmod -R 750 /var/lib/postgresql/12/main4. Проверить нужная ли это нам резервная копия:
sudo cat /var/lib/postgresql/12/main/backup_labelroot@pg01:/home/roman# cat /var/lib/postgresql/12/main/backup_label
START WAL LOCATION: 0/10000028 (file 000000010000000000000010)
CHECKPOINT LOCATION: 0/10000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2024-02-29 08:19:29 UTC
LABEL: pg_basebackup base backup
START TIMELINE: 1
5. Запустить службу сервера:
sudo systemctl stop postgresql6. Проверить подключение к серверу:
sudo -u postgres psql
Восстановление физической резервной копии, созданной через Barman
1. Остановить службу сервера:
sudo systemctl stop postgresql2. Удаляем все файлы из директории с данными:
sudo rm -rf /var/lib/pgsql/data/3. На целевом сервере (который будем восстанавливать) укажем пароль для пользователя postgres:
sudo passwd postgres[root@pg02 roman]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@pg02 roman]#4. Подготовим сервер с Barman (PG01), чтобы пользователь barman на PG01 мог удаленно подключаться по SSH на хост PG02 под пользователем postgres.
sudo su - barman
ssh-keygen
ssh-copy-id postgres@10.10.10.33root@pg01:/home/roman# su - barman
barman@pg01:~$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/barman/.ssh/id_rsa):
Created directory '/var/lib/barman/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/barman/.ssh/id_rsa
Your public key has been saved in /var/lib/barman/.ssh/id_rsa.pub
The key fingerprint is:
SHA256:aNSsDFUoYfoq1O6moy8aS7zd7sKEQsUtnti2M8/bn3Y barman@pg01
The key's randomart image is:
+---[RSA 3072]----+
| . +..o. |
| *.o.o |
| * +.. o |
| o.* + o |
|..o.o = S |
|+..* . |
|+o+.= |
|o=++.o. ..E |
|*==o+=..oo. |
+----[SHA256]-----+
barman@pg01:~$ ssh-copy-id postgres@10.10.10.33
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/barman/.ssh/id_rsa.pub"
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@10.10.10.33's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'postgres@10.10.10.33'"
and check to make sure that only the key(s) you wanted were added.
barman@pg01:~$ 5. Инициировать процедуру восстановления данных:
barman recover PG02 last /var/lib/pgsql/data --remote-ssh-command 'ssh postgres@10.10.10.33'root@pg01:/home/roman# barman recover PG02 last /var/lib/pgsql/data --remote-ssh-command 'ssh postgres@10.10.10.33'
Starting remote restore for server PG02 using backup 20240229T215120
Destination directory: /var/lib/pgsql/data
Remote command: ssh postgres@10.10.10.33
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.
Recovery completed (start time: 2024-02-29 22:15:54.931657, elapsed time: 4 seconds)
Your PostgreSQL server has been successfully prepared for recovery!
root@pg01:/home/roman#
6. Запустить службу сервера:
sudo systemctl start postgresql7. Подключаемся к серверу, который мы с вами восстанавливали (PG02) и проверяем наличие всех баз данных и других объектов:
sudo -u postgres psql
Восстановление на конкретный момент времени через Barman
Преимущество физической резервной копии заключается в том, что можно выполнить восстановление на какой-то определенный момент времени.
Все действия выполняются на сервер с Barman (PG01). Пример восстановления через Barman на конкретную точку времени во временную папку:
sudo mkdir /var/tmp/recovery
sudo chown barman:barman /var/tmp/recovery
barman recover PG02 last /var/tmp/recovery --get-wal --target-time '2024-02-29 21:50:00'root@pg01:/home/roman# barman recover PG02 last /var/tmp/recovery --get-wal --target-time '2024-02-29 21:52:00'
Starting local restore for server PG02 using backup 20240229T215120
Destination directory: /var/tmp/recovery
Doing PITR. Recovery target time: '2024-02-29 21:52:00+07:00'
Copying the base backup.
Generating recovery configuration
Identify dangerous settings in destination directory.
WARNING: 'get-wal' is in the specified 'recovery_options'.
Before you start up the PostgreSQL server, please review the postgresql.auto.conf file
inside the target directory. Make sure that 'restore_command' can be executed by the PostgreSQL user.
Recovery completed (start time: 2024-02-29 22:25:13.146035, elapsed time: less than one second)
Your PostgreSQL server has been successfully prepared for recovery!
root@pg01:/home/roman#
Восстановление таблицы
PostgreSQL поддерживает восстановление отдельных объектов. Например, восстановление таблиц.
Восстановление из логической резервной копии, созданной через pg_dump -F c
Пример восстановления таблицы:
sudo -u postgres pg_restore -d app -t t /data/app.sqlДля увеличения количества потоков вы можете использовать опцию -j.
Восстановление из логической резервной копии, созданной через pg_dump -F p
Процесс восстановления таблицы при таком сценарии потребует несколько шагов:
1. Восстановить резервную копию на тестовом сервер на тестовом сервере:
sudo -u postgres psql -f /data/app_plain.sql2. Создать резервную копию нужной таблицы:
sudo -u postgres pg_dump -F c -f /data/t.sql app3. Восстановить таблицу на исходном сервере:
sudo -u postgres pg_restore -d app -t t /data/t.sqlВосстановление из физической резервной копии
Процесс восстановления таблицы из физической резервной копии чем-то похож на процесс восстановления таблицы из логической резервной копии, созданной чере pg_dump -F p и включает в себя следующие шаги:
2. Восстановить полную физическую копию втором тестовом сервере, как указано в разделе “Восстановление физической резервной копии, созданной через pg_basebackup”.
2. Забрать нужную таблицу:
sudo -u postgres pg_dump -F c -f /data/t.sql app3. Восстановить таблицу на исходном сервере:
sudo -u postgres pg_restore -d app -t t /data/t.sqlВосстановление базы данных
PostgreSQL также поддерживает восстановление отдельных баз данных.
Восстановление из логической резервной копии, созданной через pg_dump -F c
Команда:
sudo -u postgres createdb app
sudo -u postgres pg_restore --create -d app -j 4 /data/app.sqlВосстановление из логической резервной копии, созданной через pg_dumpall
Процесс восстановления базы данных при использовании такого сценария состоит из нескольких шагов:
1. Восстановить полную логическую резервную копию на втором тестовом сервере:
sudo -u postgres psql -f /data/global.sql2. Сделать копию нужно базы данных.
sudo -u postgres pg_dump -F c -f /data/app.sql app3. Восстановить базу данных на исходном сервере.
sudo -u postgres createdb app
sudo -u postgres pg_restore --create -d app -j 4 /data/app.sqlВосстановление из физической резервной копии
Процесс восстановления выглядит следующим образом:
1. Восстановить полную физическую резервную копию на тестовый сервер, как указано в разделе “Восстановление физической резервной копии, созданной через pg_basebackup” выше.
2. Создать резервную копию базы данных:
sudo -u postgres pg_dump -F c -f /data/app.sql app3. Восстановить базу данных на исходном сервере.
sudo -u postgres createdb app
sudo -u postgres pg_restore --create -d app -j 4 /data/app.sqlУлучшение производительности операций логического резервного копирования и восстановления
Вы можете использовать опцию -j для увеличения количества потоков, которые будут использоваться в процессе создания резервной копии. Однако, при этом необходимо выбрать формат “directory” (-F d) при использовании утилиты pg_dump.
Вы также можете использовать опцию -j для увеличения количества потоков при восстановлении объектов через утилиту pg_restore. Причем эта опция доступна для резервных копий, который были созданы через pg_dump с параметрами -F c и -F d, но не -F p.
При увеличении количества потоков необходимо соблюдать баланс, чтобы не отдать все ресурсы сервера процессу восстановления объектов. При условии, что на сервер обрабатывает еще и какую-то другую нагрузку и запросы от клиентов.
Вы можете увеличить производительность утилит psql или pg_restore указан значение параметра maintenance_work_mem = 128MB или более высокое значений. Увеличить значение параметра можно в конфигурационном файле postgresql.conf или в текущей сесии пользователя.
Также для увеличения скорости восстановления можно указать для параметра wal_buffers значение 16-64 МБ, а для параметра max_wal_size указать значение, например, 20 ГБ. Не забудьте вернуть эти параметры к прежним значения после завершения процесса восстановления.
Улучшение производительности операций физического резервного копирования и восстановления
Наиболее часто ограничителями в производительности физической резервной копии становятся производительность дисковой подсистемы или производительность сети. Как правило, использование более 4-х потоков лишь незначительно повышает скорость создания резервной копии или восстановление из неё. Использование 4-х потоков позволяет увеличить производительность операций резервного копирования или восстановления на 40-60%.
Для указания числа потоков используйте параметр -j, например, при создании резервной копии через Barman:
barman -j 4 backup PG02Пример использования нескольких потоков в Barman при операции восстановления:
barman -j 4 recover PG02 last /var/lib/pgsql/14/data --remote-ssh-command 'ssh postgres@PG02'Проверка целостности резервных копий
Проверить – включена ли опция в клиенте psql можно следующей командой:
SHOW data_checksums;
В примере выше проверка целостности включена.
Если проверка целостности не включена, то включить ей можно выполнив следующие действия:
1. Остановить службу PostgreSQL:
sudo systemctl stop postgresql2. Включить проверку целостности, указав директорию с данными сервера PostgreSQL:
sudo /usr/lib/postgresql/12/bin/pg_checksums -D /var/lib/postgresql/12/main/ -e
3. Остановить службу PostgreSQL:sudo systemctl start postgresqlПосле включения проверки целостности при выполнении резервной копии через pg_basebackup будет выполняться проверка целостности контрольной суммы страниц с данными.
Другие связанные статьи
PostgreSQL. Часть 1. Информационные запросы PostgreSQL
PostgreSQL. Часть 2. Нюансы настройки сервера PostgreSQL
PostgreSQL. Часть 3. Управление сервером PostgreSQL
PostgreSQL. Часть 4. Безопасность в PostgreSQL
PostgreSQL. Часть 5. Мониторинг и диагностика PostgreSQL
PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL
PostgreSQL. Часть 7. Производительность и конкурентный доступ PostgreSQL