Тестовый сервер test2 настройка PostgreSQL и pg_probackup

Продолжение, начало:
Тестовый сервер test2 базовая настройка

Установка СУБД PostgreSQL версия 10.5-24.1C

$ mkdir /home/user/test
В папку /test положить файлы:

postgresql_10.5_24.1C_amd64_deb.tar.bz2
postgresql_10.5_24.1C_amd64_addon_deb.tar.bz2

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'
$ sudo apt update -y
$ sudo apt-get install postgresql-common libtcl8.6 -y
$ dpkg -l | grep postgres | awk -F' ' '{print $2}' | sudo xargs apt-mark hold
#pgdg-keyring помечен как зафиксированный.
#postgresql-client-common помечен как зафиксированный.
#postgresql-common помечен как зафиксированный.
$ dpkg -l | grep libtcl8.6 | awk -F' ' '{print $2}' | sudo xargs apt-mark hold
#libtcl8.6 помечен как зафиксированный.
$ mkdir -p /tmp/post
$ cp /home/user/test/postgresql_10.5_24.1C_amd64_deb.tar.bz2 /tmp/post/
$ cp /home/user/test/postgresql_10.5_24.1C_amd64_addon_deb.tar.bz2 /tmp/post/
$ cd /tmp/post
$ tar -xvf postgresql_10.5_24.1C_amd64_deb.tar.bz2
$ cd  postgresql-10.5-24.1C_amd64_deb
$ ls
# список файлов:
#libpq5_10.5-24.1C_amd64.deb  postgresql-10_10.5-24.1C_amd64.deb  postgresql-client-10_10.5-24.1C_amd64.deb
$ sudo dpkg -i *.deb
# ниже можно не ставить:
$ cd /tmp/post/
$ tar -xvf postgresql_10.5_24.1C_amd64_addon_deb.tar.bz2
$ cd postgresql-10.5-24.1C_amd64_addon_deb
$ sudo dpkg -i *.deb
# конец не ставить
$ dpkg -l | grep 10.5-24.1C | awk -F' ' '{print $2}' | sudo xargs apt-mark hold
#libecpg-compat3 помечен как зафиксированный.
#libecpg-dev помечен как зафиксированный.
#libecpg6 помечен как зафиксированный.
#libpgtypes3 помечен как зафиксированный.
#libpq-dev помечен как зафиксированный.
#libpq5 помечен как зафиксированный.
#postgresql-10 помечен как зафиксированный.
#postgresql-10-dbg помечен как зафиксированный.
#postgresql-client-10 помечен как зафиксированный.
#postgresql-doc-10 помечен как зафиксированный.
#postgresql-plperl-10 помечен как зафиксированный.
#postgresql-plpython-10 помечен как зафиксированный.
#postgresql-plpython3-10 помечен как зафиксированный.
#postgresql-pltcl-10 помечен как зафиксированный.
#postgresql-server-dev-10 помечен как зафиксированный.


$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main pg_log/postgresql-%a.log

$ sudo pg_dropcluster --stop 10 main

$ sudo pg_createcluster --locale ru_RU.UTF-8 10 main --  --data-checksums

$ sudo pg_ctlcluster 10 main start

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main pg_log/postgresql-%a.log
 
$ sudo -u postgres psql -U postgres -c "alter user postgres with password 'pass';"
$ sudo cp /etc/postgresql/10/main/postgresql.conf /etc/postgresql/10/main/postgresql.conf.bak

#Делаем проверки
$ sudo systemctl status postgresql.service
$ ss -tunpl | grep 5432
tcp    LISTEN     0      128       *:5432                  *:*
tcp    LISTEN     0      128      :::5432                 :::*
$ ps aux | grep postgres | grep -- -D
postgres  4914  0.0  0.1 320972 28864 ?        S    08:37   0:00 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf

-------------------------------------------------------------------------------------------------------

Настройка postgresql.conf для 1С Total Memory (RAM): 16 GB

sudo nano /etc/postgresql/10/main/postgresql.conf
Добавить в конец:

# DB Version: 10
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 16 GB
# Data Storage: ssd

max_connections = 1000
shared_buffers = 4GB
temp_buffers = 256MB
work_mem = 64MB
effective_cache_size = 8GB # 4GB for 1c
maintenance_work_mem = 1GB
wal_buffers = 16MB
min_wal_size = 2GB
max_wal_size = 4GB

