三、postgresql-14+repmgr-5.3.3高可用安装配置

一、资源规划

主机名

IP地址

服务器配置

角色

数据目录

cdh01

156.25.236.10

1G 1core

主库

/home/pg14/data

cdh02

156.25.236.10

1G 1core

备库

/home/pg14/data

二、操作系统调优(主备)

1、关闭防火墙和selinux

[root@cdh01 ~]# systemctl stop firewalld

[root@cdh01 ~]# systemctl disable firewalld

[root@cdh01 ~]# systemctl status firewalld

[root@cdh01 ~]# service iptables stop

[root@cdh01 ~]# service iptables status

禁用selinux

[root@cdh01 ~]# vi /etc/selinux/config

SELINUX=disabled

[root@cdh01 ~]#setenforce 0

[root@cdh01 ~]#getenforce

2、调整ulimit限制

[root@cdh01 ~]# vi /etc/security/limits.conf

* soft nofile 65536
* hard nofile 65535
* soft nproc 65536
* hard nproc 65535
* soft core unlimited
* hard core unlimited

3、调整内核参数

[root@cdh01 ~]# vi /etc/sysctl.conf 

kernel.sem = 5010 641280 5010 256

[root@cdh01 ~]# sysctl -p 生效

4、调整RemoveIPC和DefaultTasksAccounting

[root@cdh01 ~]# vi /etc/systemd/logind.conf
RemoveIPC=no

[root@cdh01 ~]# vi /etc/systemd/system.conf
DefaultTasksAccounting=no
执行
[root@cdh01 ~]# systemctl daemon-reload
[root@cdh01 ~]# systemctl darmon-reexec
[root@cdh01 ~]# systemctl restart systemd-logind

5、调整sshd

[root@cdh01 ~]# vi /etc/ssh/sshd_config
PermitRootLogin yes
GSSAPIAuthentication no
UseDNS no
[root@cdh01 ~]# systemctl restart sshd

6、配置免密登录

ssh免密登录 root-->root postgres-->postgres

节点ssh互信
cdh01:
[root@cdh01 ~]# ssh-keygen -t rsa
[postgres@cdh01 ~]$ssh-keygen -t rsa

cdh02:
[root@cdh01 ~]# ssh-keygen -t rsa
[postgres@cdh02 ~]$ssh-keygen -t rsa

cdh01:
[root@cdh01 ~]# cd .ssh/
[root@cdh01 ~/.ssh]#cat id_dsa.pub >> authorized_keys
[root@cdh01 ~/.ssh]#cat /home/postgres/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
[root@cdh01 ~/.ssh]#ssh root@156.25.236.11 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
[root@cdh01 ~/.ssh]#ssh postgres@156.25.236.11 cat /home/postgres/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
[root@cdh01 ~/.ssh]#cp authorized_keys /home/postgres/.ssh/
[root@cdh01 ~/.ssh]#scp authorized_keys root@156.25.236.11:~/.ssh/
[root@cdh01 ~/.ssh]#scp authorized_keys postgres@156.25.236.11:/home/kingbase/.ssh/
[root@cdh01 ~/.ssh]#chmod 600 authorized_keys
[postgres@cdh01 ~]$chmod 600 authorized_keys
测试
两个节点互相访问,可以直接登录无需密码说明配置成功。两个节点必须互相测试一次,否则后续集群脚本无法执行。
节点1(cdh01)上执行:

[root@cdh01 ~]#ssh cdh02
[postgres@cdh01 ~]$ssh postgres@cdh02
节点2(cdh02)上执行:
[root@cdh02 ~]#ssh cdh01
[postgres@cdh02 ~]$ssh postgres@cdh01

三、repmgr编译安装(主备)

postgresql-14.5数据库已安装完成,源码安装具体步骤可参见之前记录。数据库安装目录/home/pg14/soft,数据目录/home/pg14/data。具体介绍repmgr安装过程。

