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
Source
For 9.2, in windows:
Stop the service:
Net stop postgresql-x64-9
Modify the config file,
change data/pg_hba.conf, basically md5 to trust:
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
Start the service:
Net start postgresql-x64-9
Execute the sql statement to set your desired password, on a sql console or using psql:
alter user postgres with password ‘newpassword’;
Put back the original config file.