PostgreSQL高可用之Repmgr两节点搭建及switchover操作

原创多米爸比 2020-02-05

7451

环境
虚拟机centos7.6
HA1:192.168.99.200
HA2:192.168.99.202
PostgreSQL 12.1
repmgr 5.0.0

1.源码编译安装pg12

$ su root
--创建系统用户和组
# groupadd postgres -g 1001
# useradd postgres -g 1001 -u 1001
# id postgres
uid=1001(postgres) gid=1001(postgres) groups=1001(postgres)

# cd /opt
--源文件解压目录
# mkdir -p /opt/postgresql-12.1
# chown postgres:postgres postgresql-12.1

--安装文件目录
# mkdir -p /opt/pg12
# chown -R postgres:postgres pg12
# chmod 0700 pg12
# ln -s /opt/pg12 /opt/pgsql

--数据目录
# mkdir -p /opt/data6000
# chown -R postgres:postgres data6000
# chmod 0700 data6000
 
--归档目录
# mkdir -p /opt/archive6000
# chown -R postgres:postgres archive6000
# chmod 0700 archive6000

# su postgres
$ wget https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.gz
$ chmod 750 postgresql-12.1.tar.gz
$ tar zxvf postgresql-12.1.tar.gz
$ cd postgresql-12.1/
--编译
$ ./configure --prefix=/opt/pg12 --with-pgport=6000
--安装
$ gmake world && gmake install-world

--配置开机自启动
$ su root
# cp /opt/postgresql-12.1/contrib/start-scripts/linux /etc/init.d/postgres-12
# chmod +x /etc/init.d/postgres-12
# chkconfig postgres-12 on
# vi /etc/init.d/postgres-12
#! /bin/sh

## EDIT FROM HERE
prefix=/opt/pgsql
PGDATA="/opt/data6000"
PGUSER=postgres
## STOP EDITING HERE

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
DAEMON="$prefix/bin/postmaster"
PGCTL="$prefix/bin/pg_ctl"

set -e

# Only start if we can find the postmaster.
test -x $DAEMON ||
{
	echo "$DAEMON not found"
	if [ "$1" = "stop" ]
	then exit 0
	else exit 5
	fi
}

# Parse command line parameters.
case $1 in
  start)
	echo -n "Starting PostgreSQL: "
        su - $PGUSER -c "$PGCTL -D '$PGDATA' start >>/dev/null &"	
echo "ok"
	;;
  stop)
	echo -n "Stopping PostgreSQL: "
	su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s"
	echo "ok"
	;;
  restart)
	echo -n "Restarting PostgreSQL: "
    su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s"
    su - $PGUSER -c "$PGCTL -D '$PGDATA' start >>/dev/null &"	
       echo "ok"
	;;
  reload)
	echo -n "Reload PostgreSQL: "
	su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"
	echo "ok"
	;;
  status)
	su - $PGUSER -c "$PGCTL status -D '$PGDATA'"
	;;
  *)
	# Print help
	echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
	exit 1
	;;
esac

exit 0

--删除源文件
# cd /opt
# rm -rf postgresql-12.1/

--初始化数据目录
# su postgres
$ /opt/pgsql/bin/initdb \
-D/opt/data6000 \
-EUTF8 \
-Upostgres \
-W

--服务启停
# service postgres-12 start | stop
$ /opt/pgsql/bin/pg_ctl -D /opt/data6000 start |stop

2.异步流复制搭建

$ vi postgresql.conf
listen_addresses = '*'
port=6000
wal_level=replica
archive_mode=on
archive_command='cp %p /opt/archive6000/%f'
max_wal_senders=10
wal_keep_segments=512
hot_standby=on
primary_conninfo='host=192.168.99.202 port=6000 user=repuser'

logging_collector=on
log_destination=csvlog
log_directory='/opt/data6000/pg_log'
log_filename='pg_log_%Y-%m-%d_%H%M%S.log'
log_file_mode=0600
log_truncate_on_rotation=on
log_rotation_age=1d
log_rotation_size=10MB
log_min_messages=warning
log_min_duration_statement=30s
log_checkpoints=on
log_connections=on
log_disconnections=on
log_duration=on
log_lock_waits=on
log_statement=DDL

