Shulou(Shulou.com)06/02 Report--

In the PG9.6 version, only priority-based synchronous backup is supported.

In PG10 and later versions, synchronous_standby_names, the preferred commit mechanism for synchronous replication based on Quorum, has been introduced.

Synchronous replication supports one or more synchronous backup servers, and transactions will wait until all synchronous backup servers acknowledge receipt of their data. The number of synchronization fallbacks that the transaction must wait for its reply is specified by the synchronous_standby_names. This parameter also specifies a list of backup server names and methods (FIRST and ANY) to select synchronization backups from the listed fallbacks.

The method FIRST specifies a priority-based synchronous replication and has the transaction commit wait until their WAL records are copied to the required number of synchronization backups selected based on priority. Earlier backups that appear in the list are given higher priority and will be considered as synchronous backups. Other backup servers at the bottom of this list represent possible synchronization backups. If any current synchronization backup is disconnected for any reason, it will immediately be replaced by the next highest priority backup.

Example 1 of a priority-based multi-synchronization backup synchronous_standby_names:

Synchronous_standby_names ='S1, S2'

In this example, S1 is a synchronous standby, S2 is a potential synchronous standby, and S2 is upgraded to a synchronous standby when S1 is not available

Example 2 of a priority-based multi-synchronization backup synchronous_standby_names:

Synchronous_standby_names = 'FIRST 2 (S1, S2, S3)'

In this example, if there are four backup servers S1, S2, S3, and S4 running, the first two backup servers S1 and S2 in the list will be selected as synchronization backups.

When the main library commits the transaction, it must wait for S1 and S2 to receive and write the WAL log file before it can be returned to the client successfully.

S3 is a potential synchronization backup, and when any of S1 or S2 fails, it will be upgraded to a synchronization backup.

S4 is an asynchronous backup because its name is not in the list.

Example of synchronous replication based on the number of Quorum:

An example of synchronous_standby_names based on a specified number of multi-synchronization backups:

Synchronous_standby_names = 'ANY 2 (S1, S2, S3)'

In this example, if there are four backup servers S1, S2, S3, and S4 running, the transaction commit will wait for a reply from at least two backup servers in S1 S2 S3.

S4 is an asynchronous backup because its name is not in the list.

The synchronization status of the backup server can be viewed using the pg_stat_replication view.

When a backup server is attached to the primary server for the first time, it will be in a state that has not been synchronized correctly. This is described as the catch-up mode. Once the lag between the backup server and the primary server becomes zero for the first time, we are in a real-time streaming state. It may be in catch-up mode for a long time after the backup server is created. If the backup server is shut down, the catch-up cycle will be increased, and the increase will be determined by the length of time the backup server is shut down. Only when the backup server reaches the streaming state can it become a synchronous backup. This state can be viewed using the pg_stat_replication view.

Note:

If the primary server restarts while the commit is waiting for confirmation, those waiting transactions will be marked as fully committed when the primary database is restored. There is no way to confirm that all backup servers have received all the WAL data that has not been processed when the primary server crashes. Some transactions may not appear as committed on the backup server, even if they appear as committed on the primary server. The guarantee we provide is that the application will not receive an explicit confirmation of a transaction's successful commit until the WAL data has been safely received by all backup servers.

Experiment part:

Stream replication experiment with one master and two slaves (the cluster is built using patroni, which automatically builds synchronous replication nodes):

Postgres=# select pid,usename,application_name,client_addr,state,sync_state,sync_priority from pg_stat_replication

Pid | usename | application_name | client_addr | state | sync_state | sync_priority

-+-

58691 | replicator | pg_node3 | 192.168.2.189 | streaming | sync | 1

58712 | replicator | pg_node2 | 192.168.2.188 | streaming | potential | 1

(2 rows)

Description:

Sync represents the synchronization library

Potential represents a potential synchronization library

In the PG streaming replication cluster built by patroni, my configuration has enabled priority-based multiple repositories. So we can shut down 1 standby node at will, but if we close all the standby nodes, it will cause modification blocking of the primary node.

The following is a demo map of my self-built Quorum-based synchronous repository (because I can't find any support for Quorum in patroni. I don't bother to look for it for the time being.

Modify the following contents of postgresql.conf:

Synchronous_standby_names = 'ANY 2 (pg_node2,pg_node3)'

Then reload the configuration file for pg:

Pg_ctl reload

Then query whether the configuration takes effect in the main database:

Postgres=# show synchronous_standby_names

Synchronous_standby_names

-

ANY 2 (pg_node2, pg_node3)

(1 row)

At this time, what you see in the main database is as follows:

Postgres=# select pid,usename,application_name,client_addr,state,sync_state,sync_priority from pg_stat_replication

Pid | usename | application_name | client_addr | state | sync_state | sync_priority

-+-

65373 | replicator | pg_node3 | 192.168.2.189 | streaming | quorum | 1

65375 | replicator | pg_node2 | 192.168.2.188 | streaming | quorum | 1

(2 rows)

As can be seen in the figure, the sync_state of the two standby nodes is quorum, and the sync_priority priority is 1 (the value of the Quorum-based synchronous slave library sync_prioriy has no effect on the slave database, which can be ignored)

Then close a synchronous slave library pg_node2, when we go to the main library to insert data, we can see that it is blocked.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.