1、下载源码包

​https://repmgr.org/download/repmgr-5.3.3.tar.gz​

2、配置数据库环境变量

vi ~/.bash_profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/home/pg14/soft/bin
PGDATA=/home/pg14/data
export PGDATA
export PATH

若不配置$PGDATA,编译repmgr报错:configure: error: could not find pg_config, set PG_CONFIG or PATH

[pg14@cdh02 repmgr-5.3.3]$ ./configure 
checking for a sed that does not truncate output... /bin/sed
checking for pg_config... no
configure: error: could not find pg_config, set PG_CONFIG or PATH

3、解压编译

[pg14@cdh01 ~]$ tar xf repmgr-5.3.3.tar.gz 
[pg14@cdh01 ~]$ cd repmgr-5.3.3
[pg14@cdh01 repmgr-5.3.3]$ ./configure
checking for a sed that does not truncate output... /bin/sed
checking for pg_config... /home/pg14/soft/bin/pg_config
configure: building against PostgreSQL 14.5
checking for gnused... no
checking for gsed... no
checking for sed... yes

configure: creating ./config.status
config.status: creating Makefile
config.status: creating Makefile.global
config.status: creating config.h
[pg14@cdh01 repmgr-5.3.3]$ make install
Building against PostgreSQL 14
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr.o repmgr.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o repmgr.so repmgr.o -L/home/pg14/soft/lib -Wl,--as-needed -Wl,-rpath,'/home/pg14/soft/lib',--enable-new-dtags -L/home/pg14/soft/lib -lpq
sed -E 's/REPMGR_VERSION_DATE.*""/REPMGR_VERSION_DATE "2022-10-26"/' repmgr_version.h.in >repmgr_version.h;
sed -i -E 's/PG_ACTUAL_VERSION_NUM/PG_ACTUAL_VERSION_NUM 140005/' repmgr_version.h
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr-client.o repmgr-client.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr-action-primary.o repmgr-action-primary.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr-action-standby.o repmgr-action-standby.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr-action-witness.o repmgr-action-witness.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr-action-cluster.o repmgr-action-cluster.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr-action-node.o repmgr-action-node.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr-action-service.o repmgr-action-service.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr-action-daemon.o repmgr-action-daemon.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o configdata.o configdata.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o configfile.o configfile.c
flex -o'configfile-scan.c' configfile-scan.l
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o configfile-scan.o configfile-scan.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o log.o log.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o strutil.o strutil.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o controldata.o controldata.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o dirutil.o dirutil.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o compat.o compat.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o dbutils.o dbutils.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o sysutils.o sysutils.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC repmgr-client.o repmgr-action-primary.o repmgr-action-standby.o repmgr-action-witness.o repmgr-action-cluster.o repmgr-action-node.o repmgr-action-service.o repmgr-action-daemon.o configdata.o configfile.o configfile-scan.o log.o strutil.o controldata.o dirutil.o compat.o dbutils.o sysutils.o -L/home/pg14/soft/lib -lpgcommon -lpgport -L/home/pg14/soft/lib -lpq -L/home/pg14/soft/lib -Wl,--as-needed -Wl,-rpath,'/home/pg14/soft/lib',--enable-new-dtags -lpgcommon -lpgport -lssl -lcrypto -lz -lreadline -lpthread -lrt -ldl -lm -o repmgr
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgrd.o repmgrd.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg14/soft/include/postgresql/internal -I/home/pg14/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg14/soft/include/postgresql/server -I/home/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgrd-physical.o repmgrd-physical.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC repmgrd.o repmgrd-physical.o configdata.o configfile.o configfile-scan.o log.o dbutils.o strutil.o controldata.o compat.o sysutils.o -L/home/pg14/soft/lib -lpgcommon -lpgport -L/home/pg14/soft/lib -lpq -L/home/pg14/soft/lib -Wl,--as-needed -Wl,-rpath,'/home/pg14/soft/lib',--enable-new-dtags -lpgcommon -lpgport -lssl -lcrypto -lz -lreadline -lpthread -lrt -ldl -lm -o repmgrd
/bin/mkdir -p '/home/pg14/soft/lib/postgresql'
/bin/mkdir -p '/home/pg14/soft/share/postgresql/extension'
/bin/mkdir -p '/home/pg14/soft/share/postgresql/extension'
/bin/mkdir -p '/home/pg14/soft/bin'
/bin/install -c -m 755 repmgr.so '/home/pg14/soft/lib/postgresql/repmgr.so'
/bin/install -c -m 644 .//repmgr.control '/home/pg14/soft/share/postgresql/extension/'
/bin/install -c -m 644 .//repmgr--unpackaged--4.0.sql .//repmgr--unpackaged--5.1.sql .//repmgr--unpackaged--5.2.sql .//repmgr--unpackaged--5.3.sql .//repmgr--4.0.sql .//repmgr--4.0--4.1.sql .//repmgr--4.1.sql .//repmgr--4.1--4.2.sql .//repmgr--4.2.sql .//repmgr--4.2--4.3.sql .//repmgr--4.3.sql .//repmgr--4.3--4.4.sql .//repmgr--4.4.sql .//repmgr--4.4--5.0.sql .//repmgr--5.0.sql .//repmgr--5.0--5.1.sql .//repmgr--5.1.sql .//repmgr--5.1--5.2.sql .//repmgr--5.2.sql .//repmgr--5.2--5.3.sql .//repmgr--5.3.sql '/home/pg14/soft/share/postgresql/extension/'
/bin/install -c -m 755 repmgr repmgrd '/home/pg14/soft/bin/'

