Каталог решений - Резервное копирование PostgreSQL 12 и восстановление на определенный момент времени

Предисловие 

Линуксом увлекаюсь недавно, так что мои знания относительно скромные. На работе появилась задача сделать отказоустойчивую систему. Одного pg_dump раз в пару часов недостаточно, поэтому появляется идея архивировать WAL файлы и в случае краша восстанавливаться на самую ближайшую точку.

В интернете инфы мало, да есть записи вебинаров и статьи, НО либо они подходят на старую версию Postgres, либо написаны сложным языком (непонятны новичку).

Приступим

Для начала у вас должен быть установлен Postgres 12 версии и выше, возможно в 14 версии они опять что то переделают, но это будет не особо отличаться.

Создаем каталоги для WAL файлов и полного бэкапа 

  sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive
  
  sudo -H -u postgres mkdir /var/lib/postgresql/db_file_backup

Переходим в конфиг Postgres`a 

  sudo nano /etc/postgresql/12/main/postgresql.conf

Раскомментироваем и редактируем следующие параметры 

  archive_mode = on 
  
  archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/%f && cp %p /var/lib/postgresql/pg_log_archive/%f'
  
  archive_timeout = 1800 (указываем значение в секундах)((Принудительно переключается на новый файл сегмента WAL))

Перезапускаем кластер чтобы изменения вступили в силу

  sudo systemctl restart postgresql@12-main

После перезапуска в каталоге /var/lib/postgresql/pg_log_archive/ появляются первые WAL журналы 000000010000000000000081
000000010000000000000082 ; 00000001 — ветка времени (немного подробнее тут ) 
Делаем бэкап или же контрольную точку от пользователя postgres

  su postgres
  cd ~

  #Простая копия кластера в каталог
   pg_basebackup -D /var/lib/postgresql/db_file_backup
  #Бэкап в архиве tar
  pg_basebackup -Ft -D /var/lib/postgresql/db_file_backup
  #Бэкап в сжатом архиве 
  pg_basebackup -Ft -X none -D - | gzip > /var/lib/postgresql/db_file_backup.tar.gz

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

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

  su postgres 
  cd ~
  psql -c "select pg_switch_wal();"

Выключаем Postgres 

  sudo systemctl stop postgresql@12-main
  #Проверяем статус
  sudo systemctl status postgresql@12-main

Удаляем данные кластера 

  sudo rm -rf /var/lib/postgresql/12/main

Восстанавливаем кластер из контрольной точки ( pg_basebackup) 

  #Простой бэкап
  sudo cp -a /path/to/database_backup/. /var/lib/postgresql/12/main/ 
  #Извлечение из архива
  tar xvf /var/lib/postgresql/db_file_backup/base.tar -C /var/lib/postgresql/12/main/
  #Сжатый архив 
  tar xvfz /var/lib/postgresql/db_file_backup.tar.gz -C /var/lib/postgresql/12/main/

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

  sudo chown postgres:postgres /var/lib/postgresql/12/main
  
  sudo chmod 700 /var/lib/postgresql/12/main

Прописываем параметры восстановления 

  sudo nano /etc/postgresql/12/main/postgresql.conf
  
  #Раскомментить и прописать путь, где лежат WAL журналы
  restore_command = 'cp /var/lib/postgresql/pg_log_archive/%f %p'
  
  #Восстановление на определенный момент времени
  ##Раскомментить и прописать дату 
  recovery_target_time = 'yyyy-mm-dd hh:mm:ss.ss' ('2021-08-10 15:20:00')
  
  #Восстановление на определенную ветку времени
  ##Раскомментить и прописать значение 
  recovery_target_timeline = 'latest' - до последней ветки
  recovery_target_timeline = '1' - до указанной
  

Остальные параметры восстановления и их описание (клик)

Чтобы запустить сервер в режиме восстановления, создадим в корнем каталоге файл recovery.signal 

  sudo touch /var/lib/postgresql/12/main/recovery.signal

Как только кластер базы данных достигнет цели восстановления, он удалит recovery.signal файл

Запускаем postgres

  sudo systemctl start postgresql@12-main

  sudo systemctl status postgresql@12-main
  
  #Получим что то подобное 
  
  `79; postgresql@12-main.service - PostgreSQL Cluster 12-main
  Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled)
  Active: active (running) since Tue 2021-08-10 14:07:34 MSK; 3s ago
  Process: 29045 ExecStop=/usr/bin/pg_ctlcluster —skip-systemctl-redirect -m fast 12-main stop (code=exited, status=0/SUCCESS)
  Process: 29052 ExecStart=/usr/bin/pg_ctlcluster —skip-systemctl-redirect 12-main start (code=exited, status=0/SUCCESS)
  Main PID: 29059 (postgres)
  Tasks: 6 (limit: 4915)
  CGroup: /system.slice/system-postgresql.slice/postgresql@12-main.service
  _00;^72;29059 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgr
  _00;^72;29060 postgres: 12/main: logger
  _00;^72;29061 postgres: 12/main: startup recovering 00000002000000000000008A
  _00;^72;29065 postgres: 12/main: checkpointer
  _00;^72;29066 postgres: 12/main: background writer
  ^92;^72;29068 postgres: 12/main: stats collector
  
  авг 10 14:07:31 astra systemd[1]: Stopped PostgreSQL Cluster 12-main.
  авг 10 14:07:31 astra systemd[1]: Starting PostgreSQL Cluster 12-main...
  авг 10 14:07:34 astra systemd[1]: Started PostgreSQL Cluster 12-main.

В будущем хочу научиться потоковой репликации wal журналов, чтобы обеспечить полную отказоустойчивую систему
Так что ждите новых мануалов)