PostgreSQL-Patroni高可用启动报错

Patroni高可用部署故障排除

原创

已于 2024-08-20 16:18:37 修改 · 838 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库 #linux #dba

于 2024-08-20 15:50:52 首次发布

环境说明

一主两从：

角色	主机	组件
pa-pg-1	172.22.138.220	etcd、patroni、postgresql
pa-pg-2	172.22.138.219	etcd、patroni、postgresql
pa-pg-3	172.22.138.218	etcd、patroni、postgresql

安装部署好Patroni高可用后，主库pa-pg-1和从库pa-pg-3均启动正常，而pa-pg-2的patroni日志一直输出以下报错：

...
Aug 15 11:00:23 DB-test2 patroni: 2024-08-15 11:09:56,422 ERROR: Exception when working with leader
Aug 15 11:00:23 DB-test2 patroni: Traceback (most recent call last):
Aug 15 11:00:23 DB-test2 patroni: File "/usr/local/lib/python3.6/site-packages/patroni/postgresql/rewind.py", line 80, in check_leader_is_not_in_recovery
Aug 15 11:00:23 DB-test2 patroni: with get_connection_cursor(connect_timeout=3, options='-c statement_timeout=2000', **conn_kwargs) as cur:
Aug 15 11:00:23 DB-test2 patroni: File "/usr/lib64/python3.6/contextlib.py", line 81, in __enter__
Aug 15 11:00:23 DB-test2 patroni: return next(self.gen)
Aug 15 11:00:23 DB-test2 patroni: File "/usr/local/lib/python3.6/site-packages/patroni/postgresql/connection.py", line 157, in get_connection_cursor
Aug 15 11:00:23 DB-test2 patroni: conn = psycopg.connect(**kwargs)
Aug 15 11:00:23 DB-test2 patroni: File "/usr/local/lib/python3.6/site-packages/patroni/psycopg.py", line 104, in connect
Aug 15 11:00:23 DB-test2 patroni: ret = _connect(*args, **kwargs)
Aug 15 11:00:23 DB-test2 patroni: File "/usr/local/lib64/python3.6/site-packages/psycopg2/__init__.py", line 126, in connect
Aug 15 11:00:23 DB-test2 patroni: File "/usr/local/lib64/python3.6/site-packages/psycopg2/extensions.py", line 175, in make_dsn
Aug 15 11:00:23 DB-test2 patroni: psycopg2.ProgrammingError: invalid dsn: invalid connection option "target_session_attrs"
Aug 15 11:00:23 DB-test2 patroni: 2024-08-15 11:09:56,425 INFO: no action. I am (pa-pg-2), a secondary, and following a leader (pa-pg-1)
...

排查思路

1.首先一直关注的报错是"ERROR: Exception when working with leader"所以按照这个报错，首先去排查了patroni各节点的状态，但是通过节点状态并没有发现什么明显异常。

postgres # patronictl -c /etc/patroni.yml list
+ Cluster: pgsql (7400977424401590005) ----------+----+-----------+-----------------+-----------------------------+
| Member  | Host