PostGreSQL主从库环境下的从节点故障恢复

      已搭建的PostGreSQL11.6主从环境，因批量导入大量数据导致主从节点异常宕机，导致主节点重新启动系统，从节点wal同步信息也不完整。从节点重启pg数据库，出现下图问题：

经过分析，主从节点之间wal段缺少1天的量，很难恢复过来。所以依照以主库为基础，重置备库节点。

注意：在重置备库节点时，需要清空data下的内容，即rm -rf /opt/pgsql/data（根据自己安装时设定路径处理）。

由于主库节点上有自定义表空间，在备节点上使用pg_basebackup初始化，出现下列错误：

运行pg_basebackup -D /opt/pgsql/data -F p -X stream -v -P -h 172.22.1.XXX -p 5432 -U replica备份主节点，报“pg_basebackup: 无法创建目录 "/opt/pgsql/data/base": 文件已存在09051/19591/22300)”错误。

尝试使用在root账户下也不能成功，显然排除与权限有关。最后尝试采取在主节点下操作，具体见下面步骤：

一、主库上执行

//创建备份目录
mkdir -p /opt/pgsql/backups/`date +%Y%m%d`

//启动备份
/usr/pgsql-11/bin/psql -c "select pg_start_backup('hot_backup');"

//压缩data下文件，需排除postmaster.pid、postmaster.opts 、日志和pg_wal目录下的内容
tar -zcvPf /opt/pgsql/backups/`date +%Y%m%d`/backup.tar.gz /opt/pgsql/data/* --exclude=/opt/pgsql/data/postmaster.pid --exclude=/opt/pgsql/data/postmaster.opts --exclude=/opt/pgsql/data/log/*  --exclude=/opt/pgsql/data/pg_wal/*

//停止备份

/usr/pgsql-11/bin/psql -c "select pg_stop_backup();"

//本地复制到远程备库节点上
scp /opt/pgsql/backups/`date +%Y%m%d`/*  root@172.22.1.XX5:/opt/

二、备库上执行

//批量创建目录
mkdir -p /opt/pgsql/{data,backups,scripts}

//查看创建目录的情况

[postgres@pg-slave opt]$ cd pgsql
[postgres@pg-slave pgsql]$ ls -l

总用量 12
drwxrwxr-x. 2 postgres postgres 4096 2月  26 21:44 backups
drwxrwxr-x. 2 postgres postgres 4096 2月  26 21:44 data
drwxrwxr-x. 2 postgres postgres 4096 2月  26 21:44 scripts

//改变主库备份基础库压缩文件的属主

[root@pg-slave opt]# chown -R postgres:postgres backup.tar.gz
[root@pg-slave opt]# ls -l
总用量 26005368
-rw-r--r--. 1 postgres postgres 26629464375 2月  26 22:07 backup.tar.gz
drwx------. 2 root     root           16384 12月 23 17:16 lost+found
drwxr-xr-x. 5 postgres postgres        4096 2月  26 21:44 pgsql
drwxr-xr-x. 2 postgres postgres        4096 2月  18 18:25 pgsqlbak

//解压备份信息

tar -zxvf /opt/backup.tar.gz -C /

//查找recovery.conf样例文件

[root@pg-slave ~]# find / -name recovery.*
/tmp/recovery.conf
/usr/pgsql-11/share/recovery.conf.sample

//复制样例文件到pg的data目录下

cp /usr/pgsql-11/share/recovery.conf.sample /opt/pgsql/data/recovery.conf

//添加备库的recovery信息
vi recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=172.22.1.XXX port=5432 user=replica password=91h^$%tU*7'
trigger_file = 'failover.now'
recovery_target_timeline = 'latest'

//编辑postgresql配置信息

vi $PGDATA/postgresql.conf

max_connections = 1000              # 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on                   # 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s  # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on          # 如果有错误的数据复制，是否向主进行反馈

//启动postgresql

[postgres@pg-slave log]$ pg_ctl -D /opt/pgsql/data -l logfile start
等待服务器进程启动 .... 已停止等待
pg_ctl: 无法启动服务器进程
检查日志输出.
[postgres@pg-slave log]$ ls -l
[postgres@pg-slave log]$ vi logfile

2020-02-26 23:29:51.379 CST [23479] 致命错误:  数据目录"/opt/pgsql/data"的权限无效
2020-02-26 23:29:51.379 CST [23479] 详细信息:  权限应该为 u=rwx (0700) 或者u=rwx,g=rx (0750).

//切换到root赋予权限
chmod 0700 /opt/pgsql/data

//重新再启动，正常

[postgres@pg-slave log]$ pg_ctl -D /opt/pgsql/data -l logfile start
等待服务器进程启动 .................... 完成
服务器进程已经启动
[postgres@pg-slave log]$ ps -ef|grep postgres
postgres 23598     1  7 23:32 pts/0    00:00:01 /usr/pgsql-11/bin/postgres -D /opt/pgsql/data
postgres 23602 23598  0 23:32 ?        00:00:00 postgres: logger   
postgres 23603 23598  7 23:32 ?        00:00:01 postgres: startup   recovering 000000010000006500000062
postgres 23616 23598  2 23:32 ?        00:00:00 postgres: walreceiver   streaming 65/62000140
postgres 23617 23598  0 23:32 ?        00:00:00 postgres: checkpointer   
postgres 23618 23598  0 23:32 ?        00:00:00 postgres: background writer   
postgres 23619 23598  0 23:32 ?        00:00:00 postgres: stats collector   
postgres 23627 30257  0 23:32 pts/0    00:00:00 ps -ef
postgres 23628 30257  0 23:32 pts/0    00:00:00 grep --color=auto postgres
root     30255 20115  0 14:43 pts/0    00:00:00 su - postgres
postgres 30257 30255  0 14:43 pts/0    00:00:00 -bash

三、验证主备节点情况

//查看主库状态
[postgres@pg-master log]$ ps -ef | grep "wal" | grep -v "grep"
postgres  2563  2557  0 2月17 ?       00:00:21 postgres: walwriter   
postgres 14874  2557  0 23:32 ?        00:00:00 postgres: walsender replica 172.22.1.XX5(46144) streaming 65/62000140

//查看从库状态
[postgres@pg-slave log]$ ps -ef | grep "wal" | grep -v "grep"
postgres 23616 23598  0 23:32 ?        00:00:00 postgres: walreceiver   streaming 65/62000140
[postgres@pg-slave log]$ 

//数据库层面检查
//判断主备库
select pg_is_in_recovery();
# 如果返回t 说明是备库，返回f 说明是主库。

[postgres@pg-slave log]$ psql
psql (11.6)
输入 "help" 来获取帮助信息.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 行记录)

[postgres@pg-master log]$ psql
psql (11.6)
输入 "help" 来获取帮助信息.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 行记录)

 

希望通过这些分享，能给其他朋友带来一点帮助。