Configurazione Standby DB Postgres 9.6

Doc: https://en.wikibooks.org/wiki/PostgreSQL/Replication

 Install CentOS consigliata 7.x 
 Disable SeLinux
 disable firewalld
 Reboot         
                
echo "pathmunge /usr/pgsql-9.6/bin" > /etc/profile.d/postgres.sh
                

Su Master:

 yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-redhat96-9.6-3.noarch.rpm -y
 yum install yum install postgresql96-server postgresql96-contrib -y
 service postgresql-9.6 initdb
 chkconfig postgresql-9.6
 service postgresql-9.6 start
                
                
                

Su Slave:

 yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-redhat96-9.6-3.noarch.rpm -y
 yum install yum install postgresql96-server postgresql96-contrib -y
                
                
                
 Macchina Master 10.240.1.114 (esempio)
 Macchina Slave 10.240.1.115 (esempio)
                

Sul Master:

 Configurazione HBA for User Sync 
                
 pg_hba.conf nella Home data del contenitore di Postgres:
                
 host    replication    replicator    10.240.1.115/32    md5
                
                
                
 Add to Postgres.conf 
                
 listen_addresses = '*'
 wal_level = hot_standby
 max_wal_senders = 3
 wal_keep_segments = 8

• In releases prior to 9.6, this parameter also allowed the values archive and hot_standby. These are still accepted but mapped to replica.

 Create USER:   
 psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'orione';"
                
                
 Slave da utente Postgres Duplicate:
                
 pg_basebackup -h 10.240.1.114 -D /var/lib/pgsql/9.6/data -U replicator -P -v -x
                
                
 Create recovery.conf: 
                
 primary_conninfo = 'host=10.240.1.114 port=5432 user=replicator password=orione'
 trigger_file = '/tmp/stica2.txt'
 standby_mode = 'on'
                
 rm backup_label o altre file che sono i semafori.
                
                
                
 service postgresql-9.6 start su Slave
                
                
Esempio di insert massimo per test (fonte: Edoardo Moriani)
                
                
 CREATE TABLE t_test10 (id serial, name text);
 INSERT INTO t_test10 (name) SELECT 'paul' FROM generate_series(1, 4000000);