- Download and Install binary executable [postgresql.exe] on windows (https://www.enterprisedb.com/downloads/postgres-postgresql-downloads) this will be the server for databases. Take note of the credentials for user [postgres] that you set during the installation, we will use this to connect from WSL in the end.
- Install postgresql-client and associated libraries on WSL2:
sudo apt-get update
sudo apt-get install postgresql-client postgresql-client-common libpq-dev- Go to Windows defender firewall with Advanced Security on Windows.
- Select Inbound Rules and create a new Rule.
- Start by giving a name in General tab, make sure the enabled option is checked.
- Go to protocol and ports tab, select tcp from port type, select specific port and type 5432 in local port section. (postgresql server runs on this port)
- Under advanced tab, select Public profile
- Under scope tab, add remote ip addresses as range from 172.0.0.0 to 172.254.254.254
Why this Range of IP you ask? for this you'll have to open WSL prompt and type
$ cat /etc/resolv.confThis file /etc/resolv.conf generates DNS for WSL environment. The ip keeps changing within the range of 172.x.x.x, so by adding this ip range in firewall rule, it tells windows security to allow these range of ip to connect to postgresql server that runs on port 5432.
Assuming a default install/setup of Postgresql for Windows the following files are located under (C:\Program Files\PostgresSQL$VERSION\data)
-
Verify that <postgresql.conf> has following set: listen_addresses = '*' (This should already be set to '*' so nothing do here.)
-
Update the file <pg_hba.conf> to allow connections from WSL range, look for the similar line in the file:
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 172.0.0.0/8 scram-sha-256Restart Postgres for changes to take effect. This can be done either from the Windows Services app or from cmd with Administrator privileges e.g. for Postgresql 14:
net stop postgresql-x64-14
net start postgresql-x64-14- Its almost done now, to finally connect to the postgresql server we need to specify a host i.e. the DNS that WSL generates in the file </etc/resolv.conf>, to make this convinient, add following to your ~/.bashrc or similar:
if [ -r /etc/resolv.conf ] && grep -q "nameserver" /etc/resolv.conf; then
export winhost="$(grep nameserver /etc/resolv.conf | awk '{print $2}')"
fi- Reload your .bashrc changes:
source ~/.bashrc- Test the connection to the server from WSL
psql -h $winhost -p 5432 -U postgres
Thank you! It almost works for me, but my files are on Ubuntu WSL. How can I connect to my Postgres on Windows?
This is a NextJS project my database connection on my OLD Mac is like this:
POSTGRES_DATABASE_URL='postgresql://USER:PASSWORD@localhost:5432/DATABASE'Error: P1001: Can't reach database server at localhost:5432I can connect on Windows with TablePlus using localhost.
I can connect on Ubuntu using the terminal with
psql -h $winhost -p 5432 -U postgres