Rational Pie

I had an old installation of postgreSQL on my Win32 XP box. I never really used it until today when I was forced to ditch MySQL in favor of postgreSQL. But the classical problem now:  I had forgotten the password for my default postgres account. Since I had no other user(role) created, this meant  had no choice but to forcefully reset my postgres password.

Unfortunately people at postgreSQL seem to be suffering from a widespread tendency of treating a windows user as someone who needs to be punished for committing the cardinal sin of opting windows. I remember several of my batchmates having had their brains fried trying to install postgreSQL on their windows machines (before I stepped in to save them, of course :D).

Anyway, enough digression! Here is how it worked out for me:

1. Get the server running:

   pg_ctl.exe start -D "C:\Program Files\PostgreSQL\8.3\data"

2. Open the file

   "C:\Program Files\PostgreSQL\8.3\data\pg_hba.conf"

   Initial Content:

   # TYPE DATABASE  USER CIDR-ADDRESS  METHOD
   
   # IPv4 local connections:
   host all all  127.0.0.1/32 md5
   # IPv6 local connections:
   host all all ::1/128 md5

   Changed to:

   # TYPE DATABASE USER CIDR-ADDRESS METHOD
   
   # IPv4 local connections:
   host all all 127.0.0.1/32 trust sameuser
   # IPv6 local connections:
   host all all ::1/128 trust sameuser

3. reload the server config:

   pg_ctl.exe reload -D "C:\Program Files\PostgreSQL\8.3\data"

4. run:

   psql -U postgres

   At this point if you get this error:

   psql: FATAL:  role "postgres" does not exist

   do this:

   createuser.exe -s postgres

5. run this to start command line client:

   psql -U postgres

6. run SQL to change password:

   postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';

   Note that the inverted commas around the password are required but are not a part of the password

7. Rollback the changes made in step 2
8. reload the server config again as in step 3
9. Curse the postgre config for one last time
10. Get going!