--机器HA1(192.168.99.200)配置pg_hba.conf
$ vi pg_hba.conf
host all  all 192.168.99.1/24  md5
host replication  all  192.168.99.1/24 trust

$ /opt/pgsql/bin/psql -p6000 -h127.0.0.1 -Upostgres
create user repuser replication login connection limit 5 encrypted password'123456';
create table t(id int,info text);
insert into t values(1,now()),(2,now()),(3,now());

--重启HA1(192.168.99.200)
# service postgres-12 restart

--机器HA2(192.168.99.202)使用pg_basebackup做基础备份
$ /opt/pgsql/bin/pg_basebackup -D /opt/data6000 -Fp -Xs -v -P -h192.168.99.200 -p6000 -Urepuser
--如果报错请检查防火墙等配置，例如下面开放6000端口
# firewall-cmd --zone=public --add-port=6000/tcp --permanent
# firewall-cmd --reload

--机器HA2(192.168.99.202)修改postgresql.conf
--修改primary_conninfo信息
$ vi postgresql.conf
listen_addresses = '*'
port=6000
wal_level=replica
archive_mode=on
archive_command='cp %p /opt/archive6000/%f'
max_wal_senders=10
wal_keep_segments=512
hot_standby=on
primary_conninfo='host=192.168.99.200 port=6000 user=repuser'

logging_collector=on
log_destination=csvlog
log_directory='/opt/data6000/pg_log'
log_filename='pg_log_%Y-%m-%d_%H%M%S.log'
log_file_mode=0600
log_truncate_on_rotation=on
log_rotation_age=1d
log_rotation_size=10MB
log_min_messages=warning
log_min_duration_statement=30s
log_checkpoints=on
log_connections=on
log_disconnections=on
log_duration=on
log_lock_waits=on
log_statement=DDL

--机器HA2(192.168.99.202)配置standby.signal
$ touch /opt/data6000/standby.signal

--启动HA2(192.168.99.202)
$ /opt/pgsql/bin/pg_ctl -D/opt/data6000 start &

--异步流复制搭建完成
主库：HA1(192.168.99.200)
备库：HA2(192.168.99.202)

--ntp时间同步
# yum install ntp ntpdate
# ntpdate cn.pool.ntp.org
# hwclock --systohc

3.主库创建repmgr库存储元数据

$ /opt/pgsql/bin/psql -p6000 -Upostgres postgres
create user repmgr superuser connection limit 10 password '123456' ;
create database repmgr owner repmgr;

4.主库pg_hba配置repmgr策略

local   repmgr        repmgr                              trust
host    repmgr        repmgr      127.0.0.1/32            trust
host    repmgr        repmgr      192.168.99.0/24          trust

local   replication   repmgr                              trust
host    replication   repmgr      127.0.0.1/32            trust
host    replication   repmgr      192.168.99.0/24          trust

5.所有服务器安装repmgr

# wget https://github.com/2ndQuadrant/repmgr/archive/v5.0.0.tar.gz
# chmod 755 repmgr-5.0.0.tar.gz
# chown postgres:postgres repmgr-5.0.0.tar.gz
# tar zxvf repmgr-5.0.0.tar.gz
# cd /opt/repmgr-5.0.0
# ./configure PG_CONFIG=/opt/pgsql/bin/pg_config
# make && make install

6.停备库并删除数据文件

--停备库：HA2(192.168.99.202)，并删除数据文件
# service postgres-12 stop
# rm -rf archive6000/*
# rm -rf data6000/*

7.免密配置

--数据库访问免密
# mkdir /home/postgres
# chown -R postgres:postgres /home/postgres/
# chmod -R 0700 /home/postgres/
# usermod -d /home/postgres postgres
# su - postgres
$ chmod 0600 .pgpass
$ cat .pgpass 
192.168.99.200:6000:repmgr:repmgr:123456
192.168.99.202:6000:repmgr:repmgr:123456

