案例環境：
[kingbase@node2 bin]$ ./ksql -U system test
ksql (V8.0)
Type "help" for help.
test=# select version();
version
----------------------------------------------------------------------------------------------------------
KingbaseES V008R006C003B0010 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)
案例說明：
在kingbaseES R6集群已經部署了一主一備的架構下，現因業務需求，客戶需要再構建一個只讀的副本（流復制備庫），此備庫不需要納入repmgr的管理，
集群架構：
部署步驟：
1）查看源集群節點狀態及流復制狀態，
2）在集群主庫上執行checkpoint，
3）在新增節點下執行sys_basebackup創建流復制備庫，
4）設定新備庫data目錄700權限，
5）在新備庫data目錄下創建standby.signal檔案（可以vi編輯或從集群備庫拷貝），
6）修改新備庫kingbase.auto.conf檔案，連接集群主庫，
7）在集群主庫創建新備庫復制槽，啟動新備庫資料庫服務（sys_ctl啟動），
8）查看流復制狀態，
9）在集群主庫做DML操作，驗證資料同步，
一、查看原集群節點狀態和流復制狀態
1）查看集群節點狀態
[kingbase@node1 bin]$ ./repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------+---------+-----------+----------+----------+----------+----------+--------
1 | node248 | standby | running | node249 | default | 100 | 6 | host=192.168.7.248 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2 | node249 | primary | * running | | default | 100 | 6 | host=192.168.7.249 user=esrep dbname=esrep port=54321 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3
2）查看流復制狀態
test=# select * from sys_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_s
tart | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_la
g | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+---------------+-----------------+-------------+-
32701 | 16384 | esrep | node248 | 192.168.7.248 | | 40835 | 2021-03-01 14:14:
46.302306+08 | | streaming | 2/21000060 | 2/21000060 | 2/21000060 | 2/21000060 | |
| | 1 | quorum | 2021-03-01 17:35:03.927948+08
(1 row)
二、在主庫生成檢查點
[kingbase@node2 bin]$ ./ksql -U system test
ksql (V8.0)
Type "help" for help.
test=# checkpoint;
CHECKPOINT
三、可能新的流復制備庫（sys_basebackup）
[kingbase@node2 kingbase]$ pwd
/home/kingbase/cluster/R6HA/KHA/kingbase
[kingbase@node2 kingbase]$ mkdir data1
[kingbase@node2 bin]$ ./sys_basebackup -h 127.0.0.1 -D /home/kingbase/cluster/R6HA/KHA/kingbase/data1 -F p -X stream -v -P -U system -p 54321
sys_basebackup: initiating base backup, waiting for checkpoint to complete
sys_basebackup: checkpoint completed
sys_basebackup: write-ahead log start point: 2/20000028 on timeline 6
sys_basebackup: starting background WAL receiver
sys_basebackup: created temporary replication slot "sys_basebackup_11798"
540366/540366 kB (100%), 1/1 tablespace
sys_basebackup: write-ahead log end point: 2/20000138
sys_basebackup: waiting for background process to finish streaming ...
sys_basebackup: syncing data to disk ...
sys_basebackup: base backup completed
四、配置新備庫data目錄權限
[kingbase@node2 kingbase]$ chmod 700 data1
[kingbase@node2 kingbase]$ ls -lh data1
total 104K
-rw------- 1 kingbase kingbase 227 Mar 1 17:29 backup_label
-rw------- 1 kingbase kingbase 217 Mar 1 17:29 backup_label.old
drwx------ 10 kingbase kingbase 98 Mar 1 17:29 base
-rw-rw-r-- 1 kingbase kingbase 27K Mar 1 17:29 conf.temp
-rw------- 1 kingbase kingbase 46 Mar 1 17:29 current_logfiles
-rw-r--r-- 1 kingbase kingbase 624 Mar 1 17:29 es_rep.conf
drwx------ 2 kingbase kingbase 4.0K Mar 1 17:29 global
-rw------- 1 kingbase kingbase 415 Mar 1 17:29 kingbase.auto.conf
-rw------- 1 kingbase kingbase 27K Mar 1 17:29 kingbase.conf
-rw------- 1 kingbase kingbase 8.0K Mar 1 17:29 rw_status_file_20210301141839
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_bulkload
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_commit_ts
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_dynshmem
-rw------- 1 kingbase kingbase 4.8K Mar 1 17:29 sys_hba.conf
-rw------- 1 kingbase kingbase 1.6K Mar 1 17:29 sys_ident.conf
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_log
drwx------ 4 kingbase kingbase 65 Mar 1 17:29 sys_logical
drwx------ 4 kingbase kingbase 34 Mar 1 17:29 sys_multixact
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_notify
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_replslot
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_serial
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_snapshots
drwx------ 2 kingbase kingbase 30 Mar 1 17:29 sys_stat
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_stat_tmp
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_subtrans
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_tblspc
drwx------ 2 kingbase kingbase 6 Mar 1 17:29 sys_twophase
-rw------- 1 kingbase kingbase 3 Mar 1 17:29 SYS_VERSION
drwx------ 3 kingbase kingbase 81 Mar 1 17:29 sys_wal
drwx------ 2 kingbase kingbase 17 Mar 1 17:29 sys_xact
五、在新備庫data下生成standby.signal檔案
=== 注意：此檔案標識此節點是備庫，一定在啟動備庫資料庫服務前創建；否則資料庫服務啟動，默認為主庫，將無法再加入流復制集群，此檔案可以用vi創建，也可以從原備庫拷貝，===
[kingbase@node1 data]$ scp standby.signal node2:/home/kingbase/cluster/R6HA/KHA/kingbase/data1
standby.signal
[kingbase@node2 data1]$ ls -lh standby.signal
-rw------- 1 kingbase kingbase 20 Mar 1 17:45 standby.signal
[kingbase@node2 data1]$ cat standby.signal
# created by repmgr
六、編輯kingbase.auto.conf連接主庫
[kingbase@node2 data1]$ cat kingbase.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=esrep connect_timeout=10 host=192.168.7.249 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3'
recovery_target_timeline = 'latest'
primary_slot_name = 'repmgr_slot_3'
wal_retrieve_retry_interval = '5000'
synchronous_standby_names = 'ANY 1(*)'
=== 注意：此處填寫主庫的ip資訊，對應的復制槽要在主庫創建，===
七、創建復制槽和啟動備庫資料庫服務
1）查看已有的復制槽資訊
test=# select * from sys_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin |
restart_lsn | confirmed_flush_lsn
---------------+--------+-----------+--------+----------+-----------+--------+------------+------
repmgr_slot_1 | | physical | | | f | t | 32701 | | |
2/21000060 |
(1 row)
2）創建復制槽
test=# select sys_create_physical_replication_slot('repmgr_slot_3');
sys_create_physical_replication_slot
--------------------------------------
(repmgr_slot_3,)
(1 row)
test=# select * from sys_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin |
restart_lsn | confirmed_flush_lsn
---------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-
------------+---------------------
repmgr_slot_1 | | physical | | | f | t | 32701 | | |
2/21000148 |
repmgr_slot_3 | | physical | | | f | f | | | |
|
(2 rows)
3）修改服務埠號（因為此備庫和主庫在同一個主機，所以需要修改服務埠號，同時啟動兩個實體）
[kingbase@node2 data1]$ cat kingbase.conf |grep port
port = 54322 # (change requires restart)
4）啟動資料庫服務
[kingbase@node2 bin]$ ./sys_ctl start -D ../data1
waiting for server to start....2021-03-01 17:52:22.990 CST [16562] LOG: sepapower extension initialized
2021-03-01 17:52:22.991 CST [16562] LOG: starting KingbaseES V008R006C003B0010 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2021-03-01 17:52:22.991 CST [16562] LOG: listening on IPv4 address "0.0.0.0", port 54322
2021-03-01 17:52:22.991 CST [16562] LOG: listening on IPv6 address "::", port 54322
2021-03-01 17:52:23.070 CST [16562] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54322"
2021-03-01 17:52:23.212 CST [16562] LOG: redirecting log output to logging collector process
2021-03-01 17:52:23.212 CST [16562] HINT: Future log output will appear in directory "sys_log".
. done
server started
[kingbase@node2 bin]$ ps -ef |grep kingbase
kingbase 539 32623 0 14:14 ? 00:00:08 kingbase: esrep esrep 192.168.7.248(40847) idle
kingbase 688 32623 0 14:14 ? 00:00:19 kingbase: esrep esrep 192.168.7.249(53332) idle
kingbase 691 1 0 14:14 ? 00:00:04 /home/kingbase/cluster/R6HA/KHA/kingbase/bin/repmgrd -d -v -f /home/kingbase/cluster/R6HA/KHA/kingbase/bin/../etc/repmgr.conf
kingbase 1073 1 0 14:15 ? 00:00:01 /home/kingbase/cluster/R6HA/KHA/kingbase/bin/kbha -A daemon -f /home/kingbase/cluster/R6HA/KHA/kingbase/bin/../etc/repmgr.conf
kingbase 1354 1 0 14:15 ? 00:00:00 /home/kingbase/cluster/R6HA/KHA/kingbase/bin/../share/node_exporter
kingbase 1355 1 0 14:15 ? 00:00:00 /home/kingbase/cluster/R6HA/KHA/kingbase/bin/../share/postgres_exporter
kingbase 1409 32623 0 14:15 ? 00:00:00 kingbase: system test ::1(61541) idle
kingbase 9680 27531 0 17:23 pts/1 00:00:00 ./ksql -U system test
kingbase 9681 32623 0 17:23 ? 00:00:00 kingbase: system test [local] idle
kingbase 16562 1 0 17:52 ? 00:00:00 /home/kingbase/cluster/R6HA/KHA/kingbase/bin/kingbase -D ../data1
kingbase 16563 16562 0 17:52 ? 00:00:00 kingbase: logger
kingbase 16564 16562 1 17:52 ? 00:00:00 kingbase: startup recovering 000000060000000200000023
kingbase 16567 16562 0 17:52 ? 00:00:00 kingbase: checkpointer
kingbase 16568 16562 0 17:52 ? 00:00:00 kingbase: background writer
kingbase 16569 16562 0 17:52 ? 00:00:00 kingbase: stats collector
kingbase 16570 16562 0 17:52 ? 00:00:00 kingbase: walreceiver streaming 2/23000060
kingbase 16571 32623 0 17:52 ? 00:00:00 kingbase: walsender esrep 192.168.7.249(59875) streaming 2/23000060
八、查看流復制狀態
test=# select * from sys_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_s
tart | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_la
g | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+---------------+-----------------+-------------+
32701 | 16384 | esrep | node248 | 192.168.7.248 | | 40835 | 2021-03-01 14:14:
46.302306+08 | | streaming | 2/23000060 | 2/23000060 | 2/23000060 | 2/23000060 | |
| | 1 | quorum | 2021-03-01 17:54:12.970131+08
16571 | 16384 | esrep | walreceiver | 192.168.7.249 | | 59875 | 2021-03-01 17:52:
24.644390+08 | | streaming | 2/23000060 | 2/23000060 | 2/23000060 | 2/23000060 | |
| | 1 | quorum | 2021-03-01 17:53:04.788830+08
(2 rows)
=== 從以上可以獲知，新的備庫已經加入到流復制中，===
九、測驗資料同步
1）主庫
prod=# \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
id | integer | | |
name | character varying(10 char) | | |
prod=# select * from t2;
id | name
----+------
10 | tom
(1 row)
prod=# insert into t2 values(20,'jerry'),(30,'rose');
INSERT 0 2
prod=# select * from t2;
id | name
----+-------
10 | tom
20 | jerry
30 | rose
(3 rows)
2）備庫
[kingbase@node1 bin]$ ./ksql -U system prod -c 'select * from t2'
id | name
----+-------
10 | tom
20 | jerry
30 | rose
(3 rows)
3）只讀副本
[kingbase@node2 bin]$ ./ksql -U system prod -c 'select * from t2' -p 54322
id | name
----+-------
10 | tom
20 | jerry
30 | rose
=== 從以上獲知，此只讀的流復制備庫創建成功！===
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/308512.html
標籤：PostgreSQL
上一篇：PostgreSQL查看當前用戶