The Ultimate Guide to Running PostgreSQL on Windows 11 with WSL2

TL;DR: Embark on your data engineering journey by installing WSL2 with Ubuntu on Windows 11 and setting up PostgreSQL in a Linux environment. This setup, which accepts both internal and external connections, is your launchpad into the world of data engineering.

Why PostgreSQL and Why Linux on Windows?

Modern data engineering leans heavily on PostgreSQL as the go-to relational database. While you can install PostgreSQL directly on Windows, most pros prefer running it in Linux. Here's why:

• Performance: Linux often delivers better performance for database workloads.
• Production Similarity: Linux mirrors production environments, reducing surprises when deploying your projects.
• Fewer Compatibility Issues: Linux tends to play nicer with other data engineering tools.

Windows + Linux: The Best of Both Worlds with WSL2

Windows is popular for its user-friendliness, but Linux dominates the server landscape. Dual booting or getting a separate Linux machine can be a hassle. Enter WSL2 (Windows Subsystem for Linux).

WSL2 gives you:

• Windows' familiar interface
• Linux's robust developer environment

By the end of this guide, you'll have:

• A fully functional PostgreSQL server running in WSL2
• External connectivity configured for Windows tools like pgAdmin or DBeaver
• A production-like environment for local development and testing

Let's turn your Windows 11 machine into a professional-grade PostgreSQL development environment!

Prerequisites: What You Need to Get Started

Before diving in, make sure you have:

• Windows 11 Home or Pro edition (Home works just fine)
• Virtualization enabled in your BIOS/UEFI settings
• Administrative access to your Windows machine
• Internet connection for downloading packages
• About 15-20 minutes

Step-by-Step: Setting Up PostgreSQL on Windows 11 via WSL2

Step 1: Enable WSL2 & Install Ubuntu-22.04

1. Open PowerShell as Administrator

2. Run the following command:

   wsl --install -d Ubuntu-22.04
   

   Copy

3. To explore other Linux distributions:

   wsl --list --online
   

   Copy

Refer to the official Microsoft documentation for a complete guide on WSL2 setup.

Step 2: Install PostgreSQL on Windows

1. Download: Go to the PostgreSQL Windows download page.

2. Click "Download the installer" (redirects to EnterpriseDB).

3. Choose the latest stable version.

4. Run the installer:

   • Click Next. Choose an installation directory (default is fine).
   • Select Components (keep defaults: PostgreSQL Server, pgAdmin, StackBuilder, Command Line Tools).
   • Set a Password for the PostgreSQL superuser (postgres). (Important! Remember this password.)
   • Set Port Number: default is 5432.
   • Set Locale: default is OK.
   • Install.
   • After installation, Postgresql starts as a Windows service automatically.
   • Ignore the StackBuilder Wizard

5. pgAdmin Setup:

   • Open pgAdmin.
   • Set a Master Password.
   • Login to your server using:
     • Username: postgres
     • Password: (the one you set during install)

Step 3: Install PostgreSQL on WSL Ubuntu

1. Update your package lists:

   sudo apt update && sudo apt upgrade -y
   

   Copy

2. Install PostgreSQL and its extensions:

   sudo apt install postgresql postgresql-contrib -y
   

   Copy

3. Verify installation:

   psql --version
   

   Copy

   You should see output like psql (PostgreSQL) 16.8 (version may vary).

4. Start PostgreSQL service:

   sudo service postgresql start
   

   Copy

5. Check status:

   sudo service postgresql status
   

   Copy

6. Create a PostgreSQL User Matching Your WSL Username

   • Get your WSL username:

   whoami
   sudo -u postgres createuser --interactive
   # Enter username: kubona
   # Shall the new role be a superuser? (y/n): y
   

   Copy

7. Setup .bashrc to Default to postgres DB with Your User

   • Open up the .bashrc using the nano editor

   nano ~/.bashrc
   

   Copy
   • Add the following at the bottom:

   export PGUSER=kubona
   export PGDATABASE=postgres
   

   Copy
   • Apply changes by exiting the nano editor( Ctrl +X , Y, Enter ) then run

   source ~/.bashrc
   

   Copy
   • Now you can run the psql command on the terminal to access the postgres CLI interface

   psql
   

   Copy

8. Edit postgresql.conf to Allow External Connections (for pgAdmin)

   • Edit the config file:

   sudo nano /etc/postgresql/16/main/postgresql.conf
   

   Copy
   • Find and set the line starting with # listen_addresses = ‘localhost’ and then change it to ( Remove the # at the beginning;

    listen_addresses = '*'
   

9. Update pg_hba.conf to Allow External Authentication

   • Edit pg_hba.conf:

   sudo nano /etc/postgresql/16/main/pg_hba.conf
   

   Copy
   • Add this line at the bottom:

   host all all 0.0.0.0/0 md5
   

10. Restart PostgreSQL to Apply Changes

    sudo service postgresql restart
    

    Copy

11. Find WSL IP Address to Use in pgAdmin

    • Get WSL's IP address (from Windows PowerShell or CMD):

    wsl hostname -I
    

    Copy
    • This might return something like 172.27.94.100. Use this as the host in pgAdmin.

Step 4: Add Connection in pgAdmin on Windows

1. Open pgAdmin
2. Go to Add New Server
3. In the General tab:
   • Name: WSL PostgreSQL
4. In the Connection tab:
   • Host: (e.g., 172.27.94.100)
   • Port: 5432
   • Maintenance DB: postgres
   • Username: kubona
   • Password: yourpassword
   • ✅ Save Password

Click Save — it should connect! You can now use pgAdmin to manage your PostgreSQL database on WSL2 directly from Windows!

Step 5: Connect from WSL and Prepare for Windows Tools

1. Connect to PostgreSQL as the postgres user:

   sudo -u postgres psql
   

   Copy

2. Create a test database:

   CREATE DATABASE testdb;
   \c testdb
   CREATE TABLE test (id serial PRIMARY KEY, name varchar(50));
   INSERT INTO test (name) VALUES ('Test connection successful!');
   SELECT * FROM test;
   \q
   

   Copy

Conclusion: You've Done It!

Give yourself a pat on the back! You've successfully set up a professional PostgreSQL environment on Windows 11 using WSL2. This hybrid approach gives you the development environment used by data engineers while keeping the Windows features you enjoy. You're now ready to utilize powerful Windows tools like pgAdmin to interact with your PostgreSQL database running smoothly within the Linux subsystem.

Next Steps in Our Data Engineering Foundations Series

Now that you have PostgreSQL running on WSL2, our next article will show you how to:

• Configure DBeaver as your PostgreSQL GUI client
• Set up database users and permissions
• Create your first data engineering project schema

Timing will never be perfect – start today! Share your questions, challenges, or recommendations in the comments section below.