PostgreSQL+repmgr高可用部署

REPMGR 是一套在PostgreSQL服务器集群中用于管理复制和故障转移的开源工具 。它支持并增强了PostgreSQL的 内置流式复制，提供单个读/写主服务器 以及一个或多个只读备用数据库，其中包含主数据库的近实时副本服务器的数据库。

它提供了两个主要工具：

操作系统：Scientific Linux release 7.9 (Nitrogen)

PostgreSQL版本：PostgreSQL 15.1

repmgr版本：repmgr-5.3.3

--安装相关依赖：
yum -y install gcc gcc_c++ libyaml  python* readline readline-devel zlib zlib-devel
--关闭防火墙，设置selinux(各节点）
systemctl stop firewalld
systemctl disable firewalld
sed -i 's/^SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
--创建目录及用户
mkdir -p /data/pg15
mkdir -p /data/pgarch
mkdir -p /usr/local/pg15
useradd postgres
chown -R postgres:postgres /data
chown -R postgres:postgres /usr/local/pg15
chmod 700 /data/pg15
--源码安装PG15.2(postgres用户，所有节点)
tar -xvf postgresql-15.2.tar.gz
cd postgresql-15.2
./configure --prefix=/usr/local/pg15/
make world && make install-world

cat .bashrc
export PGDATA=/data/pg15
export PGHOME=/usr/local/pg15/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib
export PATH=$PATH:$PGHOME/bin/
export PGPORT=5435
source .bashrc

initdb -D /data/pg15
--配置pg_hba.conf
host    all        all           192.168.126.0/24   md5
local   replication   repmgr                              trust
host    replication   repmgr      127.0.0.1/32            trust
host    replication   repmgr      192.168.126.0/24          trust
local   repmgr        repmgr                              trust
host    repmgr        repmgr      127.0.0.1/32            trust
host    repmgr        repmgr      192.168.126.0/24          trust
--配置postgresql.conf
listen_addresses = '*'
port = 5435
shared_buffers = 256MB
wal_level = replica
max_wal_size = 1GB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
archive_mode = on
archive_command = 'cp %p /data/pgarch/%f'
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
--启动数据库
pg_ctl start -D /data/pg15

[root@pg15_rh7_132 ~]# cd /home/postgres/postgresql-15.1/contrib/start-scripts/
[root@pg15_rh7_132 ~]# cp linux /etc/init.d/postgresql
[root@pg15_rh7_132 ~]# chmod +x /etc/init.d/postgresql
[root@pg15_rh7_132 ~]# vim /etc/init.d/postgresql
# Installation prefix 修改安装目录位置
prefix=/usr/local/pg15
# Data directory 修改数据目录位置
PGDATA="/data/pg15"
[root@pg15_rh7_132 ~]#  chkconfig --add postgresql
[root@pg15_rh7_132 ~]#  chkconfig --list postgresql
重启服务器
[root@pg15_rh7_132 ~]#  reboot

postgres=# create user repmgr with superuser password 'qwe' connection limit 10;
CREATE ROLE
postgres=# create database repmgr owner  repmgr;
CREATE DATABASE

--安装依赖
yum check-update
yum groupinstall "Development Tools" -y
yum install -y yum-utils openjade docbook-dtds docbook-style-dsssl docbook-style-xsl
yum -y install yum-builddep flex libselinux-devel libxml2-devel libxslt-devel openssl-devel pam-devel readline-devel
--解压安装repmgr（主备库都安装）
[root@pg15_rh7_132 ~]# tar -xvf repmgr-5.3.3.tar.gz -C /home/postgres/
[root@pg15_rh7_132 ~]#  chown -R postgres:postgres /home/postgres/
[root@pg15_rh7_132 ~]# su - postgres
[postgres@pg15_rh7_132 ~]$ cd repmgr-5.3.3
[postgres@pg15_rh7_132 ~]$  ./configure
[postgres@pg15_rh7_132 ~]$ make install

[postgres@pg15_rh7_132 ~]$ ssh-keygen -t rsa
[postgres@pg15_rh7_132 ~]$ ssh-copy-id [email protected]
[postgres@pg15_rh7_133 ~]$ ssh-keygen -t rsa
[postgres@pg15_rh7_133 ~]$ ssh-copy-id [email protected]
[postgres@pg15_rh7_132 ~]$ cat .pgpass
192.168.126.132:5435:repmgr:repmgr:qwe
192.168.126.133:5435:repmgr:repmgr:qwe
[postgres@pg15_rh7_133 ~]$ cat .pgpass
192.168.126.132:5435:repmgr:repmgr:qwe
192.168.126.133:5435:repmgr:repmgr:qwe

cat repmgr.conf
node_id=1
   node_name='node1'
   conninfo='host=192.168.126.132 port=5435  user=repmgr dbname=repmgr connect_timeout=2'
   data_directory='/data/pg15'
   测试repmgr是否能免密访问主库
   [postgres@pg15_rh7_133 repmgr-5.3.3]$ psql -p5435 -h 192.168.126.132 -Urepmgr

[postgres@pg15_rh7_132 ~]$ chmod 600 .pgpass
[postgres@pg15_rh7_132 ~]$ /usr/local/pg15/bin/repmgr -f /home/postgres/repmgr.conf primary register --force
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered
--查看注册信息
[postgres@pg15_rh7_132 ~]$ repmgr -f repmgr.conf cluster show
ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                          
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------------------
1  | node1 | primary | * running |          | default  | 100      | 1        | host=192.168.126.132 port=5435  user=repmgr dbname=repmgr connect_timeout=2
repmgr=# select * from repmgr.nodes ;
-[ RECORD 1 ]----+----------------------------------------------------------------------------
node_id          | 1
upstream_node_id |
active           | t
node_name        | node1
type             | primary
location         | default
priority         | 100
conninfo         | host=192.168.126.132 port=5435  user=repmgr dbname=repmgr connect_timeout=2
repluser         | repmgr
slot_name        |
config_file      | /home/postgres/repmgr.conf

[postgres@pg15_rh7_133 ~]$ cat repmgr.conf
   node_id=2
   node_name='node2'
   conninfo='host=192.168.126.133 port=5435  user=repmgr dbname=repmgr connect_timeout=2'
   data_directory='/data/pg15'
[postgres@pg15_rh7_133 ~]$ /usr/local/pg15/bin/repmgr -h 192.168.126.132 -p5435 -U repmgr -d repmgr -f /home/postgres/repmgr.conf standby clone --dry-run
NOTICE: destination directory "/data/pg15" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.126.132 port=5435 user=repmgr dbname=repmgr
DETAIL: current installation size is 29 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 "/data/pg15"
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:
 pg_basebackup -l "repmgr base backup"  -D /data/pg15 -h 192.168.126.132 -p 5435 -U repmgr -X stream
INFO: all prerequisites for "standby clone" are met
--根据提示查看相对应的参数设置是否没有去掉注释
--注意备库repmgr.conf文件中的目录要为空
[postgres@pg15_rh7_133 ~]$  /usr/local/pg15/bin/repmgr -h 192.168.126.132 -p5435 -U repmgr -d repmgr -f /home/postgres/repmgr.conf standby clone --force
NOTICE: destination directory "/data/pg15" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.126.132 port=5435 user=repmgr dbname=repmgr
DETAIL: current installation size is 29 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 "/data/pg15" exists but is not empty
NOTICE: -F/--force provided - deleting existing data directory "/data/pg15"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
 pg_basebackup -l "repmgr base backup"  -D /data/pg15 -h 192.168.126.132 -p 5435 -U repmgr -X stream
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /data/pg15 start
HINT: after starting the server, you need to register this standby with "repmgr standby register"
--启动备库
pg_ctl -D /data/pg15 start

--连接到主库查看
repmgr=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 17730
usesysid         | 24586
usename          | repmgr
application_name | node2
client_addr      | 192.168.126.133
client_hostname  |
client_port      | 49068
backend_start    | 2023-05-04 14:21:56.605203+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/150001F0
write_lsn        | 0/150001F0
flush_lsn        | 0/150001F0
replay_lsn       | 0/150001F0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-05-04 20:51:52.968141+08

repmgr -f  /home/postgres/repmgr.conf standby register
INFO: connecting to local node "node2" (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 "node2" (ID: 2) successfully registered
--查看集群状态
[postgres@pg15_rh7_133 ~]$ repmgr -f  /home/postgres/repmgr.conf cluster show
ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                          
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------------------
1  | node1 | primary | * running |          | default  | 100      | 1        | host=192.168.126.132 port=5435  user=repmgr dbname=repmgr connect_timeout=2
2  | node2 | standby |   running | node1    | default  | 100      | 1        | host=192.168.126.133 port=5435  user=repmgr dbname=repmgr connect_timeout=2