PostgreSQL使用repmgr配置级联复制
最近的项目要求配置共享存储的四节点集群,使集群能够形成负载均衡。
但是大家知道,PostgreSQL不支持使用同一数据目录生成多个实例,在执行pg_ctl start的时候,如果指定的数据目录有实例在运行,则该实例会发生错误导致数据库down掉。故而,我们选择了基于pgpool-II + repmgr的策略来实现基于NAS共享存储的负载均衡策略。架构如下图所示:
共享磁盘的实现方法多种多样,本文主要讲解repmgr的使用方法:
(零)准备工作
本例中,准备了四台虚拟机,IP地址分别为:
192.168.100.1 node1192.168.100.2 node2192.168.100.3 node3192.168.100.4 node4
NAS存储中,分别创建目录data1、data2、data3、data4,并挂载到各数据节点上。*注意目录权限必须为700
在所有节点安装repmgr。其中,只有node1节点上执行initdb,而node2、node3、node4均不初始化。
在node1节点的postgresql.conf添加以下配置:
hot_standby = onwal_level
= "replica"max_wal_senders
= 50
创建repmgr用户及数据库
[Harvest@node1 ~] createuser -s repmgr
[Harvest@node1 ~] createdb repmgr -O repmgr
配置pg_hba.conf,添加以下规则:
local replication repmgr trusthost replication repmgr
127.0.0.1/32 trusthost replication repmgr
192.168.100.0/0 trustlocal repmgr repmgr trust
host repmgr repmgr
127.0.0.1/32 trusthost repmgr repmgr
192.168.100.0/0 trust
启动数据库,并在node2(或node3、node4)测试是否可以通过repmgr连接到数据库
[Harvest@node1 ~] pg_ctl start
[Harvest@node2 ~] psql "host=node1 user=repmgr dbname=repmgr connect_timeout=2"
(一)安装repmgr
首先,解压repmgr并安装:
[Harvest@node1 opt]cd repmgr-x.x.x
[Harvest@node1 repmgr-x.x.x] ./configure && make && makeinstall #执行configure的时候需要sed和pg_config两个应用,基本上linux系统都会自带sed,pg_config安装了postgresql也都会有
(二)配置node1
在node1上创建配置文件repmgr1.conf:
node_id=1node_name
=node1conninfo
="host=node1 user=repmgr dbname=repmgr connect_timeout=2"data_directory
="/opt/data/data1"
接下来将node1注册为主服务器:
[Harvest@node1 ~] repmgr -f repmgr1.conf primary register
如果屏幕中回显如下,则注册成功:
INFO: connecting to primary database...NOTICE: attempting to
install extension "repmgr"NOTICE:
"repmgr" extension successfully installedNOTICE: primary node record (
id: 1) registered
验证集群状态
[Harvest@node1 ~]repmgr -f repmgr.conf cluster show
回显如下:
ID | Name | Role | Status | Upstream | Connection string----+-------+---------+-----------+----------+--------------------------------------------------------
1 | node1 | primary | * running | | host=node1 dbname=repmgr user=repmgr connect_timeout=2
(二)添加node2
如上所述配置repmgr2.conf:
node_id=2 node_name=node2
conninfo="host=node2 user=repmgr dbname=repmgr connect_timeout=2"
data_directory="/opt/data/data2"
创建node2备份,该步骤会使用pg_basebackup命令将node1节点的数据目录备份到data2目录中
[Harvest@node2 ~] repmgr -h node1 -U repmgr -d repmgr -f repmgr.conf standby clone
回显如下所示说明备份成功
NOTICE: using configuration file"/etc/repmgr.conf"NOTICE: destination directory
"/opt/data/data2" providedINFO: connecting to source node
NOTICE: checking
for available walsenders on source node (2 required)INFO: sufficient walsenders available on source node (
2 required)INFO: creating directory
"/opt/data/data2"...NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some
time; consider using the -c/--fast-checkpoint optionINFO: executing:
pg_basebackup
-l "repmgr base backup" -D /opt/data/data2 -h node1 -U repmgr -X streamNOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT:
for example: pg_ctl -D /opt/data/data2 start
注册node2
[Harvest@node2 ~] repmgr -f repmgr.conf standby register
回显如下:
NOTICE: standby node "node2" (ID: 2) successfully registered
验证集群状态
[Harvest@node1 ~]repmgr -f repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Connection string ----+-------+---------+-----------+----------+----------+--------------------------------------
1 | node1 | primary | * running | | default | host=node1 dbname=repmgr user=repmgr
2 | node2 | standby | running | node1 | default | host=node2 dbname=repmgr user=repmgr
(三)注册node3、node4
node3、node4在配置上与node2相同,唯一的区别在于增加了"--upstream-node-id=2"这个参数,它指定了数据流的来源:
[Harvest@node3 ~] repmgr -h node2 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --upstream-node-id=2
[Harvest@node3 ~] repmgr -f /etc/repmgr.conf standby register --upstream-node-id=2
同理node4的数据流来源为node3:
[Harvest@node4 ~] repmgr -h node3 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --upstream-node-id=3
[Harvest@node4 ~] repmgr -f /etc/repmgr.conf standby register --upstream-node-id=3
以上,使用repmgr的级联复制配置完成。
参考
1.repmgr手册 :https://repmgr.org/docs/current/index.html
2.中文版手册:http://citusdb.cn/?p=1068
以上是 PostgreSQL使用repmgr配置级联复制 的全部内容, 来源链接: utcz.com/z/532964.html