Setup Replication (Master — Slave Setup) on PostgreSQL10

Below are step by step implementation 

Step 1 - Install PostgreSQL 10

Step 2 - Start and configure PostgreSQL 10

Step 3 - Configure Firewalld

Step 4 - Configure Master server

Step 5 - Configure Slave server

NOTE: Run the Step 1, Step 2 and Step 3 on all Master and Slaves.

In this step, we will configure a master server for the replication. This is the main server, allowing read and write process from applications running on it. PostgreSQL on the master runs only on the '10.0.15.10' IP address, and performs streaming replication to the slave server.

1) => edit the configuration file 'postgresql.conf'

Uncomment the 'listen_addresses' line and change value of the server IP address to '192.168.0.108'.

listen_addresses = '*' or  '192.168.0.108' 

2) => Uncomment 'wal_level' line and change the value to 'hot_standby'.

wal_level = hot_standby

3) VIMP: For the synchronization level, we will use local sync. Uncomment and change value line as below.

synchronous_commit = local

4) Enable archiving mode and give the archive_command variable a command as value.

archive_mode = on

archive_command = 'cp %p /u02/PostgreSQL/archive/%f'

5) For the 'Replication' settings, uncomment the 'wal_sender' line and change value to 2 (in this tutorial,  we use only 2 servers master and slave), and for the 'wal_keep_segments' value is 10.

max_wal_senders = 2

wal_keep_segments = 10

hot_standby = on

6) For the application name, uncomment 'synchronous_standby_names' line and change value to 'pgslave01'.

synchronous_standby_names = 'pgslave01'

7) Moving on, in the postgresql.conf file, the archive mode is enabled, so we need to create a new directory for archiving purposes.

Create a new directory, change its permission, and change the owner to the postgres user.

mkdir -p /u02/PostgreSQL/archive

chmod 700 /u02/PostgreSQL/archive

chown -R postgres:postgres /u02/PostgreSQL/archive

8) Now edit the pg_hba.conf file.

vim pg_hba.conf

Paste configuration below to the end of the line.

# Localhost

 host    replication     replicator         127.0.0.1/32               md5

# PostgreSQL Master IP address

host    replication     replicator         192.168.0.108/32        md5

# PostgreSQL SLave IP address

 host    replication     replicator         192.168.0.107/32       md5

1) Note: Before we start to configure the slave server, stop the postgres service

2) Then go to the postgres directory, and backup data directory.

cd /u02/PostgreSQL/10

mv data data-backup

3) Create new data directory and change the ownership permissions of the directory to the postgres user.

mkdir -p data/

chmod 700 data/

chown -R postgres:postgres data/

4) Next, login as the postgres user and copy all data directory from the 'Master' server to the 'Slave' server as replica user.

pg_basebackup -h 192.168.0.106 -U replicator -p 5432 -D /u02/PostgreSQL/10/data -P -Xs -R

Please replace the IP address with your master’s IP address.

In the above command, you see an optional argument -R. When you pass -R, it automatically creates a recovery.conf  file that contains the role of the DB instance and the details of its master. 

It is mandatory to create the recovery.conf file on the slave in order to set up a streaming replication. 

If you are not using the backup type mentioned above, and choose to take a tar format backup on master that can be copied to slave, you must create this recovery.conf file manually. Here are the contents of the recovery.conf file:

Type your password and wait for data transfer from the master to the slave server.

32512/32512 kB (100%), 1/1 tablespace

5) After the transfer is complete, go to the postgres data directory and edit postgresql.conf file on the slave server.

Check and Change standby config file like below:

hot_standby = on

hot_standby_feedback=on

6) Then check and edit recovery.conf file and add lines below:

7) In the above file, the role of the server is defined by standby_mode. standby_mode  must be set to ON for slaves in postgres.

And to stream WAL data, details of the master server are configured using the parameter primary_conninfo .

The two parameters standby_mode  and primary_conninfo are automatically created when you use the optional argument -R while taking a pg_basebackup. This recovery.conf file must exist in the data directory($PGDATA) of Slave.

8) Now start the database server using pg_ctl command.

pg_ctl -D /u02/PostgreSQL/10/data start

Final Step 6- Validate that postgresql replication is setup

As discussed earlier, a wal sender  and a wal receiver  process are started on the master and the slave after setting up replication. Check for these processes on both master and slave using the following commands.

On Master

$ ps -eaf | grep sender

On Slave

$ ps -eaf | grep receiver

$ ps -eaf | grep startup

You can see more details by querying the master’s pg_stat_replication view.

Primary - Master :-

postgres=# select pg_is_in_recovery();

 pg_is_in_recovery

-------------------

 f

Standby - Slave :-

postgres=# select pg_is_in_recovery();

 pg_is_in_recovery

-------------------

 t