what are the parameters we need to change if we keep replication in sync mode on postgresql ?
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_commitparameter 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)