PostgreSQL Asynchronous Stream Replication Setup

Node	Ip	Role
Citus-master	10.10.100.1	Master
Citus-standby	10.10.100.2	Standby

The user that is required to create the stream replication on master.

CREATE ROLE replication with replication PASSWORD ' replication ' LOGIN;

Modify Master's pg_hba.conf file to set replication user remote access permissions

# # vim/data/pgsql/data/pg_hba.conf, append the following line host replication replication 10.10.0.0/16 MD5

Setting parameters related to replication on master

Wal_level = Hot_standbymax_wal_senders = 5wal_keep_segments = 32

Restart Pgsql on Master

/etc/init.d/postgresql-9.5 restart

Standby use Pg_basebackup to complete the master once and pull the backup to standby.

Pg_basebackup-h 10.10.100.1-d/var/lib/pgsql/9.5/data/-p-u replication-r--xlog-method=stream

Modify the postgresql.conf file on the standby node to set the state of the repository to standby

# # Vim/data/pgsql/data/postgresql.confhot_standby = On

Modify the standby Recovery.done file, set the synchronization information of the master node and the trigger file

Standby_mode = ' on ' primary_conninfo = ' user=replication password=replication host=10.10.100.1 port=5432 sslmode=prefer Sslcompression=1 krbsrvname=postgres ' trigger_file = '/data/pgsql/data/postgresql.trigger.1973 '

Restart Pgsql on standby

/etc/init.d/postgresql-9.5 restart

You can create a test table on master and insert data to verify that the data is synchronized on the standby. When Master is hung up, the trigger_file parameter declaration file is created on standby, triggering standby activation, which automatically promotes standby to master.

After Master is hung, recovery.conf will be renamed to Recovery.done after the trigger file is created on standby. The standby will then take over master into the writable state. You can then set the original master to the current standby, as follows

# # Create recovery.conf, write the following content recovery_target_timeline = ' latest ' Standby_mode = ' on ' primary_conninfo = ' user=replication Password=replication host=10.10.100.2 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres '

# # Vim postgresql.conf file, open Hot_standbyhot_standby = On

Finally restart the Pgsql and view the log

/etc/init.d/postgresql-9.5   restarttail -9f  pg_log/postgresql-tue.log<  2016-11-01 16:51:53.590 cst >log:  shutting down< 2016-11-01  16:51:53.594 CST >LOG:  database system is shut down<  2016-11-01 16:51:54.679 cst >log:  database system was shut  down in recovery at 2016-11-01 16:51:53 CST< 2016-11-01  16:51:54.679 cst >log:  entering standby mode< 2016-11-01  16:51:54.692 cst >log:  consistent recovery state reached at 0 /f001430< 2016-11-01 16:51:54.692 cst >log:  redo starts at  0/f001430< 2016-11-01 16:51:54.692 cst >log:  database system  is ready to accept read only connections< 2016-11-01 16:51:54.692 cst >log:   invalid record length at 0/f001510< 2016-11-01 16:51:54.697 cst  >log:  started streaming wal from primary at 0/f000000 on  timeline 3

This article from "Brave forward, resolutely left" blog, declined reprint!

PostgreSQL Asynchronous Stream Replication Setup