编译报错make: flex: Command not found

yum install -y flex

编译安装默认不会生成repmgr.conf配置文件,可复制源码包的示例repmgr.conf.sample配置文件到指定目录。在使用repmgr命令需要指定repmgr.conf路径,通过-f/--config-file参数指定路径。如果找不到或无法读取该文件,则会引发错误,并且不会尝试检查默认位置。这是为了防止repmgr意外读取错误的配置文件。为了解决这个问题,可以将repmgr.con创建在pg_conf默认的sysconfidir路径下:/home/pg14/soft/etc/postgresql

[pg14@cdh01 data]$ pg_config --sysconfdir
/home/pg14/soft/etc/postgresql
[pg14@cdh01 repmgr-5.3.3]$ cd /home/pg14/repmgr-5.3.3
[pg14@cdh01 repmgr-5.3.3]$mkdir /home/pg14/soft/etc/postgresql -p
[pg14@cdh01 repmgr-5.3.3]$cp repmgr.conf.sample /home/pg14/soft/etc/postgresql/repmgr.conf

四、主库配置

1、初始化数据库

[pg14@cdh02 bin]$ ./initdb -Upostgres -W -D /home/pg14/data -k 
The files belonging to this database system will be owned by user "pg14".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

Enter new superuser password:
Enter it again:

creating directory /home/pg14/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

./pg_ctl -D /home/pg14/data -l logfile start

2、修改数据库配置

postgresql.conf

listen_addresses = '*'

shared_preload_libraries = 'repmgr'

wal_log_hints = on #开启支持pg_rewind

pg_hba.conf添加如下策略

host    all             all             156.25.236.10/24        trust
host all all 156.25.236.11/24 trust
host replication all 156.25.236.10/24 trust
host replication all 156.25.236.11/24 trust

配置为:

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all 156.25.236.10/24 trust
host all all 156.25.236.11/24 trust
host replication all 156.25.236.10/24 trust
host replication all 156.25.236.11/24 trust

启动数据库

[pg14@cdh01 data]$ pg_ctl start -D /home/pg14/data/
waiting for server to start....2022-10-26 17:10:38.096 CST user=,db=,id=[13508]LOG: redirecting log output to logging collector process
2022-10-26 17:10:38.096 CST user=,db=,id=[13508]HINT: Future log output will appear in directory "/pglog".
done
server started

