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.