PostgreSQL Streaming Replication Hot Standby

Streaming replication allows a standby server to stay up-to-date with primary. The standby connects to the primary, which streams WAL records to the standby as they're generated.

Hot standby is the term used to describe the ability to connect to the server and run read-only queries.

This method of replication is completely transparent to the client, it doesn't require any changes to database, allows query information from standby server and requires minimum administrative effort.

Prerequisites (consider take a look how to install postgresql here):

• Master and slave must run the same version of PostgreSQL on the same platform
• Standby is identical to primary and performs failover and readonly queries
• Primary IP address: 192.168.10.101
• Standby IP address: 192.168.10.111

Primary / Master

1. Configure streaming replication (file /etc/postgresql/9.1/main/postgresql.conf):

   wal_level = hot_standby
   max_wal_senders = 1
   checkpoint_segments = 10
   checkpoint_completion_target=0.9
   wal_keep_segments = 10
   

2. Create a user used for replication:

   su - postgres
   psql -c "CREATE USER repl REPLICATION LOGIN \
       ENCRYPTED PASSWORD 'CHANGE_ME'"
   exit
   

3. Allow standby server access the primary (file /etc/postgresql/9.1/main/pg_hba.conf):

host  replication  repl  192.168.10.111/32  md5

4. Restart postgresql

   /etc/init.d/postgresql restart
   

Standby / Slave

1. Configure streaming replication (file /etc/postgresql/9.1/main/postgresql.conf):

   wal_level = hot_standby
   hot_standby = on
   

2. Copy primary server base:

   /etc/init.d/postgresql stop
   su - postgres
   cd /var/lib/postgresql/9.1/
   mv main/ main-backup/
   pg_basebackup -h 192.168.10.101 -D main -U repl
   cp -d main-backup/server.* main/
   exit
   

3. Configure primary connection (file /var/lib/postgresql/9.1/main/recovery.conf):

   standby_mode = on
   primary_conninfo = 'host=192.168.10.101 user=repl password=CHANGE_ME'
   trigger_file = '/tmp/postgresql-repl.trigger'
   

4. Restart postgresql

   /etc/init.d/postgresql restart
   

Now you should be able access master in read/write transactions, slave is read only.

Monitoring

You can monitor replication status on master:

su - postgres
psql -x -c "SELECT * FROM pg_stat_replication"

Here is a sample output:

-[ RECORD 1 ]----+------------------------------
procpid          | 4002
usesysid         | 16384
usename          | repl
application_name | walreceiver
client_addr      | 192.168.10.111
client_hostname  |
client_port      | 51967
backend_start    | 2013-10-23 16:03:25.340673+03
state            | streaming
sent_location    | 0/3001324
write_location   | 0/3001324
flush_location   | 0/3001324
replay_location  | 0/3001324
sync_priority    | 0
sync_state       | async

You can read more about streaming replication here.

Troubleshooting

Most likely after install you will get the following hint:

Server instrumentation not installed

This requires install contrib package:

apt-get install postgresql-contrib

and create adminpack extension, here is sql command:

CREATE EXTENSION adminpack;