How to Access PostgreSQL in Ubuntu WSL with pgAdmin 4

Working with databases through a command line interface can be efficient, but sometimes you may prefer using a graphical user interface (GUI) to manage your databases. If you have PostgreSQL installed on your Ubuntu Windows Subsystem for Linux (WSL) and want to access it using a GUI client from your Windows environment, follow these organized steps.

Step-by-Step Guide

Step 1. Install a PostgreSQL Client UI Tool

Before you do anything else, you need a GUI client that can connect to PostgreSQL. For this guide, we will use pgAdmin as it is user-friendly and widely utilized. Install pgAdmin directly on your Windows host machine, not within WSL.

Go to the pgAdmin website and download the version suitable for your system.

Step 2. Configure PostgreSQL for Remote Access

By default, PostgreSQL allows only local connections. To enable remote access, you will need to adjust its configuration.

Edit the postgresql.conf File

Open a terminal in WSL and run:

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

Replace <version> with the actual version number of PostgreSQL you are using.

Find the line containing listen_addresses and change it to:

listen_addresses = '*'

This alteration tells PostgreSQL to listen for connections on all network interfaces as shown below:

Edit the pg_hba.conf File

Still within the terminal, open the pg_hba.conf file using:

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

Add the following line at the end of the file to permit connections from any IP address:

host all all 0.0.0.0/0 md5

Modify this entry if you prefer to apply more stringent IP address filters as shown below:

Restart PostgreSQL Service

After updating the configuration files, restart the PostgreSQL service to enact your changes:

sudo service postgresql restart

Step 3. Configure pgAdmin

Now, proceed to set up pgAdmin on your Windows environment for database management.

Launch pgAdmin and navigate to “Register New Server” to start setting up a connection to your PostgreSQL server.
In the General tab, assign a meaningful name to your server connection such as (e.g. PostgreSQL Local).
Switch to the Connection tab and input the following details:
- Hostname/address: Enter the WSL Ubuntu IP address which can be obtained by running ifconfig in WSL, or just use localhost if pgAdmin is on the same machine as WSL.
- Username and Password: Use the credentials established during the PostgreSQL installation process.
- Port: The port number should be the same as default 5432.
Confirm and save your settings by clicking “Save”.

Step 4. Successful Connection between pgAdmin 4 to PostgreSQL on WSL

Here’s a screenshot that shows the successful connection between pgAdmin 4 and PostgreSQL on WSL Ubuntu. Note that pgAdmin 4 has established a successful connection to the PostgreSQL database, confirming that the configuration steps outlined earlier were done correctly.

Please Note:
It’s important to keep your Ubuntu WSL open/running continuously when using pgAdmin to query Postgres SQL databases installed on Ubuntu WSL. Otherwise, you may encounter connectivity issues with Postgres SQL, potentially interrupting your query.

Conclusion

Now that we’ve wrapped up the configuration process, you should have access to PostgreSQL on your Ubuntu WSL instance via pgAdmin’s graphical interface. Take advantage of this robust GUI client to manage your database and perform operations with ease.

For more guides on SQL joins and conditional statements, visit this blog.