cweijan

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.