Как проверить архивирование

postgres=# select * from pg_stat_archiver;

$ sudo -u postgres  psql -c " select * from pg_stat_archiver;"

$ sudo -u postgres  psql -c " select last_archived_time, stats_reset  from pg_stat_archiver;" \
     |  mutt -s "archived_time" root




echo "---------------------------------------------------------------------------------------" >> sysinfo.txt
echo "sudo -u postgres  psql -c \"select last_archived_time, stats_reset  from pg_stat_archiver;\""\
>> sysinfo.txt
echo "-------------------------------+-------------------------------" >> sysinfo.txt
sudo -u postgres  psql -c "select last_archived_time, stats_reset  from pg_stat_archiver;" \
>> sysinfo.txt
echo "---------------------------------------------------------------------------------------" >> sysinfo.txt

Automatically cleaning up archived WAL files on a PostgreSQL server

Automatically cleaning up archived WAL files on a PostgreSQL server

walarchivecleanup.sh

Под windows изящного решения не нашел
остановился на:
forfiles /P "d:\PostgreSQLBackup" /D -1 /C "cmd /c del @path /q" 

PostgreSQL 10.5-24 настройка WAL архивирования windows

Continuous WAL Archiving for Windows
Настройка непрерывного архивирования в PostgreSQL 9.6
Настройка непрерывной архивации

Временная папка для backup
c:\backup

Архивы  будем складывать:
d:\PostgreSQLBackup\
 
Проверить
C:\Program Files\PostgreSQL\10.5-24.1C\data\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'
archive_command = 'copy "%p" "d:\\PostgreSQLBackup\\%f"'



Резервная копия кластера PostgreSQL
"c:\Program Files\PostgreSQL\10.5-24.1C\bin\pg_basebackup.exe" -U postgres -X fetch --format=tar -z -D "c:\backup" -P

Обслуживание базы
"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql"  -d demo -U postgres -c "VACUUM FULL FREEZE VERBOSE ANALYZE;"

"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql"  -U postgres -c "VACUUM FULL FREEZE VERBOSE ANALYZE;" 

Скрипт создающий резервную копию кластера (добавить в планировщик)

pg_bbackup.bat

REM СОЗДАНИЕ РЕЗЕРВНОЙ КОПИИ КЛАСТЕРА POSTGRESQL
REM Запускаем из папки в которую складываем архивы можно в корне
REM Архивы помещаются в папку Backup  в папке из которой запускаем скрипт
CLS
ECHO OFF
CHCP 1251
REM Установка переменных окружения
SET PGBIN=c:\Program Files\PostgreSQL\10.5-24.1C\bin
SET PGBACKUP="c:\backup"
DEL /F /Q %PGBACKUP%\base.tar.gz
SET PGUSER=postgres
SET PGPASSWORD=pass
REM Смена диска и переход в папку из которой запущен bat-файл
%~d0
CD %~dp0
REM Формирование имени файла резервной копии и файла-отчета
SET DATETIME=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%_%TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
SET DUMPFILE= %DATETIME%.tar.gz
SET LOGFILE=%DATETIME%.log
SET DUMPPATH="Backup\%DUMPFILE%"
SET LOGPATH="Backup\%LOGFILE%"
REM Создание резервной копии
IF NOT EXIST Backup MD Backup
CALL "%PGBIN%\pg_basebackup.exe" -U %PGUSER% -X fetch --format=tar -z -D %PGBACKUP% -P 2>%LOGPATH%
REM Анализ кода завершения
IF NOT %ERRORLEVEL%==0 GOTO Error
GOTO Successfull
REM В случае ошибки удаляется поврежденная резервная копия и делается соответствующая запись в журнале
:Error
DEL %DUMPPATH%
MSG * "Ошибка при создании резервной копии базы данных. Смотрите backup.log."
ECHO %DATETIME% Ошибки при создании резервной копии базы данных %DUMPFILE%. Смотрите отчет %LOGFILE%. >> backup.log
GOTO End
REM В случае удачного резервного копирования просто делается запись в журнал
:Successfull
ECHO %DATETIME% Успешное создание резервной копии %DUMPFILE% >> backup.log
COPY %PGBACKUP%\base.tar.gz %DUMPPATH%
REM В случае удачного резервного копирования удаляются вчерашние архивные записи
REM forfiles /P "d:\PostgreSQLBackup" /D -1 /C "cmd /c del @path /q" 
GOTO End
:End

