Cannot connect to postgres installed on Ubuntu

Question

I installed the Bitnami Django stack which included PostgreSQL 8.4.

When I run psql -U postgres I get the following error:

psql: 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"?

PG is definitely running and the pg_hba.conf file looks like this:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

What gives?

"Proof" that pg is running:

root@assaf-desktop:/home/assaf# ps axf | grep postgres
14338 ?        S      0:00 /opt/djangostack-1.3-0/postgresql/bin/postgres -D /opt/djangostack-1.3-0/postgresql/data -p 5432
14347 ?        Ss     0:00  \_ postgres: writer process                                                                        
14348 ?        Ss     0:00  \_ postgres: wal writer process                                                                    
14349 ?        Ss     0:00  \_ postgres: autovacuum launcher process                                                           
14350 ?        Ss     0:00  \_ postgres: stats collector process                                                               
15139 pts/1    S+     0:00              \_ grep --color=auto postgres
root@assaf-desktop:/home/assaf# netstat -nltp | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      14338/postgres  
tcp6       0      0 ::1:5432                :::*                    LISTEN      14338/postgres  
root@assaf-desktop:/home/assaf# 

Answer 1

At a guess you're using the system version of the psql command, which will look for the postgres unix domain sockets in /var/run/postgresql and the third party postgres you are using has been configured to put them somewhere else.

The easiest solution is probably to use /opt/djangostack-1.3-0/postgresql/bin/psql instead, assuming that there is one, as it will presumably look in the correct place for the unix sockets.

Otherwise, you need to look at the unix_socket_directory setting in postgresql.conf but it's quite likely that will be commented out and it is using a compiled in default.

Answer 2

I use following workaround so both clients should be happy:

sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

Answer 3

The error message refers to a Unix-domain socket, so you need to tweak your netstat invocation to not exclude them. So try it without the option -t:

netstat -nlp | grep 5432

I would guess that the server is actually listening on the socket /tmp/.s.PGSQL.5432 rather than the /var/run/postgresql/.s.PGSQL.5432 that your client is attempting to connect to. This is a typical problem when using hand-compiled or third-party PostgreSQL packages on Debian or Ubuntu, because the source default for the Unix-domain socket directory is /tmp but the Debian packaging changes it to /var/run/postgresql.

Possible workarounds:

• Use the clients supplied by your third-party package (call /opt/djangostack-1.3-0/postgresql/bin/psql). Possibly deinstall the Ubuntu-supplied packages altogether (might be difficult because of other reverse dependencies).
• Fix the socket directory of the third-party package to be compatible with Debian/Ubuntu.
• Use -h localhost to connect via TCP/IP instead.
• Use -h /tmp or equivalent PGHOST setting to point to the right directory.
• Don't use third-party packages.