3、修改repmgr.conf

vi /home/pg14/soft/etc/postgresql/repmgr.conf 

node_id=1
node_name='node1'
conninfo='host=156.25.236.10 port=5666 user=postgres dbname=postgres'
data_directory='/home/pg14/data'

4、注册主节点

[pg14@cdh01 data]$ cd /home/pg14/soft/etc/postgresql
[pg14@cdh01 postgresql]$ 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

[pg14@cdh01 postgresql]$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------
1 | node1 | primary | * running | | default | 100 | 1 | host=156.25.236.10 port=5666 user=postgres dbname=postgres

五、备库配置

1、修改repmgr.conf

vi /home/pg14/soft/etc/postgresql/repmgr.conf 

node_id=2
node_name='node2'
conninfo='host=156.25.236.11 port=5666 user=postgres dbname=postgres'
data_directory='/home/pg14/data'

2、克隆备库

[pg14@cdh02 ~]$ repmgr standby clone -h 156.25.236.10 -p5666 -U postgres
NOTICE: destination directory "/home/pg14/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=156.25.236.10 port=5666 user=postgres
DETAIL: current installation size is 42 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)
INFO: checking and correcting permissions on existing directory "/home/pg14/data"
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 /home/pg14/data -h 156.25.236.10 -p 5666 -U postgres -X stream
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /home/pg14/data start
HINT: after starting the server, you need to register this standby with "repmgr standby register"

3、启动并注册

[pg14@cdh02 data]$ pg_ctl start
waiting for server to start....2022-10-27 16:15:39.251 CST user=,db=,id=[25176]LOG: redirecting log output to logging collector process
2022-10-27 16:15:39.251 CST user=,db=,id=[25176]HINT: Future log output will appear in directory "/pglog".
done
server started
[pg14@cdh02 etc]$ cd /home/pg14/soft/etc
[pg14@cdh02 etc]$ repmgr 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
[pg14@cdh02 etc]$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------
1 | node1 | primary | * running | | default | 100 | 1 | host=156.25.236.10 port=5666 user=postgres dbname=postgres
2 | node2 | standby | running | node1 | default | 100 | 1 | host=156.25.236.11 port=5666 user=postgres dbname=postgres

六、启动repmgrd服务(主备)

1、查看repmgr服务状态

[pg14@cdh01 ~]$ repmgr service status
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+-------+---------+-----------+----------+-------------+-----+---------+--------------------
1 | node1 | primary | * running | | not running | n/a | n/a | n/a
2 | node2 | standby | running | node1 | not running | n/a | n/a | n/a

2、启动repmgrd服务

[pg14@cdh01 ~]$ repmgr service status
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+-------+---------+-----------+----------+-------------+-----+---------+--------------------
1 | node1 | primary | * running | | not running | n/a | n/a | n/a
2 | node2 | standby | running | node1 | not running | n/a | n/a | n/a
[pg14@cdh01 ~]$ repmgrd -d
[2022-10-26 23:18:08] [NOTICE] repmgrd (repmgrd 5.3.3) starting up
[2022-10-26 23:18:08] [INFO] connecting to database "host=156.25.236.10 port=5666 user=postgres dbname=postgres"
[pg14@cdh01 ~]$ INFO: set_repmgrd_pid(): provided pidfile is /tmp/repmgrd.pid
[2022-10-26 23:18:08] [NOTICE] starting monitoring of node "node1" (ID: 1)
[2022-10-26 23:18:08] [INFO] "connection_check_type" set to "ping"
[2022-10-26 23:18:08] [NOTICE] monitoring cluster primary "node1" (ID: 1)
[2022-10-26 23:18:08] [INFO] child node "node2" (ID: 2) is attached

