C++使用pqxx连接PostgreSQL报错 类中连接失败的解决方法

初始错误 #

程序抛出pqxx::broken_connection异常，错误信息如下：

terminate called after throwing an instance of 'pqxx::broken_connection'
  what():  connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "zxctatar" does not exist

排查过程 #

1. 确认服务与端口状态：PostgreSQL服务正常运行，5432端口已完成监听：

LISTEN 0      200          0.0.0.0:5432      0.0.0.0:*    users:(("postgres",pid=20277,fd=6))       
LISTEN 0      200             [::]:5432         [::]:*    users:(("postgres",pid=20277,fd=7))   

2. 验证数据库角色：目标连接角色admin和postgres均已创建，权限配置正常：

List of roles
 Role name |                         Attributes                          
-----------+------------------------------------------------------------
 admin     | Superuser
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

3. 调整数据库配置：
   • 修改postgresql.conf，允许所有地址监听：

   listen_addresses = '*'
   

   • 修改pg_hba.conf，配置本地/远程连接的认证规则：

   # Database administrative login by Unix domain socket
   local   all             postgres                                peer
   
   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
   # "local" is for Unix domain socket connections only
   local   all             all                                     peer
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            md5
   # IPv6 local connections:
   host    all             all             ::1/128                 md5
   # Allow replication connections from localhost, by a user with the
   # replication privilege.
   local   all             all                                     md5
   host    all             all             127.0.0.1/32            md5
   host    all             all             ::1/128                 md5
   host    all             all             0.0.0.0/0               md5
   

4. 终端连接测试：使用psql命令可正常连接数据库，排除服务器端配置问题：

psql "host=127.0.0.1 port=5432 dbname=postgres user=postgres password=15900521"
psql "hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=15900521"
psql -U postgres -h localhost -d postgres

5. 代码对比测试：将相同连接逻辑移至main函数可正常运行，但放入自定义Database类则报错：
   • 正常运行的main函数代码：

   int main()
   {
       try
       {
           pqxx::connection C("dbname=postgres user=postgres password=180305Vfcz hostaddr=127.0.0.1");
   
           if(C.is_open())
           {
               std::cout << "nice";
           }
       }
       catch (const std::exception &e)
       {
           std::cout << e.what() << std::endl;
       }
   
       return 0;
   }
   

   • 报错的Database类初始代码：

   #include <iostream>
   #include "../../include/Database.h"
   
   Database::Database()
   {
       createDatabase();
   }
   
   void Database::createDatabase()
   {
       try
       {
           pqxx::connection connection_to_postgres_("host=127.0.0.1 port=5432 dbname=pg_database user=admin password=15900512"); // 连接postgres服务器
   
           if(connection_to_postgres_.is_open())
           {
           }
       }
       catch (const std::exception &e)
       {
           std::cout << e.what() << std::endl;
       }
   }
   

问题解决 #

为Database类添加显式析构函数后，连接异常消失。析构函数确保pqxx连接资源被正确清理，避免因对象生命周期管理不当导致的连接错误。

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