default_statistics_target = 100
effective_io_concurrency = 2
random_page_cost = 1.1
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 20s
bgwriter_delay = 20ms
bgwriter_lru_multiplier = 4.0
bgwriter_lru_maxpages = 400
synchronous_commit = off
checkpoint_completion_target = 0.9
ssl = off
fsync = on
commit_delay = 1000
commit_siblings = 5
row_security = off
max_files_per_process = 1000
standard_conforming_strings = off
escape_string_warning = off
max_locks_per_transaction = 256
#log_min_duration_statement = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
#log_autovacuum_min_duration = 0
lc_messages='C'
#log_duration = on
#log_statement = all
log_destination = stderr
plantuner.fix_empty_table = 'on'
online_analyze.table_type = 'temporary'
online_analyze.verbose = 'off'

Сохранить.

$ sudo systemctl restart postgresql.service
 -------------------------------------------------------------------------------------------------------

Установка pg_probackup 

pg_probackup описание

$ sudo su -c "echo 'deb [arch=amd64] http://repo.postgrespro.ru/pg_probackup/deb/ $(lsb_release -cs) main-$(lsb_release -cs)' > /etc/apt/sources.list.d/pg_probackup.list" 
$ sudo su -c "wget -O - http://repo.postgrespro.ru/pg_probackup/keys/GPG-KEY-PG_PROBACKUP | apt-key add -" 
$ sudo apt-get update 
$ sudo apt-get install pg-probackup-10
$ dpkg -l | grep pg-probackup-10 | awk -F' ' '{print $2}' | sudo xargs apt-mark hold
#pg-probackup-10 помечен как зафиксированный.

Инициализация каталога резервных копий 1 экземпляр на примонтированом HDD

Каталог резервных копий /backup/pg_probackup расположено на hdd диске, в нем будем ежедневно сохранять backup за 30 дней без непрерывного архивирования.
Если нет дополнительного диска, выполняем закомментированые команды
$ sudo mkdir /backup/pg_probackup
$ sudo mkdir /backup/pg_probackup/copy
$ sudo mkdir /backup/pg_probackup/log
$ sudo chown -R postgres:postgres /backup/
#$ sudo chmod -R 777 /backup/pg_probackup

 
$ sudo -u postgres pg_probackup-10 init -B /backup/pg_probackup
Определение копируемого экземпляра 
$ sudo -u postgres pg_probackup-10 add-instance -B /backup/pg_probackup -D /var/lib/postgresql/10/main --instance main

$ sudo nano /etc/postgresql/10/main/postgresql.conf

Добавить:

max_wal_senders = 10
wal_level = replica
unix_socket_directories='/var/run/postgresql'
# Для настройки архивного резервного копирования разкомментировать:
archive_mode = on
#archive_command ='test ! -f /wal/%f && cp %p /wal/%f' 
archive_command = '/usr/bin/pg_probackup-10 archive-push -B /backup/pg_probackup --compress --instance main --wal-file-path %p --wal-file-name %f'

Сохранить.

$ sudo systemctl restart postgresql.service

Настройка политики сохранения 5 резервных копий

$ sudo -u postgres nano /backup/pg_probackup/backups/main/pg_probackup.conf

Добавить:

retention-redundancy=5

Сохранить


Создание полной автономной резервной копии: 

$ sudo -u postgres pg_probackup-10 backup -B /backup/pg_probackup --instance main -b FULL --stream --compress --delete-wal --expired


Просмотр:

$ sudo -u postgres pg_probackup-10 show -B /backup/pg_probackup


Проверка backup:

Проверка файлов:
$ sudo -u postgres pg_probackup-10 checkdb -B /backup/pg_probackup --instance main
INFO: Start checking data files
INFO: Data files are valid

Проверка backup: 
$ sudo -u postgres pg_probackup-10 validate -B /backup/pg_probackup
INFO: Validate backups of the instance 'main'
INFO: Validating backup PUVU29
INFO: Backup PUVU29 data files are valid
INFO: Backup PUVU29 WAL segments are valid
INFO: Validating backup PUVTUX
INFO: Backup PUVTUX data files are valid
INFO: Backup PUVTUX WAL segments are valid
INFO: All backups are valid

-------------------------------------------------------------------------------
Скрипт резервного копирования

Проверка авторизации по паролю
$ PGPASSWORD=pass psql -U postgres  -h 127.0.0.1 postgres

$ sudo  nano /root/fullbackup.sh

#!/bin/sh -e
#
email=f1@vu.com
#sudo sh /root/terminate.sh
# vacuumdb
#sudo systemctl stop srv1cv83.service
sudo -u postgres vacuumdb -afz > vacuumdb.txt
#sudo systemctl start srv1cv83.service
DATA=`date +"%Y-%m-%d_%H-%M-%S"`
if [ $? -ne 0 ]
then
     echo "vacuumdb неудачное завершение" | mutt -s 'vacuumdb '$DATA' - неудачное завершение' $email -a vacuumdb.txt
