Recovering/Reseting Password PostgreSQL Server

For those who had encountered error as below,

ubuntu@ip-10-128-73-74:~$ psql -U postgres -h localhost
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

1. Edit pg_hba.conf and change the following line
   

   ubuntu@ip-10-128-73-74:~$ sudo nano /etc/postgresql/9.0/main/pg_hba.conf
   # old
   local   all         postgres                         ident
   

   
   into
   

   # new
   local   all         postgres                         trust
   

2. Restart the postgreSQL service
   

   ubuntu@ip-10-128-73-74:~$ sudo service postgresql restart

3. Reset your postgres password by executing
   

   ubuntu@ip-10-128-73-74:~$ psql -U postgres -d template1 -c alter user postgres with password 'yournewpasswd';
   psql: warning: extra command-line argument "user" ignored
   psql: warning: extra command-line argument "postgres" ignored
   psql: warning: extra command-line argument "with" ignored
   psql: warning: extra command-line argument "password" ignored
   psql: warning: extra command-line argument "yournewpasswd" ignored
   ERROR:  syntax error at end of input
   LINE 1: alter
   

4. ubuntu@ip-10-128-73-74:~$ psql -U postgres -d template1 -c "alter user postgres with password 'yournewpasswd'";
   ALTER ROLE
   

5. Edit pg_hba.conf and change the following line
   

   ubuntu@ip-10-128-73-74:~$ sudo nano /etc/postgresql/9.0/main/pg_hba.conf
   # old
   local   all         postgres                         trust
   

   
   into
   

   # new
   local   all         postgres                         ident
   

6. As before, restart the service
   

   ubuntu@ip-10-128-73-74:~$ sudo service postgresql restart

7. Finally, check your new passwd
   

   ubuntu@ip-10-128-73-74:~$ psql -U postgres -h localhost
   Password for user postgres:
   psql (9.0.4)
   SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
   Type "help" for help.
   
   postgres=# \q
   ubuntu@ip-10-128-73-74:~$
   

Posted with WordPress for BlackBerry.