Re: Lost password to user postgres

On Sat, 2006-12-02 at 11:44 -0800, Philippe Salama wrote:
> 2 years ago, I installed postgresql on my desktop and used it for a
> while for learning purposes. Then, I stopped using it. I forgot the
> password to the user postgres

You don't have to reinstall. you've got three basic options:

1. Change the password in single user mode
2. Edit pg_hba.conf and set it to trust mode, change your password,
change back to md5
3. Re-initdb your cluster.

For 1:
su - postgres (from root if you have to)
pg_ctl stop
postgres template1 (or some other database you know exists)
alter user postgres password 'newpassword';

For 2:
su - postgres
cd $PGDATA
vi pg_hba.conf
// change entries at bottom to trust
pg_ctl reload
psql template1
alter user postgres password 'newpassword';

For 3:
su - postgres
pg_ctl stop
echo $PGDATA // make sure this is set to something like /var/lib/pgsql
rm -rf $PGDATA/*
initdb // with whatever options you need.

I prefer option 2, as you don't have to take down your database to do
it.