文档用途

本文档旨在介绍流复制架构下，如何进行主备切换。

详细信息

流复制主备切换根据数据库版本略有差异

具体切换步骤：

1.先停止主库服务，激活备库使其成为新主库

主节点执行：

[root@HGDB-4349 highgo]# pg_ctl stop

备节点执行pg_ctl promote：

[root@HGDB-4349 highgo]# pg_ctl promote

waiting for server to promote… 完成

server promoted

[root@HGDB-4349 data]# psql highgo sysdba

highgo=# select pg_is_in_recovery();

 pg_is_in_recovery 

-------------------

 f

(1 行记录)


#返回值为f表示备库已成功切换为主库；

2.修改原主节点数据库配置

HGDB企业版v6、HGDB安全版v4.5之前版本配置修改方式：

原主数据库data目录下创建recovery.conf文件添加以下配置

standby_mode = 'on'
primary_conninfo = 'user=sysdba password=''Highgo@123'' host=x.x.2.43 port=5866 sslmode=prefer sslcompression=1 target_session_attrs=any'

#ip及密码等信息根据现数据库主节点配置即可

HGDB企业版v6、HGDB安全版v4.5及之后版本配置修改方式：

原主节点数据库data目录下创建standby.signal文件，并修改其权限为600，修改原主数据库节点下配置文件postgresql.auto.conf，添加以下配置：

primary_conninfo = 'user=sysdba password=''Highgo@123'' host=x.x.2.43 port=5866 sslmode=prefer sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

#ip及密码等信息根据现数据库主节点配置即可

3.启动数据库并检查流复制状态，确定是否切换成功

[root@HGDB-4349 ~]# psql highgo sysdba

psql (4.3.4.9)

输入 “help” 来获取帮助信息.

highgo=# 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_ls

n  | replay_lsn |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state 

------+----------+---------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+---------

---+------------+-----------------+-----------------+-----------------+---------------+------------

 1551 |      443 | sysdba  | walreceiver      | x.x.2.44 |                 |       46082 | 2023-09-25 09:31:47.992312+08 |              | streaming | 0/2E000000 | 0/2E000000 | 0/2E0000

00 | 0/2E000000 | 00:00:00.100741 | 00:00:00.100741 | 00:00:00.100741 |             0 | async

(1 行记录)

4.创建复制槽

主节点：

[root@HGDB-4349 ~]# psql highgo sysdba

psql (4.3.4.9)

输入 “help” 来获取帮助信息.

highgo=# select * from pg_create_physical_replication_slot('node_1');

 slot_name | lsn 

-----------+-----

 node_1    | 

(1 行记录)

备节点在postgresql.auto.conf中添加参数(企业版V6，安全版V4.5之前版本配置到recovery.conf文件中),并重启备库使复制槽生效：

primary_slot_name = 'node_1'

备节点重启后登录主节点数据库检查复制槽是否已使用，流复制状态是否正常：

[root@HGDB-4349 ~]# psql highgo sysdba
psql (4.3.4.9)

输入 “help” 来获取帮助信息.

highgo=# select * from pg_replication_slots;

 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 

-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------

 node_1    |        | physical  |        |          | f         | t      |       2366 |      |              | 0/2E000060  | 

(1 行记录)

# active 列为t表示激活成功

highgo=# 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 

------+----------+---------+------------------+--------------+-----------------+-------------+------------------------------+--------------+-----------+------------+------------+----------

--+------------+-----------+-----------+------------+---------------+------------

 2366 |      443 | sysdba  | walreceiver      | x.x.2.44 |                 |       46084 | 2023-09-25 09:48:14.03783+08 |              | streaming | 0/2E000060 | 0/2E000060 | 0/2E00006

0 | 0/2E000060 |           |           |            |             0 | async

(1 行记录)

新主节点可以查看到备节点流复制信息时证明流复制主备已经切换成功；