Postgresql基于repmgr（1主2从+1witness）实现自动和手动故障切换的集群安装

最新推荐文章于 2024-09-02 16:45:44 发布

CR7-NO.1

最新推荐文章于 2024-09-02 16:45:44 发布

阅读量1.5k

点赞数 10

分类专栏：数据库文章标签： postgresql 数据库

本文链接：https://blog.youkuaiyun.com/qq_39169917/article/details/131471139

版权

数据库专栏收录该内容

2 篇文章

订阅专栏

环境:
Os:Centos 7
DB:13.8

下载安装介质
下载地址:
源码下载地址: https://www.postgresql.org/ftp/source/
二进制下载地址(新版本的没有二进制下载,只能使用源码安装):https://www.enterprisedb.com/download-postgresql-binaries
我这里下载的介质是:
postgresql-13.8.tar.gz
安装依赖包
yum install readline
yum install gcc
yum -y install -y readline-devel
yum install zlib-devel
源码安装

卸载旧版本的postgres

yum remove postgresql*

验证是都卸载完毕

rpm -qa | grep postgresq

[root@host134 soft]# tar -xvf postgresql-13.8.tar.gz
[root@host134 soft]# cd postgresql-13.8
[root@host134 soft]# mkdir -p /opt/pg13 ##这里是指定安装目录
[root@host134 soft]#./configure --prefix=/opt/pg13
[root@host134 soft]# make
[root@host134 soft]# make install

4.创建相应的用户
[root@localhost opt]# groupadd postgres
[root@localhost opt]# useradd -g postgres postgres

5.创建数据及日志目录，并做相应授权
[root@localhost soft]# mkdir -p /opt/pg13/{data,log}
[root@localhost soft]# chown -R postgres:postgres /opt/pg13

6.初始化数据库
#su - postgres
[postgres@host134 /]$ cd /opt/pg13/bin
[postgres@host134 bin]$ ./initdb -D /opt/pg13/data/

看情况开启checksums

./initdb -D /opt/pg13/data/ --data-checksums

6.修改配置文件

[postgres@host134 data]$ cd /opt/pg13/data
[postgres@host134 data]$ vi postgresql.conf

listen_addresses='*'

port=5432

max_connections=1000

####内存相关#########

work_mem=10MB

maintenance_work_mem=2GB

max_locks_per_transaction=1024

max_wal_size=64GB

checkpoint_timeout=30min

checkpoint_completion_target=0.9

shared_buffers=RAM*0.25GB #RAM指内存,最大设置为64GB

effective_cache_size=RAM*0.5GB #RAM指内存(free -g)

##归档部分

wal_level=replica

archive_mode=on

archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg13/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f'

######慢查询部分#####################

logging_collector=on

log_directory = '/opt/pg13/log' ##指定具体目录,否则会默认在pgdata目录下创建log目录

log_destination='stderr'

log_min_duration_statement=1000 ##超过1秒的慢查询都会记录

log_filename = 'postgresql-%Y-%m-%d.log'

log_truncate_on_rotation = off ##是否覆盖

log_rotation_age = 1d ##每天生成

log_rotation_size = 10MB ##每个日志大小

#log_statement = ‘all’ 和 log_min_duration_statement = 5000

#根据需要两者设置其一即可

#如果两个都进行了配置默认所有SQL全部打印，log_min_duration_statement设置失效

##log_statement = all #需设置跟踪所有语句，否则只能跟踪出错信息log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'

wal_log_hints=on

full_page_writes=on

需要创建归档目录

[postgres@host134 data]$ mkdir -p /opt/pg13/archivelog
7.启动数据库
[postgres@host134 bin]$ cd /opt/pg13/bin
[postgres@localhost bin]$ ./pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start

8.登陆使用

[postgres@host134 bin]$cd /opt/pg13/bin

[postgres@host134 bin]$ ./psql -h localhost -U postgres -p 5432

psql (13.8)

Type "help" for help.

postgres=# \du

List of roles

Role name | Attributes | Member of -----------+------------------------------------------------------------+-----------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

9.设置环境变量

su - postgres
#[postgres@localhost ~]$ vi .bash_profile

[postgres@localhost ~]$ vi ~/.bash_profile