else
     echo "vacuumdb удачное завершение" | mutt -s 'vacuumdb '$DATA' - удачное завершение' $email -a vacuumdb.txt
fi

# /backup/pg_probackup
inst='/backup/pg_probackup'
log=$inst'/log/pg_probackup.log'
if [ -f $log ]; then rm $log; fi
cd $inst

PGPASSWORD=password pg_probackup-10 backup -B $inst --instance main -U postgres -d postgres -h 127.0.0.1 -b FULL --stream --compress --expired --delete-wal --log-level-file=info

grep completed $log > /dev/null 2>&1
if [ $? -ne 0 ]
then
    msg="$inst неудачное завершение."
else
    msg="$inst удачное завершение."
fi
DATA=`date +"%Y-%m-%d %H:%M:%S"`
msg1="Размер экземпляра pg_probackup: $(du -h -s $inst)"
echo "$msg $msg1" | mutt -s "$msg $DATA" $email -a $log

# Сразу архивируем для rsync
COPY_DIR=$inst/copy
#Берем последнюю копию
COPY_ID=$(ls $inst/backups/main -1 | tail -1)
#Она должна быть completed
grep "$COPY_ID completed" $log > /dev/null 2>&1
if [ $? = 0 ]
then
    DATA=`date +"%Y-%m-%d_%H-%M-%S"`
    #rm  $inst/copy/*
    tar -cvzf $COPY_DIR/pg_pro-$DATA.tar.gz  \
           $inst/backups/main/$COPY_ID > /dev/null 2>&1
    if [ $? = 0 ]
    then
        DATA=`date +"%Y-%m-%d_%H-%M-%S"`
        msg="создан $COPY_DIR/pg_pro-$DATA.tar.gz"
    else
        msg="ошибка создания $COPY_DIR/pg_pro-$DATA.tar.gz"
    fi
    echo $msg | mutt -s "$msg" $email -a $log
fi

Проверка:

Сохранить.

$ sudo crontab -e

*/5 * * * * sh /root/fullbackup.sh

Сохранить.






Дополнительная информация:
-------------------------------------------------------------------------------------------------------
Настраиваем после установки баз 1С 
petergeoghegan/amcheck

$ sudo apt install postgresql-10-amcheck

Для каждой базы:
postgres=# CREATE EXTENSION amcheck;
или
postgres=# CREATE EXTENSION amcheck_next;

Grigory Smolkin: для PG =<10 рекомендую ( Grigory Smolkin) использовать  amcheck_next

Для каждой базы, например demo:

$ sudo -u postgres psql -d postgres -c "CREATE EXTENSION amcheck_next" 
$ sudo -u postgres psql -d demo -c "CREATE EXTENSION amcheck_next"

$ sudo -u postgres pg_probackup-10 checkdb -B /backup/pg_probackup --instance main --amcheck

INFO: Start checking data files
INFO: Data files are valid
INFO: Start amchecking PostgreSQL instance
INFO: Amchecking database 'postgres' using extension 'amcheck_next' version 2 from schema 'public'
INFO: Amchecking database 'demo' using extension 'amcheck_next' version 2 from schema 'public'
INFO: Checkdb --amcheck executed successfully
INFO: Indexes are valid


опция --heapallindexed, с ней amcheck проверяет, что хип, который должен быть проиндексирован, действительно проиндексирован

$ sudo -u postgres pg_probackup-10 checkdb -B /backup/pg_probackup --instance main --amcheck --heapallindexed

INFO: Start checking data files
INFO: Data files are valid
INFO: Start amchecking PostgreSQL instance
INFO: Amchecking database 'postgres' using extension 'amcheck_next' version 2 from schema 'public'
INFO: Amchecking database 'demo' using extension 'amcheck_next' version 2 from schema 'public'
INFO: Checkdb --amcheck executed successfully
INFO: Indexes are valid
------------------------------------------------------------------------------------------------------- 

Пример восстановления из полной автономной резервной копии (замена кластера на резервную копию)

Сделали копию:
$ sudo -u postgres pg_probackup-10 backup -B /backup/pg_probackup --instance main -b FULL --stream --compress --expired

Перед восстановлением

Просмотр:

$ sudo -u postgres pg_probackup-10 show -B

Проверка backup:
$ sudo -u postgres pg_probackup-10 validate -B /backup/backup1

INFO: Validate backups of the instance 'main'
INFO: Validating backup PRP29I
INFO: Backup PRP29I data files are valid
INFO: Backup PRP29I WAL segments are valid

