DEV Community

MJ-O
MJ-O

Posted on

Connecting PostgreSQL on a Linux (WSL) Server to DBeaver

#database #linux #postgres #tutorial

INTRODUCTION

PostgreSQL is a relational database management system used to store and manage structured data. When installed inside Windows Subsystem for Linux (WSL), it runs in a Linux environment on a Windows machine.DBeaver is a graphical database management tool that supports PostgreSQL. Connecting DBeaver to PostgreSQL running in WSL allows users to manage the database using a nice visual interface instead of only terminal commands.

Prerequisites

  • PostgreSQL installed and running inside WSL
  • DBeaver installed on your Windows machine
  • Access credentials for the PostgreSQL user (e.g., postgres user and password)

STEP 1: Confirm Installation of PostgreSQL

  • To confirm if postgreSQL has been installed and is running in WSL, Inside the WSL terminal, enter the following command 
psql --version
Enter fullscreen mode Exit fullscreen mode

If installed, it displays the postgreSQL version

  • Then check if the service is running using the following command 
sudo systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode
  • If it is not running, use the following command to start the service: 
sudo systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

STEP 2 : Create or Verify Database

  • After the postgres service is up and running,switch to the postgres account on your server: 
sudo -i -u postgres
Enter fullscreen mode Exit fullscreen mode
  • The following command will log you into the PostgreSQL prompt where you can interact with the database management system: 
psql
Enter fullscreen mode Exit fullscreen mode
  • Use the following command to show a list of databases available 
\l
Enter fullscreen mode Exit fullscreen mode
  • Suppose your database is not in the list, create one using the following command: 
CREATE DATABASE <database_name>
Enter fullscreen mode Exit fullscreen mode

NOTE: Replace the name with the required database name

STEP 3: Configure Authentication
By default, PostgreSQL on Ubuntu/WSL uses peer authentication, which works in the Linux terminal but does not work with external tools like DBeaver.Editing the method to md5 enables password-based authentication, allowing external tools to connect successfully.

  • To edit the authentication file, use the following commands: 
cd /etc/postgresql/16/main
Enter fullscreen mode Exit fullscreen mode

then:

sudo nano pg_hba.conf
Enter fullscreen mode Exit fullscreen mode
  • Find: local all postgres peer -Change it to: local all postgres md5
  • Also ensure these lines exist and are configured correctly: host all all 127.0.0.1/32 md5 host all all ::1/128 md5
  • Save and restart PostgreSQL: 
sudo service postgresql restart
Enter fullscreen mode Exit fullscreen mode

STEP 4: Set a Password for the PostgreSQL User

  • Log into postgres on your server: 
sudo -i -u postgres
Enter fullscreen mode Exit fullscreen mode
  • Set the password: 
ALTER USER postgres WITH PASSWORD 'yourpassword';
Enter fullscreen mode Exit fullscreen mode
  • Save and exit: 
\q
Enter fullscreen mode Exit fullscreen mode

STEP 5: Configure PostgreSQL to Listen on All Interfaces
By default, PostgreSQL listens only on localhost.
In WSL 2, this may prevent DBeaver (running in Windows) from connecting properly because WSL operates on a separate virtual network interface.

  • Edit the configuration file: 
sudo nano /etc/postgresql/*/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode
  • Find: listen_addresses = 'localhost' -Change it to: listen_addresses = '*'
  • Save and restart PostgreSQL

STEP 6: Obtain the WSL IP Address

  • In WSL, run: 
ip addr show eth0
Enter fullscreen mode Exit fullscreen mode
  • Find: 
inet 172.xx.xx.xx/xx
Enter fullscreen mode Exit fullscreen mode

*NOTE:This will be the value used as the host in DBeaver. *

STEP 7: Connect Using DBeaver

  • Open DBeaver, create a new PostgreSQL connection.
  • Fill in with the details of your connection

connection page in dbeaver

  • SSL tab: Set SSL mode to Disable
  • Click Test Connection.

If configured correctly, the connection should succeed.Click Finish.

CONCLUSION

Connecting PostgreSQL running inside WSL to DBeaver allows users to manage their database using a graphical interface instead of relying only on terminal commands. By configuring authentication, setting a password, adjusting the listen address, and using the correct WSL IP address, the connection can be established successfully. This setup makes it easier to run queries, manage tables, and work with databases more efficiently.

Top comments (0)