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.