[pg14@cdh01 ~]$ repmgr service status
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+-------+---------+-----------+----------+-------------+-------+---------+--------------------
1 | node1 | primary | * running | | running | 14467 | no | n/a
2 | node2 | standby | running | node1 | not running | n/a | n/a | n/a

[pg14@cdh02 ~]$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------
1 | node1 | primary | * running | | default | 100 | 1 | host=156.25.236.10 port=5666 user=postgres dbname=postgres
2 | node2 | standby | running | node1 | default | 100 | 1 | host=156.25.236.11 port=5666 user=postgres dbname=postgres
[pg14@cdh02 ~]$ repmgr service status
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+-------+---------+-----------+----------+-------------+-------+---------+--------------------
1 | node1 | primary | * running | | running | 14467 | no | n/a
2 | node2 | standby | running | node1 | not running | n/a | n/a | n/a
[pg14@cdh02 ~]$ repmgrd -d
[2022-10-27 16:33:06] [NOTICE] repmgrd (repmgrd 5.3.3) starting up
[2022-10-27 16:33:06] [INFO] connecting to database "host=156.25.236.11 port=5666 user=postgres dbname=postgres"
[pg14@cdh02 ~]$ INFO: set_repmgrd_pid(): provided pidfile is /tmp/repmgrd.pid
[2022-10-27 16:33:06] [NOTICE] starting monitoring of node "node2" (ID: 2)
[2022-10-27 16:33:06] [INFO] "connection_check_type" set to "ping"
[2022-10-27 16:33:06] [INFO] monitoring connection to upstream node "node1" (ID: 1)

[pg14@cdh02 ~]$ repmgr service status
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+-------+---------+-----------+----------+---------+-------+---------+--------------------
1 | node1 | primary | * running | | running | 14467 | no | n/a
2 | node2 | standby | running | node1 | running | 26051 | no | 0 second(s) ago

3、添加备库为witness节点

[pg14@cdh02 ~]$ repmgr witness register -h 156.25.236.11
INFO: connecting to witness node "node2" (ID: 2)
ERROR: provided node is a standby
HINT: a witness node must run on an independent primary server

4、配置repmgr开机启动

[root@cdh01 ~]# systemctl enable repmgrd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/repmgrd.service to /etc/systemd/system/repmgrd.service.

七、故障转移自动切换

1、添加配置自动切换(主备)

failover='automatic'
promote_command='/home/pg14/soft/bin/repmgr standby promote -f /home/pg14/soft/etc/postgresql/repmgr.conf' follow_command='/home/pg14/soft/bin/repmgr standby follow -f /home/pg14/soft/etc/postgresql/repmgr.conf

2、重启repmgr主库服务,使配置生效

[pg14@cdh01 ~]$ repmgr node service --action=restart
DETAIL: executing server command "pg_ctl -w -D '/home/pg14/data' restart"

3、模拟主库故障,标记为备库。

[pg14@cdh01 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[pg14@cdh01 data]$ touch standby.signal

备库查看状态

[pg14@cdh02 ~]$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+---------------+----------+----------+----------+----------+-------------------------------------------------------------
1 | node1 | primary | ? unreachable | ? | default | 100 | | host=156.25.236.10 port=5666 user=postgres dbname=postgres
2 | node2 | standby | running | ? node1 | default | 100 | 1 | host=156.25.236.11 port=5666 user=postgres dbname=postgres

WARNING: following issues were detected

- unable to connect to node "node1" (ID: 1)
- node "node1" (ID: 1) is registered as an active primary but is unreachable
- unable to connect to node "node2" (ID: 2)'s upstream node "node1" (ID: 1)
- unable to determine if node "node2" (ID: 2) is attached to its upstream node "node1" (ID: 1)

HINT: execute with --verbose option to see connection error messages

4、原备库提升为主库

pg14@cdh02 postgresql]$ /home/pg14/soft/bin/repmgr standby promote -f /home/pg14/soft/etc/postgresql/repmgr.conf
NOTICE: promoting standby to primary
DETAIL: promoting server "node2" (ID: 2) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "node2" (ID: 2) was successfully promoted to primary
[pg14@cdh02 postgresql]$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------
1 | node1 | primary | - failed | ? | default | 100 | | host=156.25.236.10 port=5666 user=postgres dbname=postgres
2 | node2 | primary | * running | | default | 100 | 2 | host=156.25.236.11 port=5666 user=postgres dbname=postgres

