人大金仓手工搭建流复制

R6手工搭建流复制：
1、主库初始化数据目录：
./initdb -Usystem -x123456 -m oracle -D /home/kingbase/data

2、修改主库kingbase.conf
listen_addresses = '*'
archive_mode = on
archive_command = 'cp "%p" "/home/kingbase/archivedir"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
wal_keep_segments = 512

3、配置 pg_hba.conf
host all all 0.0.0.0/0 trust
host replication all 0.0.0.0/0 trust

4、启动主库：
sys_ctl start -D /home/kingbase/data

5、备库sys_basebackup：
备库data路径下：会生成：standby.signal 文件
备库data路径下： postgresql.auto.conf 文件会添加连接信息
sys_basebackup -h 192.168.40.241 -p 54321 -U system -W -Fp -Xs -Pv -R -D /home/kingbase/data
cat standby.signal
vim postgresql.auto.conf
primary_conninfo = 'user=system connect_timeout=10 host=192.168.40.241 port=54321 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3'
recovery_target_timeline = 'latest'
primary_slot_name = 'repmgr_slot_1'
wal_retrieve_retry_interval = '5000'
synchronous_standby_names = 'ANY 1(*)'
注意：此处填写主库的ip信息，对应的复制槽要在主库创建。

6、启动备库：
sys_ctl start -D /home/kingbase/data

7、主库查询流复制、复制槽：
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 | 10698 | | | 0/7000148 |
(1 行记录)

test=# select * from sys_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_ls
n | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+--------
---+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
10698 | 10 | system | walreceiver | 192.168.40.242 | | 20619 | 2022-03-29 21:45:27.489376+08 | | streaming | 0/70001
48 | 0/7000148 | 0/7000148 | 0/7000148 | | | | 0 | async | 2022-03-29 21:45:32.488450+08
(1 行记录)



PostgreSQL 主从同步流复制配置

在master上连接pocdb 数据库 ，基于配置二的节点状态

pocdb=# SELECT client_addr,application_name,sync_state FROM pg_stat_replication;
client_addr | application_name | sync_state
-------------+------------------+------------
10.10.56.17 | slave1 | async
10.10.56.19 | slave2 | async
(2 rows)

master有两个slave都是async（异步）流复制，现在修改slave1节点为sync（同步）流复制，修改master 节点的postgresql.conf中参数synchronous_standby_names

synchronous_standby_names = 'slave1'
postgres@clw-db1:~> /opt/pgsql-9.6/bin/pg_ctl -D /pgdata/9.6/poc/data/ reload
server signaled