pg高可用方案repmgr带witness搭建

一、总体架构

操作系统版本： linux redhat7.6
pg版本： 12.2
repmgr版本 5.2
192.168.3.73 主库: repmgr+master
192.168.3.74 从库1: repmgr+standby
192.168.3.75 从库2: repmgr+standby
192.168.3.76 witness: repmgr+witness

所有的节点安装操作系统 ,创建用户目录 ,安装pg,安装repmgr 只初始化主库

二、环境准备
2.1 安装主机环境：略
2.2 配置主机互信

echo "192.168.3.73 pg73" >> /etc/hosts
echo "192.168.3.74 pg74" >> /etc/hosts
echo "192.168.3.75 pg75" >> /etc/hosts
echo "192.168.3.76 pg76" >> /etc/hosts

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
//将主机pg74和pg75的公钥取到本地authorized_keys文件中
ssh pg74 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh pg75 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
//分发公钥
scp ~/.ssh/authorized_keys pg74:~/.ssh/
scp ~/.ssh/authorized_keys pg75:~/.ssh/
4.三台机器分别执行 更改权限
chmod 644 ~/.ssh/authorized_keys
chmod 700 ~/.ssh
5.验证
ssh pg73 date
ssh pg74 date
ssh pg75 date

2.3配置yum

yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel perl python36 tcl openssl ncurses-devel openldap pam
yum -y groupinstall "Development Tools"
yum -y install yum-utils openjade docbook-dtds docbook-style-dsssl docbook-style-xsl

2.4 初始化主库（*注与后面witness初始化无关）

su - postgres
cd /postgresql/soft
tar zxvf postgresql-12.2.tar.gz
cd postgresql-12.2
./configure --prefix=/postgresql/pg12 --without-readline
gmake && gmake install
/postgresql/pg12/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres

三、配置repmgr
3.1 下载repmgr
https://repmgr.org/
3.2 安装repmgr

scp repmgr-5.2.0.tar.gz pg74:/postgresql/soft/
scp repmgr-5.2.0.tar.gz pg75:/postgresql/soft/
scp repmgr-5.2.0.tar.gz pg76:/postgresql/soft/

[postgres@pg73 soft]$ tar -zxvf repmgr-5.2.0.tar.gz

[postgres@pg73 soft]$ cd repmgr-5.2.0

[postgres@pg73 repmgr-5.2.0]$ ./configure
checking for a sed that does not truncate output... /bin/sed
checking for pg_config... /postgresql/pg12/bin/pg_config
configure: building against PostgreSQL 12.2
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

[postgres@pg75 repmgr-5.2.0]$ make && make install
Building against PostgreSQL 12
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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/lib -Wl,--as-needed -Wl,-rpath,'/postgresql/pg12/lib',--enable-new-dtags -L/postgresql/pg12/lib -lpq
sed -E 's/REPMGR_VERSION_DATE.*""/REPMGR_VERSION_DATE "2022-03-01"/' repmgr_version.h.in >repmgr_version.h;
/bin/install -c -m 644 .//repmgr--unpackaged--4.0.sql .//repmgr--unpackaged--5.1.sql .//repmgr--unpackaged--5.2.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 '/postgresql/pg12/share/extension/'
/bin/install -c -m 755 repmgr repmgrd '/postgresql/pg12/bin/'

[postgres@pg74 repmgr-5.2.0]$

repmgr --help
能出来帮助就安装成功了

3.3 配置repmgr
主库创建相关用户和数据库

su - postgres
pg_ctl start
createuser -s repmgr
createdb repmgr -O repmgr
psql
repmgr=# alter user repmgr with password 'repmgr';
repmgr=# \l repmgr
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------+--------+----------+------------+------------+-------------------
repmgr | repmgr | UTF8 | en_US.utf8 | en_US.utf8 |
(1 row)

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

3.4 主库修改pg_hba.conf参数文件

su - postgres
vi $PGDATA/pg_hba.conf

local repmgr repmgr md5
host repmgr repmgr 127.0.0.1/32 md5
host repmgr repmgr 192.168.3.0/24 md5

local replication repmgr md5
host replication repmgr 127.0.0.1/32 md5
host replication repmgr 192.168.3.0/24 md5

3.5 主库修改postgresql.conf参数文件

-- 修改参数
cat >> /postgresql/pgdata/postgresql.conf <<'EOF'

# 归档参数
wal_level='replica'
archive_mode='on'
archive_command='test ! -f /pgpostgresql/archive/%f && cp %p /postgresql/archive/%f'
restore_command='cp /postgresql/archive/%f %p'

