PG_repmgr搭建

已有环境一台pg13的库，有部分数据，且在运行，以此搭建repmgr

在另外两台机器上安装postgresql13

安装pg源

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装postgresql

sudo yum install -y postgresql13-server

3台主机更改hosts

[root@jiacheng postgresql]# vim /etc/hosts [root@jiacheng postgresql]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.220.199 jiacheng pg13_primary 192.168.220.128 pg13_slave 192.168.220.129 pg13_slave1

更改postgres用户密码为postgres echo "postgres" | passwd --stdin postgres

postgres用户配置ssh免密，3台相互配置

ssh-keygen ssh-copy-id -i postgres@pg13_primary ssh-copy-id -i postgres@pg13_slave ssh-copy-id -i postgres@pg13_slave1

主库修改配置文件

/var/lib/pgsql/13/data/postgresql.conf max_wal_senders = 10 max_replication_slots = 10 wal_level = hot_standby hot_standby = on archive_mode = on archive_command = '/bin/true' shared_preload_libraries = 'repmgr'

主库pg_hba.conf 配置文件

$ vim pg_hba.conf

host all all 192.168.220.199/32 trust host all all 192.168.220.128/32 trust

host all all 192.168.220.129/32 trust

host replication all 192.168.220.199/32 trust host replication all 192.168.220.128/32 trust

host replication all 192.168.220.129/32 trust

创建repmgr工具默认使用的数据库管理用户

su - postgres

$ createuser -s repmgr $ createdb repmgr -O repmgr

各个节点使用postgres安装repmgr

PG使用yum装的这里repmgr也需要用yum装 PostgreSQL 13 通用公共 RPM 存储库 curl https://dl.2ndquadrant.com/default/release/get/13/rpm | sudo bash yum -y install repmgr13

修改环境变量 repmgr命令在pg安装目录下

PATH=/usr/pgsql-13/bin:$PATH

配置文件位置 vim /etc/repmgr/13/repmgr.conf

修改主节点配置文件

$ cat repmgr.conf

node_id=1 # 节点ID，高可用集群各节点标识 node_name='pg1' # 节点名称，高可用集群各节点名称 conninfo='host=192.168.220.199 user=repmgr password=repmgr dbname=repmgr connect_timeout=2' # 本节点数据库连接信息 data_directory='/var/lib/pgsql/13/data' # pg数据目录 replication_user='repmgr' # 流复制数据库用户，默认使用repmgr pg_bindir='/usr/pgsql-13/bin' # pg软件目录 #shutdown_check_timeout=10

注册主节点

这里密码不对折腾半天，还去库里改了下密码

su postgres -bash-4.2$ repmgr primary register INFO: connecting to primary database... ERROR: connection to database failed DETAIL: FATAL: password authentication failed for user "repmgr"

DETAIL: attempted to connect using: user=repmgr password=repmgr connect_timeout=2 dbname=repmgr host=192.168.220.199 fallback_application_name=repmgr options=-csearch_path= -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

查看集群信息

repmgr cluster show

修改备库配置：

vim /etc/repmgr/13/repmgr.conf

node_id=3 node_name='pg3' conninfo='host=192.168.220.129 user=repmgr password=repmgr dbname=repmgr connect_timeout=2' data_directory='/var/lib/pgsql/13/data' replication_user='repmgr' pg_bindir='/usr/pgsql-13/bin' #shutdown_check_timeout=10

备服务器节点使用repmgr进行克隆

创建密码文件

# 例子 echo "#ip:port:db:user:pwd" >> ~/.pgpass

su - postgres echo "192.168.220.199:5432:repmgr:repmgr:repmgr" >> ~/.pgpass echo "192.168.220.128:5432:repmgr:repmgr:repmgr" >> ~/.pgpass echo "192.168.220.129:5432:repmgr:repmgr:repmgr" >> ~/.pgpass chmod 0600 ~/.pgpass

测试连通性

$ psql 'host=192.168.220.199 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'

-- dry-run表示命令测试，并不会实际执行，可用于验证是否会出现一些基本错误 $ repmgr -h ${主服务器_ip} -U repmgr -d repmgr -f /etc/repmgr/13/repmgr.conf standby clone --dry-run

repmgr -h 192.168.220.199 -U repmgr -d repmgr -f /etc/repmgr/13/repmgr.conf standby clone --dry-run