# .bash_profile

# Get the aliases and functionsif [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/pg13/bin

export PATH

使环境变量生效

[postgres@localhost ~]$ source ~/.bash_profile

参考网站：

https://blog.youkuaiyun.com/weixin_49756466/article/details/122267766

http://www.taodudu.cc/news/show-1310084.html?action=onClick

https://www.cnblogs.com/hxlasky/p/16846972.html

10、数据库安装

主库、从库1、从库2、witness安装至初始完成

11、docker安装--创建自定义ip（注意不要有冲突）

[root@localhost ~]#yum list installed | grep docker#查看是否已安装docker列表

[root@localhost ~]#yum -y install docker#安装

[root@localhost ~]#systemctl start docker#启动

[root@localhost ~]#systemctl enable docker#开机启动

[root@localhost ~]#systemctl status docker#查看状态

[root@localhost ~]#curl -L https://github.com/docker/compose/releases/download/1.21.2/docker-compose-`uname -s`-`uname -m` -o /usr/local/bin/docker-compose#.安装docker-compose插件，用于docker容器里面部署应用

[root@localhost ~]#chmod +x /usr/local/bin/docker-compose#给docker-compose执行权限，运行命令

[root@localhost ~]#docker-compose version#查看版本

[root@localhost ~]#docker network create --subnet=192.168.120.0/24 pg-network #创建自定义ip

[root@localhost ~]#docker network list #查看网络

[root@localhost ~]#docker network rm pg-network #删除网络pg-network

12、repmgr安装--参考数据库安装

[root@localhost ~]#tar -xvf repmgr-5.2.1.tar.gz #将下载好的安装在soft里面解压

[root@localhost ~]#cd repmgr-5.2.1#进入解压后的文件

[root@localhost ~]#mkdir -p /opt/repmgr#指定安装目录（没有用该命令）

[root@localhost ~]#yum -y install flex

[root@localhost ~]#./configure --prefix=/opt/pg13/

[root@localhost ~]#make

[root@localhost ~]#make install

[root@localhost ~]#repmgr --version#查看版本

13、建立节点直接通信

每个节点执行以下命令

ssh-keygen -t rsa

ssh-copy-id -i postgres@192.168.88.158

ssh-copy-id -i postgres@192.168.88.154

ssh-copy-id -i postgres@192.168.88.159

ssh-copy-id -i postgres@192.168.88.156

ssh 192.168.88.158 date

ssh 192.168.88.154 date

ssh 192.168.88.159 date

ssh 192.168.88.156 date

14、配置repmgr

主库修改pg_hba.conf参数文件

[root@localhost ~]# su postgres

[postgres@localhost root]$ cat >> /opt/pg13/data/pg_hba.conf <<"EOF"

local repmgr repmgr md5

host repmgr repmgr 127.0.0.1/32 md5

host repmgr repmgr 192.168.88.0/24 md5

local replication repmgr md5

host replication repmgr 127.0.0.1/32 md5

host replication repmgr 192.168.88.0/24 md5

EOF

从库1修改pg_hba.conf参数文件

[root@localhost ~]# su postgres

[postgres@localhost root]$ cat >> /opt/pg13/data/pg_hba.conf <<"EOF"

local repmgr repmgr md5

host repmgr repmgr 127.0.0.1/32 md5

host repmgr repmgr 192.168.88.0/24 md5

local replication repmgr md5

host replication repmgr 127.0.0.1/32 md5

host replication repmgr 192.168.88.0/24 md5

EOF

主库修改postgresql.conf参数文件

[postgres@localhost pg13]$ cat >> /opt/pg13/data/postgresql.conf <<"EOF"

# 归档参数

wal_level='replica'

archive_mode='on'

archive_command='test ! -f /opt/pg13/archivelog/%f && cp %p /opt/pg13/archivelog/%f'

restore_command='cp /opt/pg13/archivelog/%f %p'

# 主从流复制

hot_standby=on

max_wal_senders=10

wal_sender_timeout=60s

wal_keep_size=16MB

# 主从切换参数，启用PG数据库的复制槽，PG12不需要"use_replication_slots=true"这个参数了。

max_replication_slots=10

