Postgres

Using Windows PostgreSQL from WSL

by
published on

My first PostgreSQL install on my Windows laptop, so when I started using WSL, I didn't want to install another PostgreSQL DB under WSL as I wanted to share the DB. Note: Prior to this I was using cloud based DBs which I was using from both Windows and WSL.

WSL by default will not be able to access the Windows version of PostgreSQL and when you try and connect you will get a message similar to this:

psql: error: could not connect to server: Connection refused
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5432?

I was obviously not the first to encounter this issues and I found a post on serverfault.com which was very helpful.

My setup used Windows 11 and WSL2 Ubuntu and PostgreSQL 15 on PORT 5433 (normally 5432 by default).

There are three parts to this solution:

  1. Windows - firewall configuration
  2. Linux - finding the host IP address
  3. Windows - PostgreSQL configuration file setting

Step 1:

Edit the Windows Firewall settings and add a PORT rule for 5432 (the default PostgreSQL port) or the port number you are using for the instance of PostgreSQL you are connecting to. I have two instances so I used 5433 for my PostgreSQL 15 instance.

  • Enter wf.msc at the CMD prompt.
  • Select "Inbound Rules" from the list on the left.
  • Select "Action" and then "New Rule".
  • Select "Port", then next.
  • Select TCP and enter the port number in the specific port space. Usually 5432.
  • Continue clicking next until complete.

Step 2:

Enter the following in the WSL ".profile":

# set Windows host environment variable so I can access host DBs
export WINDOWS_HOST=$(cat /etc/resolv.conf | grep nameserver | cut -d ' ' -f 2)
 
You can find the profile you are using and the home directory where the profile is located using the following commands (tested in WSL Ubuntu):
echo $SHELL
echo $HOME

Once you have edited the profile, restart WSL:

wsl --shutdown

wsl

Step 3:

The PostgreSQL configuration file, pg_hba.conf, needs to be changed to allow connections from any IP address. The pg_hba.conf files resides in the "data" directory of the PostgreSQL installation.

The location on my machine was here:

<disk>:\Program Files\PostgreSQL\15\data

# host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0            scram-sha-256

Restart PostgreSQL in the Windows services app by right-clicking on the instance of PostgreSQL you wish to restart and click "restart". I have two versions running

This should allow you to connect from WSL to the Windows instance of PostgreSQL.