--ssh免密步骤(操作系统用户postgres)
--1.每个节点都执行
$ ssh-keygen -t rsa
--2.每个节点分别执行
$ ssh-copy-id -i .ssh/id_rsa.pub postgres@192.168.99.202
$ ssh-copy-id -i .ssh/id_rsa.pub postgres@192.168.99.200
--测试ssh互通

8.主库创建配置文件repmgr.conf

--HA1(192.168.99.200)
# vi /opt/repmgr.conf
node_id=1
node_name='node1'
conninfo='host=192.168.99.200 port=6000 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/opt/data6000'
replication_user='repmgr'
replication_type='physical'
repmgr_bindir='/opt/pgsql/bin'
pg_bindir='/opt/pgsql/bin'
monitoring_history=yes
monitor_interval_secs=5
log_level='debug'
log_file='/opt/data6000/repmgr.log'
# chown -R postgres:postgres repmgr.conf
# chmod 0600 repmgr.conf

--HA2(192.168.99.202)测试repmgr是否能免密访问主库
$ /opt/pgsql/bin/psql -p6000 -h192.168.99.200 -Urepmgr

9.使用repmgr命令注册主库

--HA1(192.168.99.200)
$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf primary register --force
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered

10.使用repmgr命令克隆备库

--HA2(192.168.99.202)
# vi /opt/repmgr.conf
node_id=2
node_name='node2'
conninfo='host=192.168.99.202 port=6000 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/opt/data6000'
replication_user='repmgr'
replication_type='physical'
repmgr_bindir='/opt/pgsql/bin'
pg_bindir='/opt/pgsql/bin'
monitoring_history=yes
monitor_interval_secs=5
log_level='debug'
log_file='/opt/data6000/repmgr.log'
# chown -R postgres:postgres repmgr.conf
# chmod 0600 repmgr.conf

--check
$ /opt/pgsql/bin/repmgr -h 192.168.99.200 -p6000 -U repmgr -d repmgr -f /opt/repmgr.conf standby clone --dry-run
--clone
$ /opt/pgsql/bin/repmgr -h 192.168.99.200 -p6000 -U repmgr -d repmgr -f /opt/repmgr.conf standby clone

--HA2(192.168.99.202)修改配置文件，primary_conninfo里host修改HA2指向HA1
# vi /opt/data6000/postgresql.conf
primary_conninfo='host=192.168.99.200 port=6000 user=repuser'

--启动备库
# service postgres-12 start

--主库查看postgres进程
[postgres@ha1 ~]$  ps f -u postgres
  PID TTY      STAT   TIME COMMAND
 6155 pts/1    S      0:00 -bash
 6408 pts/1    R+     0:00  \_ ps f -u postgres
 6337 ?        Ss     0:00 /opt/pg12/bin/postgres -D /opt/data6000
 6338 ?        Ss     0:00  \_ postgres: logger   
 6340 ?        Ss     0:00  \_ postgres: checkpointer   
 6341 ?        Ss     0:00  \_ postgres: background writer   
 6342 ?        Ss     0:00  \_ postgres: walwriter   
 6343 ?        Ss     0:00  \_ postgres: autovacuum launcher   
 6344 ?        Ss     0:00  \_ postgres: archiver   last was 000000030000000000000012.00000028.backup
 6345 ?        Ss     0:00  \_ postgres: stats collector   
 6346 ?        Ss     0:00  \_ postgres: logical replication launcher  
 6404 ?        Ss     0:00  \_ postgres: walsender repmgr 192.168.99.202(55371) streaming 0/13001A28

