In this post, we configure Streaming Replication between One Master and 2 Standbys(Standby 1/Standby 2). Replication will be 1:1 but not cascading. Below are my setup details

    OS           - RedHat Enterprise Linux 7.x
	Master IP    - 172.31.34.34
	Standby 1 IP - 172.31.32.122
	Standby 2 IP - 172.31.41.249

Installation

Today www.postgresql.org distributes binaries in different formats contributed by many companies. For this setup, am going to use YUM method of installation as shown in the main website link here. Follow the steps as shown on each nodes(Master/Standby1/Standby2)

On Master

1. Install PostgreSQL 10 binaries

    # yum install postgresql10-server postgresql10
   

2. Now, create data directory using setup script provided by the binaries. Script will create "/var/lib/pgsql/10/data" data directory.

    # /usr/pgsql-10/bin/postgresql-10-setup initdb
   

Standby 1 & Standby 2

1. Install PostgreSQL 10 binaries on Standby 1 and Standby 2 nodes like we did for Master Node.

    yum install postgresql10-server postgresql10
   

   Remember, Standby nodes are created from a backup copy of the Master node. Once Standby Nodes started, the replication operation rolls forward the group commit operations to the standby database. As database modifications are applied to the Master database, WAL-SENDER process sends the changes to WAL-RECEIVER process on the Standby to stay synchronized.

   Thus, we have to install PostgreSQL 10 binaries and no need to create database directory (initdb) on the Standby Nodes.

Configuration

Follow the steps on each node to complete the Streaming Replication.

On Master

In “Installation” section on “Master Node” we have installed the PostgreSQL 10 binaries and also create the data directory. Now, before starting the Master Database we need to adjust the parameters to enable the replication feature. Below are the parameters I have changed in my setup for Connection, Logging, Write-ahead-log & Replication. I have added all parameter changes to the bottom of the “/var/lib/pgsql/10/data/postgresql.conf” file. (Don’t worry, am not confusing :), PostgreSQL take the latest parameter values from the “postgresql.conf” file)

    #cat <<EOT >>/var/lib/pgsql/10/data/postgresql.conf

	# Connection
	#---------------
	listen_addresses = '*'
	max_connections = 150

	# Logging to run Pgbader reports
	#-------------------------------
	log_directory = 'log'
	log_rotation_size = 100MB
	log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
	log_checkpoints = on
	log_lock_waits = on
	log_temp_files = 0
	log_autovacuum_min_duration = 0
	log_error_verbosity = default
	log_min_duration_statement = 2000 # 2secnds - change as per monitoirng


	# Write-Ahead Log
	#----------------
	synchronous_commit = off
	archive_mode = on 
	archive_command = '/bin/rsync -a %p /path/to/wal_archive/location/%f'
	wal_log_hints = on
	min_wal_size = 512MB
	max_wal_size = 2GB

	# Replication
	#------------
	wal_level = replica
	max_wal_senders = 5
	max_replication_slots = 10
	wal_keep_segments = 500
	hot_standby = on
	hot_standby_feedback = on
	max_logical_replication_workers = 6
	max_sync_workers_per_subscription = 2
	EOT

Now, adjust the “/var/lib/pgsql/10/data/pg_hba.conf” to allow Standby 1, Standby 2 and other client IPs to connect to Master Node. Below, my setup related entries in $PGDATA/pg_hba.conf file snippet.

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

	# "local" is for Unix domain socket connections only
	local   all             all                                     md5
	# IPv4 local connections:
	host    all             all             127.0.0.1/32            md5
	host    all             all             172.31.32.122/32        md5
	host    all             all             172.31.41.249/32        md5
	# IPv6 local connections:
	host    all             all             ::1/128
	# Allow replication connections from localhost, by a user with the
	# replication privilege.
	local   replication     all                                     md5
	host    replication     all             127.0.0.1/32            md5
	host    replication     all             172.31.32.122/32        md5
	host    replication     all             172.31.41.249/32        md5
	host    replication     all             ::1/128                 md5

Thats all, start the Master Node database service.

    [root@masterdb ~]# systemctl start postgresql-10

On Standby 1 Node

After configuring Master Node, we need to take the file system level backup of Master database to build the Standby Nodes.

1. Switch as Postgres User

    # su - postgres
   

2. Take the backup using “pg_basebackup” utility of PostgreSQL 10 version installed on the Standby nodes.

    -bash-4.2$ pg_basebackup --host='172.31.34.34' \
    > --port=5432 \
    > --username=postgres \
    > --wal-method=stream \
    > --write-recovery-conf \
    > --progress \
    > --pgdata=/var/lib/pgsql/10/data
   

3. Backup command creates “recovery.conf” file which includes Master nodes connection information, hwever we need to add recovery/failover related parameters to “/var/lib/pgsql/10/data/recovery.conf” file. Run below command to add the parameters to recovery.conf file.

    cat <<EOT >>/var/lib/pgsql/10/data/recovery.conf
    trigger_file = '/tmp/master_down_now.txt'
    recovery_target_timeline = 'latest'
    EOT
   

   Reference links:

   https://www.postgresql.org/docs/10/static/recovery-target-settings.html https://www.postgresql.org/docs/10/static/recovery-config.html

4. Start the Standby node using service.

    [root@additional-dbs 10]# systemctl start postgresql-10
   

On Standby 2 Node

Every step shown in “On Standby 1 Node” should be folllowed for Standby Node 2 also.

Verification

After completing the configuration on Master, Standby 1 & Standby 2 Nodes, you can connect to Master node to verify the Standby Nodes are connected and in sync with Master.

    postgres=# select pid,usename,application_name,client_addr,state,sync_state from pg_stat_replication ;
     pid  | usename  | application_name |  client_addr  |   state   | sync_state
	------+----------+------------------+---------------+-----------+------------
     2135 | postgres | walreceiver      | 172.31.41.249 | streaming | async
     1930 | postgres | walreceiver      | 172.31.32.122 | streaming | async
	(2 rows)

That’s all. Its very easy and simple. Hope you liked it. Thank you

Raghav