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