pqxx: broken_connection on trying to connect to posgres db

Question

I have this problem trying to connect to the local postrgres databases. Until now i succeeded to connect to the same databases using jdbc or working directly in PGAdmin, this error happens when i try to connect using pqxx library or from shell:

pqxx code:

#include <pqxx/connection.hxx>

int main(){
    pqxx::connection conn("user=postgres port=5432 dbname=pqxx-test password=postgres");
    return 0;
} 

or from shell, as user postgres:

psql pqxx-test

both the cases raise this error:

could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"

Searching for the solution i think i understood i have to edit pq_hba.conf and postgresql.conf files in order to connect in these ways, but in pg_hba.conf is already set:

local all all md5

and in postgresql.conf

listen_addresses='*'

And by the way, i'm not even able to edit these files because of a problem with the viminfo, but maybe this is another problem..

Can anyone help me?

edit: using PostgreSQL 9.6.2 on Ubuntu 14.04

edit:

as postgres user, if i ask for the postgre version using: psql -V

the result is 9.6.2

but looking at the file system hierarchy, all conf file are inside this folder

/opt/PostreSQL/9.4/data/

can that be the problem?

Answer 1

You seem to have a mix of client-side postgresql 9.6 configured for the ubuntu FS layout and a postgresql server 9.4 presumably downloaded from EntrepriseDB (and installed under /opt/PostgreSQL) using its own layout.

Assuming that the postgresql is running, the simplest solution is to add host=localhost to your connection string, so that it will use a TCP connection instead of the Unix domain socket /var/run/postgresql/.s.PGSQL.5432 that does not exist because of the mismatch.