wal_log_hints=on

# 自动切换

shared_preload_libraries ='repmgr'

EOF

[postgres@localhost bin]$$cd /opt/pg13/bin

[postgres@localhost bin]$$ ./psql -h localhost -U postgres -p 5432

postgres=#select * from pg_settings where name in ('wal_level','archive_mode','archive_command');

-- 切换归档

postgres=#select pg_switch_wal();

15、主库创建相关用户和数据库（4个节点创建）

[root@localhost ~]# su postgres

[postgres@localhost bin]$ createuser -s repmgr

[postgres@localhost bin]$ createdb repmgr -O repmgr

[postgres@localhost bin]$ psql -h 127.0.0.1 -c "alter user repmgr with password '2023@Q@';"

ALTER ROLE

[postgres@localhost bin]$ psql -h 127.0.0.1 -c "alter user repmgr set search_path to repmgr, \"\$user\",public;"

ALTER ROLE

[postgres@localhost bin]$ ./psql -h localhost -U postgres -p 5432

postgres=# \du

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------------------+-----------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

repmgr | Superuser, Create role, Create DB | {}

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(4 rows)

16、4个节点分别修改repmgr.conf

4个节点执行以下命令

[root@localhost ~]# su postgres

-- 主节点

cat > /opt/pg13/repmgr.conf << "EOF"

node_id=1

node_name=wcbrepmgr64361

conninfo='host=192.168.88.158 user=repmgr password=2023@Q@ dbname=repmgr connect_timeout=2'

data_directory='/opt/pg13/data'

pg_bindir='/opt/pg13/bin'

EOF

-- 从库1

cat > /opt/pg13/repmgr.conf << "EOF"

node_id=2

node_name=wcbrepmgr64362

conninfo='host=192.168.88.154 user=repmgr password=2023@Q@ dbname=repmgr connect_timeout=2'

data_directory='/opt/pg13/data'

pg_bindir='/opt/pg13/bin'

EOF

-- 从库2

cat > /opt/pg13/repmgr.conf << "EOF"

node_id=3

node_name=wcbrepmgr64363

conninfo='host=192.168.88.159 user=repmgr password=2023@Q@ dbname=repmgr connect_timeout=2'

data_directory='/opt/pg13/data'

pg_bindir='/opt/pg13/bin'

EOF

-- witness节点

cat > /opt/pg13/repmgr.conf << "EOF"

node_id=4

node_name=wcbrepmgr64364

conninfo='host=192.168.88.128 user=repmgr password=2023@Q@ dbname=repmgr connect_timeout=2'

data_directory='/opt/pg13/data'

pg_bindir='/opt/pg13/bin'

EOF

各个节点开放端口

打开主节点5432端口

firewall-cmd --permanent --zone=public --add-port=5432/tcp

firewall-cmd --state

firewall-cmd --reload

-- 测试

psql 'host=192.168.88.158 user=repmgr password=2023@Q@ dbname=repmgr connect_timeout=2'

psql 'host=192.168.88.154 user=repmgr password=2023@Q@ dbname=repmgr connect_timeout=2'

psql 'host=192.168.88.159 user=repmgr password=2023@Q@ dbname=repmgr connect_timeout=2'

psql 'host=192.168.88.128 user=repmgr password=2023@Q@ dbname=repmgr connect_timeout=2'

17、在主库注册主库服务

[postgres@localhost bin]$ repmgr -f /opt/pg13/repmgr.conf 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

-- 查询

[postgres@localhost bin]$ repmgr -f /opt/pg13/repmgr.conf cluster show

----+----------------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------------------------

[postgres@localhost bin]$ psql -U repmgr -h 192.168.88.158 -p 5432 -d repmgr

Password for user repmgr:

psql (13.8)

Type "help" for help.

repmgr=#

repmgr=# select * from repmgr.nodes;

---------+------------------+--------+----------------+---------+----------+----------+----------------------------------------------------------------------------------+----------+-----------+----------------------

(1 row)

18、克隆备库1和备库2

4个节点都配~/.pgpass密码文件

[root@localhost pg13]# pwd

/opt/pg13

[root@localhost pg13]# echo "#ip:port:db:user:pwd" >> ~/.pgpass