--备库查看postgres进程
[postgres@ha2 ~]$  ps f -u postgres
  PID TTY      STAT   TIME COMMAND
 6297 pts/1    S      0:00 -bash
 7040 pts/1    R+     0:00  \_ ps f -u postgres
 3880 pts/0    S+     0:00 -bash
 7030 ?        Ss     0:00 /opt/pg12/bin/postgres -D /opt/data6000
 7031 ?        Ss     0:00  \_ postgres: logger   
 7032 ?        Ss     0:00  \_ postgres: startup   recovering 000000030000000000000013
 7033 ?        Ss     0:00  \_ postgres: checkpointer   
 7034 ?        Ss     0:00  \_ postgres: background writer   
 7035 ?        Ss     0:00  \_ postgres: stats collector   
 7036 ?        Ss     0:00  \_ postgres: walreceiver 
--可以看到主库walsender进程，备库walreceiver进程   

--主库查看
SELECT * FROM pg_stat_replication;

11.使用repmgr命令注册备库

--HA2(192.168.99.202)
[postgres@ha2 ~]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf standby register --force
INFO: connecting to local node "node2" (ID: 2)
INFO: connecting to primary database
INFO: standby registration complete
NOTICE: standby node "node2" (ID: 2) successfully registered

12.查看集群状态

--HA1和HA2查看状态一致
[postgres@ha2 ~]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                        
----+-------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 1        | host=192.168.99.200 port=6000 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | standby |   running | node1    | default  | 100      | 1        | host=192.168.99.202 port=6000 user=repmgr dbname=repmgr connect_timeout=2

13.HA2执行有计划的switchover

[postgres@ha2 ~]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf standby switchover -U repmgr --verbose
NOTICE: using provided configuration file "/opt/repmgr.conf"
WARNING: following problems with command line parameters detected:
  database connection parameters not required when executing UNKNOWN ACTION
NOTICE: executing switchover on node "node2" (ID: 2)
INFO: searching for primary node
INFO: checking if node 1 is primary
INFO: current primary node is 1
INFO: SSH connection to host "192.168.99.200" succeeded
INFO: 0 pending archive files
INFO: replication lag on this standby is 0 seconds
NOTICE: local node "node2" (ID: 2) will be promoted to primary; current primary "node1" (ID: 1) will be demoted to standby
NOTICE: stopping current primary node "node1" (ID: 1)
NOTICE: issuing CHECKPOINT
DETAIL: executing server command "/opt/pgsql/bin/pg_ctl  -D '/opt/data6000' -W -m fast stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
INFO: checking for primary shutdown; 2 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/14000028
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
INFO: standby promoted to primary after 1 second(s)
NOTICE: STANDBY PROMOTE successful
DETAIL: server "node2" (ID: 2) was successfully promoted to primary
INFO: local node 1 can attach to rejoin target node 2
DETAIL: local node's recovery point: 0/14000028; rejoin target node's fork point: 0/140000A0
NOTICE: setting node 1's upstream to node 2
WARNING: unable to ping "host=192.168.99.200 port=6000 user=repmgr dbname=repmgr connect_timeout=2"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
NOTICE: starting server using "/opt/pgsql/bin/pg_ctl  -w -D '/opt/data6000' start"
NOTICE: NODE REJOIN successful
DETAIL: node 1 is now attached to node 2
NOTICE: switchover was successful
DETAIL: node "node2" is now primary and node "node1" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully
--从日志可以看到
1.check主库为node1
2.ssh主库node1
3.主库node1检查归档文件和wal
4.主库node1触发checkpoint
5.fast关闭主库node1
6.check主库node1是否关闭
7.主库node1关闭clean后开始promte备库node2
8.节点node2调用pg_promote()函数提升备库为主库
9.等待60秒(promote_check_timeout) 以便promotion完成
10.节点node2完成promotion
11.node1满足rejoin到node2
12.node1执行pg_ctl启动服务，rejoin，attached node2
13.switchover完成

--查看集群状态
[postgres@ha2 ~]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                        
----+-------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------
 1  | node1 | standby |   running | node2    | default  | 100      | 4        | host=192.168.99.200 port=6000 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | primary | * running |          | default  | 100      | 4        | host=192.168.99.202 port=6000 user=repmgr dbname=repmgr connect_timeout=2

