DEV Community

John M. Kuria
John M. Kuria

Posted on

From Zero to Hero on psql: My Weekend Adventure Installing PostgreSQL on WSL 🐧🐘

#postgres #tutorial #beginners #database

Hey folks! 👋

So here's the deal — last weekend I decided I was done fighting with database installers that never quite worked right on Windows. A senior dev friend of mine said, "just use WSL and run it like you're on a real Linux box," and honestly? Best advice I got all semester. So grab your coffee (or chai, no judgment ☕), and let's walk through this together, step by step, exactly how I did it.

By the end of this post you'll have:

  • WSL running Ubuntu on your Windows machine
  • PostgreSQL installed and running
  • Your very own database user created
  • A clear understanding of that mysterious postgres default user everyone talks about
  • The ability to log into psql like a pro

Let's get into it.

Step 1: What Even Is WSL? (60-Second Explainer)

WSL stands for Windows Subsystem for Linux. It's basically Microsoft saying, "Hey, we know developers love Linux tools, so here's a real Linux kernel running right inside Windows — no dual boot, no clunky virtual machine, no drama."

For anyone learning backend development, databases, or DevOps, this is huge. Most real-world servers run Linux, so practicing in a Linux environment (even from your Windows laptop) is exactly the muscle memory you want to build.

Step 2: Installing WSL

This part is refreshingly simple these days. Microsoft made it a one-liner.

  1. Open PowerShell as Administrator. (Right-click the Start button → "Terminal (Admin)" or search "PowerShell," right-click, "Run as administrator.")
  2. Type this magic incantation: 
wsl --install
Enter fullscreen mode Exit fullscreen mode
  1. Hit Enter and let it do its thing. This installs WSL2 (the good version, with a full Linux kernel) along with Ubuntu as the default distro.
  2. Restart your computer when prompted. Yes, actually restart it — don't be that person who skips the restart and wonders why nothing works. 😅

Once your machine boots back up, Ubuntu should launch automatically. If it doesn't, just search "Ubuntu" in your Start Menu and open it.

First launch checklist:

  • It'll take a minute to finish setting up.
  • You'll be asked to create a UNIX username and password. This is your Ubuntu login — totally separate from your Windows account, and separate from PostgreSQL too (keep that in mind, it trips people up later).

💡 Pro tip: Pick a username you'll remember and a password you can type without staring at your keyboard. You'll use sudo a lot, and it'll ask for this password constantly.

Verify everything installed correctly by running this in PowerShell:

wsl --list --verbose
Enter fullscreen mode Exit fullscreen mode

You should see Ubuntu listed with VERSION 2. If you see version 1, don't panic — but it's worth upgrading (wsl --set-version Ubuntu 2) since WSL2 is faster and more compatible.