# 主从流复制
hot_standby=on
max_wal_senders=10
wal_sender_timeout=60s
wal_keep_size=16MB
EOF

-- 重启
pg_ctl restart

3.6 参数配置

-- 以postgres用户修改
su - postgres

-- 主库
cd /postgresql/pg12
vi repmgr.conf
node_id=1
node_name=pg73
conninfo='host=192.168.3.73 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
data_directory='/postgresql/pgdata'
pg_bindir='/postgresql/pg12/bin'

-- 从库1
cd /postgresql/pg12
vi repmgr.conf
node_id=2
node_name=pg74
conninfo='host=192.168.3.74 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
data_directory='/postgresql/pgdata'
pg_bindir='/postgresql/pg12/bin'

-- 从库2
cd /postgresql/pg12
vi repmgr.conf
node_id=3
node_name=pg75
conninfo='host=192.168.3.75 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
data_directory='/postgresql/pgdata'
pg_bindir='/postgresql/pg12/bin'


-- witness节点 后续在配置也行 ，先把主从先配好
cd /postgresql/pg12
vi repmgr.conf
node_id=1
node_name=pg73
conninfo='host=192.168.3.76 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
data_directory='/postgresql/pgdata'
pg_bindir='/postgresql/pg12/bin'

3.7 主库注册服务

[postgres@pg73 pg12]$ repmgr -f /postgresql/pg12/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@pg73 pg12]$ repmgr -f /postgresql/pg12/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------------------------
1 | pg73 | primary | * running | | default | 100 | 1 | host=192.168.3.73 user=repmgr password=repmgr dbname=repmgr connect_timeout=2

[postgres@pg73 pg12]$ psql
psql (12.2)
Type "help" for help.

postgres=# \c repmgr repmgr
You are now connected to database "repmgr" as user "repmgr".
repmgr=# select * from repmgr.nodes;
node_id | upstream_node_id | active | node_name | type | location | priority | conninfo | repluser | slot_name | config_file

---------+------------------+--------+-----------+---------+----------+----------+-------------------------------------------------------------------------------+----------+-----------+----------------------
--------
1 | | t | pg73 | primary | default | 100 | host=192.168.3.73 user=repmgr password=repmgr dbname=repmgr connect_timeout=2 | repmgr | | /postgresql/pg12/repm
gr.conf
(1 row)

3.8 克隆备库

3.9 所有节点都配~/.pgpass密码文件

# 例子 echo "#ip:port:db:user:pwd" >> ~/.pgpass
su - postgres
echo "192.168.3.73:5432:repmgr:repmgr:repmgr" >> ~/.pgpass
echo "192.168.3.74:5432:repmgr:repmgr:repmgr" >> ~/.pgpass
echo "192.168.3.75:5432:repmgr:repmgr:repmgr" >> ~/.pgpass
chmod 0600 ~/.pgpass

3.10 注册从库服务（从库1）

[postgres@pg74 pg12]$ repmgr -h 192.168.3.73 -U repmgr -d repmgr -f /postgresql/pg12/repmgr.conf standby clone --dry-run
NOTICE: destination directory "/postgresql/pgdata" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.3.73 user=repmgr dbname=repmgr
DETAIL: current installation size is 31 MB
INFO: "repmgr" extension is installed in database "repmgr"
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: all prerequisites for "standby clone" are met

#执行 repmgr -h 192.168.3.73 -U repmgr -d repmgr -f /postgresql/pg12/repmgr.conf standby clone

[postgres@pg74 pg12]$ repmgr -h 192.168.3.73 -U repmgr -d repmgr -f /postgresql/pg12/repmgr.conf standby clone
NOTICE: destination directory "/postgresql/pgdata" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.3.73 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: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
INFO: checking and correcting permissions on existing directory "/postgresql/pgdata"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
/postgresql/pg12/bin/pg_basebackup -l "repmgr base backup" -D /postgresql/pgdata -h 192.168.3.73 -p 5432 -U repmgr -X stream
Password:
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /postgresql/pgdata start <<<<<<<<<<<<<<<<<
HINT: after starting the server, you need to register this standby with "repmgr standby register" <<<<<<<<<<<<<<<<

