MogDB主备流复制—异步转为同步

原创李先生 2021-12-15

489

MogDB主备流复制—异步转为同步

在搭建MogHA时，提示主备不是同步复制状态。查看了流复制状态为异步复制。

本节将介绍如何将异步流复制转化为同步流复制。

涉及参数

synchronous_commit

流复制的同步方式，有主库配置文件postgresql.conf，中的synchronous_commit控制着。所以理解该参数的配置十分重要。

单机环境

参数值	说明	优点	缺点
on 或 local	当事务提交时，WAL先写入WAL buffer 再写到 WAL文件（落盘）中。设置为on表示提交事务时需要等待本地WAL最终落盘后，才向客户端返回成功。	非常安全	数据库性能有损耗
off	当事务提交时，不需要等待WAL先写入WAL buffer 再写到 WAL文件（落盘）中。	提升数据库性能	数据库宕机是最新提交的少量事务可能丢失

流复制环境

参数值	说明	优点	缺点
remote_write	当主库提交事务后，需等待备库接收主库发送的WAL日志流并写入WAL buffer，就向客户端返回成功	只有主库的WAL是落盘的	事务响应时间快
on	当主库提交事务后，需等待备库接收主库发送的WAL日志流并写入WAL buffer 以及写入WAL文件，就向客户端返回成功	主、备库WAL均落盘，有两份持有化文件保护	事务响应时间相对较慢
remote_apply	当主库提交事务后，需等待备库接收主库发送的WAL日志流并写入WAL buffer 以及写入WAL文件，同时备库apply之后，就向客户端返回成功	数据保护最好	影响事务性能

查看参数

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

postgres=# select name,setting from pg_settings where name ='synchronous_commit';
        name        | setting 
--------------------+---------
 synchronous_commit | off
(1 row)

postgres=#

查看同步情况

在主库执行查询， sync_state字段为async表示异步同步方式

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]------------+------------------------------
pid                      | 139867209197312
usesysid                 | 10
usename                  | omm
application_name         | WalSender to Standby[dn_6002]
client_addr              | 192.168.60.191
client_hostname          | mogdb1
client_port              | 38226
backend_start            | 2021-12-07 12:19:36.29931+08
state                    | Streaming
sender_sent_location     | 0/59E44A0
receiver_write_location  | 0/59E44A0
receiver_flush_location  | 0/59E44A0
receiver_replay_location | 0/59E44A0
sync_priority            | 0
sync_state               | Async

postgres=# 

[omm@mogdb ~]$ gs_ctl query 
[2021-12-07 14:36:23.220][40397][][gs_ctl]: gs_ctl query ,datadir is /mogdb/data/db1 
 HA state:           
        local_role                     : Primary
        static_connections             : 1
        db_state                       : Normal
        detail_information             : Normal

 Senders info:       
        sender_pid                     : 125801
        local_role                     : Primary
        peer_role                      : Standby
        peer_state                     : Normal
        state                          : Streaming
        sender_sent_location           : 0/59E45B8
        sender_write_location          : 0/59E45B8
        sender_flush_location          : 0/59E45B8
        sender_replay_location         : 0/59E45B8
        receiver_received_location     : 0/59E45B8
        receiver_write_location        : 0/59E45B8
        receiver_flush_location        : 0/59E45B8
        receiver_replay_location       : 0/59E45B8
        sync_percent                   : 100%
        sync_state                     : Async
        sync_priority                  : 0
        sync_most_available            : Off
        channel                        : 192.168.60.190:26001-->192.168.60.191:38226

 Receiver info:      
No information 
[omm@mogdb ~]$

配置同步复制

[omm@mogdb ~]$ cd /mogdb/data/db1/
[omm@mogdb db1]$ vi postgresql.conf
#synchronous_commit = off
synchronous_commit = on

重启数据库

[omm@mogdb db1]$ gs_ctl restart -D /mogdb/data/db1
[2021-12-07 15:16:35.051][55721][][gs_ctl]: gs_ctl restarted ,datadir is /mogdb/data/db1 
waiting for server to shut down... done
server stopped
[2021-12-07 15:16:44.073][55721][][gs_ctl]: waiting for server to start...
.0 LOG:  [Alarm Module]can not read GAUSS_WARNING_TYPE env.

0 LOG:  [Alarm Module]Host Name: mogdb 

0 LOG:  [Alarm Module]Host IP: 192.168.60.190 

0 LOG:  [Alarm Module]Cluster Name: dbCluster 

0 LOG:  [Alarm Module]Invalid data in AlarmItem file! Read alarm English name failed! line: 52

0 WARNING:  failed to open feature control file, please check whether it exists: FileName=gaussdb.version, Errno=2, Errmessage=No such file or directory.
0 WARNING:  failed to parse feature control file: gaussdb.version.
0 WARNING:  Failed to load the product control file, so gaussdb cannot distinguish product version.
0 LOG:  Failed to initialze environment for codegen.
0 WARNING:  bbox_dump_path is set to /opt/mogdb/corefile/