WARNING: following issues were detected

- unable to connect to node "node1" (ID: 1)

HINT: execute with --verbose option to see connection error messages

5、原主库克隆新主库

[pg14@cdh01 ~]$ repmgr standby clone  -h 156.25.236.11 -Upostgres -p5666 --force -f /home/pg14/soft/etc/postgresql/repmgr.conf 
NOTICE: destination directory "/home/pg14/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=156.25.236.11 user=postgres port=5666
DETAIL: current installation size is 42 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)
INFO: checking and correcting permissions on existing directory "/home/pg14/data"
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 /home/pg14/data -h 156.25.236.11 -p 5666 -U postgres -X stream
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /home/pg14/data start
HINT: after starting the server, you need to re-register this standby with "repmgr standby register --force" to update the existing node record

6、启动新备库,重新注册

[pg14@cdh01 ~]$ pg_ctl start
waiting for server to start....2022-10-27 12:07:25.819 CST user=,db=,id=[23067]LOG: redirecting log output to logging collector process
2022-10-27 12:07:25.819 CST user=,db=,id=[23067]HINT: Future log output will appear in directory "/pglog".
done
server started
[pg14@cdh01 ~]$ repmgr standby register --force
INFO: connecting to local node "node1" (ID: 1)
INFO: connecting to primary database
INFO: standby registration complete
NOTICE: standby node "node1" (ID: 1) successfully registered
[pg14@cdh01 ~]$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------
1 | node1 | standby | running | node2 | default | 100 | 2 | host=156.25.236.10 port=5666 user=postgres dbname=postgres
2 | node2 | primary | * running | | default | 100 | 2 | host=156.25.236.11 port=5666 user=postgres dbname=postgres
[pg14@cdh02 postgresql]$ repmgr service status
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+-------+---------+-----------+----------+---------+-------+---------+--------------------
1 | node1 | standby | running | node2 | running | 14467 | no | 0 second(s) ago
2 | node2 | primary | * running | | running | 26051 | no | n/a

7、自动切换

1)、主备添加

service_start_command = '/home/pg14/soft/bin/pg_ctl -D /home/pg14/data start'
service_stop_command = '/home/pg14/soft/bin/pg_ctl -D /home/pg14/data stop'
service_restart_command = '/home/pg14/soft/bin/pg_ctl -D /home/pg14/data restart'
service_reload_command = '/home/pg14/soft/bin/pg_ctl -D /home/pg14/data reload'

repmgrd_service_start_command = '/home/pg14/soft/bin/repmgrd -f /home/pg14/soft/etc/postgresql/repmgr.conf --pid-file /tmp/repmgrd.pid --daemonize'
repmgrd_service_stop_command = 'repmgr node service --list-actions --action=stop'

2)、使配置生效

[pg14@cdh01 ~]$ repmgr node service --action=restart
DETAIL: executing server command "pg_ctl -w -D '/home/pg14/data' restart"

3)、停止主库node2

[pg14@cdh02 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

4)、备库node1变为新主

[pg14@cdh01 ~]$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------
1 | node1 | primary | * running | | default | 100 | 3 | host=156.25.236.10 port=5666 user=postgres dbname=postgres
2 | node2 | primary | - failed | ? | default | 100 | | host=156.25.236.11 port=5666 user=postgres dbname=postgres

5)、重新克隆node2为备库

发表评论

相关文章