Резервирование и восстановление 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 айди)
Если всё так то тест пройден успешно 

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

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)

Тестирование 

настройка окружения для начала тестирования 

Установка 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 
Установите и запустите две службы постгрес(14 и 17 версия) у себя локально(тут инструкций не будет смотрите команды под вашу ось)

Тестирование с pg_backrest

1. Открываем dbeaver 
2. Открываем sql консоль Postgresql 14 на линукс
3. Выполняем show data_directory; запоминаем путь на будущее 
4. Выполняем команду show config_file;
5. Ищем этот файл и редактируем его следующим образом: 
   

   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
   Эти строчки нужно найти раскомментить и задать эти значения 

   
   
6. После этого запускаем скрипт: sudo ./pgBackRestLinux.sh
   
7. Проверяем что скрипт выполнился успешно: 
   Смотрим логи в консоли - должно быть примерно так:
   

   

   
   Проверяем рабочую директорию var/lib/pgbackrest
   Там должен быть первый полный бекап и вал файлы в /archive
8. Скачиваем директорию pgbackrest с диска - https://disk.360.yandex.ru/client/disk/Эволюция%20технологий%20-%20общая/Софт/Резервирование%20и%20восстановление%20Postgres/Linux/pg_backRest *Это бекап с нашего vps 
9. Выключаем службу постгрес (

   Найдите сервисы командой systemctl list-units | grep postgresql (например, postgresql@14-main).

   Затем sudo systemctl stop <имя_сервиса>, например, sudo systemctl stop postgresql@14-main.

   )
10. Чистим содержимое директории кластера - найти эту директорию мы нашли на одном из первых шагов sql командой show data_directory; * Удалите всё содержимое папки но не саму папку 
    
11. Директорию var/lib/pgbackrest заменяете той что скачали с диска (директорию не удаляйте лучше переименуйте её для безопасности и положите на том же пути ту что скачали)
12. Запускайте команду 

    sudo -u postgres pgbackrest --stanza=main restore

13. Выполните sudo systemctl start postgresql также для конкретного тестируемого постгреса(см выше)

14. sudo systemctl status postgresql - проверьте что статус активен 
15. В Dbeaver проверьте данные таблицы sgtins - айди должны быть примерно в таком диапазоне 

    188892 - 871338 - это значит что сейчас у вас данные которые были в бд на vps на момент когда мы сделали там бекап 

16. Выполните ещё два раза отдельно команду 
    sudo -u postgres pgbackrest --stanza=main --type=full backup

    убедитесь в том, что вал файлы, полный бекап и связанные с ним дифференциальные и инкрементальные бекапы очистились(посмотрите по времени создания файлов и папок - не должно быть ничего старше времени старшего бекапа который остался в памяти)
    
    
    
    
17. Выполните всё то же самое для службы постгрес 17 на linux но измените в скрипте переменную 
    REPO_PATH="/var/lib/pgbackrest"

    допустим на 

    REPO_PATH="/var/lib/pgbackrest2"

    
    
18. После восстановления вы можете увидеть ошибку в логах связанную с тем что версии отличаются
19. Выполните sudo -u postgres pgbackrest --stanza=main stanza-upgrade
20. Запустите службу постгрес 
21. Проверьте данные и очистку 
22. Заполните бд данными(запустите л3 с подключением к тестируемой бд и закажите партию Бадэ как вариант)
23. Остановите постгрес и удалите данные из директории pg_data кластера потсгрес 
24. Выполните sudo -u postgres pgbackrest --stanza=main restore \
      --type=time \
      --target="2026-02-16 14:30:00" \    * Тут меняйте на время до заполнения данных 
      --log-level-console=info
25. Запустите сервис постгрес 
26. Убедитесь что выполнилось Pitr восстановление на момент когда новых данных ещё не было в бд 

Тестирование с pg_basebackup 

1. Откройте Dbeaver 
2. Запустите sql консоль для linux postgres 14 

3. Создайте директорию /var/lib/postgresql/wal_archive на своём wsl 

