Pgbouncer连接PostgreSQL失败：不存在该数据库问题排查求助

部署Pgbouncer与Odoo时出现异常：Odoo需要连接PostgreSQL的postgres库，但Pgbouncer日志提示“no such database: postgres”，连接失败；但连接应用数据库mandaladb无异常，且在Pgbouncer所在服务器终端直接连接postgres库完全正常，导致应用无法正常访问。需要排查哪些内容或调整哪些配置？

Pgbouncer错误日志 #

2024-01-07 19:19:38.080 UTC [1] LOG C-0x7f19b3eeb3d0: (nodb)/odoo@172.10.0.8:37798 login attempt: db=postgres user=odoo tls=no
2024-01-07 19:19:38.080 UTC [1] LOG C-0x7f19b3eeb3d0: (nodb)/odoo@172.10.0.8:37798 login failed: db=postgres user=odoo
2024-01-07 19:19:38.080 UTC [1] LOG C-0x7f19b3eeb3d0: (nodb)/odoo@172.10.0.8:37798 closing because: no such database: postgres (age=0s)
2024-01-07 19:19:38.080 UTC [1] WARNING C-0x7f19b3eeb3d0: (nodb)/odoo@172.10.0.8:37798 pooler error: no such database: postgres

应用数据库连接正常日志 #

2024-01-07 19:14:23.733 UTC [1] LOG S-0x7f19b3e76b00: mandaladb/odoo@172.17.221.210:5432 new connection to server (from 172.10.0.7:36534)
2024-01-07 19:14:23.738 UTC [1] LOG S-0x7f19b3e76b00: mandaladb/odoo@172.17.221.210:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1
2024-01-07 19:14:24.806 UTC [1] LOG S-0x7f19b3e76850: mandaladb/odoo@172.17.221.210:5432 new connection to server (from 172.10.0.7:48656)
2024-01-07 19:14:24.811 UTC [1] LOG S-0x7f19b3e76850: mandaladb/odoo@172.17.221.210:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1

终端直连测试结果 #

devops@id-dcvs-oddo-prod:~/clinicmandala$ psql -h fhcaredb -U odoo -d postgres
Password for user odoo:
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

排查与配置调整建议 #

• 检查Pgbouncer配置文件pgbouncer.ini的数据库映射
  • 确认[databases]段是否包含postgres库的明确映射，示例：

    postgres = host=fhcaredb port=5432 dbname=postgres
    

  • 若使用通配映射（如* = host=fhcaredb），需确认是否开启ignore_startup_parameters = dbname，避免参数冲突导致数据库无法识别
• 检查Pgbouncer的pg_hba.conf访问控制规则
  • 确保允许Odoo所在IP（172.10.0.8）连接postgres库，规则示例：

    host postgres odoo 172.10.0.8/32 md5
    

• 验证Pgbouncer自身的postgres库连接性
  • 在Pgbouncer服务器上，直接通过Pgbouncer端口测试连接：

    psql -h localhost -U odoo -d postgres -p <你的Pgbouncer端口>
    

  • 若连接失败，说明Pgbouncer未正确映射到postgres库，需调整数据库配置
• 检查Pgbouncer的pool_mode设置
  • 若当前为statement模式，postgres库可能不兼容该模式，建议切换为transaction或session模式
• 核对Odoo的数据库连接配置
  • 确认Odoo连接Pgbouncer时使用的数据库名称正确，且无额外参数干扰连接逻辑

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