Kingbase支持物理流复制，使用流复制时，只要主库一产生日志，就会马上传递到备库。流复制传递日志的方式有两种,分为异步流复制和同步流复制，异步流复制是指主库上提交事务时不需要等待备库接收WAL日志流并写入到备库WAL日志文件时便返回成功，而同步复流复制恰恰相反。
异步流复制是指主库提交事务时不需要等待备库接收WAL日志流，并写入备库WAL日志文件时便返回成功。
异步流复制实验环境
| 主机 | IP地址 | 操作系统 | KingbaseES版本 |
|---|---|---|---|
| 主节点 | 10.10.9.164 | CentOS 7.6 | KingbaseESV8R6 |
| 备节点 | 10.10.9.165 | CentOS 7.6 | KingbaseESV8R6 |
Server/bin/initdb -Usystem -D data_async -x123456
wal_level = replica#wal日志级别，流复制必须使用 replica 或更高级别
max_wal_senders = 10#指定来自后备服务器或流式基础备份客户端的并发连接的最大数量
max_replication_slots =3#指定服务器可以支持的复制槽最大数量
hot_standby = on#指定在恢复期间，是否能够连接并运行查询
wal_keep_segments = 512#指定在后备服务器需要为流复制获取日志段文件
full_page_writes = on#开启该参数可以 避免数据坏块，建议为 on$ ksql -Usystem -d test -p9000
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# create user kes_rep replication login connection limit 10 encrypted password '123456';
CREATE ROLEhost replication kes_rep ::1/128 trust
host replication kes_rep 0.0.0.0/0 trust$ Server/bin/sys_basebackup -D data_async -U kes_rep -h 10.10.9.164 -p9000 -X stream -R -v -P
sys_basebackup: initiating base backup, waiting for checkpoint to complete
sys_basebackup: checkpoint completed
sys_basebackup: write-ahead log start point: 0/2000028 on timeline 1
sys_basebackup: starting background WAL receiver
sys_basebackup: created temporary replication slot "sys_basebackup_4037"
50349/50349 kB (100%), 1/1 tablespace
sys_basebackup: write-ahead log end point: 0/20000F8
sys_basebackup: waiting for background process to finish streaming ...
sys_basebackup: syncing data to disk ...
sys_basebackup: base backup completed恢复配置参数kingbase.conf文件
recovery_target_timeline='latest'
primary_conninfo='host=10.10.9.164 port=9000 user=kes_rep'主库查看sys_stat_replication视图的sync_state字段
$ ksql -Usystem -d test -p9000
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# \x
扩展显示已打开.
test=# select * from sys_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 2181
usesysid | 16386
usename | kes_rep
application_name | kingbase_*&+_++
client_addr | 10.10.9.165
client_hostname |
client_port | 62675
backend_start | 2023-05-25 22:04:31.052140+08
backend_xmin |
state | streaming
sent_lsn | 0/3000130
write_lsn | 0/3000130
flush_lsn | 0/3000130
replay_lsn | 0/3000130
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2023-05-25 22:07:25.659621+08#主库插入数据
$ ksql -Usystem -d test -p9000
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# create table t1(id int);
CREATE TABLE
test=# insert into t1 select generate_series(1,100000);
INSERT 0 100000
test=# select count(*) from t1;
count
--------
100000
(1 行记录)
test=#
#从库查询数据
$ ksql -Usystem -d test -p9000 -h 10.10.9.165
用户 system 的口令：
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# select count(*) from t1;
count
--------
100000
(1 行记录)同步流复制是指主库上提交事务时需要等待备库接收WAL日志流，当主库至少收到一个备库发回的确认信息便返回成功，同步流复制确保了至少一个备库收到主库发送的WAL日志。
同步流复制实验环境
| 主机 | IP地址 | 操作系统 | KingbaseES版本 |
|---|---|---|---|
| 主节点 | 10.10.9.164 | CentOS 7.6 | KingbaseESV8R6 |
| 备节点 | 10.10.9.165 | CentOS 7.6 | KingbaseESV8R6 |
| 级联备库 | 10.10.9.166 | CentOS 7.6 | KingbaseESV8R6 |
$ Server/bin/initdb -Usystem -D data_sync -x123456$ ksql -Usystem -d test -p 54321
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# create user kes_rep replication login connection limit 10 encrypted password '123456';
CREATE ROLEhost replication kes_rep ::1/128 trust
host replication kes_rep 0.0.0.0/0 trustwal_level = replica
max_wal_senders = 10
max_replication_slots =3
hot_standby = on
wal_keep_segments = 512
full_page_writes = on
synchronous_commit=on
synchronous_standby_names='node2'主配置参数kingbase.conf
wal_level = replica
max_wal_senders = 2
max_replication_slots =2
hot_standby = on
wal_keep_segments = 512
wal_log_hints = on
full_page_writes = on
synchronous_commit=on
synchronous_standby_names='node2'
恢复配置参数"kingbase.auto.conf"
primary_conninfo = 'user=kes_rep passfile=''/home/kingbase/.kbpass'' host=10.10.9.164 port=54321 application_name=''node2'' sslmode=prefer sslcompression=0 gssencmode=disable target_session_attrs=any'$ ksql -Usystem -d test -p54321
ksql (V8.0)
输入 "help" 来获取帮助信息.
test=# \x
扩展显示已打开.
test=# select * from sys_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 19230
usesysid | 16384
usename | kes_rep
application_name | node2
client_addr | 10.10.9.165
client_hostname |
client_port | 45602
backend_start | 2023-05-25 22:42:07.233996+08
backend_xmin |
state | streaming
sent_lsn | 0/30000D0
write_lsn | 0/30000D0
flush_lsn | 0/30000D0
replay_lsn | 0/30000D0
write_lag | 00:00:00.000262
flush_lag | 00:00:00.000262
replay_lag | 00:00:00.000262
sync_priority | 1
sync_state | sync
reply_time | 2023-05-25 22:42:21.520564+08
streaming 0/30001D8$ Server/bin/sys_basebackup -D data_sync -U kes_rep -h 10.10.9.164 -p54321 -X stream -R -v -P
sys_basebackup: initiating base backup, waiting for checkpoint to complete
sys_basebackup: checkpoint completed
sys_basebackup: write-ahead log start point: 0/2000028 on timeline 1
sys_basebackup: starting background WAL receiver
sys_basebackup: created temporary replication slot "sys_basebackup_16091"
50349/50349 kB (100%), 1/1 tablespace
sys_basebackup: write-ahead log end point: 0/2000128
sys_basebackup: waiting for background process to finish streaming ...
sys_basebackup: syncing data to disk ...
sys_basebackup: base backup completed主库10.10.9.164:53421可以看到walsender进程
kingbase 17582 1 0 22:42 ? 00:00:00 Server/bin/kingbase -D data_sync
kingbase 17730 17582 0 22:42 ? 00:00:00 kingbase: logger
kingbase 17783 17582 0 22:42 ? 00:00:00 kingbase: checkpointer
kingbase 17784 17582 0 22:42 ? 00:00:00 kingbase: background writer
kingbase 17785 17582 0 22:42 ? 00:00:00 kingbase: walwriter
kingbase 17786 17582 0 22:42 ? 00:00:00 kingbase: autovacuum launcher
kingbase 17787 17582 0 22:42 ? 00:00:00 kingbase: stats collector
kingbase 17788 17582 0 22:42 ? 00:00:00 kingbase: ksh writer
kingbase 17790 17582 0 22:42 ? 00:00:00 kingbase: ksh collector
kingbase 17792 17582 0 22:42 ? 00:00:00 kingbase: kwr collector
kingbase 17793 17582 0 22:42 ? 00:00:00 kingbase: logical replication launcher
kingbase 19230 17582 0 22:42 ? 00:00:00 kingbase: walsender kes_rep 10.10.9.165(45602) streaming 0/30001D8
备库10.10.9.165:54321可以看到walreceiver进程
kingbase 16448 1 0 22:41 ? 00:00:00 / Server/bin/kingbase -D data_sync
kingbase 16454 16448 0 22:41 ? 00:00:00 kingbase: logger
kingbase 16455 16448 0 22:41 ? 00:00:00 kingbase: startup recovering 000000010000000000000003
kingbase 16462 16448 0 22:41 ? 00:00:00 kingbase: checkpointer
kingbase 16463 16448 0 22:41 ? 00:00:00 kingbase: background writer
kingbase 16464 16448 0 22:41 ? 00:00:00 kingbase: stats collector
kingbase 16749 16448 0 22:42 ? 00:00:00 kingbase: walreceiver streaming 0/30001D8Server/bin/sys_basebackup -D data_cascade -U kes_rep -h 10.10.9.165 -p54321 -X stream -R -v -Pwal_level = replica
max_replication_slots =3
hot_standby = on
wal_keep_segments = 512
wal_log_hints = on
full_page_writes = on#host和port指向上一级备库地址
primary_conninfo = 'user=kes_rep passfile=''/home/kingbase/.kbpass'' host=10.10.9.165 port=54321 application_name=''node3'' sslmode=prefer sslcompression=0 gssencmode=disable target_session_attrs=any'$ Server/bin/sys_ctl -D data_cascade starttest=# select * from sys_stat_wal_receiver;
-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 22380
status | streaming
receive_start_lsn | 0/3000000
receive_start_tli | 1
received_lsn | 0/3000000
received_tli | 1
last_msg_send_time | 2023-05-26 15:29:46.219583+08
last_msg_receipt_time | 2023-05-26 15:29:32.035829+08
latest_end_lsn | 0/30001D8
latest_end_time | 2023-05-26 15:20:45.036284+08
slot_name |
sender_host | 10.10.9.165
sender_port | 54321
conninfo | user=kes_rep passfile=/home/kingbase/.kbpass dbname=replication host=10.10.9.165 port=54321 application_name=node3 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=disable target_session_attrs=anytest=# select client_addr,state,sync_state,client_port,usename,application_name from sys_stat_replication;
-[ RECORD 1 ]----+------------
client_addr | 10.10.9.164
state | streaming
sync_state | async
client_port | 32996
usename | kes_rep
application_name | node3