How to reset postgres password

1. Find the file pg_hba.conf - it may be located, for example in /etc/postgresql/9.6/main/pg_hba.conf.

cd /etc/postgresql/9.6/main

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):

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

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