Setting Up PostgreSQL in VS Code — From Download to Your First Query (Plus Password Rescue)
PostgreSQL is one of the most popular open-source relational databases in the world, and you don't need to leave your editor to work with it. In this guide, we'll go from a completely clean machine to running your first SQL query inside VS Code.
What we'll cover:
1. Downloading and installing PostgreSQL
2. Verifying the server is running
3. Installing a PostgreSQL extension for VS Code
4. Creating your first connection
5. Creating a database, table, and running queries
6. Already installed but forgot your credentials? — jump to [the rescue section](#-already-have-postgresql-installed)
7. Troubleshooting common issues
---
📥 Step 1: Download and Install PostgreSQL
PostgreSQL works on Windows, macOS, and Linux. Grab the installer for your OS from the official downloads page:
👉 **postgresql.org
Windows (EDB Installer)
1. Go to the Windows installers page: enterprisedb.com
2. Download the latest version (16.x at the time of writing) for **Windows x86-64.
3. Run the installer. The defaults are fine, but pay attention to these screens:
| Screen | What to do |
|---|---|
| Password | Set a password for the postgres superuser. Write this down — this is the #1 thing people forget. |
| Port | Leave the default 5432 unless you have a reason not to. |
| Components | Keep "PostgreSQL Server", "Command Line Tools", and "pgAdmin 4" checked. |
| Stack Builder | You can uncheck this — it's for optional add-ons you don't need right now. |
4. Finish the installer and let it launch pgAdmin at the end if you want to confirm the install visually (we won't need pgAdmin — VS Code is about to replace it).
> 📝 The installer also creates a Windows service called postgresql-x64-16 that starts PostgreSQL automatically when you boot. You rarely need to touch it, but we'll use it later.
macOS
Easiest option is Homebrew:
brew install postgresql@16
brew services start postgresql@16
(Or use the EDB installer from the macOS downloads page — it's the same graphical installer as Windows.)
Linux (Debian/Ubuntu)
sudo apt update
sudo apt install postgresql postgresql-contrib
PostgreSQL starts automatically and runs under the postgres system user. See the Linux downloads page for other distros.
---
✅ Step 2: Verify the Server Is Running
Before opening VS Code, confirm the database is actually up.
Windows
# Check the service status
sc query postgresql-x64-16
# Check PostgreSQL is accepting connections
"C:\Program Files\PostgreSQL\16\bin\pg_isready.exe"
You should see STATE: 4 RUNNING and accepting connections.
> ⚠️ If the service is stopped, open services.msc, find postgresql-x64-16, right-click → Start (you'll need admin rights).
Optional but recommended — add the tools to your PATH so you can just type psql anywhere:
1. Press Win and search for "Edit the system environment variables" → Environment Variables
2. Under User variables, edit Path → New → add C:\Program Files\PostgreSQL\16\bin
3. Open a new terminal and run psql --version to confirm.
macOS
brew services list # should show postgresql@16 as "started"
pg_isready
Linux
sudo systemctl status postgresql
sudo -u postgres pg_isready
---
🧩 Step 3: Install a PostgreSQL Extension in VS Code
Open VS Code → Extensions (Ctrl+Shift+X) and install:
PostgreSQL Client by Weijan Chen
👉 **marketplace.visualstudio.com
This gives you a database explorer in the sidebar, a query editor with IntelliSense, and the ability to view/edit data in a grid — basically a lightweight pgAdmin that lives inside your editor.
> 💡 If you work with multiple databases (MySQL, MongoDB, SQLite…), the same author's Database Client extension bundles everything into one. For PostgreSQL-only work, the lighter PostgreSQL Client is perfect.
---
🔌 Step 4: Create Your First Connection
1. Click the database icon that appears in VS Code's activity bar (left sidebar), or press Ctrl+Alt+D.
2. Click + Create Connection (or the + button in the panel).
3. Fill in the form:
| Field | Value | Notes |
|---|---|---|
| Connection Name | Local PostgreSQL | Just a display label — anything you like |
| Server Type | PostgreSQL | Tells the extension which driver to use |
| Server Host | localhost | The database is on your machine |
| Port | 5432 | The default from installation |
| Username | postgres | The default superuser |
| Password | the password you set during installation | |
| Database | postgres | Every install has this default database |
4. Click Save Connection.
Your connection now appears in the sidebar. Expand it to browse Databases → Schemas → Tables. Click any table to see its data in a grid, edit cells inline, and inspect column definitions — no SQL required for basic browsing.
---
🧪 Step 5: Your First Query
Right-click your connection (or any database under it) → New Query, and try this:
-- Create a playground database and switch to it
CREATE DATABASE playground;
-- (Right-click the connection → Refresh to see it appear)
Select the playground database in the sidebar, open a new query, and run:
CREATE TABLE devs (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
language TEXT NOT NULL DEFAULT 'SQL',
joined DATE NOT NULL DEFAULT CURRENT_DATE
);
INSERT INTO devs (name, language) VALUES
('Ada', 'SQL'),
('Grace', 'COBOL'), -- legend
('You', 'everything');
SELECT * FROM devs;
You'll see the results in a panel below the query. From here you can:
- Run everything or a selection — highlight specific statements and run only those
- Export results as CSV/JSON
- Generate INSERT statements from existing rows (right-click a table)
- View table DDL — right-click → View Table Definition
---
🔑 Already Have PostgreSQL Installed?
You remember your password
Then you're 90% done — skip straight to [Step 4](#-step-4-create-your-first-connection) and connect. If the connection fails, make sure the server is running ([Step 2](#-step-2-verify-the-server-is-running)).
You forgot your password 😅
This happens to everyone. The fix: temporarily tell PostgreSQL to stop asking for passwords, change the password, then turn authentication back on. It's the officially documented recovery procedure and takes ~5 minutes.
> ⚠️ Note: while trust auth is active, anyone on your machine can connect as any user without a password. This is fine for a personal dev machine done quickly; think twice before doing it on a shared system.
The steps below are for Windows (where this bites most often). macOS/Linux users: same idea, but your pg_hba.conf is usually at /etc/postgresql/16/main/pg_hba.conf (Debian/Ubuntu) or /usr/local/var/postgres/@PREFIX@/etc (Homebrew), and you restart with brew services restart postgresql@16 or sudo systemctl restart postgresql.
1. Open pg_hba.conf as administrator
It lives at:
C:\Program Files\PostgreSQL\16\data\pg_hba.conf
(Adjust 16 to your version.) Open it with an editor launched as Administrator (e.g., Notepad → right-click → Run as administrator → File → Open).
2. Make a backup, then change the auth method
Copy the file somewhere safe first. Then find the two host lines near the bottom and change scram-sha-256 to trust:
# BEFORE
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
# AFTER
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
3. Restart the PostgreSQL service
Open PowerShell as administrator:
Restart-Service postgresql-x64-16
4. Set the new password
In a normal (non-admin) terminal:
"C:\Program Files\PostgreSQL\16\bin\psql.exe" -h localhost -U postgres -d postgres -c "ALTER USER postgres WITH PASSWORD 'YourNewPassword123!';"
You should see ALTER ROLE.
5. Restore pg_hba.conf and restart again
Put the original lines back (scram-sha-256), save, then once more in the admin PowerShell:
Restart-Service postgresql-x64-16
6. Verify
PGPASSWORD='YourNewPassword123!' "C:\Program Files\PostgreSQL\16\bin\psql.exe" -h localhost -U postgres -d postgres -c "SELECT version();"
If you get a version string back, plug the new password into your VS Code connection and you're in business. 🎉
---
🛠️ Troubleshooting
| Symptom | Likely cause & fix |
|---|---|
| connection refused in VS Code | The server isn't running. Start it via services.msc (Windows), brew services start postgresql@16 (macOS), or sudo systemctl start postgresql (Linux). |
| password authentication failed | Wrong password for postgres. Use the [rescue procedure](#-already-have-postgresql-installed) above. |
| Service won't start, event log shows could not access directory ... Permission denied | The service account lost permissions on the data directory (happens after Windows updates or cleanup tools). Fix with an admin terminal: icacls "C:\Program Files\PostgreSQL\16\data" /grant "postgres:(OI)(CI)F" /T then start the service. |
| Port 5432 already in use | Another instance is running, or a stale process holds the port. Check with netstat -ano \| findstr 5432 (Windows) and stop the conflicting service, or change the port in postgresql.conf + your VS Code connection. |
| psql is not recognized | The bin folder isn't on your PATH — see [Step 2](#-step-2-verify-the-server-is-running). New terminals only pick up PATH changes after reopening. |
| Connection asks for password every time | In the extension's connection settings, the password field saves per-connection — re-save the connection with the password filled in. |
---
🎯 Wrapping Up
You now have a full PostgreSQL setup without ever leaving VS Code:
- ✅ PostgreSQL 16 installed and running as a service
- ✅ A database explorer + query editor inside your editor
- ✅ A recovery plan for the day you forget the password (it's okay, we all do)
Useful links for going further:
- PostgreSQL Official Docs — postgresql.org
- PostgreSQL Tutorial (great for SQL basics) — postgresqltutorial.com
- The extension's docs/issue tracker — cweijan/vscode-database-client
Happy querying! 🐘
---
Thanks for reading! If this helped you, a ❤️ and a follow are much appreciated. Drop a comment if you hit an issue that isn't covered here.
1 reaction
Mentions
Setting Up PostgreSQL in VS Code — From Download to Your First Query (Plus Password Rescue) PostgreSQL is one of the most popular open-source relational databases in the world, and you don't need to leave your editor to work with it. In this guide, we'll go from a completely clean machine to running your first SQL query inside VS Code. What we'll cover: 1. Downloading and installing PostgreSQL 2. Verifying the server is running 3. Installing a PostgreSQL extension for VS Code 4. Creating your first connection 5. Creating a database, table, and running queries 6. Already installed but forgot your credentials? — jump to [the rescue section](#-already-have-postgresql-installed) 7. Troubleshooting common issues --- 📥 Step 1: Download and Install PostgreSQL PostgreSQL works on Windows, macOS, and Linux. Grab the installer for your OS from the official downloads page: 👉 **postgresql.org Windows (EDB Installer) 1. Go to the Windows installers page: enterprisedb.com 2. Download the latest version (16.x at the time of writing) for **Windows x86-64. 3. Run the installer. The defaults are fine, but pay attention to these screens: | Screen | What to do | |---|---| | Password | Set a password for the postgres superuser. Write this down — this is the #1 thing people forget. | | Port | Leave the default 5432 unless you have a reason not to. | | Components | Keep "PostgreSQL Server", "Command Line Tools", and "pgAdmin 4" checked. | | Stack Builder | You can uncheck this — it's for optional add-ons you don't need right now. | 4. Finish the installer and let it launch pgAdmin at the end if you want to confirm the install visually (we won't need pgAdmin — VS Code is about to replace it). > 📝 The installer also creates a Windows service called postgresql-x64-16 that starts PostgreSQL automatically when you boot. You rarely need to touch it, but we'll use it later. macOS Easiest option is Homebrew: brew install postgresql@16 brew services start postgresql@16 (Or use the EDB installer from the macOS downloads page — it's the same graphical installer as Windows.) Linux (Debian/Ubuntu) sudo apt update sudo apt install postgresql postgresql-contrib PostgreSQL starts automatically and runs under the postgres system user. See the Linux downloads page for other distros. --- ✅ Step 2: Verify the Server Is Running Before opening VS Code, confirm the database is actually up. Windows # Check the service status sc query postgresql-x64-16 # Check PostgreSQL is accepting connections "C:\Program Files\PostgreSQL\16\bin\pg_isready.exe" You should see STATE: 4 RUNNING and accepting connections. > ⚠️ If the service is stopped, open services.msc, find postgresql-x64-16, right-click → Start (you'll need admin rights). Optional but recommended — add the tools to your PATH so you can just type psql anywhere: 1. Press Win and search for "Edit the system environment variables" → Environment Variables 2. Under User variables, edit Path → New → add C:\Program Files\PostgreSQL\16\bin 3. Open a new terminal and run psql --version to confirm. macOS brew services list # should show postgresql@16 as "started" pg_isready Linux sudo systemctl status postgresql sudo -u postgres pg_isready --- 🧩 Step 3: Install a PostgreSQL Extension in VS Code Open VS Code → Extensions (Ctrl+Shift+X) and install: PostgreSQL Client by Weijan Chen 👉 **marketplace.visualstudio.com This gives you a database explorer in the sidebar, a query editor with IntelliSense, and the ability to view/edit data in a grid — basically a lightweight pgAdmin that lives inside your editor. > 💡 If you work with multiple databases (MySQL, MongoDB, SQLite…), the same author's Database Client extension bundles everything into one. For PostgreSQL-only work, the lighter PostgreSQL Client is perfect. --- 🔌 Step 4: Create Your First Connection 1. Click the database icon that appears in VS Code's activity bar (left sidebar), or press Ctrl+Alt+D. 2. Click + Create Connection (or the + button in the panel). 3. Fill in the form: | Field | Value | Notes | |---|---|---| | Connection Name | Local PostgreSQL | Just a display label — anything you like | | Server Type | PostgreSQL | Tells the extension which driver to use | | Server Host | localhost | The database is on your machine | | Port | 5432 | The default from installation | | Username | postgres | The default superuser | | Password | the password you set during installation | | | Database | postgres | Every install has this default database | 4. Click Save Connection. Your connection now appears in the sidebar. Expand it to browse Databases → Schemas → Tables. Click any table to see its data in a grid, edit cells inline, and inspect column definitions — no SQL required for basic browsing. --- 🧪 Step 5: Your First Query Right-click your connection (or any database under it) → New Query, and try this: -- Create a playground database and switch to it CREATE DATABASE playground; -- (Right-click the connection → Refresh to see it appear) Select the playground database in the sidebar, open a new query, and run: CREATE TABLE devs ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, language TEXT NOT NULL DEFAULT 'SQL', joined DATE NOT NULL DEFAULT CURRENT_DATE ); INSERT INTO devs (name, language) VALUES ('Ada', 'SQL'), ('Grace', 'COBOL'), -- legend ('You', 'everything'); SELECT * FROM devs; You'll see the results in a panel below the query. From here you can: - Run everything or a selection — highlight specific statements and run only those - Export results as CSV/JSON - Generate INSERT statements from existing rows (right-click a table) - View table DDL — right-click → View Table Definition --- 🔑 Already Have PostgreSQL Installed? You remember your password Then you're 90% done — skip straight to [Step 4](#-step-4-create-your-first-connection) and connect. If the connection fails, make sure the server is running ([Step 2](#-step-2-verify-the-server-is-running)). You forgot your password 😅 This happens to everyone. The fix: temporarily tell PostgreSQL to stop asking for passwords, change the password, then turn authentication back on. It's the officially documented recovery procedure and takes ~5 minutes. > ⚠️ Note: while trust auth is active, anyone on your machine can connect as any user without a password. This is fine for a personal dev machine done quickly; think twice before doing it on a shared system. The steps below are for Windows (where this bites most often). macOS/Linux users: same idea, but your pg_hba.conf is usually at /etc/postgresql/16/main/pg_hba.conf (Debian/Ubuntu) or /usr/local/var/postgres/@PREFIX@/etc (Homebrew), and you restart with brew services restart postgresql@16 or sudo systemctl restart postgresql. 1. Open pg_hba.conf as administrator It lives at: C:\Program Files\PostgreSQL\16\data\pg_hba.conf (Adjust 16 to your version.) Open it with an editor launched as Administrator (e.g., Notepad → right-click → Run as administrator → File → Open). 2. Make a backup, then change the auth method Copy the file somewhere safe first. Then find the two host lines near the bottom and change scram-sha-256 to trust: # BEFORE host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 # AFTER host all all 127.0.0.1/32 trust host all all ::1/128 trust 3. Restart the PostgreSQL service Open PowerShell as administrator: Restart-Service postgresql-x64-16 4. Set the new password In a normal (non-admin) terminal: "C:\Program Files\PostgreSQL\16\bin\psql.exe" -h localhost -U postgres -d postgres -c "ALTER USER postgres WITH PASSWORD 'YourNewPassword123!';" You should see ALTER ROLE. 5. Restore pg_hba.conf and restart again Put the original lines back (scram-sha-256), save, then once more in the admin PowerShell: Restart-Service postgresql-x64-16 6. Verify PGPASSWORD='YourNewPassword123!' "C:\Program Files\PostgreSQL\16\bin\psql.exe" -h localhost -U postgres -d postgres -c "SELECT version();" If you get a version string back, plug the new password into your VS Code connection and you're in business. 🎉 --- 🛠️ Troubleshooting | Symptom | Likely cause & fix | |---|---| | connection refused in VS Code | The server isn't running. Start it via services.msc (Windows), brew services start postgresql@16 (macOS), or sudo systemctl start postgresql (Linux). | | password authentication failed | Wrong password for postgres. Use the [rescue procedure](#-already-have-postgresql-installed) above. | | Service won't start, event log shows could not access directory ... Permission denied | The service account lost permissions on the data directory (happens after Windows updates or cleanup tools). Fix with an admin terminal: icacls "C:\Program Files\PostgreSQL\16\data" /grant "postgres:(OI)(CI)F" /T then start the service. | | Port 5432 already in use | Another instance is running, or a stale process holds the port. Check with netstat -ano \| findstr 5432 (Windows) and stop the conflicting service, or change the port in postgresql.conf + your VS Code connection. | | psql is not recognized | The bin folder isn't on your PATH — see [Step 2](#-step-2-verify-the-server-is-running). New terminals only pick up PATH changes after reopening. | | Connection asks for password every time | In the extension's connection settings, the password field saves per-connection — re-save the connection with the password filled in. | --- 🎯 Wrapping Up You now have a full PostgreSQL setup without ever leaving VS Code: - ✅ PostgreSQL 16 installed and running as a service - ✅ A database explorer + query editor inside your editor - ✅ A recovery plan for the day you forget the password (it's okay, we all do) Useful links for going further: - PostgreSQL Official Docs — postgresql.org - PostgreSQL Tutorial (great for SQL basics) — postgresqltutorial.com - The extension's docs/issue tracker — cweijan/vscode-database-client Happy querying! 🐘 --- Thanks for reading! If this helped you, a ❤️ and a follow are much appreciated. Drop a comment if you hit an issue that isn't covered here.