PostgreSQL高可用工具-repmgr-集群部署
一主两从+见证节点-yum安装配置
安装环境：
服务 主机名 主机ip地址 操作系统 部署版本 安装路径
主节点 node13 172.16.1.13 CentOS7.9 pg12+rpmmgr12
备节点 node14 172.16.1.14 CentOS7.9 pg12+rpmmgr12
备节点 node15 172.16.1.15 CentOS7.9 pg12+rpmmgr12
见证节点 node16 172.16.1.16 CentOS7.9 pg12+rpmmgr12
配置主机hosts文件：(每台主机都要配置)
[root@node13 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.1.13 node13
172.16.1.14 node14
172.16.1.15 node15
172.16.1.16 node16
关闭防火墙和selinux
systemctl stop firewalld
systemctl mask firewalld
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled' /etc/selinux/config
配置各节点间免密登录(每个节点都需要配置，命令不变)：
在各节点配置postgres用户的密码：
echo '123456'|passwd --stdin postgres
[root@node13 12]# su - postgres
ssh-keygen -t rsa -N ''
ssh-copy-id postgres@node13
ssh-copy-id postgres@node14
ssh-copy-id postgres@node15
ssh-copy-id postgres@node16
YUM安装PostgreSQL-12:(每台主机都要安装)
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
安装postgresql-12软件：
yum install postgresql12-server
yum install postgresql-devel
上面只是把软件装好了，并未创建数据库实例。创建数据库实例的命令如下：
/usr/pgsql-12/bin/postgresql-12-setup initdb
该数据库创建在“/var/lib/pgsql/12/data”目录下，同时会生成开机自启动的配置，我们
可以通过下面的命令允许开机自启动PostgreSQL数据库：
systemctl enable postgresql-12
也可以使用下面的命令安装contrib包，contrib包中包含了一些插件和工具：
yum install postgresql12-contrib
默认情况下，PostgreSQL的数据目录在“/var/lib/pgsql/<verson>/data”目录下：
安装完后我们就可以用psql来连接数据库，首先用su命令切换到postgres用户下：
[root@pg01 ~]# su - postgres
-bash-4.2$ psql
psql (12.1)
Type "help" for help.
postgres=#
然后在psql中输入“\q”退出psql。
修改postgresql.conf配置文件，监听所有的ip:
sed -i "s/#listen_addresses/listen_addresses/g" /var/lib/pgsql/12/data/postgresql.conf
sed -i "s/'localhost'/'*'/g" /var/lib/pgsql/12/data/postgresql.conf
cat /var/lib/pgsql/12/data/postgresql.conf 修改以下参数：
wal_log_hints = on
max_wal_senders = 10
max_replication_slots = 10
wal_level = 'hot_standby'
hot_standby = on
archive_mode = on
archive_command = '/bin/true'
systemctl restart postgresql-12
添加环境变量：
[root@node13 ~]# tail /etc/profile
PATH=$PATH:$HOME/bin:/usr/pgsql-12/bin
export PATH
[root@node14 ~]# source /etc/profile
创建软连接：
ln -sf /usr/pgsql-12/bin/pg_ctl /usr/bin/pg_ctl
ln -sf /usr/pgsql-12/bin/pg_rewind /usr/bin/pg_rewind
所有节点修改pg_hba.conf文件：
[root@node13 ~]# su - postgres
-bash-4.2$ vim $PGDATA/pg_hba.conf
host replication all 0.0.0.0/0 trust
host repmgr repmgr 172.16.1.13/24 trust
host repmgr repmgr 172.16.1.14/24 trust
host repmgr repmgr 172.16.1.15/24 trust
host repmgr repmgr 172.16.1.16/24 trust
YUM安装2ndquadrant官网的repmgr包：(每台主机都要安装)
[root@node13 ~]# curl https://dl.2ndquadrant.com/default/release/get/12/rpm | sudo bash
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 5375 100 5375 0 0 1763 0 0:00:03 0:00:03 --:--:-- 1765
Installation finished: 2ndquadrant.com RPM repository dl/default/release for PG12
[root@node13 ~]#yum install -y repmgr12
在postgresql.conf中添加对repmgr库的引用（每台主机都要配置)：
vim /$PGDATA/postgresql.conf
shared_preload_libraries = 'repmgr'
或者：
echo "shared_preload_libraries = 'repmgr'" >> /var/lib/pgsql/12/data/postgresql.conf
systemctl restart postgresql-12
node13主节点配置：
创建用于管理的用户和数据库
postgres=# create user repmgr with superuser replication password '123456';
CREATE ROLE
postgres=# create database repmgr owner repmgr;
CREATE DATABASE
重新启动pg数据库：
[root@node13 ~]# systemctl restart postgresql-12
配置repmgr.conf文件
[root@node13 12]# pwd
/etc/repmgr/12
[root@node13 12]#mv repmgr.conf repmgr.conf.ori
[root@node13 12]# ls
repmgr.conf repmgr.conf.ori
[root@node13 etc]# cat /etc/repmgr/12/repmgr.conf
node_id=1
node_name='node13'
conninfo='host=node13 port=5432 dbname=repmgr user=repmgr password=123456'
priority=3
reconnect_attempts=6
reconnect_interval=10
data_directory='/var/lib/pgsql/12/data'
pg_bindir='/usr/bin'
config_directory='/var/lib/pgsql/12/data'
log_level=INFO
log_facility=STDERR
log_file='/var/lib/pgsql/12/replogs/repmgr.log'
创建日志文件:
su - postgres
mkdir -p /var/lib/pgsql/12/replogs/
touch /var/lib/pgsql/12/replogs/repmgr.log
注册当前服务器为主节点：
su - postgres
-bash-4.2$ repmgr primary register
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered
验证集群状态：
-bash-4.2$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+--------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------
1 | node13 | primary | * running | | default | 3 | 1 | host=node13 port=5432 dbname=repmgr user=repmgr password=123456
也可以通过登录数据库查看repmgr元数据nodes表获取集群中节点的状态：
-bash-4.2$ psql -Urepmgr -h172.16.1.13 repmgr -W
repmgr=# \d
关联列表
架构模式 | 名称 | 型别 | 拥有者
----------+--------------------+--------+--------
repmgr | events | 资料表 | repmgr
repmgr | monitoring_history | 资料表 | repmgr
repmgr | nodes | 资料表 | repmgr
repmgr | replication_status | 视观表 | repmgr
repmgr | show_nodes | 视观表 | repmgr
repmgr | voting_term | 资料表 | repmgr
(6 行记录)
repmgr=# select * from nodes;
node_id | upstream_node_id | active | node_name | type | location | priority | conninfo
| repluser | slot_name | config_file
---------+------------------+--------+-----------+---------+----------+----------+--------------------------------------
---------------------------+----------+-----------+----------------------------
1 | | t | node13 | primary | default | 3 | host=node13 port=5432 dbname=repmgr u
ser=repmgr password=123456 | repmgr | | /etc/repmgr/12/repmgr.conf
(1 行记录)
开启主库守护进程repmgrd:
-bash-4.2$ repmgrd -d
[2023-05-28 11:28:06] [NOTICE] redirecting logging output to "/var/lib/pgsql/12/replogs/repmgr.log"
日志如下：
[2023-05-28 11:33:23] [NOTICE] repmgrd (repmgrd 5.4dev) starting up
[2023-05-28 11:33:23] [INFO] connecting to database "host=node13 port=5432 dbname=repmgr user=repmgr password=123456"
信息: set_repmgrd_pid(): provided pidfile is /tmp/repmgrd.pid
[2023-05-28 11:33:23] [NOTICE] starting monitoring of node "node13" (ID: 1)
[2023-05-28 11:33:23] [INFO] "connection_check_type" set to "ping"
[2023-05-28 11:33:23] [NOTICE] monitoring cluster primary "node13" (ID: 1)
在node14上配置克隆从库：
配置repmgr.conf文件：
[root@node14 12]# cat repmgr.conf
node_id=2
node_name='node14'
conninfo='host=node14 port=5432 dbname=repmgr user=repmgr password=123456'
priority=2
reconnect_attempts=6
reconnect_interval=10
data_directory='/var/lib/pgsql/12/data'
pg_bindir='/usr/bin'
config_directory='/var/lib/pgsql/12/data'
log_level=INFO
log_facility=STDERR
log_file='/var/lib/pgsql/12/replogs/repmgr.log'
创建日志文件:
su - postgres
mkdir -p /var/lib/pgsql/12/replogs/
touch /var/lib/pgsql/12/replogs/repmgr.log
从库需要停止数据服务，使用-dry-run选项待机是否可以克隆：
[root@node14 12]# systemctl stop postgresql-12
su - postgres
-bash-4.2$ repmgr -h 172.16.1.13 -U repmgr -d repmgr standby clone --dry-run
WARNING: following problems with command line parameters detected:
"config_directory" set in repmgr.conf, but --copy-external-config-files not provided
NOTICE: destination directory "/var/lib/pgsql/12/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=172.16.1.13 user=repmgr dbname=repmgr
DETAIL: current installation size is 32 MB
INFO: "repmgr" extension is installed in database "repmgr"
WARNING: target data directory appears to be a PostgreSQL data directory
DETAIL: target data directory is "/var/lib/pgsql/12/data"
HINT: use -F/--force to overwrite the existing data directory
INFO: replication slot usage not requested; no replication slot will be set up for this standby
INFO: parameter "max_wal_senders" set to 10
NOTICE: checking for available walsenders on the source node (2 required)
INFO: sufficient walsenders available on the source node
DETAIL: 2 required, 10 available
NOTICE: checking replication connections can be made to the source server (2 required)
INFO: required number of replication connections could be made to the source server
DETAIL: 2 replication connections required
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
NOTICE: standby will attach to upstream node 1
HINT: consider using the -c/--fast-checkpoint option
INFO: would execute:
/usr/bin/pg_basebackup -l "repmgr base backup" -D /var/lib/pgsql/12/data -h 172.16.1.13 -p 5432 -U repmgr -X stream
INFO: all prerequisites for "standby clone" are met
在node14主机上使用以下命令克隆node13节点
-bash-4.2$ repmgr -h 172.16.1.13 -U repmgr -d repmgr standby clone --force
WARNING: following problems with command line parameters detected:
"config_directory" set in repmgr.conf, but --copy-external-config-files not provided
NOTICE: destination directory "/var/lib/pgsql/12/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=172.16.1.13 user=repmgr dbname=repmgr
DETAIL: current installation size is 32 MB
INFO: replication slot usage not requested; no replication slot will be set up for this standby
NOTICE: checking for available walsenders on the source node (2 required)
NOTICE: checking replication connections can be made to the source server (2 required)
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
WARNING: directory "/var/lib/pgsql/12/data" exists but is not empty
NOTICE: -F/--force provided - deleting existing data directory "/var/lib/pgsql/12/data"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
/usr/bin/pg_basebackup -l "repmgr base backup" -D /var/lib/pgsql/12/data -h 172.16.1.13 -p 5432 -U repmgr -X stream
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: -D /var/lib/pgsql/12/data start
HINT: after starting the server, you need to register this standby with "repmgr standby register"
启动新副本数据库：
[root@node14 12]# systemctl start postgresql-12
使用postgres用户注册node14:
su - postgres
-bash-4.2$ repmgr standby register
INFO: connecting to local node "node14" (ID: 2)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID: 1)
INFO: standby registration complete
NOTICE: standby node "node14" (ID: 2) successfully registered
启动repmgrd守护进程：
-bash-4.2$ repmgrd -d
[2023-05-28 15:03:32] [NOTICE] redirecting logging output to "/var/lib/pgsql/12/replogs/repmgr.log"
-bash-4.2$ cat /var/lib/pgsql/12/replogs/repmgr.log
[2023-05-28 15:03:32] [NOTICE] repmgrd (repmgrd 5.4dev) starting up
[2023-05-28 15:03:32] [INFO] connecting to database "host=node14 port=5432 dbname=repmgr user=repmgr password=123456"
信息: set_repmgrd_pid(): provided pidfile is /tmp/repmgrd.pid
[2023-05-28 15:03:32] [NOTICE] starting monitoring of node "node14" (ID: 2)
[2023-05-28 15:03:32] [INFO] "connection_check_type" set to "ping"
[2023-05-28 15:03:32] [INFO] monitoring connection to upstream node "node13" (ID: 1)
在node15上配置克隆从库：
配置repmgr.conf文件：
[root@node15 12]# cat repmgr.conf
node_id=3
node_name='node15'
conninfo='host=node15 port=5432 dbname=repmgr user=repmgr password=123456'
priority=1
reconnect_attempts=6
reconnect_interval=10
data_directory='/var/lib/pgsql/12/data'
pg_bindir='/usr/bin'
config_directory='/var/lib/pgsql/12/data'
log_level=INFO
log_facility=STDERR
log_file='/var/lib/pgsql/12/replogs/repmgr.log'
创建日志文件:
su - postgres
mkdir -p /var/lib/pgsql/12/replogs/
touch /var/lib/pgsql/12/replogs/repmgr.log
从库需要停止数据服务，使用-dry-run选项待机是否可以克隆：
[root@node15 12]# systemctl stop postgresql-12
su - postgres
-bash-4.2$ repmgr -h 172.16.1.13 -U repmgr -d repmgr standby clone --dry-run
在node15主机上使用以下命令克隆node13节点
-bash-4.2$ repmgr -h 172.16.1.13 -U repmgr -d repmgr standby clone --force
启动新副本数据库：
[root@node15 12]# systemctl start postgresql-12
使用postgres用户注册node15:
su - postgres
-bash-4.2$ repmgr standby register
启动repmgrd守护进程：
-bash-4.2$ repmgrd -d
在node13上查看数据同步状态：
su - postgres
-bash-4.2$ psql -U repmgr -h 172.16.1.13 -d repmgr -W
repmgr=# select * from show_nodes;
node_id | node_name | active | upstream_node_id | upstream_node_name | type | priority | c
onninfo
---------+-----------+--------+------------------+--------------------+---------+----------+-----------------------------
------------------------------------
1 | node13 | t | | | primary | 3 | host=node13 port=5432 dbname
=repmgr user=repmgr password=123456
2 | node14 | t | 1 | node13 | standby | 2 | host=node14 port=5432 dbname
=repmgr user=repmgr password=123456
3 | node15 | t | 1 | node13 | standby | 1 | host=node15 port=5432 dbname
=repmgr user=repmgr password=123456
(3 行记录)
查看从库repmgr状态：
-bash-4.2$ repmgr service status
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+--------+---------+-----------+----------+---------+------+---------+--------------------
1 | node13 | primary | * running | | running | 1918 | no | n/a
2 | node14 | standby | running | node13 | running | 1879 | no | 1 second(s) ago
3 | node15 | standby | running | node13 | running | 1793 | no | 0 second(s) ago
测试集群安装是否成功：
在主机上创建表和插入数据，如果从机同步更新，则证明数据同步成功
node13:
-bash-4.2$ psql -U repmgr -h 172.16.1.13 -d repmgr -W
repmgr=# \d
关联列表
架构模式 | 名称 | 型别 | 拥有者
----------+--------------------+--------+--------
repmgr | events | 资料表 | repmgr
repmgr | monitoring_history | 资料表 | repmgr
repmgr | nodes | 资料表 | repmgr
repmgr | replication_status | 视观表 | repmgr
repmgr | show_nodes | 视观表 | repmgr
repmgr | voting_term | 资料表 | repmgr
(6 行记录)
repmgr=# create table test(id int);
CREATE TABLE
repmgr=# insert into test select 1;
INSERT 0 1
repmgr=# select * from test;
id
----
1
(1 行记录)
node14:
-bash-4.2$
-bash-4.2$ psql -U repmgr -d repmgr -h172.16.1.14 -W
口令：
psql (12.15)
输入 "help" 来获取帮助信息.
repmgr=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+--------------------+--------+--------
repmgr | events | 数据表 | repmgr
repmgr | monitoring_history | 数据表 | repmgr
repmgr | nodes | 数据表 | repmgr
repmgr | replication_status | 视图 | repmgr
repmgr | show_nodes | 视图 | repmgr
repmgr | test | 数据表 | repmgr
repmgr | voting_term | 数据表 | repmgr
(7 行记录)
repmgr=# select * from test;
id
----
1
(1 行记录)
node15:
-bash-4.2$ psql -U repmgr -d repmgr -h172.16.1.15 -W
口令：
psql (12.15)
输入 "help" 来获取帮助信息.
repmgr=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+--------------------+--------+--------
repmgr | events | 数据表 | repmgr
repmgr | monitoring_history | 数据表 | repmgr
repmgr | nodes | 数据表 | repmgr
repmgr | replication_status | 视图 | repmgr
repmgr | show_nodes | 视图 | repmgr
repmgr | test | 数据表 | repmgr
repmgr | voting_term | 数据表 | repmgr
(7 行记录)
repmgr=# select * from test;
id
----
1
(1 行记录)
主机负责写操作，从库负责读操作，实现读写分离.如果修改从库中的数据失败，则证明读写操作配置正确，在从机上执行以下命令
尝试在从库中执行创建表语句失败：
repmgr=# create table aa(id int);
错误: 不能在一个只读模式的事务中执行CREATE TABLE
证明读写操作配置无误，从库只能进行读操作，不能进行写操作
配置见证节点node16:
node16已安装好repmgr和postgresql-12数据库
使用initdb命令创建一个新的空PG实例：
systemctl stop postgresql-12
su - postgres
-bash-4.2$ initdb -D $PGDATA
修改postgresql.conf文件：
listen_addresses = '*' # what IP address(es) to listen on;
shared_preload_libraries = 'repmgr'
修改pg_hba.conf文件
host repmgr repmgr 172.16.1.0/24 trust
重启pg
systemctl restart postgresql-12
连接到数据库创建以下用户：
postgres=# create user repmgr with superuser replication password '123456';
CREATE ROLE
postgres=# create database repmgr owner repmgr;
CREATE DATABASE
下面开始配置repmgr：
配置repmgr.conf文件：
[root@node16 12]# cat /etc/repmgr/12/repmgr.conf
node_id=4
node_name='node16'
conninfo='host=node16 port=5432 dbname=repmgr user=repmgr password=123456'
data_directory='/var/lib/pgsql/12/data'
pg_bindir='/usr/bin'
monitoring_history='true'
primary_visibility_consensus='true'
log_level=INFO
log_facility=STDERR
log_file='/var/lib/pgsql/12/replogs/repmgr.log'
创建日志文件:
su - postgres
mkdir -p /var/lib/pgsql/12/replogs/
touch /var/lib/pgsql/12/replogs/repmgr.log
登录数据库创建用户：
postgres=# create user repmgr with superuser replication password '123456';
postgres=# create database repmgr owner repmgr;
CREATE ROLE
使用以下命令将此节点注册为见证节点：
-bash-4.2$ repmgr witness register -h 172.16.1.13 -d repmgr
INFO: connecting to witness node "node16" (ID: 4)
INFO: connecting to primary node
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
INFO: witness registration complete
NOTICE: witness node "node16" (ID: 4) successfully registered
启动repmgrd守护进程：
-bash-4.2$ repmgrd -d
[2023-05-28 15:47:58] [NOTICE] redirecting logging output to "/var/lib/pgsql/12/replogs/repmgr.log"
查看集群状态：
-bash-4.2$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+--------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------
1 | node13 | primary | * running | | default | 3 | 1 | host=node13 port=5432 dbname=repmgr user=repmgr password=123456
2 | node14 | standby | running | node13 | default | 2 | 1 | host=node14 port=5432 dbname=repmgr user=repmgr password=123456
3 | node15 | standby | running | node13 | default | 1 | 1 | host=node15 port=5432 dbname=repmgr user=repmgr password=123456
4 | node16 | witness | * running | node13 | default | 0 | n/a | host=node16 port=5432 dbname=repmgr user=repmgr password=123456
-bash-4.2$ psql
psql (12.15)
输入 "help" 来获取帮助信息.
postgres=# \c repmgr repmgr
您现在已经连接到数据库 "repmgr",用户 "repmgr".
repmgr=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+--------------------+--------+--------
repmgr | events | 数据表 | repmgr
repmgr | monitoring_history | 数据表 | repmgr
repmgr | nodes | 数据表 | repmgr
repmgr | replication_status | 视图 | repmgr
repmgr | show_nodes | 视图 | repmgr
repmgr | voting_term | 数据表 | repmgr
(6 行记录)
repmgr=# select * from nodes;
node_id | upstream_node_id | active | node_name | type | location | priority | conninfo
| repluser | slot_name | config_file
---------+------------------+--------+-----------+---------+----------+----------+---------------------------------------
--------------------------+----------+-----------+----------------------------
1 | | t | node13 | primary | default | 3 | host=node13 port=5432 dbname=repmgr us
er=repmgr password=123456 | repmgr | | /etc/repmgr/12/repmgr.conf
2 | 1 | t | node14 | standby | default | 2 | host=node14 port=5432 dbname=repmgr us
er=repmgr password=123456 | repmgr | | /etc/repmgr/12/repmgr.conf
3 | 1 | t | node15 | standby | default | 1 | host=node15 port=5432 dbname=repmgr us
er=repmgr password=123456 | repmgr | | /etc/repmgr/12/repmgr.conf
4 | 1 | t | node16 | witness | default | 0 | host=node16 port=5432 dbname=repmgr us
er=repmgr password=123456 | repmgr | | /etc/repmgr/12/repmgr.conf
(4 行记录)
repmgr=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+--------------------+--------+--------
repmgr | events | 数据表 | repmgr
repmgr | monitoring_history | 数据表 | repmgr
repmgr | nodes | 数据表 | repmgr
repmgr | replication_status | 视图 | repmgr
repmgr | show_nodes | 视图 | repmgr
repmgr | voting_term | 数据表 | repmgr
(6 行记录)
从以上的表可以看到，witness不会同步test表结构以及数据
