PostgreSQL Replication

The following setup were tested with PostgreSQL 9.1.1.  As far as I know, the same setup can also be achieved starting from version 9.0.  Version 9 added built-in replication to the PostgreSQL database.  In the past PostgreSQL replication can be achieved by using third party software (e.g., Bucardo).  Here are the steps:

On the master server assuming an IP address of 192.168.1.107:

Step 1

Edit the postgresql.conf file as follows:

listen_addresses = 'localhost, 192.168.1.107'

wal_level=hot_standby
max_wal_senders=1
wal_keep_segments=25

Step 2

Assuming the slave server IP address of 192.168.1.104, edit the pg_hba.conf as follows:

host replication all 192.168.1.104/32 trust 

Step 3

On psql CLI (as postgres user), issue the command:

SELECT pg_start_backup('backup');

Step 4

Copy the data directory of the master server and place it on the slave server.   Use the command on psql CLI (as postgres user) to determine the physical location of the directory:

SHOW data_directory;

On the Slave Server assuming an IP address of 192.168.1.104: 

Step 5

Edit the postgresql.conf as follows:

hot_standby=on

Step 6
Create recovery.conf file as follows:

standby_mode='on'
primary_conninfo='host=192.168.1.107'

Step 7
Delete the postmaster.pid file

# rm postmaster.pid

On the Master Server:

Step 8
Issue the command on psql CLI as follows:

SELECT pg_stop_backup();

Simple ignore any warning messages.


Step 9
Restart PostgreSQL on the master server.

On the Slave Server:

Step 10
Start the PostgreSQL server.

That's it.  One last final note.  Test your setup by issuing an INSERT or CREATE TABLE command on the master server.  Check if the data are reflected on the slave server.  If it is reflected, then you have just done database replication on PostgreSQL.