Резервирование и восстановление Postgresql

PG_BACKREST

Этот инструмент делает физический снимок бд 
*Физический означает, что инструмент снимает директорию данных Postgresql, которая ФИЗИЧЕСКИ находится на диске. Из-за этого мы имеем некоторые ограничения, такие как: этот инструмент работает только на Линукс, обратная совместимость версий - можно сделать бекап 14 постгреса и восстановить на 17, но не наоборот. Также этот инструмент не является встроенной утилитой постгрес соответственно необходимо установить его на целевой сервер. В скрипте используется перезапуск службы постгрес. 

Так выглядит директория данных постгрес: 

Найти это можно выполнив: 

show data_directory в sql консоли 

Опциональные параметры скрипта: 

sudo -u postgres pgbackrest --stanza=main restore \
  --type=time \
  --target="2026-02-16 14:30:00" \
  --log-level-console=info

Обычное восстановление к последним возможным изменениям: 

sudo -u postgres pgbackrest --stanza=main restore

Если восстанавливаете бекап версии постгрес старшей чем у вас то после команды восстановления вы увидите ошибку при попытке запуска потсгрес 

Тогда выполните: sudo -u postgres pgbackrest --stanza=main stanza-upgrade

Единственное что нужно сделать перед восстановлением это остановить постгрес и удалить data_directory

До запуска этого скрипта необходимо настроить файл конфигурации постгрес таким образом:
archive_mode = on

archive_command = 'pgbackrest --stanza=main archive-push %p'

archive_timeout = 60

wal_level = replica

max_wal_senders = 10

wal_keep_size = 1GB

Эти строчки нужно найти раскомментить и задать эти значения 

Файл конфигурации можно найти выполнив show config_file в sql консоли 

Потом запускаем скрипт - этот скрипт нужно запустить один раз - он создаст недостающие репозитории и права к ним, скачает утилиту pg_backRest и поставит задачи планировщику для создания дифференциальных и полных бекапов 

Предлагаемый сценарий тестирования

На своей локальной бд(Ось должна быть Линукс, если такой нет используйте wsl или виртуалку, постгрес поставьте две версии 14 и 17 запустите две службы соответственно на разных портах) меняете файл конфигурации постгрес и применяете скрипт на обоих версиях постгрес
Затем скачиваете директорию с диска https://disk.360.yandex.ru/client/disk/Эволюция%20технологий%20-%20общая/Софт/Резервирование%20и%20восстановление%20Postgres/Linux/pg_backRest

Заменяете свою директорию с бекапами и wal архивом(переменная REPO_PATH в скрипте)на ту что скачали 

останавливаете постгрес, удаляете директории с данными и запускаете команду восстановления(для 14 версии обычную для 17 версии с upgrade дополнительно)

Папка на диске это бекап с нашего vps и после восстановления вам нужно проверить что данные соответствуют тем, что были на vps(проверьте таблицу sgtin там должно быть около 188892 - 871338 айди)
Если всё так то тест пройден успешно 

Также сделайте третий полный бекап и убедитесь в том, что вал файлы, полный бекап и связанные с ним дифференциальные и инкрементальные бекапы очистились(посмотрите по времени создания файлов и папок - не должно быть ничего старше времени старшего бекапа который остался в памяти)

Шаг первый - настройка окружения для начала тестирования 

Установка Linux Ubuntu 

wsl --install

После этого перезапустите компьютер - затем вам предложат ввести логин пароль для пользователя линукс 

Установка двух версий постгрес 

sudo apt update
sudo apt install -y curl ca-certificates gnupg
sudo install -d /usr/share/postgresql-common/pgdg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update

sudo apt install -y postgresql-14 postgresql-17

pg_lsclusters - посмотрите вывод этой команды и убедитесь что там появилось два новых кластера постгрес(см статус и порт нужно чтобы были активны и порты были разные)

Теперь нужно открыть и настроить файл параметров подключений чтобы вы могли подключится ко всем кластерам из dbeaver 

sudo nano /etc/postgresql/14/main/pg_hba.conf

# local         DATABASE  USER  METHOD  [OPTIONS]
host          all  all  0.0.0.0/0  md5

Также с sudo nano /etc/postgresql/17/main/pg_hba.conf

Далее подключайтесь к кластерам из pg_admin создавайте бд smartl3 для обоих кластеров и подключайтесь к ним из dbeaver 

Установите и запустите две службы постгрес у себя локально(тут инструкций не будет смотрите команды под вашу ось)

PG_BASEBACKUP

Этот инструмент также делает физический снимок бд и мы получаем похожие ограничения - кроссплатформенности нет, несмотря на то что этот инструмент является встроенной утилитой постгрес - это означает что его не нужно отдельно скачивать. Совместимость между версиями также только обратная. Скрипт существует в двух вариантах для Windows и Linux - это означает что мы можем настроить автоматизированное резервирование и восстановление на этих двух осях, но как уже было написано не означает что можно восстановить бекап Лиукс на винде или наоборот. Для бекапов этим инструментом необходимо создать пользователя с правами на репликацию: 

