[How to] Install and Connect to PostgreSQL from WSL in Windows

How to series

Welcome to this guide on setting up a development environment with PostgreSQL in the Windows Subsystem for Linux (WSL). This approach is highly recommended for developers as it combines the robust, Linux-native experience of PostgreSQL with the convenience of your Windows machine, allowing you to manage your database with powerful Windows-based tools like DBeaver or any GUI Tools of your choice.

Step 1: Install PostgreSQL in WSL

The first step is to get PostgreSQL up and running inside your WSL environment. This guide assumes you are using a Debian-based distribution like Ubuntu.

• Open your WSL terminal (e.g., Ubuntu).
• Update the package list to ensure you’re installing the latest version.

sudo apt update

• Install PostgreSQL and its extensions using the following command.

sudo apt install postgresql postgresql-contrib

postgresql: The main server package.
postgresql-contrib: A package of useful utilities and extensions.

Step 2: Set a Password for the postgres User

By default, the postgres superuser has no password, which is a security risk and prevents external connections. You need to set one.

• Switch to the postgres user to access the database administration tools.

sudo -i -u postgres

• Access the PostgreSQL command-line prompt

psql

• Set a new password for the postgres user. Replace your_new_password with a strong, secure password.

ALTER USER postgres WITH PASSWORD 'your_new_password';

• Exit the psql prompt and return to your regular WSL user

==> \q
==> exit

Step 3: Allow External Connections from Windows

For your Windows applications (like DBeaver) to connect to the database in WSL, you must configure PostgreSQL to listen for external connections and update its authentication rules.

Configure postgresql.conf

• Open the PostgreSQL configuration file. The path may vary slightly depending on your installed version.
  (e.g., sudo nano /etc/postgresql/14/main/postgresql.conf)

sudo nano /etc/postgresql/<version>/main/postgresql.conf

• Change the listen_addresses line. Find the line that looks like

#listen_addresses = 'localhost',

• Uncomment it by removing the #, and change the value to ‘*’ to accept connections from all IP addresses.

listen_addresses = '*'

Configure pg_hba.conf

• Open the client authentication file.

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

• Add a new entry to allow connections from your Windows IP address.
• Add the following line at the bottom of the file to allow all hosts to connect to all databases as the postgres user with password authentication.

host    all             postgres        0.0.0.0/0               md5

• Save the changes and exit (Ctrl+X, then Y, then Enter).
• Restart the PostgreSQL service to apply the new configurations.

sudo service postgresql restart

Step 4: Connect with DBeaver on Windows

Now that your server is configured to accept connections, you can set up DBeaver.

• Find your WSL IP Address: Open a Command Prompt or PowerShell window on your Windows machine and run the following command to get the IP address of your WSL instance.

wsl hostname -I

Open DBeaver and create a new connection.
Go to Database -> New Database Connection.
Select PostgreSQL and click Next.
Enter the Connection Details.
Host: Use the IP address from the previous step.
Port: 5432 (default).
Database: Enter the name of your desired database (e.g., postgres or a new database you created).
Username: postgres
Password: The password you set in Step 2.
Click Test Connection… to confirm it works, and then click Finish.

You are now successfully connected to your PostgreSQL database in WSL from DBeaver on Windows. You can use DBeaver’s graphical interface to manage your databases, tables, and data. This powerful combination gives you the best of both worlds: a native Linux database server and the familiar, feature-rich tools of Windows. Happy coding!