How to configure replication and recovery of Postgres

Environment

Virtualbox 4.2.6

CentOS 6.3 --> image: CentOS-6.3-x86_64-minimal.iso

Postgres --> rpm: pgdg-centos91-9.1-4.noarch.rpm

 

Create 2 virtual machines in virtualbox,named with MASTER and SLAVE and install Postgres respectively.

Configure Postgres

The following steps are on MASTER.

Step1. Create replication user

A role is an entity that can own database objects and have database privileges; a role can be considered a "user", a "group",or both depending on how it is used.

Postgres =#create role replication Replication;

Postgres =#\du

Step2. Enable client authentication

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

Append the following configuration lines to give access to network.

host replication all all trust

 

Step 3: Allow TCP/IP socket

# vi/var/lib/pgsql/data/postgresql.conf
Find configuration line that read as follows:
listen_addresses='localhost'
Next set IP address(es) to listen on; you can use comma-separated list of addresses; defaults to 'localhost', and '*' is all ip address:
listen_addresses='*'

Add firewall rules or stop firewall, here we stop firewall for simplicity.

#service iptables stop

If this step is not configured correctly,you will receive error like this : “psql : could not connect to server: noroute to host”…

Restart Postgres and you can insert some test records in the master DB here.

 

The following steps are on SLAVE.

Step 1. Stop Postgres and delete data directory

#servicePostgres-9.1 stop

#mv ./data ./data.ori

#mkdir ./data

#chown –R postgres.postgres ./data

#chmod –R 700 ./data

 

Step2. Create base backup

#/usr/pgsql-9.1/bin/pg_basebackup -D ./data -Ft -x -z -P -v -U replication -h MASTER

Note: you need to add host resolution to /etc/hosts if you use hostname of MASTER.

Make sure we got a backup, base.tar.gz in the data directory.

 

Step3. Extract base backup

#cd./data

#tar –xzf base.tar.gz

#rm –rf pg_xlog

#mkdir pg_xlog

 

Step4. Create recovery.conf file

In the data directory

#virecovery.conf

standby_mode = 'on'

primary_conninfo = 'host=MASTER user=replication'

trigger_file='/var/lib/pgsql/9.1/data/trigger.txt'

Trigger_file is a file whose presence ends recovery in the standby

 

step5. Update permissions

#chown -R postgres.postgres ./data

#chmod -R 700 ./data

Test the correctness of configuration

On the SLAVE server

#cd /var/lib/pgsql/9.1/data

#touch trigger.txt

After a while, the recovery.conf file changes its name to recovery.done, and this means the failover succeeds. You can psql to the SLAVE node and check whether the data is the same as MASTER.