PostgreSQL 9.6 настройка WAL архивирования ubuntu

PostgreSQL Backup and Recovery Orchestration: WAL Archiving
Point in time recovery: PITR using pg_basebackup with PostgreSQL 9.2
Настройка непрерывной архивации

$ sudo mkdir -p /backup
$ sudo chown -R postgres:postgres /backup

$ sudo mkdir -p /wal
$ sudo chown -R postgres:postgres /wal

sudo nano /etc/postgresql/9.6/main/pg_hba.conf
Раскомментировать:
local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            md5

Сохранить

$ sudo nano  /etc/postgresql/9.6/main/postgresql.conf
Добавить:


max_replication_slots = 10
max_wal_senders = 10
wal_level = replica
archive_mode = on
archive_command ='test ! -f /wal/%f && cp %p /wal/%f'


Сохранить.

$ sudo systemctl restart postgresql


$ nano pg_basebackup.sh

#!/bin/sh
set -e
DATA=`date +"%Y-%m-%d_%H-%M-%S"`
DATA_FTP=`date +"%Y-%m-%d_%H-%M-%S"` # без роатации
# Если на ftp мало места включить ротацию:
#DATA_FTP=`date +"%d"` # для ротации по номеру дня месяца
#DATA_FTP=`date +"%u"` # для ротации по номеру дня недели
DIR_TMP='/backup'
DIR_BACKUP='/home/user/backup'
DIR_YANDEX='/home/user/backup/yandex'
echo "--------------------------------------------------------------------" > /home/user/backup/backup.log
# Записываем информацию в лог с секундами
echo "$DATA Начало backup"
echo "$DATA Начало backup" >> /home/user/backup/backup.log

sudo /bin/su postgres -c "rm -rf $DIR_TMP/*" \
     && sudo /bin/su postgres -c "/usr/bin/pg_basebackup -D $DIR_TMP -Ft -z -Xf -P" \
     && sudo mv $DIR_TMP/base.tar.gz $DIR_BACKUP/base-$DATA.tar.gz

#echo $?

DATA=`date +"%Y-%m-%d_%H-%M-%S"`
if [ $? -ne 0 ]
then
     echo "$DATA Ошибка завершения для backup"
     echo "$DATA Ошибка завершения для backup" >> /home/user/backup/backup.log
     exit
else
     echo "$DATA Успешное завершение backup"
     echo "$DATA Успешное завершение backup" >> /home/user/backup/backup.log
fi
echo "--------------------------------------------------------------------" >> /home/user/backup/backup.log


$ time sudo sh pg_basebackup.sh


restore:
$ sudo systemctl stop postgresql

Удалим кластер (сделать копию!!!):
$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/9.6/main/*"

Поднимим базовую копию кластера:
$ sudo -u postgres tar -xvC /var/lib/postgresql/9.6/main -f /home/user/backup/base-2019-04-12_13-15-20.tar.gz

Просто поднять, без wal, но мы этого делать не будем
#$ sudo systemctl start postgresql

$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/9.6/main/pg_xlog/*"

Заодно просто логи
$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/9.6/main/pg_log/*"

#Скопируем /wal файлы из архива в  /wal.bac :
$ sudo mkdir -p /wal.bac
$ sudo chown -R postgres:postgres /wal.bac 
$ sudo -u postgres cp /wal/* /wal.bac

 

#Скопируем /wal файлы из архива в  /var/lib/postgresql/9.6/main/pg_xlog : 
$ sudo /bin/su postgres -c "cp /wal.bac/* /var/lib/postgresql/9.6/main/pg_xlog"

# Удалим /wal/*
#$ sudo /bin/su postgres -c "rm -rf /wal/*"


Добавим файл  recovery.conf

$ sudo -u postgres cp /usr/share/postgresql/9.6/recovery.conf.sample  /var/lib/postgresql/9.6/main/recovery.conf
$ sudo -u postgres nano /var/lib/postgresql/9.6/main/recovery.conf

Добавить в конец файла:

restore_command = 'cp /wal.bac/%f "%p"'


Сохранить.



$ sudo systemctl start postgresql

Очистка логов транзакций postgresql

$ sudo systemctl stop postgresql
$ sudo -u postgres /usr/lib/postgresql/9.6/bin/pg_controldata /var/lib/postgresql/9.6/main/
.....
Положение последней конт. точки:      33/99000028
Положение предыдущей конт. точки:     33/98000098
Положение REDO последней конт. точки: 33/99000028
Файл WAL c REDO последней к. т.:      000000010000003300000099
.....
NextXID последней конт. точки:        0:2067748
NextOID последней конт. точки:        4303721
.....
Выполняем команду pg_resetxlog которой указываем NextOID и NextXID (команда выполняется из под пользователя postgres)



$ sudo -u postgres /usr/lib/postgresql/9.6/bin/pg_resetxlog -o 4303721 -x 2067748 -f /var/lib/postgresql/9.6/main/

Журнал транзакций сброшен

$ sudo systemctl start postgresql