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

Standard

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

postgres replication configuration

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

pg_hba

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();"

postgres low level backup

Now move this data to slave database server.

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

scp pgbase backup

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

unzip data directory

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

postgres hotstandby

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'

postgres standy recovery

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

start postgres 92

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.

postgres replication test

If you have any question, please post them below.

Facebooktwitterredditpinterestlinkedinmail

About Prashanth Goriparthi

Data Janitor

28 Comments

  1. Pingback: maillot

  2. 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!

  3. 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! 🙂

    • 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.

  4. 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!

  5. 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

  6. 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

  7. 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.

  8. 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

  9. 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?

  10. 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!!

Leave a Reply

* Captcha [Please fill/solve below] : * Time limit is exhausted. Please reload the CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.