CREATE ROLE backup_user WITH REPLICATION LOGIN PASSWORD 'some_pass';

Далее создаём директорию для wal файлов - в моём случае C:\backup\pg\wal_archive(смотрите в скрипте там указано какая дирректория ему нужна для работы)

В файле конфигурации постгреса (sudo -u postgres psql -c "show config_file;") найти и раскомментировать:

wal_level = replica

archive_mode = on

archive_command = 'copy /Y "%p" "C:\\backup\\pg\\wal_archive\\%f"'

* Тут для линукс меняйте на archive_command = 'test ! -f /backup/pg/wal_archive/%f && cp %p /backup/pg/wal_archive/%f'

max_wal_senders = 3

Перезапустить сервис постгри

Проверить пути и значение переменных в script.sh и при необходимости поменять на свои

Применить скрипт

Должна по итогу получится такая структура каталогов

*Важное замечание

Версия скрипта для Виндовс во время тестирования выполнялась успешно, но ОС не принимала то как Постгрес пишет логи в терминал и поэтому отмечала их красным и кодировку тоже ломает(у меня это было из-за старой версии постгрес может у вас не будет такого) - в любом случае после выполнения скрипта что бы не было в логах проверьте папку с бекапами если там в папке  current есть бекап(директория с данными) то всё ок

Восстановление

1. Остановить сервис постгреса

2. удалить все данные из дирректории с данными постгреса(не папку только данные так как там на этой папке правильно выставленные права которые нельзя нарушить)

3. там где сохранён бекап(в стандартом варианте скрипта это C:\backup\pg\current) скопировать все данные и переместить их в директорию откуда мы удалили данные до этого

4. создать файл recovery.signal в дирректории с данными постгрес

5. в файл postgresql.auto.conf вставить строчки:

restore_command = 'copy /Y "C:\\backup\\pg\\wal_archive\\%f" "%p"'

Для линукса: restore_command = 'cp /backup/pg/wal_archive/%f "%p"'

recovery_target_timeline = 'latest'   -- Для восстановления к последним доступным в вал архиве изменениям

recovery_target_action = 'promote' - для запуска службы в случае успешного восстанолвения

6. Запустить сервис постгрес

Если восстанавливаете бекап Постгреса версии старшей чем у вас стоит то: 

Восстановление: 
1. Остановить сервис PostgreSQL

2. Удалить данные из data-директории (оставить права на папку)

3. Скопировать base backup PG14 из C:\backup\pg\current → data/

6. Выполнить pg_upgrade:
   sudo -u postgres pg_upgrade \
  --old-datadir=/backup/pg14/data \
  --new-datadir=/var/lib/pgsql/17/data \
  --old-bindir=/usr/pgsql-14/bin \
  --new-bindir=/usr/pgsql-17/bin

7. После этого в папке где запускалась команда появится analyze_new_cluster.sh который нужно запустить

8. Теперь создать recovery.signal в PG17 data/

9. Добавить в postgresql.auto.conf PG17:
   restore_command = 'copy /Y "C:\\backup\\pg\\wal_archive\\%f" "%p"'
   recovery_target_timeline = 'latest'
   recovery_target_action = 'promote'

    * recovery_target_time = '2026-02-16 14:30:00' - пример если нужно восстановиться к конкретному времени

10. Запустить PG17 сервис

*Менять настройки постгрес и перезапускать его чтобы он начал архивировать данные нужно строго после того как бд созданы в кластере(скрипт применять нужно соответственно после перезапуска) - в доке про это ни слова но у меня иначе не реботало

Для тестов задача поставлена планировщик на пол часа - делать бекап кластера раз в пол часа, это нужно менять

Предлагаемый сценарий тестирования: 

Протестировать базовое восстановление, pitr восстановление и восстановление между версиями на обеих осях(Винда и Линукс) - чётко по шагам, описанным выше просто с двумя разными скриптами

Выполните второй бекап и убедитесь что вал все вал файлы что старше нового бекапа очистились - должны остаться только лёгкие .history файлы 

PG_DUMP

Просто открываете терминал и запускаете скрипт - перезапускать постгрес, менять его настройки конфигурации не нужно, Единственный момент, что в скрипте нужно менять параметры "раз в какое время будет выполняться снимок бд"

 и "сколько мы будем хранить снимки"
RETENTION_DAYS=7
 7 дней по умолчанию потом все что старше удаляются

Восстановление выполняется update запросами поэтому во избежание ошибок рекомендуется отключить по чтобы не было обращений к бд во время восстановления

Предлагаемый сценарий тестирования

 Всего 4 скрипта - Восстановление и инициализация резервирования под две оси - протестируйте всё на локале а потом возьмите дамп с другой оси(линукса например) и восстановите на своей(винде)
Этот инструмент также обратно совместим по версиям так что восстановить дамп сделанный через pg_dump постгреса 17 версии не получится на постгрес 14 без применения pg_upgrade(см выше в описании PG_BASEBACKUP)