Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Replication Configuration & Async to Sync

We discussing about how to setup  Synchronous and Asynchronous Replication in PostgreSQL

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 )
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 superuserFor 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)
In PostgreSQL-9.3.1 the field is called 'pid' rather than 'procpid'

postgres=# select pid,usesysid, usename, application_name, client_addr, state, sent_location,write_location,sync_state from pg_stat_replication;
 pid | 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)
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)
In PostgreSQL-9.3.1 the field is called 'pid' rather than 'procpid'

postgres=# select pid,usesysid, usename, application_name, client_addr, state, sent_location,write_location,sync_state from pg_stat_replication;
 pid | 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;

How to improve the performance of postgresql replication ?

Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

PostgreSQL Pgbadger Installation On Linux

7 Steps to configure BDR replication in postgresql

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform