postgresql 14 主备切换

1. 主从切换

1.1 停止主节点

$ pg_ctl stop
waiting for server to shut down.... done
server stopped

1.2 从节点提升为主节点

$ pg_ctl promote
waiting for server to promote.... done
server promoted

1.3 验证是否切换成功

$ psql -h localhost -p 5432 -U postgres -W postgres -c "select name,setting,category,short_desc from pg_settings where name ='primary_conninfo';"
Password: 
       name       | setting |           category            |                               short_desc                                
------------------+---------+-------------------------------+-------------------------------------------------------------------------
 primary_conninfo |         | Replication / Standby Servers | Sets the connection string to be used to connect to the sending server.
(1 row)

$ psql -h localhost -p 5432 -U postgres -W postgres -c "select pg_is_in_recovery();"
Password: 
 pg_is_in_recovery 
-------------------
 f

当输出值为 f 则说明该节点为主节点，输出值为 t 则说明是从节点

2. 原主加入集群

2.1 同步主库的数据

$ pg_rewind --target-pgdata=/data/pg_data --source-server="host=192.168.32.127 port=5432 user=postgres password=123456 dbname=postgres"  --progress

缺少什么文件就从主库拷贝过来就行

2.2 从库相关配置

如果已经存在以下配置项，无需添加

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

# 同步(实时)复制配置
synchronous_commit = on
synchronous_standby_names = 'FIRST 2 (pg02, pg03)'
EOF

特别留意 synchronous_standby_names 参数

2.3 配置连接主库的信息

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 验证

# 主库执行，打印从库信息则说明原主库加入成功
$ 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 
-------+---------+------------------+----------------+-----------+------------+---------------
 48688 | repl    | pg01             | 192.168.32.132 | streaming | sync       |             1
(1 row)