PostgreSQL provides the option of synchronous replication to ensure that transactions are committed on the standby servers before they are acknowledged as being committed on the primary server. This can provide an added level of data durability for high availability (HA) systems.
The synchronous_commit configuration parameter controls the behavior of synchronous replication in PostgreSQL. It has three possible values:
- on – This is the default setting. In this mode, the primary server waits for confirmation that the data has been written to disk on all synchronous standby servers before sending the acknowledgement of the commit to the client. This provides the highest level of data durability but can result in slower performance due to the additional overhead of waiting for confirmation from the standby servers.
- off – In this mode, the primary server sends the acknowledgement of the commit to the client as soon as it has written the data to its own disk. This provides the highest level of performance but may result in data loss in the event of a failure.
- remote_write – In this mode, the primary server waits for confirmation that the data has been written to the disk on at least one synchronous standby server before sending the acknowledgement of the commit to the client. This provides a balance between data durability and performance.
Synchronous Standby Replication (SSR) is a feature that allows synchronous replication to be used between the primary and standby servers in a streaming replication setup. In this setup, the primary server sends the transaction data to one or more standby servers in real-time, and waits for confirmation that the data has been written to disk on all synchronous standby servers before sending the acknowledgement of the commit to the client.
To set up SSR, the synchronous_standby_names parameter needs to be set on the primary server to a list of the names of the synchronous standby servers that are allowed to participate in the synchronous replication. On the standby servers, the synchronous_standby_names parameter needs to be set to the name of the synchronous standby server as specified on the primary server.
In summary, synchronous replication provides an added level of data durability for high availability systems. The synchronous_commit parameter controls the behavior of synchronous replication, and Synchronous Standby Replication (SSR) allows synchronous replication to be used between the primary and standby servers in a streaming replication setup.
