PostgreSQL Synchronous Stream Replication Setup

Node	Ip	Role
Master	10.10.100.1	Master
Standby1	10.10.100.2	Standby1
Standby2	10.10.100.3	Standby2

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 = 32synchronous_standby_names = ' standby01,standby02 '

Restart Pgsql on Master

/etc/init.d/postgresql-9.5 restart

All two standby need to get a basic, fully prepared

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

The postgresql.conf file on the standby node needs to be modified on all two standby to set the state of standby for the repository.

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

Modify the Standby1 recovery.c file, set the synchronization information of the master node and the trigger file

Standby_mode = ' on ' primary_conninfo = ' application_name=standby01 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 '

Modify the Standby1 recovery.conf file, set the synchronization information of the master node and the trigger file

Standby_mode = ' on ' primary_conninfo = ' application_name=standby02 user=replication password=replication host= 10.10.100.1 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres '

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.

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

PostgreSQL Synchronous Stream Replication Setup