4.  

   В файле конфигурации постгреса(см как найти в шагах для тестирования pg_backrest) найти и раскомментировать: 
   

   wal_level = replica

   archive_mode = on

   archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'

   max_wal_senders = 3

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

   Найдите сервисы командой systemctl list-units | grep postgresql (например, postgresql@14-main).

   Затем sudo systemctl restart <имя_сервиса>, например, sudo systemctl restart postgresql@14-main.

   )

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

   PG_HOST="localhost"

   PG_PORT="5432"

   PG_USER="backup_user" - пользователь с правами на репликацию 

   PG_PASS="22316" - его пароль 

   BACKUP_DIR="/var/lib/postgresql"    - тут будут лежать бекапы и вал файлы 

   NEW_BACKUP="${BACKUP_DIR}/current.new"

   FINAL_CURRENT="${BACKUP_DIR}/current"

   PREVIOUS_BACKUP="${BACKUP_DIR}/previous"

   LOG_FILE="${BACKUP_DIR}/logs/backup.log" - тут можно будет посмотреть логи выполнения бекапов

   WAL_ARCHIVE="${BACKUP_DIR}/wal_archive"

   PG_BIN_PATH="/usr/lib/postgresql/14/bin" - путь к бинарникам вашего кластера постгрес

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

   
   

8. Шаги выше примените для обоих кластеров(повторите для постгрес 17 на линукс) только измените BACKUP_DIR например на /var/lib/postgresql/test и соотвественно при создании директории для вал файлов(шаг 3) учитывайте именно этот путь аналогично нужно этот путь учесть в
   archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
9. Заполнить бд данными
10. Проверьте логи в терминале и структуру каталогов (см как должна выглядеть в описании работы с этим инструментом выше)
    

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

12. Удалить все данные из дирректории с данными постгреса

13. Копируйте данные из папки /var/lib/postgresql/current и вставляете в директорию данных которую мы очистили

14. Создать файл recovery.signal в дирректории с данными постгрес(просто пустой файл важно только его наличие)

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

    restore_command = 'cp /backup/pg/wal_archive/%f "%p"'

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

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

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

17. Проверьте что все данные которые вы добавили после бекапа есть в бд(это означает что постгрес прогнал цепочку wal файлов до конца и восстановил данных из них)
18. Остановите службу постгрес 
19. выполните всё те же шаги по восстановлению но вместо recovery_target_timeline = 'latest' напишите 
      recovery_target_time = '2026-02-16 14:30:00'  в postgresql.auto.conf * измените на время до заполнения данными чтобы восстановится примерно к сделанному бекапу 
20. Убедитесь в том что данные которые были сделаны до бекапа не остались в бд 
21. Остановите постгрес 17 
22. Очистите директорию данных 
23. Скопируйте в директорию данных содержимое BACKUP_DIR для 14 версии(/var/lib/postgresql/current)

24. Выполнить 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

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

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

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

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

29. Убедитесь в том что данные аналогичны тем что были у вас в постгрес 14 

Повторите всё то же самое со скриптом pg_baseBackupWindows.ps1 на виндовс (в руководстве по этому инструменту выше посмотрите примеры команд для виндовс)

Тестирование pg_dump 

Поменяйте переменные в скрипте на ваши данные: 
 

В таком виде у вас появится дамп бд 

теперь  поменяйте также параметры в скрипте и на виндовс примените ./pg_dumpScript.ps1 для postgresql 14 

Лучше чтобы бд различались по содержимому 

Теперь поменяйте дампы местами - тот что на виндовс вставьте в 

Не автоматизированное резервирование

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

Pg_dump 

*Когда вам нужно только данные из одной бд

Linux 

pg_dump -h localhost -p 5432 -U username -Fc dbname -f backup.dump

Windows 

& "C:\Program Files\PostgreSQL\16(ваша версия)\bin\pg_dump.exe" -h localhost -p 5432 -U username -Fc dbname -f "C:\backups\backup.dump"

Pg_basebackup 

Когда вам нужно сделать снимок всего кластера в случае если нужно сохранить параметры файлов конфигурации и забекапить все бд в кластере например 

Linux 
pg_basebackup -h localhost -p 5433 -U replicator -D /path/to/backup/dir -P -Fp

Windows 
& "C:\Program Files\PostgreSQL\16\bin\pg_basebackup.exe" -h localhost -p 5433 -U replicator -D "C:\backups\datadir" -P -Fp