[root@localhost pg13]# echo "192.168.88.158:5432:repmgr:repmgr:2023@Q@" >> ~/.pgpass

[root@localhost pg13]# echo "192.168.88.154:5432:repmgr:repmgr:2023@Q@" >> ~/.pgpass

[root@localhost pg13]# echo "192.168.88.159:5432:repmgr:repmgr:2023@Q@" >> ~/.pgpass

[root@localhost pg13]# echo "192.168.88.128:5432:repmgr:repmgr:2023@Q@" >> ~/.pgpass

[root@localhost pg13]# chmod 0600 ~/.pgpass

-- 测试，若不需要密码，那就证明配置正确

[root@localhost pg13]# psql 'host=192.168.88.158 user=repmgr dbname=repmgr connect_timeout=2'

psql (13.8)

Type "help" for help.

repmgr=#

[root@localhost pg13]# psql 'host=192.168.88.154 user=repmgr dbname=repmgr connect_timeout=2'

psql (13.8)

Type "help" for help.

repmgr=#

[root@localhost pg13]# psql 'host=192.168.88.159 user=repmgr dbname=repmgr connect_timeout=2'

psql (13.8)

Type "help" for help.

repmgr=#

[root@localhost pg13]# psql 'host=192.168.88.156 user=repmgr dbname=repmgr connect_timeout=2'

psql (13.8)

Type "help" for help.

repmgr=#

克隆备库1

-- 其中--dry-run表示不真实执行克隆过程，只是检查是否有问题

[postgres@localhost bin]$ repmgr -h 192.168.88.158 -U repmgr -d repmgr -f /opt/pg13/repmgr.conf standby clone --force --dry-run

NOTICE: destination directory "/opt/pg13/data" provided

ERROR: specified data directory "/opt/pg13/data" appears to contain a running PostgreSQL instance

HINT: ensure the target data directory does not contain a running PostgreSQL instance

[postgres@localhost data]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop -m fast

waiting for server to shut down.... done

server stopped

[postgres@localhost /]$ repmgr -h 192.168.88.158 -U repmgr -d repmgr -f /opt/pg13/repmgr.conf standby clone --force --dry-run

NOTICE: destination directory "/opt/pg13/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=192.168.88.158 user=repmgr dbname=repmgr

DETAIL: current installation size is 31 MB

INFO: "repmgr" extension is installed in database "repmgr"

WARNING: target data directory appears to be a PostgreSQL data directory and will be overwritten

DETAIL: target data directory is "/opt/pg13/data"

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

NOTICE: standby will attach to upstream node 1

HINT: consider using the -c/--fast-checkpoint option

INFO: all prerequisites for "standby clone" are met

[postgres@localhost /]$ repmgr -h 192.168.88.158 -U repmgr -d repmgr -f /opt/pg13/repmgr.conf standby clone

NOTICE: destination directory "/opt/pg13/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=192.168.88.158 user=repmgr dbname=repmgr

DETAIL: current installation size is 31 MB

ERROR: target data directory appears to be a PostgreSQL data directory

DETAIL: target data directory is "/opt/pg13/data"

HINT: use -F/--force to overwrite the existing data directory

[postgres@localhost /]$ repmgr -h 192.168.88.158 -U repmgr -d repmgr -f /opt/pg13/repmgr.conf standby clone --force

NOTICE: destination directory "/opt/pg13/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=192.168.88.158 user=repmgr dbname=repmgr

DETAIL: current installation size is 31 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: directory "/opt/pg13/data" exists but is not empty

NOTICE: -F/--force provided - deleting existing data directory "/opt/pg13/data"

NOTICE: starting backup (using pg_basebackup)...

HINT: this may take some time; consider using the -c/--fast-checkpoint option

INFO: executing:

/opt/pg13/bin/pg_basebackup -l "repmgr base backup" -D /opt/pg13/data -h 192.168.88.158 -p 5432 -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 /opt/pg13/data start

HINT: after starting the server, you need to register this standby with "repmgr standby register"

[postgres@localhost /]$ su postgres

Password:

[postgres@localhost /]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start

waiting for server to start.... done

server started

[postgres@localhost /]$ psql

