Asynchronous/Synchronous Streaming Replication in PostgreSQL 9.1

Since, PostgreSQL 9.1 has already been released and there are lot of new features added in it, so, I thought to blog about each features. Today, I am discussing about Synchronous and Asynchronous Replication supported in PostgreSQL 9.1. For Asynchronous Replication, user can use following method: 1. Change Following Parameters in postgresql.conf file of Primary Database:

archive_command = cp -i %p /Users/postgres/archive/%f
archive_mode = on
max_wal_senders = 3 # Maximum 'wal_senders', processes responsible for managing a connection with a standby server
wal_keep_segments = # How many WAL segments (=files) should be kept on the primary, whatever may happen (you won't have to copy them manually on the standby if the standby gets too far behind)
wal_level = hot_standby

2. Make Following changes in pg_hba.conf

host    replication     postgres        [Ipv4 address of Standby Server]/32  trust
host    replication     postgres        [Ipv4 address of Master Server]/32   trust

3. Restart the PostgreSQL Cluster using pg_ctl as given below:

pg_ctl -D [data directory path] restart -m fast

4. Take base Backup of PostgreSQL(Primary) using pg_basebackup command on Standby(This is a new command which has been introduced in PostgreSQL 9.1)

pg_basebackup -D /Users/vibhor/testbackup -v -Fp -l Testbackup -h 127.0.0.1 -U postgres 

Sytax of pg_basebackup is given below:

pg_basebackup -D  -v -Fp -l [backup label] -h [PG server hostname/ip] -U superuser

For more options, user can use following command:

pg_basebackup --help

5. Create recovery.conf file and include following parameters:

restore_command = 'cp -i /Users/postgres/archive/%f %p'  # e.g. 'cp /mnt/server/archivedir/%f %p'
standby_mode = on
primary_conninfo = 'host=localhost port=5432'          # e.g. 'host=localhost port=5432'
trigger_file = '/tmp/makeprimary.trigger'

6. Change following parameters in Postgresql.conf file of Standby:

hot_standby=on/off # If you want to use Hot Standby at the same time.

7. Then Start the Standby using following command:

pg_ctl -D [standby directory] start.

To verify the about asynchronous replication, use can use following command on primary:

postgres=# select procpid,usesysid, usename, application_name, client_addr, state, sent_location,write_location,sync_state from pg_stat_replication;
 procpid | usesysid | usename  | application_name | client_addr |   state   | sent_location | write_location | sync_state 
---------+----------+----------+------------------+-------------+-----------+---------------+----------------+------------
    3661 |       10 | postgres | walreceiver      | ::1         | streaming | 0/D0001D0     | 0/D0001D0      | async
(1 row)

To switch Asynchronous replication to Synchronous Replication,use following steps: 1. Change following parameter in postgresql.conf on Primary Server:

 synchronous_standby_names = 'sync_replication'

2. Reload the above changes on primary using following command:

pg_ctl -D [primary data directory] reload

3. Change following parameter in recovery.conf file on Standby:

primary_conninfo = 'host=localhost port=5432 application_name=sync_replication'
4. Restart the standby using following command:
pg_ctl -D [standby directory] restart -m fast

To verify the switch from Asynchronous to Synchronous, user can use following command on primary:

postgres=# select procpid,usesysid, usename, application_name, client_addr, state, sent_location,write_location,sync_state from pg_stat_replication;
 procpid | usesysid | usename  | application_name | client_addr |   state   | sent_location | write_location | sync_state 
---------+----------+----------+------------------+-------------+-----------+---------------+----------------+------------
    3830 |       10 | postgres | sync_replication | ::1         | streaming | 0/E000078     | 0/E000078      | sync
(1 row)

PostgreSQL 9.1 also, gives flexibility of controlling Synchronous Replication session wise. So suppose if you want one transaction/session to be replicated as asynchronous, then user can set following parameter in his session on primary:

In Transaction:
BEGIN;
  set synchronous_commit=false;
END;

In Session:

set synchronous_commit=false;