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 <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.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;

Author: Database Technologies

Director, Solutions Architecture @ EnterpriseDB, Bachelor's Degree in Computer Science. Master's Degree in Computer Application Oracle Certified Professional, PostgreSQL Certified Professional, DB2 Certified, ITIL V3 Certified, MongoDB Certified DBA, MongoDB Certified DBA, PostgreSQL Certified Trainer, PostgreSQL Certified Trainer.

12 thoughts on “Asynchronous/Synchronous Streaming Replication in PostgreSQL 9.1”

  1. Is it possible to set up master-master ( two master ) in Postgresql 9.0 version ? I need two master server those will be sync simultaneously. So that when one server will down, other master server can handle everything. Pl. tell me which version this feature have ?

    1. No. PostgreSQL 9.0 supports only Asynchronous Replication (Master -> Slave). Right now, PostgreSQL doesn’t have Master-Master Replication (where reads/write both are allow from any nodes). However, if you want you can look at Postgres-XC which is under development would meet lot of criteria.

  2. Is there a way to replicate only a selected db in DBMS? If so is it possible to configure serverA as master for dbA and serverB as slave for dbA , while using ServerB as master for dbB and ServerA as slave for dbB.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s