Можно восстанавливать.

Останавливаем 1с
$ sudo systemctl stop srv1cv83.service

Останавливаем postgresql
$ sudo systemctl stop postgresql.service

Удалим кластер main
$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/10/main/*"

Восстановим кластер main
$ sudo -u postgres pg_probackup-10 restore -B /backup/backup1 --instance main -i PRP29I
INFO: Validating backup PRP29I
INFO: Backup PRP29I data files are valid
INFO: Backup PRP29I WAL segments are valid
INFO: Backup PRP29I is valid.
INFO: Restore of backup PRP29I completed.


Запускаем postgresql
$ sudo systemctl start postgresql.service

Запускаем 1с
$ sudo systemctl start srv1cv83.service

------------------------------------------------------------------------------------------------------- 

Пример восстановления из полной автономной резервной копии c непрерывным архивированием (замена кластера на резервную копию)

$ sudo -u postgres pg_probackup-10 backup -B /backup/pg_probackup --instance main -b FULL --stream --compress --delete-wal --expired


Просмотр:

$ sudo -u postgres pg_probackup-10 show -B /backup/pg_probackup


Проверка backup:
$ sudo -u postgres pg_probackup-10 validate -B /backup/pg_probackup

INFO: Validate backups of the instance 'main'
INFO: Validating backup PRP6Q5
INFO: Backup PRP6Q5 data files are valid
INFO: Backup PRP6Q5 WAL segments are valid
 Можно восстанавливать, но проверим непрерывность архивирования:


Войти в 1с создать документ, провести, выйти из 1с.


$ sudo -u postgres pg_probackup-10 validate -B /backup/pg_probackup   --instance main -i PRP6Q5 --time='2042-04-16 12:26:00'
INFO: Validating backup PRP6Q5
INFO: Backup PRP6Q5 data files are valid
WARNING: Thread [1]: Could not read WAL record at 1/88000000
ERROR: Thread [1]: WAL segment "/backupwal/wal/main/000000010000000100000088" is absent
WARNING: Recovery can be done up to time 2019-05-18 14:36:30+03, xid 0 and LSN 1/2A000198
ERROR: Not enough WAL records to time 2042-04-16 12:26:00+03



Останавливаем 1с
$ sudo systemctl stop srv1cv83.service

Останавливаем postgresql
$ sudo systemctl stop postgresql.service

Удалим кластер main
$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/10/main/*"

Восстановим кластер main
$ sudo -u postgres pg_probackup-10 restore -B /backup/pg_probackup --instance main -i PRP6Q5 --recovery-target='latest'


INFO: Validating backup PRP6Q5
INFO: Backup PRP6Q5 data files are valid
INFO: Backup PRP6Q5 WAL segments are valid
INFO: Backup PRP6Q5 is valid.
INFO: Restore of backup PRP6Q5 completed.


Запускаем postgresql
$ sudo systemctl start postgresql.service

Запускаем 1с
$ sudo systemctl start srv1cv83.service

-------------------------------------------------------------------------------------------------------
Примечание 1


wal архивация сразу не заработала, проблема в pg_probackup 2.1.1
странное сообщение в логах - could not find a "pg_probackup" to execute #39

#archive_command = 'pg_probackup-10 archive-push -B /backupwal --compress --instance main --wal-file-path %p --wal-file-name %f'

нужно указать полный путь к pg_probackup-10:

archive_command = '/usr/bin/pg_probackup-10 archive-push -B /backupwal --compress --instance main --wal-file-path %p --wal-file-name %f'

В логе было:

2019-05-18 12:27:24 MSK [20683]: [68-1] user=,db=,client= LOG:  archive command failed with exit code 1
2019-05-18 12:27:24 MSK [20683]: [69-1] user=,db=,client= DETAIL:  The failed archive command was: pg_probackup-10 archive-push -B /backupwal --compress --instance main --wal-file-path pg_wal/0000000100000000000000F1 --wal-file-name 0000000100000000000000F1
2019-05-18 12:27:24 MSK [20683]: [70-1] user=,db=,client= WARNING:  archiving write-ahead log file "0000000100000000000000F1" failed too many times, will try again later
could not find a "pg_probackup-10" to execute
pg_probackup-10: could not find own program executable


Для проверки стандартной wal архивации:

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


$ sudo nano /etc/postgresql/10/main/postgresql.conf

расскоментировать:
archive_command ='test ! -f /wal/%f && cp %p /wal/%f'

Сохранить.

$ sudo systemctl restart postgresql.service

 
-------------------------------------------------------------------------------------------------------