Setup Postgres Master Slave Replication

Configure Master Server

$ cd /home/postgres/data
$ sudo vi postgresql.conf

Edit following lines:
listen_address = ‘*’
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = ‘ cp %p /home/postgres/data/archive/%f’
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = ‘pgslave001’
hot_standby = on

The archive mode is enabled, so we need to create a new directory for archiving purposes.
$ cd /home/postgres/data/
$ sudo mkdir archive
$ sudo chmod 700 archive
Sudo chown –R postgres:postgres /home/postgres/data/archive

$ cd /home/postgres/data/
$ sudo vi pg_hba.conf
Edit following lines:
# Localhost
host replication replica md5

# PostgreSQL Master IP address
host replication replica md5

# PostgreSQL Slave IP address
host replication replica md5

$ sudo service postgresql restart

After that, login as postgres user, and create a new ‘replica’ user with password ‘XXXXX’.
Ignore this if user ‘replica’ is created.

Configure Slave server

Before we start to configure the slave server, stop the postgres service.

$ sudo service postgres stop

Then, backup the /home/postgres/data.

$ cd /home/postgres/data
$ sudo cp data data-backup
$ cd /home/postgres/data
$ sudo rm –rf *

Next, login as the postgres user and copy all data directory from the ‘Master’ server to the ‘Slave’ server as replica user.

$ su – postgres
$ pg_basebackup -h -U replica -D /home/postgres/data -P –xlog

Then, replace the Slave postgresql.conf & recovery.conf back to /home/postgres/data
$ cd /home/postgres/data-backup
$ sudo cp postgresql.conf recovery.conf /home/postgres/data

Example of postgres.conf in Slave:
hot_standby = on

Example of recovery.conf in Slave:
$ sudo chmod 600 recovery.conf
$ sudo chown postgres:postgres recovery.conf

Restart postgresql
$ sudo service postgresql start

pg_is_in_recovery() : Function which tells whether standby is still in recovery mode or not.
1. select pg_is_in_recovery();
Result: t = Slave, f = Master

Check the streaming state replication of PostgreSQL.

2. psql -c “select application_name, state, sync_priority, sync_state from pg_stat_replication;”

Example Screenshot:

3. psql -x -c “select * from pg_stat_replication;”

Example Screenshot:

Note: Start all Tomcat Projects after Master-Slave replication is up.

Slave is READ Only


To pause, run the following query on the slave server:
select pg_xlog_replay_pause();

To resume, run
select pg_xlog_replay_resume();

Be the first to comment

Leave a Reply

Your email address will not be published.