Asynchronous/Synchronous Streaming Replication in PostgreSQL 9.2

Since, PostgreSQL 9.2 has add more functions 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.2.
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.2)

$ 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 <local directory> -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.2 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;