psql (13.8)

Type "help" for help.

postgres=# select * from pg_stat_wal_receiver;

slot_name | sender_host | sender_port | conninfo

------+-----------+-------------------+-------------------+-------------+-------------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------+

-----------+----------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------

3826 | streaming | 0/7000000 | 1 | 0/7000280 | 0/7000280 | 1 | 2023-06-15 11:45:47.147227+08 | 2023-06-15 11:45:47.041923+08 | 0/7000280 | 2023-06-15 11:45:17.072334+08 |

| 192.168.88.158 | 5432 | user=repmgr password=******** channel_binding=disable connect_timeout=2 dbname=replication host=192.168.88.158 port=5432 application_name=wcbrepmgr64362 fallback_applicati

on_name=walreceiver sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any

(1 row)

[postgres@localhost /]$ repmgr -f /opt/pg13/repmgr.conf standby register --force

INFO: connecting to local node "wcbrepmgr64362" (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 "wcbrepmgr64362" (ID: 2) successfully registered

[postgres@localhost /]$ repmgr -f /opt/pg13/repmgr.conf cluster show

----+----------------+---------+-----------+----------------+----------+----------+----------+----------------------------------------------------------------------------------

克隆备库2

-- 其中--dry-run表示不真实执行克隆过程，只是检查是否有问题

[postgres@localhost bin]$ repmgr -h 192.168.88.158 -U repmgr -d repmgr -f /opt/pg13/repmgr.conf standby clone --force --dry-run

NOTICE: destination directory "/opt/pg13/data" provided

ERROR: specified data directory "/opt/pg13/data" appears to contain a running PostgreSQL instance

HINT: ensure the target data directory does not contain a running PostgreSQL instance

[postgres@localhost data]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop -m fast

waiting for server to shut down.... done

server stopped

[postgres@localhost /]$ repmgr -h 192.168.88.158 -U repmgr -d repmgr -f /opt/pg13/repmgr.conf standby clone --force --dry-run

NOTICE: destination directory "/opt/pg13/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=192.168.88.158 user=repmgr dbname=repmgr

DETAIL: current installation size is 31 MB

INFO: "repmgr" extension is installed in database "repmgr"

WARNING: target data directory appears to be a PostgreSQL data directory and will be overwritten

DETAIL: target data directory is "/opt/pg13/data"

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

NOTICE: standby will attach to upstream node 1

HINT: consider using the -c/--fast-checkpoint option

INFO: all prerequisites for "standby clone" are met

[postgres@localhost /]$ repmgr -h 192.168.88.158 -U repmgr -d repmgr -f /opt/pg13/repmgr.conf standby clone

NOTICE: destination directory "/opt/pg13/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=192.168.88.158 user=repmgr dbname=repmgr

DETAIL: current installation size is 31 MB

ERROR: target data directory appears to be a PostgreSQL data directory

DETAIL: target data directory is "/opt/pg13/data"

HINT: use -F/--force to overwrite the existing data directory

[postgres@localhost /]$ repmgr -h 192.168.88.158 -U repmgr -d repmgr -f /opt/pg13/repmgr.conf standby clone --force

NOTICE: destination directory "/opt/pg13/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=192.168.88.158 user=repmgr dbname=repmgr

DETAIL: current installation size is 31 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: directory "/opt/pg13/data" exists but is not empty

NOTICE: -F/--force provided - deleting existing data directory "/opt/pg13/data"

NOTICE: starting backup (using pg_basebackup)...

HINT: this may take some time; consider using the -c/--fast-checkpoint option

INFO: executing:

/opt/pg13/bin/pg_basebackup -l "repmgr base backup" -D /opt/pg13/data -h 192.168.88.158 -p 5432 -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 /opt/pg13/data start

HINT: after starting the server, you need to register this standby with "repmgr standby register"

[postgres@localhost /]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start

waiting for server to start.... done

server started

[postgres@localhost /]$ psql

psql (13.8)

Type "help" for help.

postgres=# select * from pg_stat_wal_receiver;

slot_name | sender_host | sender_port | conninfo

------------------------------------------------------------------------------------------------------------------------------------------------------

3214 | streaming | 0/9000000 | 1 | 0/9000318 | 0/9000318 | 1 | 2023-06-15 11:51:31.388155+08 | 2023-06-15 11:51:30.939989+08 | 0/9000318 | 2023-06-15 11:51:31.388155+08 |

| 192.168.88.158 | 5432 | user=repmgr password=******** channel_binding=disable connect_timeout=2 dbname=replication host=192.168.88.158 port=5432 application_name=wcbrepmgr64363 fallback_applicati

on_name=walreceiver sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any

(1 row)

[postgres@localhost bin]$ repmgr -f /opt/pg13/repmgr.conf standby register --force

INFO: connecting to local node "wcbrepmgr64363" (ID: 3)

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 "wcbrepmgr64363" (ID: 3) successfully registered

[postgres@localhost bin]$ repmgr -f /opt/pg13/repmgr.conf cluster show

----+----------------+---------+-----------+----------------+----------+----------+----------+----------------------------------------------------------------------------------

19、配置 witness

[postgres@localhost data]$ rm -rf /opt/pg13/data

[postgres@localhost pg13]$ cd bin/

[postgres@localhost bin]$ ./initdb -D /opt/pg13/data/

The files belonging to this database system will be owned by user "postgres".

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 disabled.

creating directory /opt/pg13/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 ... Asia/Shanghai

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 /opt/pg13/data/ -l logfile start

cat >> /opt/pg13/data/postgresql.conf <<"EOF"

listen_addresses = '*'

port=5432

unix_socket_directories='/opt/pg13/data'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%a.log'

log_truncate_on_rotation = on

# 归档参数

wal_level='replica'

archive_mode='on'

archive_command='test ! -f /opt/pg13/archivelog/%f && cp %p /opt/pg13/archivelog/%f'

restore_command='cp /opt/pg13/archivelog/%f %p'

# 主从流复制

hot_standby=on

max_wal_senders=10

wal_sender_timeout=60s

wal_keep_size=16MB

# 主从切换参数，启用PG数据库的复制槽，PG12不需要"use_replication_slots=true"这个参数了。

max_replication_slots=10

wal_log_hints=on

# 自动切换

shared_preload_libraries ='repmgr'

EOF

#pg_hba.conf 添加

host all all 0.0.0.0/0 md5

host replication all 0.0.0.0/0 md5

local repmgr repmgr md5

host repmgr repmgr 127.0.0.1/32 md5

host repmgr repmgr 192.168.88.0/24 md5

local replication repmgr md5

host replication repmgr 127.0.0.1/32 md5

host replication repmgr 192.168.88.0/24 md5

#以下命令实践会有坑，建议使用上面方式

cat >> /opt/pg13/data/pg_hba.conf <<"EOF"

# TYPE DATABASE USER ADDRESS METHOD

local all all trust

host all all 127.0.0.1/32 trust

host all all 0.0.0.0/0 md5

host replication all 0.0.0.0/0 md5

local repmgr repmgr md5

host repmgr repmgr 127.0.0.1/32 md5

host repmgr repmgr 192.168.88.0/24 md5

local replication repmgr md5

host replication repmgr 127.0.0.1/32 md5

host replication repmgr 192.168.88.0/24 md5

EOF

[postgres@localhost pg13]$ /opt/pg13/bin/pg_ctl -D /opt/pg13/data -l logfile start

waiting for server to start.... done

server started

[postgres@localhost bin]$ repmgr -f /opt/pg13/repmgr.conf witness register -h 192.168.88.158 -U repmgr -d repmgr --force

INFO: connecting to witness node "wcbrepmgr64364" (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 "wcbrepmgr64364" (ID: 4) successfully registered

[postgres@localhost bin]$ repmgr -f /opt/pg13/repmgr.conf cluster show

----+----------------+---------+-----------+----------------+----------+----------+----------+----------------------------------------------------------------------------------

各个节点执行以下命令 in production 代表主；in archive recovery代表从库

[postgres@localhost bin]$ pg_controldata /opt/pg13/data/| grep 'Database cluster state'

Database cluster state: in production

[postgres@localhost bin]$ pg_controldata /opt/pg13/data/| grep 'Database cluster state'

Database cluster state: in archive recovery