2021-12-07 15:16:44.281 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 DB010  0 [REDO] LOG:  Recovery parallelism, cpu count = 2, max = 4, actual = 2
2021-12-07 15:16:44.281 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 DB010  0 [REDO] LOG:  ConfigRecoveryParallelism, true_max_recovery_parallelism:4, max_recovery_parallelism:4
2021-12-07 15:16:44.281 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  [Alarm Module]can not read GAUSS_WARNING_TYPE env.

2021-12-07 15:16:44.281 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  [Alarm Module]Host Name: mogdb 

2021-12-07 15:16:44.281 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  [Alarm Module]Host IP: 192.168.60.190 

2021-12-07 15:16:44.281 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  [Alarm Module]Cluster Name: dbCluster 

2021-12-07 15:16:44.281 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  [Alarm Module]Invalid data in AlarmItem file! Read alarm English name failed! line: 52

2021-12-07 15:16:44.281 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  Transparent encryption disabled.

2021-12-07 15:16:44.323 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  loaded library "security_plugin"
2021-12-07 15:16:44.329 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  InitNuma numaNodeNum: 1 numa_distribute_mode: none inheritThreadPool: 0.
2021-12-07 15:16:44.329 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (100 Mbytes) or shared memory (1287 Mbytes) is larger.
2021-12-07 15:16:44.346 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [CACHE] LOG:  set data cache  size(78643200)
2021-12-07 15:16:44.349 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [CACHE] LOG:  set metadata cache  size(26214400)
2021-12-07 15:16:44.616 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  mogdb: fsync file "/mogdb/data/db1/gaussdb.state.temp" success
2021-12-07 15:16:44.616 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  create gaussdb state file success: db state(STARTING_STATE), server mode(Normal)
2021-12-07 15:16:44.640 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  max_safe_fds = 979, usable_fds = 1000, already_open = 11
bbox_dump_path is set to /opt/mogdb/corefile/
2021-12-07 15:16:44.643 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  the configure file /opt/mogdb/app/etc/gscgroup_omm.cfg doesn't exist or the size of configure file has changed. Please create it by root user!
2021-12-07 15:16:44.643 61af0a5c.1 [unknown] 140035127822080 [unknown] 0 dn_6001_6002 00000  0 [BACKEND] LOG:  Failed to parse cgroup config file.

[2021-12-07 15:16:45.085][55721][][gs_ctl]:  done
[2021-12-07 15:16:45.085][55721][][gs_ctl]: server started (/mogdb/data/db1)
[omm@mogdb db1]$

查看参数

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

postgres=# select name,setting from pg_settings where name ='synchronous_commit';
        name        | setting 
--------------------+---------
 synchronous_commit | on
(1 row)

postgres=#

查看同步情况

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]------------+------------------------------
pid                      | 140032092731136
usesysid                 | 10
usename                  | omm
application_name         | WalSender to Standby[dn_6002]
client_addr              | 192.168.60.191
client_hostname          | mogdb1
client_port              | 45404
backend_start            | 2021-12-07 15:28:44.900271+08
state                    | Streaming
sender_sent_location     | 0/5CD3728
receiver_write_location  | 0/5CD3728
receiver_flush_location  | 0/5CD3728
receiver_replay_location | 0/5CD3610
sync_priority            | 1
sync_state               | Sync

postgres=# 

[omm@mogdb ~]$ gs_ctl query
[2021-12-07 15:41:24.032][58982][][gs_ctl]: gs_ctl query ,datadir is /mogdb/data/db1 
 HA state:           
        local_role                     : Primary
        static_connections             : 1
        db_state                       : Normal
        detail_information             : Normal

 Senders info:       
        sender_pid                     : 57261
        local_role                     : Primary
        peer_role                      : Standby
        peer_state                     : Normal
        state                          : Streaming
        sender_sent_location           : 0/5CD3728
        sender_write_location          : 0/5CD3728
        sender_flush_location          : 0/5CD3728
        sender_replay_location         : 0/5CD3728
        receiver_received_location     : 0/5CD3728
        receiver_write_location        : 0/5CD3728
        receiver_flush_location        : 0/5CD3728
        receiver_replay_location       : 0/5CD3728
        sync_percent                   : 100%
        sync_state                     : Sync
        sync_priority                  : 1
        sync_most_available            : Off
        channel                        : 192.168.60.190:26001-->192.168.60.191:45404

 Receiver info:      
No information 
[omm@mogdb ~]$

mogdb 墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MogDB主备流复制—异步转为同步

MogDB主备流复制—异步转为同步

涉及参数

查看参数

查看同步情况

配置同步复制

重启数据库

查看参数

查看同步情况

评论