PostgreSQL备库postgresqldb02启动超时90秒，请求协助恢复

错误信息 #

ERROR:  recovery is checking if postmaster is started
DETAIL:  postmaster on hostname:"postgresqldb02" database:"template1" user:"postgres" failed to start in 90 second.

集群节点状态 #

node_id |    hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | postgresqldb01 | 5432 | up     | up        | 0.500000  | primary | primary | 52         | true              | 0                 |                   |                        | 2024-06-03 09:05:36
 1       | postgresqldb02 | 5432 | down   | down      | 0.500000  | standby | unknown | 0          | false             | 0                 | streaming         | async                  | 2024-06-03 09:03:58
(2 rows)

备库postgresqldb02日志 #

-rw-r-----  1 postgres adm      10019 Jun  4 01:49 postgresql-12-main.log
root@postgresqldb02:/var/log/postgresql# cat postgresql-12-main.log
2024-06-03 09:33:19.390 UTC [52442] LOG:  starting PostgreSQL 12.19 (Ubuntu 12.19-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
2024-06-03 09:33:19.390 UTC [52442] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-06-03 09:33:19.393 UTC [52442] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-06-03 09:33:19.423 UTC [52443] LOG:  database system was interrupted; last known up at 2024-06-03 09:33:18 UTC
Permission denied, please try again.
Permission denied, please try again.
postgres@postgresqldb01: Permission denied (publickey,password).
2024-06-03 09:33:19.532 UTC [52443] LOG:  entering standby mode
Permission denied, please try again.
Permission denied, please try again.
postgres@postgresqldb01: Permission denied (publickey,password).
2024-06-03 09:33:19.608 UTC [52443] LOG:  redo starts at 0/2C000028
2024-06-03 09:33:19.611 UTC [52443] LOG:  consistent recovery state reached at 0/2C000100
2024-06-03 09:33:19.611 UTC [52442] LOG:  database system is ready to accept read only connections
Permission denied, please try again.

问题分析 #

从备库日志中的postgres@postgresqldb01: Permission denied (publickey,password)错误可以确定，备库的postgres用户无法通过SSH访问主库的postgres用户，导致备库无法获取主库的WAL日志（可能是归档日志拉取或基础备份同步的依赖），进而引发恢复超时错误。

解决步骤 #

1. 配置备库到主库的免密SSH登录

   • 切换到备库的postgres用户：su - postgres
   • 生成SSH密钥（一路回车默认配置即可）：ssh-keygen -t rsa
   • 将公钥复制到主库postgres用户的授权列表：ssh-copy-id postgres@postgresqldb01，输入主库postgres用户的密码完成复制
   • 测试免密连接：ssh postgres@postgresqldb01，确认无需输入密码即可登录主库

2. 验证PostgreSQL流复制权限配置

   • 编辑主库的pg_hba.conf文件，添加备库IP的复制连接权限：

     host    replication     postgres         <备库IP地址>/32        scram-sha-256
     

     （如果使用MD5认证，将scram-sha-256替换为md5）
   • 确保主库postgresql.conf的以下参数配置正确：

     wal_level = replica
     max_wal_senders = 10
     wal_keep_segments = 64
     

   • 重启主库PostgreSQL服务使配置生效：sudo systemctl restart postgresql@12-main

3. 重启备库服务并检查状态

   • 重启备库PostgreSQL服务：sudo systemctl restart postgresql@12-main
   • 使用集群管理工具（如Patroni）检查节点状态：patronictl list，确认备库状态恢复为up，pg_role变为standby

内容的提问来源于stack exchange，提问作者it alliancepharma