Setup replication with Postgres 9.2 on CentOS 6/Redhat EL6/Fedora

This post explains how to setup replication with Postgres 9.2 on CentOS 6/Redhat EL6/Fedora in Master slave configuration. This is also often called as streaming replication. Before you dig into this topic more, I would suggest going through my other post of implementing point in time recovery (PITR) using postgres 9.2. I am assuming that you have at-least two PostgreSQL 9.2 servers for establishing replication between those two database servers. If you don’t have experience installing PostgreSQL 9.2 on CentOS or Redhat please read my other blog post here on how to install PostgreSQL 9.2 server and get it up and running in no time. First we need to work on master database server and we will eventually move on to slave server configuration.

1. Master server configuration:

Lets begin with master server configuration updates that we need to make in-order to enable replication.

Edit configuration file:

vi /var/lib/pgsql/9.2/data/postgresql.conf

And update the following variables:

wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 50

Now we need to grant access to read this servers WAL logs from hot standby server.

Edit host base auth configuration file:

vi /var/lib/pgsql/9.2/data/pg_hba.conf

and add the following line (replace this ip with your slave host name or its ip):

host     replication     postgres       192.168.1.131/32     trust

Restart database engine to get all the changes into effect.

service postgresql-9.2 restart

Next thing we need to do is take data snapshot of data from master and then move that to slave server.

su - postgres
psql -c "SELECT pg_start_backup('replbackup');"
tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/9.2/data
psql -c "SELECT pg_stop_backup();"

Now move this data to slave database server.

scp /tmp/db_file_backup.tar root@opensourcedbms_pg_slave:/tmp/

2. Slave server configuration:

First stop server if it is running

service postgresql-9.2 stop

Move the existing data directory to a new folder.

mv /var/lib/pgsql/9.2/data/ /var/lib/pgsql/9.2/data.old

Unzip master server data snapshot file that is copied into this server.

tar xvfP /tmp/db_file_backup.tar

Remove postmaster.pid so standby server does not see the primary server’s pid as its own.

rm -f /var/lib/pgsql/9.2/data/postmaster.pid

Now edit configuration file and tweak hot_standby variable.

Edit configuration file :

vi /var/lib/pgsql/9.2/data/postgresql.conf

and update the following variable:

hot_standby = on

Now we need to create a recovery.conf file for this slave server to start receiving logs from master. Postgres installation comes with a sample recovery file, please copy it from appropriate location or you can find a sample file in my other PITR for postgres post.

cp /usr/pgsql-9.2/share/recovery.conf.sample /var/lib/pgsql/9.2/data/recovery.conf

Edit this recovery.conf file and update standby server settings:

vi /var/lib/pgsql/9.2/data/recovery.conf

standby_mode = on
primary_conninfo = 'host=opensourcedbms_pg_master port=5432'

Update permissions on recovery.conf file:

chown postgres.postgres /var/lib/pgsql/9.2/data/recovery.conf

Now start slave database server.

service postgresql-9.2 start

Congratulations, you have now successfully started your streaming replication hot-standby server. It may take few minutes for the server to be fully up and running as it needs to sync logs with master.

To test replication, simple add/insert into a table on master server and query the same from slave server.

If you have any question, please post them below.

29 Comments

Pingback: maillot

Thanks !
Very usefull post and clear explanations !

Nice post, thanks. I was able to configure with the provided steps.

Just one thing to keep in mind:
while editing recovery.conf on slave server, you should specify complete server hostname or IP

cheers!

Nice post, thanks. I was able to configure with the provided steps.

Just one thing to keep in mind:
while editing recovery.conf on slave server, you should specify complete server hostname or IP.
example:
primary_conninfo = ‘host=192.168.1.187 port=5432’
or
primary_conninfo = ‘host=server1.xyz.com port=5432’

Cheers! 🙂

One question:
I can do this in a virtual machine with different instances?. or I have to do it with 2 different servers?

Prashanth Goriparthi

August 19, 2013 at 8:36 pm

Yes you can do in a VM. You don’t have to have 2 different servers. But the real use case usually is when replicated across multiple machines.

Reply

thnks Prashanth

Super! Thank you, Prashanth!
I’ve just created replication with master on CentOS6.2 server and slave on RedHat6.1
Your description is very helpful!

Shankar

July 2, 2015 at 5:17 am

Hi Andrily,
Can i configure postgres replication

Reply
- Shankar
  
  July 2, 2015 at 5:18 am
  
  Can i configure replication between rhel and ubuntu?
  
  Reply

Nice post, thanks.
Please, in case of failure of the master, as I proceed to take the slave to the master settlement?

hi Sir, I was implement follow you. now I want you to show how to do failover. Thanks!

Hi, Nice post.
We need to do master-master replication between postgresql. I got master-slave configuration working but not getting idea how we can configure master-master replication.

Will you able to point out the places to make master-master replication working?

Please let me know.

Thanks,
Samir

Thanks for the great post Prashanth,

I am able to do replication (master-active and slave-standby) with the help of this post.

Could you help me to achieve active active HA pair in postgresql-9.3, it would be a great help for me.

Thanks in advance,
Sethupathi.T

Nice Document.its very helpful to us

Thanks..

It’s very useful one..

Thanks!….

Thanks!!!!! 🙂

Many Thanks, just one question:
While the Slave Server is replicating Master’s Data this means all the Slave’s DB is read-only right?
Is there a Possibility to actually create any new object (Trigger or View) into the Slave server, like setting Slave into Operation Mode? I know is not very common as is a Replication of it’s Master but is there a way?
Please Advice.

Hi I have a SAP running on a RHEL 6.0 OS on a standalone production server I want to make a hot standby server which is just the replication and can take over in case of failure how to go about the same

This method replicates the whole DB structure correct? If I have several DB inside postgre and I only want to replicate some of the DB and not all of them how can I accomplish that?

hi … thanks for your tutorial.. this helful ..:D

thank you for this great post! I want a help to do a replication with archiving wal files(archive and restore command) is postgres 9.5 / RedHat 7 – thank you for helping prashanth!!