Postgresql – changed password for postgres login in pgAdmin, now can’t connect to server

postgresqlpostgresql-9.3Ubuntu

I was trying to use pg_dump to script out a database and in the process I used pgAdmin to change the password for the postgres user. I realize (now) that changing that password was a bad idea. But now I can't get back in to the server to undo it. Postgres is running: 

$ ps aux | grep postgr
postgres 18888  0.0  0.1 206480 13984 ?        S    16:39   0:00 /opt/PostgreSQL/9.3/bin/postgres -D /home/dev/postgres_data
postgres 18889  0.0  0.0  60060  1180 ?        Ss   16:39   0:00 postgres: logger process                                   
postgres 18891  0.0  0.0 206480  1376 ?        Ss   16:39   0:00 postgres: checkpointer process

But when I connect from the command line, I get an error I've never seen before: 

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

/var/run/postgresql/ doesn't exist. When I search for that error I only get hits for Mac OS X but I'm on Ubuntu 12.04. The closest I can get is: 

$ psql -h localhost -U postgres
psql: FATAL:  password authentication failed for user "postgres"
password retrieved from file "/home/dev/.pgpass"

I've tried adding passwords to /home/dev/.pgpass (there's really only 4 that I use on this system) and none of them are allowing me access. I've tried connecting with pgAdmin with the same result. I've restarted the server with sudo /etc/init.d/postgresql-9.3 restart and then logging in, didn't help.

I'm at a loss for what to do next.

Postgres 9.3, Ubuntu 12.

Best Answer

I think there are two unrelated problems here:

1) failure to connect on Unix domain socket

This part of ps output:

/opt/PostgreSQL/9.3/bin/postgres -D /home/dev/postgres_data

indicates that you're not running postgresql as packaged for Ubuntu. Ubuntu doesn't install its binaries inside /opt (it doesn't even create /opt) and your data directory /home/dev/postgres_data is non-standard too.

The usual suspect is a postgres server installed with the EntrepriseDB installer instead.

/var/run/postgresql is the default unix_socket_directory of Ubuntu's postgres, not Entreprisedb's or any other. Using the psql command-line tool shipping with Ubuntu against a non-Ubuntu leads to this error you mentioned:

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

This mismatch is frequent on Mac OS X because there are quite a few incompatible PostgreSQL installers for it and Apple ships with a client-side postgresql that's not configured for any of them.

Short of getting rid of EntrepriseDB's postgres and installing Ubuntu's (good idea for the future but overkill for just that problem), you need to locate your unix_socket_directory, typically by consulting your postgresql.conf, which should be inside the data directory: /home/dev/postgres_data according to ps.

Then give it as the -h argument to psql, this will be tried instead of /var/run/postgresql. Per the manual page:

 -h hostname, --host=hostname
           Specifies the host name of the machine on which the server is
           running. If the value begins with a slash, it is used as the
           directory for the Unix-domain socket.

2) bad postgres password

If you successfully changed a db password with pgAdmin but can't use it, it may be that you're experiencing a known pgAdmin bug.

See PostgreSQL user can not connect to server after changing password

Related Question