Install PostgreSQL on Windows 11: A Data Engineer's Guide Using WSL2

Modern data engineering relies heavily on PostgreSQL as a relational database standard. While direct installation on Windows is possible, most professionals prefer running it in Linux for enhanced performance and production environment similarity.

This guide shows you how to set up a PostgreSQL development environment on Windows 11 using WSL2 (Windows Subsystem for Linux). You get the best of both worlds: Windows' user-friendliness, and Linux's robust development environment.

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

• A PostgreSQL server running in WSL2.
• External connectivity configured for Windows tools.
• A production-like environment for local testing.

Prerequisites: Getting Ready to Install PostgreSQL

Before you start, ensure you have the following:

• Windows 11 (Home or Pro).
• Virtualization enabled in your BIOS/UEFI settings.
• Administrative access to your Windows machine.
• Internet connection.
• 15-20 minutes of your time.

Step-by-Step Guide: PostgreSQL Setup on Windows 11

Step 1: Enable WSL2 and Install Ubuntu-22.04

First, open PowerShell as Administrator. Then, run the following command to install Ubuntu-22.04:

wsl --install -d Ubuntu-22.04

To explore other available Linux distributions, use:

wsl --list --online

Microsoft provides a detailed guide on setting up WSL2 and Ubuntu-22.04 if needed.

Step 2: Install PostgreSQL on Windows

1. Download: Head to the PostgreSQL downloads page, and select the Windows version.
2. Run Installer: Click "Next," and choose an installation directory (the default is fine).
3. Select Components: Keep the defaults: PostgreSQL Server, pgAdmin, StackBuilder, Command Line Tools.
4. Set Password: Create a password for the PostgreSQL superuser ("postgres").
5. Configure: Keep the default Port Number (5432) and Locale.
6. Install: After installation PostgreSQL starts as a Windows service automatically.

You can ignore the StackBuilder Wizard.

PgAdmin Setup

After the installation, you get pgAdmin installed.

1. Open pgAdmin.
2. Set a Master Password.
3. Log in to your server using:
   • Username: postgres
   • Password: The one you set during install

Step 3: Install PostgreSQL on WSL Ubuntu

Now, let's set up PostgreSQL within our WSL environment. This assumes you've completed Step 1 (WSL2 and Ubuntu-22.04 installation).

1. Update Packages: Keep your system up-to-date.

   sudo apt update && sudo apt upgrade -y
   

   Copy

2. Install PostgreSQL: Install PostgreSQL and extensions.

   sudo apt install postgresql postgresql-contrib -y
   

   Copy

3. Verify Installation: Check the PostgreSQL version.

   psql --version
   

   Copy

4. Start Service: Start the PostgreSQL service.

   sudo service postgresql start
   

   Copy

5. Check Status: Verify the service is running.

   sudo service postgresql status
   

   Copy

Create a PostgreSQL User Matching Your WSL Username

1. Get Username: Find your WSL username.

   whoami
   

   Copy

2. Create User: Use the username to create a new PostgreSQL user

   sudo -u postgres createuser --interactive
   

   Copy

Setup .bashrc to Default to Postgres DB With Your User

1. Open .bashrc: Open the .bashrc file using the nano editor

   nano ~/.bashrc
   

   Copy

2. Add Configuration:

   export PGUSER=your_user
   export PGDATABASE=postgres
   

   Copy

3. Apply Changes:

   source ~/.bashrc
   

   Copy

Now, you can run the psql command to access the postgres CLI interface.

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

1. Edit Config File:

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

   Copy

2. Uncomment listen_addresses and set it to '*'.

   listen_addresses = '*'
   

Update pg_hba.conf to Allow External Authentication

1. Edit pg_hba.conf:

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

   Copy

2. Add this line at the bottom:

   host all all 0.0.0.0/0 md5
   

Restart PostgreSQL to Apply Changes

sudo service postgresql restart

Find WSL IP Address to Use in pgAdmin

1. Get IP Address: (from Windows PowerShell or CMD):

   wsl hostname -I
   

   Copy

Use this IP address as the host in pgAdmin.

Step 4: Add Connection in pgAdmin on Windows

1. Open pgAdmin: Launch pgAdmin on Windows.
2. Add New Server:
   • General Tab:
     • Name: WSL PostgreSQL
   • Connection Tab:
     • Host: (e.g., 172.27.94.100)
     • Port: 5432
     • Maintenance DB: postgres
     • Username: your_user
     • Password: your_password
     • Enable "Save Password"

Click "Save" to connect.

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

You should see your test data displayed.

Conclusion: Your PostgreSQL Journey Begins

You've successfully set up a professional PostgreSQL environment on Windows 11 using WSL2. This setup combines the advantages of both Windows and Linux for data engineering development. This approach to install PostgreSQL gives you a versatile environment!

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

Start your journey today to kickstart your data engineering project! Feel free to ask questions or share recommendations in the comments section below.