#启动
[postgres@pg74 pg12]$ pg_ctl -D /postgresql/pgdata start
waiting for server to start....2022-03-01 21:19:21.724 CST [29599] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-03-01 21:19:21.725 CST [29599] LOG: listening on IPv4 address "0.0.0.0", port 5432
2022-03-01 21:19:21.739 CST [29599] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-03-01 21:19:22.112 CST [29599] LOG: redirecting log output to logging collector process
2022-03-01 21:19:22.112 CST [29599] HINT: Future log output will appear in directory "/postgresql/pgdata/pg_log".
. done
server started

#注册 repmgr -f /postgresql/pg12/repmgr.conf standby register

[postgres@pg74 pg12]$ repmgr -f /postgresql/pg12/repmgr.conf standby register
INFO: connecting to local node "pg74" (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 "pg74" (ID: 2) successfully registered

#检查 repmgr -f /postgresql/pg12/repmgr.conf cluster show

[postgres@pg74 pg12]$ repmgr -f /postgresql/pg12/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------------------------
1 | pg73 | primary | * running | | default | 100 | 1 | host=192.168.3.73 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
2 | pg74 | standby | running | pg73 | default | 100 | 1 | host=192.168.3.74 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
[postgres@pg74 pg12]$

[root@pg74 postgresql]# su - postgres
Last login: Tue Mar 1 15:41:31 CST 2022 on pts/0
[postgres@pg74 ~]$ psql
psql (12.2)
Type "help" for help.

postgres=# select * from pg_stat_wal_receiver;
pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time | slo
t_name | sender_host | sender_port | conninfo

-------+-----------+-------------------+-------------------+--------------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------+----
-------+--------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
29605 | streaming | 0/86000000 | 1 | 0/8A000060 | 1 | 2022-03-01 21:25:54.262099+08 | 2022-03-01 21:25:54.715279+08 | 0/8A000060 | 2022-03-01 21:22:53.878664+08 |
| 192.168.3.73 | 5432 | user=repmgr password=******** connect_timeout=2 dbname=replication host=192.168.3.73 port=5432 application_name=pg74 fallback_application_name=walreceiver sslmode=disabl
e sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any
(1 row)

3.11 注册从库服务（从库2）
[postgres@pg74 pg12]$ repmgr -h 192.168.3.73 -U repmgr -d repmgr -f /postgresql/pg12/repmgr.conf standby clone --dry-run

执行 repmgr -h 192.168.3.73 -U repmgr -d repmgr -f /postgresql/pg12/repmgr.conf standby clone

[postgres@pg75 postgresql]$ repmgr -h 192.168.3.73 -U repmgr -d repmgr -f /postgresql/pg12/repmgr.conf standby clone

[postgres@pg75 postgresql]$ repmgr -f /postgresql/pg12/repmgr.conf standby register
INFO: connecting to local node "pg75" (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 "pg75" (ID: 3) successfully registered

[root@pg74 postgresql]# su - postgres
Last login: Tue Mar 1 15:41:31 CST 2022 on pts/0
[postgres@pg74 ~]$ psql
psql (12.2)
Type "help" for help.

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

29605 | streaming | 0/86000000 | 1 | 0/8A000060 | 1 | 2022-03-01 21:25:54.262099+08 | 2022-03-01 21:25:54.715279+08 | 0/8A000060 | 2022-03-01 21:22:53.878664+08 |
| 192.168.3.73 | 5432 | user=repmgr password=******** connect_timeout=2 dbname=replication host=192.168.3.73 port=5432 application_name=pg74 fallback_application_name=walreceiver sslmode=disabl
e sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any
(1 row)

四、配置witness
4.1 修改repmgr.conf参数文件

cd /postgresql/pg12
vi repmgr.conf
node_id=4
node_name=pg76
conninfo='host=192.168.3.73 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
data_directory='/postgresql/pgdata'
pg_bindir='/postgresql/pg12/bin'

4.2 配置密码文件

# 例子 echo "#ip:port:db:user:pwd" >> ~/.pgpass
su - postgres
echo "192.168.3.73:5432:repmgr:repmgr:repmgr" >> ~/.pgpass
echo "192.168.3.74:5432:repmgr:repmgr:repmgr" >> ~/.pgpass
echo "192.168.3.75:5432:repmgr:repmgr:repmgr" >> ~/.pgpass
echo "192.168.3.76:5432:repmgr:repmgr:repmgr" >> ~/.pgpass
chmod 0600 ~/.pgpass

4.3 初始化数据库（*注这个初始化是repmgr的witness使用，用来存repmgr相关元数据）

# /postgresql/pg12/bin/initdb -D /postgresql/pgdata -E UTF8 --lc-collate=C --locale=en_US.utf8 -U postgres

[postgres@pg76 pg12]$ /postgresql/pg12/bin/initdb -D /postgresql/pgdata -E UTF8 --lc-collate=C --locale=en_US.utf8 -U postgres
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 locales
COLLATE: C
CTYPE: en_US.utf8
MESSAGES: en_US.utf8
MONETARY: en_US.utf8
NUMERIC: en_US.utf8
TIME: en_US.utf8
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /postgresql/pgdata ... 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:

/postgresql/pg12/bin/pg_ctl -D /postgresql/pgdata -l logfile start

4.4 从库1 scp pg_hba.conf postgresql.conf到witness 节点上
（注*生产这里不建议使用拷贝的方式，因为witness上配置与cluster没有直接关系，可以配置需要的参数即可，这个是自己的测试环境就先进行拷贝的方式）

postgres@pg76 pgdata]$ scp pg74:/postgresql/pgdata/pg_hba.conf .
The authenticity of host 'pg74 (192.168.3.74)' can't be established.
ECDSA key fingerprint is SHA256:jPtFDnb9chze1xKLufd71ZoE7y3aET7QcIlehcbmB48.
ECDSA key fingerprint is MD5:33:e3:c2:a9:12:3e:8a:6e:ae:e1:07:28:10:0f:bf:a6.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'pg74' (ECDSA) to the list of known hosts.
pg_hba.conf 100% 4797 4.9MB/s 00:00
[postgres@pg76 pgdata]$ scp pg74:/postgresql/pgdata/postgresql.conf .
postgresql.conf 100% 26KB 19.3MB/s 00:00

4.5 启动witness 数据库并创建用户

[postgres@pg76 pgdata]$ pg_ctl start
waiting for server to start....2022-03-01 22:24:30.426 CST [29857] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-03-01 22:24:30.426 CST [29857] LOG: listening on IPv4 address "0.0.0.0", port 5432
2022-03-01 22:24:30.447 CST [29857] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-03-01 22:24:30.458 CST [29857] LOG: redirecting log output to logging collector process
2022-03-01 22:24:30.458 CST [29857] HINT: Future log output will appear in directory "/postgresql/pgdata/pg_log".
done
server started

[postgres@pg76 ~]$ createuser -s repmgr
[postgres@pg76 ~]$ createdb repmgr -O repmgr
[postgres@pg76 ~]$ psql
psql (12.2)
Type "help" for help.

postgres=# alter user repmgr with password 'repmgr';
alter user repmgr set search_path to repmgr, '\$user',public;

4.6 安装witness 的repmgr

[postgres@pg76 soft]$ cd repmgr-5.2.0/
[postgres@pg76 repmgr-5.2.0]$ ./configure
checking for a sed that does not truncate output... /bin/sed
checking for pg_config... /postgresql/pg12/bin/pg_config
configure: building against PostgreSQL 12.2
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
[postgres@pg76 repmgr-5.2.0]$ make
Building against PostgreSQL 12
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 format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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 -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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/include/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/postgresql/pg12/include/internal -I/postgresql/pg12/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/postgresql/pg12/include/server -I/postgresql/pg12/

4.7注册witness

# repmgr -f /postgresql/pg12/repmgr.conf -h 192.168.3.73 -U repmgr -d repmgr witness register

[postgres@pg76 pg12]$ repmgr -f /postgresql/pg12/repmgr.conf -h 192.168.3.73 -U repmgr -d repmgr witness register
INFO: connecting to witness node "pg76" (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 "pg76" (ID: 4) successfully registered

4.8 所有节点查询都可以获取如下的结果

repmgr -f /postgresql/pg12/repmgr.conf cluster show

ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------------------------
1 | pg73 | primary | * running | | default | 100 | 1 | host=192.168.3.73 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
2 | pg74 | standby | running | pg73 | default | 100 | 1 | host=192.168.3.74 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
3 | pg75 | standby | running | pg73 | default | 100 | 1 | host=192.168.3.75 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
4 | pg76 | witness | * running | pg73 | default | 0 | n/a | host=192.168.3.76 user=repmgr password=repmgr dbname=repmgr connect_timeout=2

posted on 2024-02-19 19:44 欣欣闹天下阅读(302) 评论(1) 编辑收藏举报

刷新页面返回顶部

欣欣闹天下

导航

公告

pg高可用方案repmgr带witness搭建

执行 repmgr -h 192.168.3.73 -U repmgr -d repmgr -f /postgresql/pg12/repmgr.conf standby clone