I forgot the password I entered during postgres installation

1. find the file pg_hba.conf - it may be located, for example in /etc/postgresql-9.1/pg_hba.conf.

   cd /etc/postgresql-9.1/

2. Back it up

   cp pg_hba.conf pg_hba.conf-backup

3. place the following line (as either the first uncommented line, or as the only one):

For all occurrence of below (local and host) , exepct replication section if you don't have any it has to be changed as follow ,no MD5 or Peer autehication should be present.

local  all   all   trust

4. restart your PostgreSQL server (e.g., on Linux:)

   sudo /etc/init.d/postgresql restart

   If the service (daemon) doesn't start reporting in log file:

   local connections are not supported by this build

   you should change

   local all all trust

   to

   host all all 127.0.0.1/32 trust

5. you can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)

   psql -U postgres

   or

   psql -h 127.0.0.1 -U postgres

   (note that with the first command you will not always be connected with local host)

6. Reset password ('replace my_user_name with postgres since you are resetting postgres user)

   ALTER USER my_user_name with password 'my_secure_password';

7. Restore the old pg_hba.conf as it is very dangerous to keep around

   cp pg_hba.conf-backup pg_hba.conf

8. restart the server, in order to run with the safe pg_hba.conf

   sudo /etc/init.d/postgresql restart

Further Reading about that pg_hba file: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

When connecting to postgres from command line, don't forget to add -h localhost as command line parameter. If not, postgres will try to connect using PEER authentication mode.

The below shows a reset of the password, a failed login with PEER authentication and a successful login using a TCP connection.

# sudo -u postgres psqlcould not change directory to "/root"psql (9.1.11)Type "help" for help.postgres=# \passwordEnter new password:Enter it again:postgres=# \q

Failing:

# psql -U postgres -WPassword for user postgres:psql: FATAL:  Peer authentication failed for user "postgres"

Working with -h localhost:

# psql -U postgres -W  -h localhostPassword for user postgres:psql (9.1.11)SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)Type "help" for help.postgres=#

The pg_hba.conf (C:\Program Files\PostgreSQL\9.3\data) file has changed since these answers were given. What worked for me, in Windows, is to open the file and change the METHOD from md5 to trust:

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

Then, using pgAdmin III, I logged in using no password and changed user postgres' password by going to File -> Change Password