I'm writing this article because I recently ran into an issue with PostgreSQL where I had to reset the password. The steps are simple, but if you’re new to PostgreSQL or running it on Windows, it might get tricky. So, let’s break it down into clear steps that are easy to follow.
Step 1: Stop the PostgreSQL Service
First, we need to stop the PostgreSQL service before we can reset the password. Here’s how you do that:
- Press Win + R, type services.msc, and hit Enter.
- Look for PostgreSQL in the list.
- Right-click on it and choose Stop.
Step 2: Start PostgreSQL in Single-User Mode
Now, we’ll start PostgreSQL in single-user mode. This mode allows us to reset the password without needing the current one.
Open Command Prompt as an administrator.
To do this, press Win + S, type cmd, right-click on Command Prompt, and choose Run as administrator.Now, navigate to the PostgreSQL bin directory where the executable is located. Typically, it’s in:
cd "C:\Program Files\PostgreSQL\16\bin"
- After that, run PostgreSQL in single-user mode with this command:
postgres --single -D "C:\Program Files\PostgreSQL\16\data" postgres
-
Note: Replace
"C:\Program Files\PostgreSQL\16\data"
with your actual data directory path if it’s different.
Step 3: Reset the Password
Once PostgreSQL starts in single-user mode, you’ll see a prompt where you can run SQL commands. To reset the password for the postgres user, use this command:
ALTER USER postgres WITH PASSWORD 'your_new_password';
- Replace
'your_new_password'
with the new password you want to set.
After entering the command, press Enter to reset the password.
Step 4: Restart the PostgreSQL Service
After resetting the password, exit the single-user mode by closing the Command Prompt window. Then, go back to the Services tool:
- Right-click on PostgreSQL and choose Start to restart the service.
Step 5: Reconnect with pgAdmin
Finally, open pgAdmin 4 (or any other PostgreSQL client you use) and try connecting with the new password.
If it still doesn’t work, you may need to update the saved password:
- Right-click on the server in the Object Browser.
- Choose Properties → Connection.
- Enter the new password and click Save.
Simplified Process (For Quick Reference)
In simpler terms, here’s what you need to do:
- Stop PostgreSQL: Go to services.msc and stop PostgreSQL.
- Open Command Prompt as Admin: Search for cmd, right-click, and run as administrator.
- Navigate to PostgreSQL Folder: Use this command:
cd "C:\Program Files\PostgreSQL\16\bin"
- Run PostgreSQL in Single-User Mode:
postgres --single -D "C:\Program Files\PostgreSQL\16\data" postgres
- Reset the Password:
ALTER USER postgres WITH PASSWORD 'your_new_password';
- Restart PostgreSQL: Go back to Services and start PostgreSQL again.
- Reconnect with pgAdmin: Use the new password to connect.
Final Thoughts
I hope this guide makes it easy for you to reset your PostgreSQL password on Windows using single-user mode. It’s a lifesaver when you’re locked out of your database.
- - - - - - - - - - - - - - - - - - - - -
Stay connected - @syedamaham.dev 🐬
- - - - - - - - - - - - - - - - - - - - -
Top comments (0)