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.
Scope
This blog covers the essential steps needed to set up and connect to PostgreSQL for users on Windows Subsystem for Linux Ubuntu.
Purpose
The purpose of this tutorial is to help developers and system administrators on how to:
- Access and manage PostgreSQL databases in a WSL environment.
By following this guide, readers will learn how to install pgAdmin 4 and establish a connection to their PostgreSQL database within WSL.
Setup Instructions
Follow these steps to access and manage PostgreSQL database in WSL:
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
ifconfigin WSL, or just uselocalhostif 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.
- Hostname/address: Enter the WSL Ubuntu IP address which can be obtained by running
- 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.
Well explained, thanks a lot.