This is my eighth day of the August Challenge.
Background:
L: Why can’t I find the data in the backup database submitted by the primary database when I set the synchronous stream replication?
I: it is estimated that you set the synchronization level is not high enough ~
L: Is there a synchronization level? The synchronization mode is to ensure that the data on both sides of the same? How can the primary database find the data but the standby database can not find the data? If “sync” is displayed in PG_stat_replication, it means that data is consistent.
Me: Take me to tell you slowly.
Pg synchronous stream replication and asynchronous stream replication depend on two parameters:
Synchronous_standby_names Controls which standby synchronization policies are applied to. The usage of this parameter is not described here.
Synchronous_commit Controls the synchronization policy level.
What you think is synchronized may not be what you think is synchronized. Even if the value of sync_state in pg_STAT_replication is sync, it is not necessarily true synchronization. Here is a picture and table to show when withdrawals are synchronous, when withdrawals are asynchronous, and the difference between the different levels.
| Level of synchronization | The set value | An overview of the | Ensure that the scope of |
|---|---|---|---|
| synchronous | remote_apply | After the application initiates a COMMIT, it waits until WAL (update data) is applied on the standby database. It returns a COMMIT response and can be referenced on the standby database. Because data synchronization is fully guaranteed, it is suitable for load allocation scenarios where the standby repository is always up to date. | 1-9 |
| synchronous | On (default) | After the application initiates a COMMIT, WAL is written to the standby database and the COMMIT response is returned. This option provides the best balance between performance and reliability. | 1-6 |
| quasi-synchronous | remote_write | After the application initiates the COMMIT, the application returns the COMMIT response after the WAL is transmitted to the standby database. | From 1 to 5 |
| asynchronous | local | After the application initiates a COMMIT, writes WAL to the primary library, and returns a COMMIT response. | 1 to 2 |
| asynchronous | off | After an application initiates a COMMIT, the application returns a COMMIT response without waiting for WAL to complete the write. | 1 |
As an example, set synchronous_COMMIT to ON for the primary library and recovery_min_apply_delay = ‘1d’ for the standby library. In this case, pg_STAT_replication queries that the value of sync_state column is sync, but fails to find a data backup database.