Tag Archives: libpqxx

The use of libpqxx interface in Linux solves the error of PSQL: connections on UNIX domain socket “/TMP/.S.pgsql.5432”

When using PostgreSQL database in the project, it is required to be compatible on both windows and Linux platforms. So the interface used in windows broke out an exception in Linux

psql：connections on Unix domain socket "/tmp/.s.PGSQL.5432"

After analyzing and checking the documentation the problem was solved as follows.

1. Reason: This is due to the fact that when connecting to pg under linux, the default unix domain socket method is used to connect to pg, and the port of pg is running on a non-default port (default port is 5432). Under windows the same interface uses the TCP protocol of socket to connect to pg.

2. When using pgxx::connection(“”), the string to be passed in is incomplete, resulting in a connection using the unix domain socket method under linux.

The following is the official postgresql documentation explaining.

host

Name of host to connect to.If a host name begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. If multiple host names are specified, each will be tried in turn in the order given. The default behavior whenhostis not specified is to connect to a Unix-domain socketin/tmp(or whatever socket directory was specified whenPostgreSQLwas built). On machines without Unix-domain sockets, the default is to connect tolocalhost.

A comma-separated list of host names is also accepted, in which case each host name in the list is tried in order.

hostaddr

Numeric IP address of host to connect to. This should be in the standard IPv4 address format, e.g.,172.28.40.9. If your machine supports IPv6, you can also use those addresses. TCP/IP communication is always used when a nonempty string is specified for this parameter.

Usinghostaddrinstead ofhostallows the application to avoid a host name look-up, which might be important in applications with time constraints. However, a host name is required for GSSAPI or SSPI authentication methods, as well as forverify-fullSSL certificate verification. The following rules are used:

Ifhostis specified withouthostaddr, a host name lookup occurs.

Ifhostaddris specified withouthost, the value forhostaddrgives the server network address. The connection attempt will fail if the authentication method requires a host name.

If bothhostandhostaddrare specified, the value forhostaddrgives the server network address. The value forhostis ignored unless the authentication method requires it, in which case it will be used as the host name.

Note that authentication is likely to fail ifhostis not the name of the server at network addresshostaddr. Also, note thathostrather thanhostaddris used to identify the connection in a password file .

A comma-separated list ofhostaddrvalues is also accepted, in which case each host in the list is tried in order. SeeSection33.1.1.3for details.

Without either a host name or host address,libpqwill connect using a local Unix-domain socket; or on machines without Unix-domain sockets, it will attempt to connect tolocalhost.

Therefore, there are 2 main things wrong with our interface.

1. std::string strConnection has a problem with string splicing when using strConnection = “dbname=”+pg_dbname+” user=”+pg_username+” password=” under windows. So when splicing the string again use

std::string strConnection = "dbname=";
strConnection += pg_dbname.c_str();
strConnection += " user=";
strConnection += pg_username.c_str();
strConnection += " password=";
strConnection += pg_password.c_str();
strConnection += " hostaddr=";
strConnection += pg_ip.c_str();
strConnection += " port=";
strConnection += pg_port.c_str();

2. It is better to add the configuration of host = localhost in the strconnection string to ensure that there is no problem