Настройка postgresql 9.6 в связке c pgpoo2

Берем debian 9

Ставим postgres (на всех нодах)

wget http://ftp.ro.debian.org/debian/pool/main/o/openssl/libssl1.0.0_1.0.2k-1~bpo8+1_amd64.deb
wget http://ftp.hu.debian.org/debian/pool/main/o/openssl/libssl1.0.0_1.0.1t-1+deb8u6_amd64.deb
dpkg -i *.deb
apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get upgrade
apt-get install postgresql-9.6 postgresql-9.6-pgpool2

Делаем в базе на всех нодах

psql

ALTER USER postgres WITH PASSWORD 'itc-pass';
CREATE ROLE replication WITH REPLICATION PASSWORD 'itc-pass-replica' LOGIN;

Создаем файл коннета с паролем для пользователя репликации (на всех нодах)

 nano /var/lib/postgresql/.pgpass

*:*:*:replication:itc-pass-replica

chown postgres:postgres /var/lib/postgresql/.pgpass
chmod 0600 /var/lib/postgresql/.pgpass

Правим конмиг на мастер

 nano /etc/postgresql/9.6/main/postgresql.conf

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
listen_addresses = '*'
port = 5433
max_connections = 300
shared_buffers = 2560MB
effective_cache_size = 7680MB
maintenance_work_mem = 1280MB
default_statistics_target = 500
track_activities = on
track_counts = on
autovacuum = off
temp_buffers = 16MB
work_mem = 128MB
effective_io_concurrency = 4
shared_preload_libraries = 'pg_stat_statements,pg_buffercache'
track_activity_query_size = 2048
pg_stat_statements.track = all
track_io_timing = on
lc_messages = 'ru_RU.UTF-8'
lc_monetary = 'ru_RU.UTF-8'
lc_numeric = 'ru_RU.UTF-8'
lc_time = 'ru_RU.UTF-8'
hot_standby = on
wal_level = 'hot_standby'
wal_log_hints = on
archive_mode = on
archive_command = 'cd .'
max_replication_slots = 3
max_wal_senders = 3
wal_keep_segments = 64

Правим файл с подлючениями

/etc/postgresql/9.6/main/pg_hba.conf

#
# Database administrative login by Unix domain socket
local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
host  replication     replication     10.0.0.0/8          md5
host  replication     replication     10.0.0.0/8          md5
host  all             all        0.0.0.0/0             md5
hostssl  all             all        0.0.0.0/0             md5

Рестаруем postgres

/etc/init.d/postresql restart

Создаем слот репликации для сервера. Для этого выполняем команду

su postgres -c "psql"

#SELECT * FROM pg_create_physical_replication_slot('it_rdbms02');

Настройка слейва postgres(stand-by)

/etc/init.d/postgresql stop

Создаем на нем также .pgpass

Правим коннекты аналогично мастеру

Сливаем с мастера данные

su postgres
cd /var/lib/postgresql/9.6/
rm -rf main/*
pg_basebackup -v -D main -R -P -h 10.9.3.53 -p 5433 -U replication

Настраиваем recovery.conf

nano /var/lib/postgresql/9.6/main/recovery.conf
Приводим к

standby_mode = 'on'
primary_conninfo = 'user=replication password=itc-pass-replica host=10.9.3.53 port=5433 sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'it_rdbms02'
trigger_file = '/etc/postgresql/9.6/main/im_the_master'

Рестаруем postgres

/etc/init.d/postresql restart

Настраиваем pgpool2

su postgres 

cd /var/lib/postgresql/
wget http://www.pgpool.net/mediawiki/images/pgpool-II-3.6.4.tar.gz
tar -xvf pgpool-II-3.6.4.tar.gz
cd pgpool-II-3.6.4/src/sql
cd pgpool-recovery
psql -f pgpool_recovery--1.1.sql template1