Forgot Admin Password on Postgres (Windows Installation), can't reset
Based on AK47's answer and some additional info I fixed it by doing the following,
1) Stop Postgres if currently running, command line below. Need to give it the 'data' dir. In my case C:\PostgreSQL\data
pg_ctl -D C:\PostgreSQL\data stop
2) Edit the file pg_hba.conf
(it's also in the \data dir) as follows:
As AK40 wrote, change all MD5 references to trust , e.g.
# 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
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
3) Now run
psql -U postgres
4) In the PG Command Prompt that appears type,
ALTER USER Postgres WITH PASSWORD '<newpassword>';
5) Save this by typing wq
enter to exit the PG Prompt
6) Now start Postgres
pg_ctl -D C:\PostgreSQL\data start
7) Might want to revert the MD5 -> Trust
change later in the pg_hba.conf
.
Update your pg_hba.conf
file to allow for trusted local connections
[root@server] vim pg_hba.conf
>> local all all trust
then restart your PostgreSQL server
[user@machine] pg_ctl -D C:\PostgreSQL\data restart (Windows)
[root@server] service postgresql restart (Linux)
at this point you can connect to your server as postgres user using a local connection without the need to enter a password (omitting the -h
parameter when calling the psql
command will use a local connection - if you pass -h
then this will match the line host all all 0.0.0.0/0 <method>
in your pg_hba.conf
file)
[root@server] psql -U postgres
You can then alter the postgres user role and set the password to whatever you like using the following command in the psql
terminal
[psql] alter role postgres password <new_password>;
Once this is done you can restart your PostgreSQL server again
[user@machine] pg_ctl -D C:\PostgreSQL\data restart (Windows)
[root@server] service postgresql restart (Linux)
and at this point your password should be changed to the new password