How To Set Up Master Slave Replication on PostgreSQL

Sometimes we come across situations wherein data needs to be replicated as it is in another database. In this blog we will see how to set up Master Slave Replication on PostgreSQL using Ubuntu OS.

We are assuming that you already have a setup of postgreSQL in the main server (now on called as master) and also a setup of Postgres in the replication server (now on called as slave). As we are aware that PostgreSQL creates a user called “postgres” in order to handle its initial databases, lets login to this particular user to start our process.

Step:1
sudo passwd postgres
Switch over to the postgres user like this:
sudo su – postgres

Step:2
Generate an ssh key for the postgres user:
ssh-keygen
Press “ENTER” to all of the prompts that follow.

Step:3
Transfer the keys to the other server by following cmd:
ssh-copy-id IP if opposite_server

Step:4
Configure the Master Server

First, we will create a user called “rep” that can be used solely for replication:
psql -c “CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD ‘yourpassword’;”

Step:5
cd /etc/postgresql/9.1/main

vim  pg_hba.conf
add below line at bottom of the file

host    replication     rep     IP_address_of_slave/32   md5

:wq!(SAVE FILE)

Step:6
vim postgresql.conf

Find below parameters. Uncomment them if they are commented.

listen_addresses = ‘localhost,IP_address_of_current_host’
wal_level = ‘hot_standby’
archive_mode = on
archive_command = ‘cd .’
max_wal_senders = 1
hot_standby = on

:wq!(SAVE FILE)

Restart the master server to take effect your changes:
service postgresql restart

Step:7
Configure the Slave Server
service postgresql stop

cd /etc/postgresql/9.1/main
Adjust the access file to allow the other server to connect to this.

vim pg_hba.conf
add below line at bottom of the file
host    replication     rep     IP_address_of_master/32  md5

:wq!(SAVE FILE)

Step:8
vim postgresql.conf

You can use the same configuration options you set for the master server, modifying only the IP address to reflect the slave server’s address:

listen_addresses = ‘localhost,IP_address_of_THIS_host’
wal_level = ‘hot_standby’
archive_mode = on
archive_command = ‘cd .’
max_wal_senders = 1
hot_standby = on

:wq!(SAVE FILE)

Step:9

Replicating the Initial database:
On the master server, we can use an internal postgres backup start command to create a backup label command. We then will transfer the database data to our slave and then issue an internal backup stop command to clean up:

psql -c “select pg_start_backup(‘initial_backup’);”
rsync -cva –inplace –exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/
psql -c “select pg_stop_backup();”

Step:10
We now have to configure a recovery file on our slave.
cd /var/lib/postgresql/9.1/main
vim recovery.conf

Fill in the following information in to it,Make sure to change the IP address of your master server and the password for the rep user you created:

standby_mode = ‘on’
primary_conninfo = ‘host=master_IP_address port=5432 user=rep password=yourpassword’
trigger_file = ‘/tmp/postgresql.trigger.5432’

The last line in the file, trigger_file, is one of the most interesting parts of the entire configuration. If you create a file at that location on your slave machine, your slave will reconfigure itself to act as a master.

Now start your slave server. Type:

service postgresql start

Step:11
You’ll want to check the logs to see if there are any problems. They are located on both machines here:

less /var/log/postgresql/postgresql-9.1-main.log

Step:12
Test the Replication
On the master server, as the postgres user, log into the postgres system by typing:

psql

We will create a test table to create some changes:

CREATE TABLE rep_test (test varchar(50));

Now insert value into it

INSERT INTO rep_test VALUES (‘data1’);
INSERT INTO rep_test VALUES (‘data2’);
INSERT INTO rep_test VALUES (‘data3’);
INSERT INTO rep_test VALUES (‘data4’);
INSERT INTO rep_test VALUES (‘data5’);

You can now exit out of this interface by typing:
\q

Step:13

Now, on the slave, enter the database interface in the same way:

psql

Now, we can see if the data we entered in the master database has been replicated on the slave:

SELECT * FROM rep_test;

test
—————–
data1
data2
data3
data4
data5
(5 rows)

Excellent! Our data has been written to both the master and slave servers !!!

2 responses to “How To Set Up Master Slave Replication on PostgreSQL”

  1. pramod says:

    hello am newbie am learning about replication and cluster in postgres and in step 3 i cant understand ssh-copy-id ip if opposite_sever as i should type in terminl as ex: ssh-copy-id 192.168.x.xx (or as it is i give it give error)

    • nevpro says:

      Hi,

      First check are you able to ssh that particular ip by giving this command=> ssh -v 192.168.x.xx.
      and
      remove know host from file
      ssh-keygen -f “/root/.ssh/known_hosts” -R 192.168.x.xx

You must be logged in to post a comment.