Setting Up PostgreSQL Locally on WSL: A Practical Guide

PostgreSQL WSL Linux DevOps

Setting Up PostgreSQL Locally on WSL: A Practical Guide

When you're starting a brand-new project or want to quickly prototype an idea, the easiest way to move fast is by setting up your database locally. Before committing to a cloud-hosted PostgreSQL service or containerizing your environment, getting PostgreSQL running on your local machine can help unblock early development.

Setting this up is straightforward in most Linux environments. Personally, I use Windows Subsystem for Linux (WSL) to combine the flexibility of Linux with the convenience of Windows. In this guide, I’ll walk through the exact steps to get PostgreSQL running on WSL (Ubuntu), so you can test, build, and iterate without external dependencies.

Step 1: Update Your WSL Ubuntu Packages

Before installing anything, make sure your system is up to date.

sudo apt update
sudo apt upgrade -y

This ensures that your package manager is aware of the latest available versions.

Step 2: Install PostgreSQL

To install PostgreSQL and the standard utilities:

sudo apt install postgresql postgresql-contrib -y

This installs the PostgreSQL server and commonly used extensions like pg_stat_statements, pg_trgm, etc.

Step 3: Start PostgreSQL and Access the CLI

Start the PostgreSQL service:

sudo service postgresql start

Switch to the postgres user (PostgreSQL runs under this user by default):

sudo -i -u postgres

Enter the PostgreSQL interactive shell:

psql

Once inside, you’ll see a prompt like postgres=#.

Step 4: Create a New User and Database

You can now create a user and database for your project.

CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydb OWNER myuser;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
\q
exit

Now you’ve created a development user and database, without touching the default postgres role or database.

Step 5: Connect Using psql Locally

To test that everything works:

psql -U myuser -d mydb -h localhost

If authentication is successful, you’ll get a prompt like mydb=>.

If you get an error about authentication, see the next section on pg_hba.conf.

Step 6 (Optional): Configure for Password Authentication

By default, PostgreSQL on Ubuntu uses peer authentication locally (i.e., it expects the system user to match the PostgreSQL user). To enable password-based logins, do the following:

  1. Open the pg_hba.conf file: 
    sudo nano /etc/postgresql/*/main/pg_hba.conf
  2. Find this line: 
    local   all             all                                     peer
    Change it to: 
    local   all             all                                     md5
  3. Restart PostgreSQL: 
    sudo service postgresql restart

Now you can use password authentication even for local CLI access.

Step 7 (Optional): Enable GUI Access from Windows (pgAdmin, DBeaver, etc.)

  1. Edit postgresql.conf: 
    sudo nano /etc/postgresql/*/main/postgresql.conf
    Find and change: 
    listen_addresses = 'localhost'
  2. Ensure pg_hba.conf has this line (or add it if missing): 
    host    all             all             127.0.0.1/32            md5
  3. Restart the service: 
    sudo service postgresql restart
  4. In your GUI tool:
    • Host: 127.0.0.1
    • Port: 5432
    • Username: myuser
    • Password: mypassword
    • Database: mydb

Make sure your WSL version (WSL2 or newer) supports localhost forwarding, which it should by default.

Optional: Start PostgreSQL Automatically When Opening WSL

To avoid having to manually start PostgreSQL every time you open a terminal, you can add this line to your .bashrc or .zshrc:

sudo service postgresql start > /dev/null

Or use a shell alias:

alias startdb='sudo service postgresql start'

This way, you can just run startdb to bring up the service.

Common Troubleshooting Tips

IssueSolution
psql: could not connect to serverMake sure PostgreSQL is running: sudo service postgresql start
Authentication errorMake sure you've set up password authentication in pg_hba.conf
GUI client can't connectEnsure listen_addresses is set to localhost, and PostgreSQL is listening on port 5432

Conclusion

Running PostgreSQL locally on WSL is a quick and reliable way to bootstrap your backend development. It behaves just like any Linux installation, but gives you the flexibility to develop from within your preferred Windows tools. This setup is ideal for rapid prototyping, local testing, or even CI-like workflows.

While this article focused on Ubuntu under WSL, the same general approach works across most Debian-based Linux environments. With a working local PostgreSQL instance, you're now free to iterate, debug, and explore without worrying about cloud latency or credentials.

References

You can find my Resume here!

My Resume