首页 > 数据库 >postgresql-主备同步模式测试

postgresql-主备同步模式测试

时间:2022-10-14 09:46:39浏览次数:60  
标签:10 同步 postgresql lsn synchronous standby 主备 172.16 20000000

测试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。

标签:10,同步,postgresql,lsn,synchronous,standby,主备,172.16,20000000
From: https://www.cnblogs.com/ddlearning/p/16790565.html

相关文章

  • 表同步更新的问题的触发器
      1sql server 2000 触发器,表同步更新的问题   2有三个表,A ,B,C  3A、B表中含有: A1,B1,C1 三个字段,  4C 表中存放A、B表中的A1、B1......
  • PostgreSQL的WAL日志管理
    wal日志介绍wal日志即writeaheadlog预写式日志,简称wal日志。wal日志可以说是PostgreSQL中十分重要的部分,相当于oracle中的redo日志。当数据库中数据发生变更时:(1)change发生......
  • ubuntu Error: You must install at least one postgresql-client-<version> package
    ubuntu系统上执行psql报错Warning:Noexistingclusterissuitableasadefaulttarget.Pleaseseemanpg_wrapper(1)howtospecifyone.Error:Youmustinsta......
  • 世界局势风云变幻,经济战同步上演!加密货币发挥关键作用!
    现如今,俄乌冲突已成为二战以来发生在欧洲的最大军事冲突。世界局势风云变幻,围绕在武装战争的背后,伴随而来的经济战争也在同步上演。当前加密货币作为全球金融体系中不可忽视......
  • PostgreSQL数据库用户权限管理ACL访问控制示例
    ACL权限缩写权限缩写适用对象类型​​SELECT​​​​r​​(“读”)​​LARGEOBJECT​​​,​​SEQUENCE​​​,​​TABLE​​(andtable-likeobjects),tablecolumn......
  • ogg不同版本之间同步
    环境:主库ogg版本:21C从库ogg版本:12C 同步遇到的问题:2022-10-1314:52:48ERROROGG-02598File/goldengate12c/dirdat/cp000000000,withtrailformatrelea......
  • iPhone备忘录莫名清空?用云同步备忘录可避免这种情况
    最近有多名iPhone用户在社交平台称,自己的iPhone备忘录内容被莫名清空了,并且在苹果的云端服务iCloud中也没有办法找回。对于这种情况,苹果的客户回应,如果有用户遇到类似情况......
  • mysql数据同步
    Navicat同步数据库中数据kettle实现mysql单表增量同步使用Kettle同步mysql数据,增量同步教程执行步骤Navicat定时同步数据库使用Kettle进行数据同步(增量)KETTLE安装及连接M......
  • Java并发(线程状态、线程调度、线程同步)
    Java并发(线程状态、线程调度、线程同步)线程状态​ 线程共有5种状态,在特定情况下,线程可以在不同的状态之间切换。5种具体状态创建状态:实例化一个新的线程对象,还未启......
  • MySQL主从搭建及主从不同步问题处理
    1、使用主从同步的好处:1.通过增加从服务器来提高数据库的性能,在主服务器上执行写入和更新,在从服务器上向外提供读功能,可以动态地调整从服务器的数量,从而调整整个数据库的......