注意1：pg12的recover.conf文件合并到了postgresql.conf中
参考 https://www.postgresql.org/docs/12/release-12.html
注意2：
1、主备大版本必须一致，小版本可以不一致
2、主备wal_blocksize 在编译时必须一致 否则会报错 。如果主库编译安装设置了下面的一个或多个参数，那么备库必须要编译安装采取同样的大小设置。注意RPM安装均是采用默认值大小。
默认值看参考官网文档
官网编译安装手册
参数和代码对应关系

  1. XLOG_SEG_SIZE  ----  --with-wal-segsize
  2. RELSEG_SIZE    ----  --with-segsize
  3. XLOG_BLCKSZ    ----  --with-wal-blocksize
  4. BLCKSZ         ----  --with-blocksize

一、创建数据库复制账户

主库创建复制用户

CREATE USER repl REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'Re9102'

二、配置参数文件

2.1 配置 postgresql.conf
下面为流复制需要更改的参数：

listen_addresses = '*'
archive_mode = on
archive_command = 'test ! -f /data/pgsql/12/arch/%f && cp %p /data/pgsql/12/arch/%f'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
wal_keep_segments = 512

2.2 配置 pg_hba.conf

host    all             all             0.0.0.0/24            md5
host    replication     all             0.0.0.0/24            md5

2.3 重启服务

三、备份恢复

清空备库的数据目录，然后在备库执行

pg_basebackup -h 主库ip地址 -p 5432 -U repl -W -Fp -Xs -Pv -R -D 备库数据目录

备库数据目录下：会生成：standby.signal 文件
并且postgresql.auto.conf 文件会添加连接信息

[postgres@db02 data]$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl password=re9102 host=192.168.20.151 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any'

四、启动

启动备库：

systemctl start postgresql-12

五、主备切换脚本

  • 注意
    首先要在主库的 postgresql.auto.conf文件中添加如下信息:
primary_conninfo = 'user=repl password=re9102 host=192.168.20.152 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any'

这样在主库切成备库的时候，只需要在$PGDATA下 touch 一个 standby.signal文件就可以了。

主库IP地址为 192.168.20.151
备库IP地址为 192.168.20.152

PRIMARY_IP=192.168.20.152 # 脚本在备库，此处填写主库ip地址；脚本在主库，此处填写备库IP地址
STANDBY_IP=192.168.20.151 # 脚本在备库，此处填写备库ip地址；脚本在主库，此处填写备主库IP地址
VIP=192.168.20.242
PGHOME=/usr/pgsql-12
DBHOME=/data/pgsql/12/data
SUPER_USER=postgres
SUPER_USER_PWD=postgres
NETDEV=bond0

# stop old primary db

echo 'stop old primary...'

ssh $PRIMARY_IP "ifconfig $NETDEV:0 down"

if [ $? -ne 0 ]; then
    echo ifconfig $NETDEV:0 down old primary failed
    res=`ping -c 3 $VIP|grep "100% packet loss"`
    if [[ -z $res ]];then
        echo $VIP is alive,exit....
        exit 1
    fi
fi

ssh $PRIMARY_IP "systemctl stop postgresql-12"

if [ $? -ne 0 ]; then
    echo "systemctl stop postgresql-12" on old primary failed
    res=`ping -c 3 $PRIMARY_IP|grep "100% packet loss"`
    if [[ -z $res ]];then
        echo $PRIMARY_IP is alive,contiune....
    fi
fi

# switch standby db to primary

echo 'current standby switch to primary...'

$PGHOME/bin/psql -h $STANDBY_IP "user=$SUPER_USER password=$SUPER_USER_PWD" --command "SELECT pg_promote(true,60)"

if [ $? -ne 0 ]; then
    echo current primary promote failed
    exit 1
fi

echo 'current primary attach vip...'

ssh $STANDBY_IP "ifconfig $NETDEV:0 inet $VIP netmask 255.255.255.0"

if [ $? -ne 0 ]; then
    echo fconfig $NETDEV:0 inet $VIP netmask 255.255.255.0 on current primary failed
    exit 1
fi

# old primary switch to standby

echo 'old primary switch standby'

ssh $PRIMARY_IP  "touch $DBHOME/standby.signal && chown postgres.postgres $DBHOME/standby.signal"

if [ $? -ne 0 ]; then
    echo touch standby.signal on old primary failed
    exit 1
fi

ssh $PRIMARY_IP  "systemctl start postgresql-12"

if [ $? -ne 0 ]; then
    echo "systemctl start postgresql-12" on old primary failed
    exit 1
fi