0 like 0 dislike

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (13.4k points)  
edited by
Best answer

The benefit of a synchronous PostgreSQL replication over an asynchronous is that in case of master PostgreSQL database server failure you are sure you did not lose any data. The database client connected to the master PostgreSQL database gets a acknowledgement only once the data is written to both, master and standby PostgreSQL database server therefore there is no chance of loosing valuable data in the process.

you have to wait for acknowledgement an every commit so you will get little bit performance issue

If you want to break this performance issue you can adjust syncronous_commit parameter as per your requirement

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) 

More information:https://www.tutorialdba.com/2017/09/postgresql-replication-sync-to-async.html?m=1

Related questions

0 like 0 dislike
1 answer