Сохранить.

Целесообразно настроить и расскоментировать в скрипте:
forfiles /P "d:\PostgreSQLBackup" /D -1 /C "cmd /c del @path /q"

где "d:\PostgreSQLBackup", то хранилище wal, которое указано
archive_command = 'copy "%p" "d:\\PostgreSQLBackup\\%f"'

Предполагаем что резервную копию кластера, делаем не реже одного раза в день,
в случае удачного резервного копирования удаляются вчерашние архивные записи wal
   

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

Остановить службу pgsql-10.5-24.1C-x64 от администратора!
net stop  pgsql-10.5-24.1C-x64
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" останавливается.
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" успешно остановлена.


Очистить содержимое:
C:\Program Files\PostgreSQL\10.5-24.1C\data

Разархивировать архив вида  2019-03-20_18-00-57.tar.gz
в C:\Program Files\PostgreSQL\10.5-24.1C\data


Если удалили папку
C:\Program Files\PostgreSQL\10.5-24.1C\data


Создать

C:\Program Files\PostgreSQL\10.5-24.1C\data



Но нужно,
 
Задать на папку права полного доступа для пользователя NETWORK SERVICE от имени которого запускает  pgsql-10.5-24.1C-x64

Для пользователей нужны права на запись в папку:

 C:\Program Files\PostgreSQL\10.5-24.1C\data

После разархивирования архива

Смотрим файл

c:\Program Files\PostgreSQL\10.5-24.1C\data\backup_label

START WAL LOCATION: C/82000028 (file 000000010000000C00000082)
CHECKPOINT LOCATION: C/82000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-04-13 13:20:01 MSK
LABEL: pg_basebackup base backup

Находим имя начального wal (file 000000010000000C00000082) 

Нужно проверить что он есть в d:\PostgreSQLBackup\

После этого можно очистить (вместе с папками)

"c:\Program Files\PostgreSQL\10.5-24.1C\data\pg_wal\"

Тонкий момент, поскольку настроено архивирование, логично перед запуском скопировать

d:\PostgreSQLBackup\ в d:\PostgreSQLBackup.bac\ со всеми wal файлами

Содержимое d:\PostgreSQLBackup\ очистить

Создадим файл  recovery.conf

"c:\Program Files\PostgreSQL\10.5-24.1C\data\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 = 'copy "d:\\PostgreSQLBackup.bac\\%f" "%p"'
recovery_end_command='echo finish'



Сохранить.


Запускаем PostgreSQL от администратора!

net start  pgsql-10.5-24.1C-x64

Служба "PostgreSQL Database Server 10.5-24.1C(x64)" запускается.

Служба "PostgreSQL Database Server 10.5-24.1C(x64)" успешно запущена.

Если компьютер выключается на ночь, скрипт можно поставить в планировщик windows по событию при входе в систему:

"c:\Program Files\PostgreSQL\10.5-24.1C\bin\pg_archivecleanup" d:\PostgreSQLBackup  000000020000000C000000A8

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

Настройка непрерывного архивирования в PostgreSQL 9.6

Настройка непрерывного архивирования в PostgreSQL 9.6
25.3. Непрерывное архивирование и восстановление на момент времени (Point-in-Time Recovery, PITR)
Потоковая репликация и непрерывное архивирование базы данных PostgreSQL - делюсь небольшим опытом
PostgreSQL Backup and Recovery Orchestration: WAL Archiving