How to recover or reset a lost Postgresql password

This has been doing my head in all day – so hopefully it will save some of you the trouble. I’m on a CentOS box.

First thing you want to do is check which version of postgres you have, with:

psql --version

Which will give you something like:

psql (PostgreSQL) 9.2.2

So with that info you will know which pg_hba.conf to open, for 9.2 it’s:

vim /var/lib/pgsql/9.2/data/pg_hba.conf

In that file, change this line:

local all all md5

To:

local all postgres ident

Then, at the command type:

su - postgres
pg_ctl reload
psql

Which will let you log in as postgres, then set your new password with:

ALTER USER postgres WITH ENCRYPTED PASSWORD 'your_password';

And then go back to your pg_hba.conf file with:

vim /var/lib/pgsql/9.2/data/pg_hba.conf

And change this line:

local all postgres ident

…back to:

local all all md5

Then:

pg_ctl reload

And that’s it – done!