Install PostgreSQL on Windows 11 with WSL2: The Data Engineer's Guide

Ready to dive into data engineering but want the best of both Windows and Linux? This guide shows you how to set up PostgreSQL on Windows 11 using WSL2 (Windows Subsystem for Linux). Enjoy the user-friendliness of Windows with the power and compatibility of a Linux environment for your database needs.

TL;DR: Install WSL2 with Ubuntu on Windows 11, then set up PostgreSQL within Ubuntu. This setup allows both internal and external connections, perfect for data engineering projects.

Why Use PostgreSQL with WSL2 on Windows?

Best of Both Worlds: Windows for ease of use, Linux for server-like performance.
Production-Ready Environment: Mimic your production environment for accurate testing.
No Need for Dual Booting: Avoid the hassle of switching between operating systems.
Improved Performance: Run PostgreSQL in a Linux environment for optimal speed.

Modern Data Engineering stacks use PostgreSQL as the standard for relational databases. While PostgreSQL can be installed directly on Windows, data engineers often prefer running it in Linux for better performance and fewer compatibility issues.

What You'll Achieve

A fully functional PostgreSQL server running within WSL2 on Windows 11.
External connections configured, allowing access from Windows tools like pgAdmin.
A production-like environment for local development and testing.

Prerequisites

Before getting started with installing PostgreSQL on Windows 11, make sure you have the following:

Windows 11: Home or Pro edition.
Virtualization Enabled: Check your BIOS/UEFI settings.
Admin Access: Required for installing WSL2 and PostgreSQL.
Internet Connection: For downloading necessary packages during the install PostgreSQL on Windows 11 process.
Time: Allocate about 15-20 minutes.

Step-by-Step Guide: PostgreSQL on Windows 11

Step 1: Enable WSL2 and Install Ubuntu

Open PowerShell as Administrator.
Run the installation command:
```
wsl --install -d Ubuntu-22.04
```
This command installs Ubuntu 22.04, a popular Linux distribution, within WSL2.
Explore other distributions (optional):
```
wsl --list --online
```

Step 2: Install PostgreSQL on Windows

Download the Installer: Go to the PostgreSQL downloads page and select the latest version for Windows.
Run the Installer:
- Click "Next" through the initial screens.
- Choose an installation directory (the default is fine).
- Select components: Keep the defaults (PostgreSQL Server, pgAdmin, StackBuilder, Command Line Tools).
- Set a Password for the superuser ("postgres"). Remember this password!
- Set the Port Number: The default is 5432.
- Set the Locale: The default is usually OK.
- Complete the Installation.

Step 3: Install PostgreSQL on WSL2 Ubuntu

Now, let's install PostgreSQL within your Ubuntu environment in WSL2 for optimal performance.

Update Package Lists:
```
sudo apt update && sudo apt upgrade -y
```

Install PostgreSQL:

sudo apt install postgresql postgresql-contrib -y

Verify Installation:
```
psql --version
```
You should see the PostgreSQL version number displayed.
Start the PostgreSQL Service:
```
sudo service postgresql start
```
Check the Service Status:
```
sudo service postgresql status
```

Create a PostgreSQL User (Matching Your WSL Username):

whoami # Get your WSL username
sudo -u postgres createuser --interactive
# Follow the prompts to create a new user (e.g., kubona)

Set up .bashrc for Default PostgreSQL User:
```
nano ~/.bashrc
```
Add the following lines at the end of the file:
```
export PGUSER=your_wsl_username
export PGDATABASE=postgres
```
Save and exit the file (Ctrl+X, Y, Enter), then run:
```
source ~/.bashrc
```
Now you can run psql in the terminal to access the PostgreSQL CLI.
Configure PostgreSQL for External Connections: * Edit postgresql.conf:

```bash

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

* Find the line `#listen_addresses = 'localhost'` and change it to `listen_addresses = '*'` (remove the `#`).
*   **Update pg_hba.conf**

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

    *   Add this line at the bottom:

    ```bash
    host all all 0.0.0.0/0 md5
    ```

    *   **Restart PostgreSQL to Apply Changes**

    ```bash
    sudo service postgresql restart
    ```

**Important:** This allows connections from any IP address. For production environments, restrict this to specific IP ranges for enhanced security.

9. Find WSL IP Address:

From Windows PowerShell or CMD:

```powershell
wsl hostname -I
```

Note the IP address (e.g., 172.27.94.100). You'll use this to connect from pgAdmin.

Step 4: Add Connection in pgAdmin on Windows

Open pgAdmin on Windows.
Create a New Server Connection: Right-click on "Servers" and select "Create" -> "Server".
General Tab:
- Name: WSL PostgreSQL
Connection Tab:
- Host: (The WSL IP address you found earlier, e.g., 172.27.94.100)
- Port: 5432
- Maintenance DB: postgres
- Username: Your WSL username (e.g., kubona)
- Password: Your PostgreSQL user password
- ✅ Save Password
Click "Save." You should now be connected to your PostgreSQL server running in WSL2.

Step 5: Test the Connection

Connect Through WSL:
```
sudo -u postgres psql
```

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

You should see the test data displayed successfully, confirming that installing PostgreSQL on Windows 11 has worked.

Conclusion

Congratulations on setting up PostgreSQL on Windows 11 using WSL2! You've created a powerful development environment that combines the best of both worlds. Now you're ready to start building your data engineering projects.

Next Steps

Configure DBeaver: Learn how to use DBeaver as a GUI client for PostgreSQL.
Set up Database Users and Permissions: Enhance your database security.
Create Your First Schema: Start designing your database structure.

Get started today to install PostgreSQL on Windows 11 and start your data engineering journey!