14.HA1执行有计划的switchover

[postgres@ha1 ~]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf standby switchover -U repmgr --verbose
NOTICE: using provided configuration file "/opt/repmgr.conf"
WARNING: following problems with command line parameters detected:
  database connection parameters not required when executing UNKNOWN ACTION
NOTICE: executing switchover on node "node1" (ID: 1)
INFO: searching for primary node
INFO: checking if node 2 is primary
INFO: current primary node is 2
INFO: SSH connection to host "192.168.99.202" succeeded
INFO: 0 pending archive files
INFO: replication lag on this standby is 0 seconds
NOTICE: local node "node1" (ID: 1) will be promoted to primary; current primary "node2" (ID: 2) will be demoted to standby
NOTICE: stopping current primary node "node2" (ID: 2)
NOTICE: issuing CHECKPOINT
DETAIL: executing server command "/opt/pgsql/bin/pg_ctl  -D '/opt/data6000' -W -m fast stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
INFO: checking for primary shutdown; 2 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/15000028
NOTICE: promoting standby to primary
DETAIL: promoting server "node1" (ID: 1) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
INFO: standby promoted to primary after 1 second(s)
NOTICE: STANDBY PROMOTE successful
DETAIL: server "node1" (ID: 1) was successfully promoted to primary
INFO: local node 2 can attach to rejoin target node 1
DETAIL: local node's recovery point: 0/15000028; rejoin target node's fork point: 0/150000A0
NOTICE: setting node 2's upstream to node 1
WARNING: unable to ping "host=192.168.99.202 port=6000 user=repmgr dbname=repmgr connect_timeout=2"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
NOTICE: starting server using "/opt/pgsql/bin/pg_ctl  -w -D '/opt/data6000' start"
NOTICE: NODE REJOIN successful
DETAIL: node 2 is now attached to node 1
NOTICE: switchover was successful
DETAIL: node "node1" is now primary and node "node2" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully
--从日志可以看到
1.check主库为node2
2.ssh主库node2
3.主库node2检查归档文件和wal
4.主库node2触发checkpoint
5.fast关闭主库node2
6.check主库node2是否关闭
7.主库node2关闭clean后开始promte备库node1
8.节点node1调用pg_promote()函数提升备库为主库
9.等待60秒(promote_check_timeout) 以便promotion完成
10.节点node1完成promotion
11.node2满足rejoin到node1
12.node2执行pg_ctl启动服务，rejoin，attached node1
13.switchover完成

--查看集群状态
[postgres@ha1 ~]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                        
----+-------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 5        | host=192.168.99.200 port=6000 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | standby |   running | node1    | default  | 100      | 4        | host=192.168.99.202 port=6000 user=repmgr dbname=repmgr connect_timeout=2

经过两次switchover已经恢复为HA1为主库，HA2为备库。

常见错误1

ERROR: unable to connect via SSH to host "192.168.99.200", user ""
--排查觉得可能是主机名重复引起，因为是虚拟机复制，只改了ip没改主机名
--改完主机名后重新配置ssh互信还是有问题，别的普通用户正常
--删除postgres用户和组
# userdel postgres
# groupdel postgres
# rm -rf /home/postgres/
--重新创建用户和组
# groupadd postgres -g 1001
# useradd postgres -g 1001 -u 1001
# passwd postgres
# su - postgres
--重新配置ssh免密
--1.每个节点都执行
$ ssh-keygen -t rsa
--2.每个节点分别执行
$ ssh-copy-id -i .ssh/id_rsa.pub 192.168.99.202
$ ssh-copy-id -i .ssh/id_rsa.pub 192.168.99.200
--再次测试ssh免密访问成功

常见错误2

[postgres@ha2 ~]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf standby register --force
INFO: connecting to local node "node2" (ID: 2)
ERROR: unable to connect to local node "node2" (ID: 2)
HINT: to register a standby which is not running, additionally provide the primary connection parameters
--备库HA2数据库服务未启动，注册repmgr会报错，启动服务即可