Postgresql流复制管理工具repmgr安装及使用
安装数据库
tar -jxvf postgresql-12.6.tar.bz2
mkdir -p /opt/pgdata/pg_root
mkdir -p /opt/pgsql12
yum -y install coreutils glib2 lrzsz mpstat dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl-devel perl-Ext Utils* openldap-devel jadetex openjade bzip2 nc mutt perl-ExtUtils-Embed
cd postgresql-12.6
./configure --prefix=/opt/pgsql12 --with-pgport=1921 --with-segsize=8 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety
gmake world && gmake install-world
chown -R postgres.postgres /opt/pgsql12/
chown -R postgres.postgres /opt/pgdata/pg_root/
安装repmgr和pglogic
wget https://repmgr.org/download/repmgr-5.2.1.tar.gz
tar -zxvf repmgr-5.2.1.tar.gz
cd repmgr-5.2.1
./configure --prefix=/opt/pgsql12/
make && make install
wget https://github.com/2ndQuadrant/pglogical/archive/REL2_x_STABLE.zip
unzip REL2_x_STABLE.zip
cd pglogical-REL2_x_STABLE/
USE_PGXS=1 make clean
USE_PGXS=1 make
USE_PGXS=1 make install
初始化数据库
initdb -D /opt/pgdata/pg_root/
修改相关参数
# Enable replication connections; set this value to at least one more
# than the number of standbys which will connect to this server
# (note that repmgr will execute "pg_basebackup" in WAL streaming mode,
# which requires two free WAL senders).
#
# See: https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-WAL-SENDERS
max_wal_senders = 10
# If using replication slots, set this value to at least one more
# than the number of standbys which will connect to this server.
# Note that repmgr will only make use of replication slots if
# "use_replication_slots" is set to "true" in "repmgr.conf".
# (If you are not intending to use replication slots, this value
# can be set to "0").
#
# See: https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS
max_replication_slots = 10
# Ensure WAL files contain enough information to enable read-only queries
# on the standby.
#
# PostgreSQL 9.5 and earlier: one of 'hot_standby' or 'logical'
# PostgreSQL 9.6 and later: one of 'replica' or 'logical'
# ('hot_standby' will still be accepted as an alias for 'replica')
#
# See: https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LEVEL
wal_level = 'hot_standby'
# Enable read-only queries on a standby
# (Note: this will be ignored on a primary but we recommend including
# it anyway, in case the primary later becomes a standby)
#
# See: https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-HOT-STANDBY
hot_standby = on
# Enable WAL file archiving
#
# See: https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-MODE
archive_mode = on
# Set archive command to a dummy command; this can later be changed without
# needing to restart the PostgreSQL instance.
#
# See: https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-COMMAND
archive_command = '/bin/true'
主节点建用户和数据库,配置pg_hba.conf
createuser -s repmgr
createdb repmgr -O repmgr
#修改pg_hba.conf
local replication repmgr trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 0.0.0.0/0 trust
local repmgr repmgr trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 0.0.0.0/0 trust
pg_ctl reload
配置文件并在主节点注册
cat /home/postgres/repmgr/repmgr5.2.1.conf
node_id=1
node_name='db1'
conninfo='host=db1 user=repmgr dbname=repmgr port=1921 connect_timeout=2'
data_directory='/opt/pgdata/pg_root'
pg_bindir='/opt/pgsql12/bin'
use_replication_slots=true
failover=automatic
monitoring_history=yes
reconnect_interval=5
reconnect_attempts=3
log_file='/home/postgres/repmgr/repmgr_melot.log'
#主节点注册
repmgr -f repmgr5.2.1.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
查看主节点状态
#节点状态正常
repmgr -f repmgr5.2.1.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------
1 | db1 | primary | * running | | default | 100 | 1 | host=db1 user=repmgr dbname=repmgr port=1921 connect_timeout=2
#查看数据字典
repmgr=# \x
Expanded display is on.
repmgr=# SELECT * FROM repmgr.nodes;
-[ RECORD 1 ]----+---------------------------------------------------------------
node_id | 1
upstream_node_id |
active | t
node_name | db1
type | primary
location | default
priority | 100
conninfo | host=db1 user=repmgr dbname=repmgr port=1921 connect_timeout=2
repluser | repmgr
slot_name | repmgr_slot_1
config_file | /home/postgres/repmgr/repmgr5.2.1.conf
备节点配置文件
cat /home/postgres/repmgr/repmgr5.2.1.conf
node_id=2
node_name='db2'
conninfo='host=db2 user=repmgr dbname=repmgr port=1921 connect_timeout=2'
data_directory='/opt/pgdata/pg_root'
pg_bindir='/opt/pgsql12/bin'
log_file='/home/postgres/repmgr/repmgr_melot.log'
use_replication_slots=true
failover=automatic
monitoring_history=yes
reconnect_interval=5
reconnect_attempts=3
检查备节点是否满足条件，并执行克隆命令
#检查
repmgr -h db1 -U repmgr -d repmgr -f /home/postgres/repmgr/repmgr5.2.1.conf standby clone --dry-run
#执行克隆命令，其实还是调用pg_basebackup
repmgr -h db1 -U repmgr -d repmgr -f /home/postgres/repmgr/repmgr5.2.1.conf standby clone
启动数据库并注册备节点
pg_ctl -D /opt/pgdata/pg_root start
waiting for server to start....2021-03-16 16:14:51.972 CST [1436] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-03-16 16:14:51.972 CST [1436] LOG: listening on IPv4 address "0.0.0.0", port 1921
2021-03-16 16:14:51.972 CST [1436] LOG: listening on IPv6 address "::", port 1921
2021-03-16 16:14:51.977 CST [1436] LOG: listening on Unix socket "/tmp/.s.PGSQL.1921"
2021-03-16 16:14:52.029 CST [1436] LOG: redirecting log output to logging collector process
2021-03-16 16:14:52.029 CST [1436] HINT: Future log output will appear in directory "log".
done
server started
repmgr -f /home/postgres/repmgr/repmgr5.2.1.conf standby register
INFO: connecting to local node "db2" (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 "db2" (ID: 2) successfully registered
查看备节点状态
repmgr -f /home/postgres/repmgr/repmgr5.2.1.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------
1 | db1 | primary | * running | | default | 100 | 1 | host=db1 user=repmgr dbname=repmgr port=1921 connect_timeout=2
2 | db2 | standby | running | db1 | default | 100 | 1 | host=db2 user=repmgr dbname=repmgr port=1921 connect_timeout=2
postgres=# SELECT * FROM pg_stat_wal_receiver;
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 1442
status | streaming
receive_start_lsn | 0/3000000
receive_start_tli | 1
received_lsn | 0/3000948
received_tli | 1
last_msg_send_time | 2021-03-16 16:16:23.069495+08
last_msg_receipt_time | 2021-03-16 16:16:23.069284+08
latest_end_lsn | 0/3000948
latest_end_time | 2021-03-16 16:15:53.019783+08
slot_name | repmgr_slot_2
sender_host | db1
sender_port | 1921
conninfo | user=repmgr passfile=/home/postgres/.pgpass connect_timeout=2 dbname=replication host=db1 port=1921 application_name=db2 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any