DEV Community

Cover image for Setting Up PostgreSQL in VS Code — From Download to Your First Query (Plus Password Rescue)
Ikegbo Ogochukwu
Ikegbo Ogochukwu

Posted on

Setting Up PostgreSQL in VS Code — From Download to Your First Query (Plus Password Rescue)

#postgressql #vscode #sql #database

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

👉 https://www.postgresql.org/download/

Windows (EDB Installer)

  1. Go to the Windows installers page: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
  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. |

  1. 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
Enter fullscreen mode Exit fullscreen mode

(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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Linux 

sudo systemctl status postgresql
sudo -u postgres pg_isready
Enter fullscreen mode Exit fullscreen mode

🧩 Step 3: Install a PostgreSQL Extension in VS Code

Open VS Code → Extensions (Ctrl+Shift+X) and install:

PostgreSQL Client by Weijan Chen
👉 https://marketplace.visualstudio.com/items?itemName=cweijan.vscode-postgresql-client2

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 |

  1. 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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 and connect. If the connection fails, make sure the server is running (Step 2).

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
Enter fullscreen mode Exit fullscreen mode

(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
Enter fullscreen mode Exit fullscreen mode

3. Restart the PostgreSQL service

Open PowerShell as administrator:

Restart-Service postgresql-x64-16
Enter fullscreen mode Exit fullscreen mode

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!';"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

6. Verify

PGPASSWORD='YourNewPassword123!' "C:\Program Files\PostgreSQL\16\bin\psql.exe" -h localhost -U postgres -d postgres -c "SELECT version();"
Enter fullscreen mode Exit fullscreen mode

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 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 \
{% raw %}psql is not recognized The bin folder isn't on your PATH — see Step 2. 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:

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.

Top comments (0)