PostgreSQL HA

master# aptitude install postgresql postgresql-client
master# sed -i "s/^#listen_addresses = 'localhost'\(.*\)/listen_addresses = '*'\1/" /etc/postgresql/9.6/main/postgresql.conf
master# sed -i 's/^#max_replication_slots = 0\(.*\)/max_replication_slots = 10\1/' /etc/postgresql/9.6/main/postgresql.conf
master# sed -i 's/^#wal_level = minimal\(.*\)/wal_level = replica\1/' /etc/postgresql/9.6/main/postgresql.conf
master# sed -i 's/^#max_wal_senders = 0\(.*\)/max_wal_senders = 10\1/' /etc/postgresql/9.6/main/postgresql.conf
master# echo "host    replication     replicator      10.0.120.36/32          md5" >> /etc/postgresql/9.6/main/pg_hba.conf
master# systemctl restart postgresql.service

master# su - postgres
master$ psql
postgres=# CREATE ROLE replicator LOGIN REPLICATION ENCRYPTED PASSWORD 'replicator123';
postgres=# SELECT * FROM pg_create_physical_replication_slot('replicator');

master# systemctl restart postgresql.service

replica# aptitude install postgresql postgresql-client
replica# systemctl stop postgresql.service
replica# sed -i "s/^#listen_addresses = 'localhost'\(.*\)/listen_addresses = '*'\1/" /etc/postgresql/9.6/main/postgresql.conf
replica# sed -i 's/^#hot_standby = off\(.*\)/hot_standby = on\1/' /etc/postgresql/9.6/main/postgresql.conf

replica# mv /var/lib/postgresql/9.6/main /var/lib/postgresql/9.6/main.bkp
replica# pg_basebackup -h 10.0.120.37 -U replicator -D /var/lib/postgresql/9.6/main/ -P --password --slot=replicator --xlog-method=stream
replica# chown -R postgres:postgres /var/lib/postgresql/9.6/main

• create /var/lib/postgresql/9.6/main/recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=10.0.120.37 port=5432 user=replicator password=replicator123'
primary_slot_name = 'replicator'
trigger_file = '/var/lib/postgresql/9.6/main/failover.trigger'

replica# chown postgres:postgres /var/lib/postgresql/9.6/main/recovery.conf
replica# chmod 0600 /var/lib/postgresql/9.6/main/recovery.conf

replica# systemctl start postgresql.service

replica# touch /tmp/failover.trigger
replica# chown postgres:postgres /tmp/failover.trigger
replica# mv /tmp/failover.trigger /var/lib/postgresql/9.6/main/failover.trigger