postgresql使用docker镜像bitnami/postgresql-repmgr快速搭建主备环境，可自动切换

postgresql使用docker镜像bitnami/postgresql-repmgr快速搭建主备环境，可自动切换

https://hub.docker.com/u/bitnami

环境准备：

[root@pg-0 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

节点1：
hostnamectl set-hostname pg-0

节点2：
hostnamectl set-hostname pg-1
两节点：
vi /etc/hosts
192.168.207.131 pg-0
192.168.207.132 pg-1

两节点安装docker

yum install docker -ysystemctl start dockersystemctl enable docker

节点1和2操作：准备pg挂接目录。

mkdir -p /opt/pg
chown -R 1001:1001 /opt/pg

节点1操作：bitnami/postgresql-repmgr:latest为最新版本，如果选择14版本，则修改latest为14

docker run -detach --restart always --name pg-0 \
--net=host \
--env REPMGR_PARTNER_NODES=pg-0,pg-1 \
--env REPMGR_NODE_NAME=pg-0 \
--env REPMGR_NODE_NETWORK_NAME=pg-0 \
--env REPMGR_PRIMARY_HOST=pg-0 \
--env REPMGR_PASSWORD=Abcd1234 \
--env POSTGRESQL_PASSWORD=Abcd1234 \
-v /opt/pg/:/bitnami/postgresql/ \
bitnami/postgresql-repmgr:latest

节点2操作：

docker run -detach --restart always --name pg-1 \
  --net=host \
  --env REPMGR_PARTNER_NODES=pg-0,pg-1 \
  --env REPMGR_NODE_NAME=pg-1 \
  --env REPMGR_NODE_NETWORK_NAME=pg-1 \
  --env REPMGR_PRIMARY_HOST=pg-0 \
  --env REPMGR_PASSWORD=Abcd1234 \
  --env POSTGRESQL_PASSWORD=Abcd1234 \
  -v /opt/pg/:/bitnami/postgresql/ \
  bitnami/postgresql-repmgr:latest

两节点均连接pg库，创建检查用户

CREATE USER checkuser WITH PASSWORD 'Abcd1234';

d:\pgAdmin4\v6\runtime>"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.133' port='5432' dbname='postgres' user='postgres' sslmode='prefer' sslcompression='False' password='Abcd1234'"
psql (15.1, server 15.2)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repmgr    | Superuser, Replication                                     | {}


postgres=# CREATE USER checkuser WITH PASSWORD 'Abcd1234';
CREATE ROLE
postgres=#
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 checkuser |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repmgr    | Superuser, Replication                                     | {}


postgres=# \q

节点1和2生成pgpool容器，这样连接节点131或132的9999端口任意节点宕机都可以连接到主库。

docker run -detach --restart always --name pgpool \
--env PGPOOL_BACKEND_NODES=0:pg-0:5432,1:pg-1:5432 \
--env PGPOOL_SR_CHECK_USER=checkuser \
--env PGPOOL_SR_CHECK_PASSWORD=Abcd1234 \
--env PGPOOL_ENABLE_LDAP=no \
--env PGPOOL_POSTGRES_USERNAME=postgres \
--env PGPOOL_POSTGRES_PASSWORD=Abcd1234 \
--env PGPOOL_ADMIN_USERNAME=admin \
--env PGPOOL_ADMIN_PASSWORD=Abcd1234 \
--add-host=pg-0:192.168.207.131 \
--add-host=pg-1:192.168.207.132 \
-p 9999:5432 \
bitnami/pgpool:latest

测试记录：

停止主库pg-0后再启动观察连接情况：

启停容器：docker stop pg-0
docker start pg-0
查看日志docker logs -f pg-0docker logs -f pgpooldocker logs --tail=10 pg-0删除容器：docker rm pg-0docker rm pgpool----------------------------------------------------------------------------d:\pgAdmin4\v6\runtime>"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.131' port='5432' dbname='postgres' user='postgres' ss
lmode='prefer' sslcompression='False' " 2>>&1
psql (15.1, server 15.2)
Type "help" for help.

postgres=# create database t3;
ERROR:  cannot execute CREATE DATABASE in a read-only transaction
postgres=# exit

d:\pgAdmin4\v6\runtime>"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.131' port='9999' dbname='postgres' user='postgres' ss
lmode='prefer' sslcompression='False' " 2>>&1
psql (15.1, server 15.2)
Type "help" for help.

postgres=# create database t3;
CREATE DATABASE
postgres=# exit

d:\pgAdmin4\v6\runtime>"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.132' port='5432' dbname='postgres' user='postgres' ss
lmode='prefer' sslcompression='False' " 2>>&1
psql (15.1, server 15.2)
Type "help" for help.

postgres=# create database t4;
CREATE DATABASE
postgres=# exit

d:\pgAdmin4\v6\runtime>"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.132' port='9999' dbname='postgres' user='postgres' ss
lmode='prefer' sslcompression='False' " 2>>&1
psql (15.1, server 15.2)
Type "help" for help.

postgres=# create database t5;
CREATE DATABASE

d:\pgAdmin4\v6\runtime>echo SELECT * from pg_is_in_recovery();|"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.132' port='5432' dbname='
postgres' user='postgres' sslmode='prefer' sslcompression='False' " 2>>&1
 pg_is_in_recovery
-------------------
 f
(1 row)


d:\pgAdmin4\v6\runtime>echo SELECT * from pg_is_in_recovery();|"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.132' port='9999' dbname='
postgres' user='postgres' sslmode='prefer' sslcompression='False' " 2>>&1
 pg_is_in_recovery
-------------------
 f
(1 row)


d:\pgAdmin4\v6\runtime>echo SELECT * from pg_is_in_recovery();|"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.131' port='9999' dbname='
postgres' user='postgres' sslmode='prefer' sslcompression='False' " 2>>&1
 pg_is_in_recovery
-------------------
 f
(1 row)


d:\pgAdmin4\v6\runtime>echo SELECT * from pg_is_in_recovery();|"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.131' port='5432' dbname='
postgres' user='postgres' sslmode='prefer' sslcompression='False' " 2>>&1
 pg_is_in_recovery
-------------------
 t
(1 row)

修改参数，保留归档日志：

两节点创建归档目录：[root@pg-0 pg]# pwd
/opt/pg
[root@pg-0 pg]# ll
total 4
drwxr-xr-x  2 1001 root   48 May  5 09:34 conf
drwx------ 19 1001 root 4096 May  5 10:21 data
drwxr-xr-x  2 1001 root   25 May  5 09:35 lock
[root@pg-0 pg]# mkdir arch
[root@pg-0 pg]# chown -R 1001:root arch
[root@pg-0 pg]# ll arch
total 0
[root@pg-0 pg]# ll arch
total 16384
-rw------- 1 1001 root 16777216 May  5 10:23 000000010000000000000007
C:\Users\jyc>"d:\pgAdmin4\v6\runtime\psql.exe" "host='192.168.207.133' port='5432' dbname='postgres' user='postgres' password='Abcd1234' "
alter system set archive_command = 'test ! -f /bitnami/postgresql/arch/%f && cp --preserve=timestamps %p /bitnami/postgresql/arch/%f ; find /bitnami/postgresql/arch/ -type f -mtime +7 -exec rm -f {} \;';
select pg_reload_conf();
select pg_switch_wal();

遇到问题：如果reboot节点或者手动docker stop pgpool

docker stop pg-0

则切换后的参数\竟然会出现丢失的问题：查看备库日志发现docker logs -f pg-1

重新执行解决：

由于斜杠的问题，所以我们只能考虑换一种办法来处理保留归档策略，这样就不受影响了：

alter system set archive_command = 'test ! -f /bitnami/postgresql/arch/%f && cp --preserve=timestamps %p /bitnami/postgresql/arch/%f ; for i in $(find /bitnami/postgresql/arch/ -type f -mtime +7); do rm -f $i; done';
select pg_reload_conf();
select pg_switch_wal();
show archive_command;

相关参考：

https://www.cnblogs.com/pearlcity/p/16882508.html

https://www.cnblogs.com/helontian/p/14867284.html