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

Want to set up a professional data engineering environment on your Windows 11 machine? This guide shows you how to install PostgreSQL on Windows 11 using WSL2 (Windows Subsystem for Linux), combining the best of both worlds: Windows' user-friendliness and Linux's robust development capabilities. Learn to set up PostgreSQL for data engineering using WSL2.

Why Use WSL2 for PostgreSQL?

• Production-like environment: Mimic your production server's environment for more accurate testing.
• Better performance: Linux often outperforms Windows for database tasks.
• Fewer compatibility issues: Avoid potential conflicts with Windows-specific software.

This guide provides a step-by-step approach to setting up PostgreSQL inside a Linux environment on Windows 11. This is a crucial skill for any aspiring data engineer or analyst.

Prerequisites: Getting Ready for PostgreSQL

Before you begin, make sure you have the following:

• Windows 11: Home or Pro edition.
• Virtualization Enabled: Check your BIOS/UEFI settings.
• Admin Access: Required for installing software.
• Internet Connection: For downloading packages.
• Time: Allocate 15-20 minutes.

Step 1: Enable WSL2 and Install Ubuntu 22.04

WSL2 allows you to run a Linux distribution directly within Windows. Here’s how to get started:

1. Open PowerShell as Administrator.
2. Run the following command:

   wsl --install -d Ubuntu-22.04
   

   Copy
3. Explore other distributions (optional):

   wsl --list --online
   

   Copy

Need more help? Refer to the official Microsoft WSL installation guide.

Step 2: Install PostgreSQL and pgAdmin on Windows

This step provides a GUI-based tool to manage PostgreSQL.

1. Download the PostgreSQL Installer: Go to PostgreSQL Downloads for Windows.
2. Run the Installer:
   • Click "Next" and choose an installation directory (default is fine).
   • Keep the default components selected (PostgreSQL Server, pgAdmin, StackBuilder, Command Line Tools).
   • Set a strong password for the postgres superuser. Remember this password!
   • Keep the default port number (5432).
   • Keep the default locale.
3. pgAdmin Setup:
   • Open pgAdmin after installation.
   • Set a Master Password for pgAdmin.
   • Log in to your server using the username "postgres" and the password you set during installation.

Step 3: Install PostgreSQL Inside WSL Ubuntu

Now, let's install PostgreSQL within your WSL Ubuntu environment for command-line access and a production-like setup. Make sure you have completed step 1 first.

1. Update Package Lists:

   sudo apt update && sudo apt upgrade -y
   

   Copy
2. Install PostgreSQL:

   sudo apt install postgresql postgresql-contrib -y
   

   Copy
3. Verify Installation:

   psql --version
   

   Copy
   You should see the PostgreSQL version number.
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:

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

   Copy
7. Setup .bashrc to Default to Your User and Postgres DB

   • nano ~/.bashrc
     

     Copy
   • Add the following lines to the end of the file, replacing 'your_wsl_username' with your actual username:

     export PGUSER=your_wsl_username
     export PGDATABASE=postgres
     

     Copy
   • Save and close the file (Ctrl+X, Y, Enter).
   • Apply the changes:

     source ~/.bashrc
     

     Copy
   • Now you can simply type psql in the terminal to access the PostgreSQL CLI.
8. Configure PostgreSQL to Allow External Connections (For pgAdmin):

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

   Copy
   • Find the line "#listen_addresses = 'localhost'" and change it to remove the # at the beginning and set the value to '*'.

     listen_addresses = '*'
     

9. Update pg_hba.conf for External Authentication:

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

   Copy
   • Add the following line to the bottom:

     host all all 0.0.0.0/0 md5
     

10. Restart PostgreSQL:

    sudo service postgresql restart
    

    Copy
11. Find WSL IP Address:
    • From Windows PowerShell or CMD:

      wsl hostname -I
      

      Copy
    • Note the IP address (e.g., 172.27.94.100). You'll use this in pgAdmin.

Step 4: Add a Connection in pgAdmin on Windows

Connect pgAdmin on Windows to your PostgreSQL server running inside WSL.

1. Open pgAdmin.
2. Add New Server.
3. In the "General" tab:
   • Name: "WSL PostgreSQL"
4. In the "Connection" tab:
   • Host: (The IP address you found in Step 3, e.g., 172.27.94.100)
   • Port: 5432
   • Maintenance DB: postgres
   • Username: your_wsl_username
   • Password: Your PostgreSQL user password
   • Check "Save Password"
5. Click "Save"

Step 5: Connect from WSL and Prepare for Windows Tools

Verify your setup by connecting to PostgreSQL from within WSL.

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

Next Steps in Your Data Engineering Journey

Congratulations! You've successfully installed PostgreSQL on Windows 11 using WSL2. This setup provides a solid foundation for data engineering tasks.

In our next article, you'll learn how to:

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

Don't wait for the "perfect" moment. Start building your data engineering skills today!