Step 3: Updating Ubuntu (Don't Skip This!)

Inside your Ubuntu terminal, run:

sudo apt update && sudo apt upgrade -y
Enter fullscreen mode Exit fullscreen mode

This refreshes the package lists and updates existing software. Think of it as Ubuntu clearing its throat before doing real work. It might take a few minutes — perfect time to stretch or refill that coffee.

Step 4: Installing PostgreSQL

Now for the main event. PostgreSQL is one of the most powerful, most respected open-source relational databases out there — used by companies from startups to giants like Instagram and Spotify.

Install it with:

sudo apt install postgresql postgresql-contrib -y
Enter fullscreen mode Exit fullscreen mode
  • postgresql is the core database engine.
  • postgresql-contrib adds handy extra modules and extensions you'll likely want later (things like additional data types and functions).

Once it's done installing, start the PostgreSQL service:

sudo service postgresql start
Enter fullscreen mode Exit fullscreen mode

And check that it's actually running:

sudo service postgresql status
Enter fullscreen mode Exit fullscreen mode

You should see something confirming it's active. If it's not running for any reason, sudo service postgresql start will get it going.

⚠️ Note for WSL users: Unlike a real Linux server, PostgreSQL won't auto-start when you open a new WSL session — you'll need to run that service postgresql start command each time you reopen your terminal (or set up a startup script, but that's a story for another day).

Step 5: Meet postgres — The Default User Everyone's Confused About

Here's where a lot of students get tripped up, so let's clear the fog. 🌫️

When PostgreSQL installs, it automatically creates:

  1. A Linux system user called postgres
  2. A PostgreSQL database role also called postgres

This postgres role is the superuser of your database — it has full administrative rights over everything: creating databases, creating other users, deleting things, all of it. Think of it like the "root" or "admin" account of your PostgreSQL world.

By default, PostgreSQL is set up so that only the Linux postgres user can log in as the postgres database role, using something called "peer authentication" — basically, PostgreSQL checks "hey, is your Linux username the same as the database username you're trying to log in as?" If yes, you're in, no password needed (locally, at least).

This is why you'll often see people typing this to get into the database for the first time:

sudo -i -u postgres
Enter fullscreen mode Exit fullscreen mode

This switches you (temporarily) into the Linux postgres user, which then lets you access the database postgres role seamlessly.

Step 6: Logging Into psql for the First Time

psql is PostgreSQL's command-line interface — your direct line of communication with the database engine. Let's log in.

sudo -i -u postgres
psql
Enter fullscreen mode Exit fullscreen mode

If everything worked, your terminal prompt should now look like this:

postgres=#
Enter fullscreen mode Exit fullscreen mode

That # symbol means you're logged in as a superuser. Congrats — you're officially inside PostgreSQL! 🎉

To exit psql at any time, type:

\q
Enter fullscreen mode Exit fullscreen mode

Step 7: Creating Your Own User (Because You Shouldn't Live as postgres Forever)

Working as the superuser for everything isn't great practice — even for personal projects. Let's create a proper user for yourself.

While still logged into psql as postgres, run:

CREATE USER student_dev WITH PASSWORD 'yourStrongPassword123';
Enter fullscreen mode Exit fullscreen mode

Replace student_dev with whatever username you like, and obviously pick a real password (not literally that one 😉).

Want to give this user the ability to create their own databases too? Add that privilege:

ALTER USER student_dev CREATEDB;
Enter fullscreen mode Exit fullscreen mode

Now let's create a database owned by this new user:

CREATE DATABASE student_dev_db OWNER student_dev;
Enter fullscreen mode Exit fullscreen mode

Exit psql:

\q
Enter fullscreen mode Exit fullscreen mode

And exit back out of the postgres Linux user:

exit
Enter fullscreen mode Exit fullscreen mode

Step 8: Logging In as Your New User

Now the moment of truth — let's log in as you, not as the almighty postgres.

psql -U student_dev -d student_dev_db -h localhost
Enter fullscreen mode Exit fullscreen mode

Here's what those flags mean:

  • -U → the username you want to log in as
  • -d → the database you want to connect to
  • -h localhost → connect over the network (even though it's local), which forces PostgreSQL to ask for a password instead of relying on peer authentication

Enter the password you set earlier, and boom — you're in as your own user, scoped to your own database. This is the setup you'll actually want for real projects.

Quick Recap Cheat Sheet 📝

Task Command
Install WSL + Ubuntu wsl --install
Update Ubuntu sudo apt update && sudo apt upgrade -y
Install PostgreSQL sudo apt install postgresql postgresql-contrib -y
Start PostgreSQL sudo service postgresql start
Switch to postgres Linux user sudo -i -u postgres
Open psql psql
Create new DB user CREATE USER name WITH PASSWORD 'pass';
Create new database CREATE DATABASE dbname OWNER name;
Log in as your user psql -U name -d dbname -h localhost
Exit psql \q

Final Thoughts

Honestly, once I stopped fighting Windows-native installers and just leaned into WSL, everything clicked. It genuinely feels like working on a real Linux server — which, as a student prepping for internships and real-world dev work, is exactly the experience worth building.

Next up on my list: connecting this PostgreSQL instance to a Node.js or Django app, and maybe exploring pgAdmin for a GUI view of everything we just built by hand. If that sounds interesting, let me know and I'll write that one up next!

Until then — happy querying! 🐘💻

Got stuck somewhere or found a better way to do this? Drop a comment below — half the fun of learning this stuff is comparing notes with fellow students.

Top comments (0)