postgresql-主备同步模式测试

测试1：synchronous_standby_names=* 是否等同于synchronous_standby_names=any1(s1,s2,s3)

结论：不等同

ANY 2(s1,s2):quorum,quorum,async

ANY 1(s1,s2,s3):quorum,quorum,quorum

*:potential,potential,sync

ANY 2(s1,s2,s3):quorum,quorum,quorum

FIRST 2(s1,s2,s3):sync,sync,potential

postgres=# select application_name ,client_addr,state,sent_lsn,write_lsn,flush_lsn,write_lag,sync_state from pg_stat_replication ;
  application_name  | client_addr  |   state   |  sent_lsn  | write_lsn  | flush_lsn  | write_lag | sync_state 
--------------------+--------------+-----------+------------+------------+------------+-----------+------------
 172.16.16.2:11011  | 172.16.16.2  | streaming | 3/20000000 | 3/20000000 | 3/20000000 |           | quorum
 172.16.16.15:11009 | 172.16.16.15 | streaming | 3/20000000 | 3/20000000 | 3/20000000 |           | quorum
 172.16.16.16:11000 | 172.16.16.16 | streaming | 3/20000000 | 3/20000000 | 3/20000000 |           | quorum
(3 rows)

postgres=# show synchronous_standby_names ;
                   synchronous_standby_names                    
----------------------------------------------------------------
 ANY 2(172.16.16.2:11011,172.16.16.15:11009,172.16.16.16:11000)
(1 row)

postgres=# select application_name ,client_addr,state,sent_lsn,write_lsn,flush_lsn,write_lag,sync_state from pg_stat_replication ;
  application_name  | client_addr  |   state   |  sent_lsn  | write_lsn  | flush_lsn  | write_lag | sync_state 
--------------------+--------------+-----------+------------+------------+------------+-----------+------------
 172.16.16.2:11011  | 172.16.16.2  | streaming | 3/21000000 | 3/21000000 | 3/21000000 |           | sync
 172.16.16.15:11009 | 172.16.16.15 | streaming | 3/21000000 | 3/21000000 | 3/21000000 |           | sync
 172.16.16.16:11000 | 172.16.16.16 | streaming | 3/21000000 | 3/21000000 | 3/21000000 |           | potential
(3 rows)

postgres=# show synchronous_commit ;
 synchronous_commit 
--------------------
 off
(1 row)

postgres=# show synchronous_standby_names ;
                    synchronous_standby_names                     
------------------------------------------------------------------
 FIRST 2(172.16.16.2:11011,172.16.16.15:11009,172.16.16.16:11000)
(1 row)

postgres=# show synchronous_standby_names ;
          synchronous_standby_names           
----------------------------------------------
 ANY 2(172.16.16.15:11009,172.16.16.16:11000)
(1 row)

postgres=# select * from pg_stat_replication ;
 pid  | usesysid | usename |  application_name  | client_addr  | client_hostname | client_port |           backend_start           | backend_xmin |   state  
 |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state 
------+----------+---------+--------------------+--------------+-----------------+-------------+-----------------------------------+--------------+----------
-+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
 3338 |       10 | tbase   | 172.16.16.16:11000 | 172.16.16.16 |                 |       59810 | 2022-10-13 17:58:23.965964 +08:00 |              | streaming
 | 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 |           |           |            |             1 | quorum
 3318 |       10 | tbase   | 172.16.16.15:11009 | 172.16.16.15 |                 |       60350 | 2022-10-13 17:58:23.834684 +08:00 |              | streaming
 | 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 |           |           |            |             1 | quorum
 3298 |       10 | tbase   | 172.16.16.2:11011  | 172.16.16.2  |                 |       58738 | 2022-10-13 17:58:23.774640 +08:00 |              | streaming
 | 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 |           |           |            |             0 | async
(3 rows)

postgres=# select * from pg_stat_replication ;
 pid  | usesysid | usename |  application_name  | client_addr  | client_hostname | client_port |           backend_start           | backend_xmin |   state  
 |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state 
------+----------+---------+--------------------+--------------+-----------------+-------------+-----------------------------------+--------------+----------
-+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
 3338 |       10 | tbase   | 172.16.16.16:11000 | 172.16.16.16 |                 |       59810 | 2022-10-13 17:58:23.965964 +08:00 |              | streaming
 | 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 |           |           |            |             1 | quorum
 3318 |       10 | tbase   | 172.16.16.15:11009 | 172.16.16.15 |                 |       60350 | 2022-10-13 17:58:23.834684 +08:00 |              | streaming
 | 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 |           |           |            |             1 | quorum
 3298 |       10 | tbase   | 172.16.16.2:11011  | 172.16.16.2  |                 |       58738 | 2022-10-13 17:58:23.774640 +08:00 |              | streaming
 | 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 |           |           |            |             1 | quorum
(3 rows)

postgres=# show synchronous_standby_names ;
                   synchronous_standby_names                    
----------------------------------------------------------------
 ANY 1(172.16.16.2:11011,172.16.16.15:11009,172.16.16.16:11000)
(1 row)

postgres=# select * from pg_stat_replication ;
  pid  | usesysid | usename |  application_name  | client_addr  | client_hostname | client_port |           backend_start           | backend_xmin |   state 
  |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state 
-------+----------+---------+--------------------+--------------+-----------------+-------------+-----------------------------------+--------------+---------
--+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
 31590 |       10 | tbase   | 172.16.16.15:11009 | 172.16.16.15 |                 |       41754 | 2022-10-13 17:02:21.581610 +08:00 |              | streamin
g | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 |           |           |            |             1 | potential
 31589 |       10 | tbase   | 172.16.16.16:11000 | 172.16.16.16 |                 |       64470 | 2022-10-13 17:02:21.540937 +08:00 |              | streamin
g | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 |           |           |            |             1 | potential
 31588 |       10 | tbase   | 172.16.16.2:11011  | 172.16.16.2  |                 |       42160 | 2022-10-13 17:02:21.447645 +08:00 |              | streamin
g | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 |           |           |            |             1 | sync
(3 rows)


postgres=# show synchronous_standby_names ;
 synchronous_standby_names 
---------------------------
 *
(1 row)

postgres=# show synchronous_commit ;
 synchronous_commit 
--------------------
 local
(1 row)

postgres=# 

测试2：synchronous_standby_names和synchronous_commit的优先级

如果synchronous_standby_names=*，synchronous_commit=off的一主三备环境，是如何实现的主备同步状态

结论：

如果 synchronous_standby_names 参数不指定值，同步复制不会启用。

即使同步复制被启用，通过设置 synchronous_commit 配置参数为 local 或者 off，单个事务可以被配置为不等待 standby 端的 wal 被应用。

所以可以简单理解synchronous_standby_names 优先级大于synchronous_commit。