postgresql 14 流复制(主从)配置

1 主库操作

1.1 创建用于数据流的用户

主库操作

$ psql -h 127.0.0.1 -p 5432 -U postgres -W
Password: 
psql (14.10)
Type "help" for help.

postgres=# create role repl login replication encrypted password 'oLfex^5pfe';
CREATE ROLE
postgres=# \q

1.2 配置登录权限

主库操作

cat <<'EOF' | tee -a $PGDATA/pg_hba.conf > /dev/null
# 配置postgresql集群主机 replication 免密登录
host    replication     all             192.168.32.127/32       trust
host    replication     all             192.168.32.128/32       trust
host    replication     all             192.168.32.132/32       trust
# 配置 同网段 主机密码登录
host    all             all             192.168.32.0/24         scram-sha-256
EOF

pg_ctl reload

2. 从库操作（异步）

2.1 创建从库数据目录

从库操作

su - postgres

sudo rm -rf /data/pg_* && \
sudo mkdir -p /data/{pg_data,pg_wal,pg_archive} && \
sudo chown -R postgres. /data/{pg_data,pg_wal,pg_archive} && \
sudo chmod 700 /data/{pg_data,pg_wal,pg_archive}

2.2 基础备份

从库操作

pg_basebackup -h 192.168.32.132 -p 5432 -U repl --pgdata=/data/pg_data --waldir=/data/pg_wal --write-recovery-conf --progress --verbose

2.3 从库相关配置

所有节点确认该参数，如没有则添加

cat <<'EOF' | tee -a $PGDATA/postgresql.conf > /dev/null
# 备用服务器相关设置
hot_standby = on # 控制是否允许备用服务器在进行热备份时同时处理读取查询
wal_receiver_status_interval = 10s # 控制 walreceiver 进程向主服务器发送心跳消息的时间间隔
hot_standby_feedback = on # 控制备用服务器是否会向主服务器发送关于自己的复制状态和进度的信息

EOF

2.4 连接主库配置

从库操作

cat $PGDATA/postgresql.auto.conf
primary_conninfo = 'host=192.168.32.127 port=5432 user=repl'

2.4 创建从库标识文件

从库操作

ls $PGDATA/standby.signal &> /dev/null || touch $PGDATA/standby.signal

2.5 启动服务

从库操作

pg_ctl start

2.6 查看日志

从库操作

cd $PGDATA/logs/
tail -f postgresql-`date "+%Y-%m-%d"`.log

3 从库操作（同步）

3.1 基础配置

按照上面异步流复制操作一遍

3.2 连接主库配置

从库操作

cat $PGDATA/postgresql.auto.conf
primary_conninfo = 'host=192.168.32.127 port=5432 user=repl application_name=pg01'

添加 application_name 参数

3.3 从库相关配置

所有节点确认该参数，如没有则添加

cat <<'EOF' | tee -a $PGDATA/postgresql.conf > /dev/null
# 同步(实时)复制配置
synchronous_commit = on # 控制事务提交的同步方式。该参数决定了在事务提交时是否等待数据同步到磁盘上
synchronous_standby_names = 'FIRST 2(pg01, pg03)' # 该参数指定了在主服务器提交事务时，需要等待哪些热备服务器将数据同步到磁盘上

EOF

参数 synchronous_standby_names 的值说明：

• FIRST num (name1, name2)：前面num台为同步服务器，其他为异步服务器
• ANY num (name1, name2)：从括号中选择num台为同步服务器，其他为异步服务器

其中括号里面的 name1、name2 为 3.2步骤 对应的 application_name 值

4.3 重新加载配置

所有节点

pg_ctl reload

4 验证

# 主库执行，打印从库信息则说明搭建成功
$ psql -h localhost -p 5432 -U postgres -W postgres -c "select pid,usename,application_name,client_addr,state,sync_state,sync_priority from pg_stat_replication;"
Password: 
  pid  | usename | application_name |  client_addr   |   state   | sync_state | sync_priority 
-------+---------+------------------+----------------+-----------+------------+---------------
 22541 | repl    | pg03             | 192.168.32.128 | streaming | sync       |             2
 22542 | repl    | pg01             | 192.168.32.132 | streaming | sync       |             1
(2 rows)

sync_state参数说明

• async: 这台服务器是异步的
• sync: 这台服务器是同步的
• potential: 这台服务器现在是异步的，但可能在当前的同步服务器失效时变成同步的
• quorum: 这台服务器被当做规定数量后备服务器的候选