Thursday, October 20, 2011

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;

2 comments:

  1. We have got a configuration in PostgreSQL 9.1
    Application server writes concurrently to Database Server 1 ( primary) and Database Server 2 (secondary ). When a DML operation takes place application sever has to write to both Database servers. I have read that this can be achieved by firing a trigger from Database Server 1 while any DML operation is committed. But considering the overheads we avoid that plan.
    Can you suggest a method to achieve data replication in PostgreSQL 9.1 for the configuration. .

    ReplyDelete
    Replies
    1. PostgreSQL 9.1, support Asynchronous and Synchronous Replication. You can try anyone of those if its fit your requirement.
      With Streaming Replication + Hot Standby you get High Availability and Read Scalability.

      Delete