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

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

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

PostgreSQL Database startup / shutdown /restart

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger