I have a client that has a PostgreSQL database and he cannot remember the password that we used when the database was setup. Is there a way to recover that information so I do not have to blow away his database and start from scratch?
The database is running on a PC.
Step 1: Edit PostgreSQL config file to establish trust relationship to login without password:
vi /var/lib/pgsql/data/pg_hba.conf
Old Line:
local all postgres password
Change it to:
local all postgres trust
Step 2: Restart PostgreSQL Server:
service postgresql restart
Step 3: Change password:
psql -U postgres template1 -c alter user postgres with password ‘newpassword’;
Step 4: Password has been updated. Revert back the original settings of config file:
vi /var/lib/pgsql/data/pg_hba.conf
Old Line:
local all postgres trust
Change it to:
local all postgres password
Step 5: Restart server and use your new password to access PostgreSQL Server.
service postgresql restart