WSL2 FOR WINDOWS: INSTALL AND POSTGRESSQL SETUP 

Preamble

Why do I need PostgreSQL on WSL2?

Although the perception that a true programmer uses Linux in their work is strong, this is not really the case. Stack Overflow’s survey from 2021 suggests the following, at least:

There are many reasons to use WSL2 with PostgreSQL already built in, but here are a few:

• psql is the standard tool for learning and working with PostgreSQL. However, there are some limiting issues under Windows, e.g., the lack of tab completion, issues with encoding, etc. Running psql under WSL2 will provide you with a smoother experience.
• Since WSL2 is a standalone virtual machine under the hood, using it might be the easiest way to achieve this. It’s a good idea to test and debug your application in a remote environment rather than on a local host. That way, you can immediately find issues with client authentication, or with connection settings.
• Advanced developers will be able to create and test various PostgreSQL extensions, such as pg_squeeze, pg_show_plans, pg_crash, and pg_partman, that are not binary-based or made specifically for Linux using the environment provided by WSL2.

Run the following command to install WSL2 using PowerShell or the Windows Command Prompt:

 PS> wsl --install

the instruction book

• This command will download the most recent Linux kernel, enable the necessary optional components, set WSL2 as your default, and automatically install the Ubuntu distribution for you.
• All subsequent launches should take less than a second; the first time you run a newly installed Linux distribution, a console window will open and you’ll be asked to wait for files to decompress and be stored on your system.

If you want to change the installed distribution, you can choose from the ones that are available. Run the following command to get a list of recognized distributions:

 PS> wsl --list --online The following is a list of valid distributions that can be installed. Install using 'wsl --install -d <Distro>'. NAME FRIENDLY NAME Ubuntu Ubuntu Debian Debian GNU/Linux kali-linux Kali Linux Rolling openSUSE-42 openSUSE Leap 42 SLES-12 SUSE Linux Enterprise Server v12 Ubuntu-16.04 Ubuntu 16.04 LTS Ubuntu-18.04 Ubuntu 18.04 LTS Ubuntu-20.04 Ubuntu 20.04 LTS

After that, you can run the command to install the chosen Linux distribution on WSL2:

 PS> wsl --install -d Debian

I’ll use the Ubuntu operating system to demonstrate my points in this post.

The Ubuntu WSL2 session should be used to run all subsequent commands.

To work with console sessions, I highly recommend using Windows Terminal.

Install PostgreSQL on WSL2 Ubuntu

Please follow the instructions provided on the official website:

echo "deb http://apt.postgresql.org/pub/repos/apt" with sudo sh -c $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - $ sudo apt-get update $ sudo apt-get -y install postgresql postgresql-contrib $ psql --version psql (PostgreSQL) 14.0 (Ubuntu 14.0-1.pgdg20.04+1) $ sudo service postgresql status 14/main (port 5432): down $ sudo service postgresql start * Starting PostgreSQL 14 database server

Please take note: we are not usingsystemctl because WSL2 doesn’t use systemd to operate:

 $ sudo systemctl status postgresql System has not been booted with systemd as init system (PID 1). Can't operate. Failed to connect to bus: Host is down

Set up PostgreSQL on WSL2

We now have to configure PostgreSQL so that it will:

• accept connections from the Windows host;
• have custom-created users;
• allow authentication from remote hosts.

By the way, I’d like to suggest you visit my friend Ltitia Avrot’s blog, where all of these subjects are discussed.

How do I accept connections from the Windows host for PostgreSQL on WSL2?

I know that the most recent version of WSL2 lets you forward localhost, but I think it’s important to know about this, especially when setting up a development environment!

By default, every PostgreSQL installation listens on 127.0.0.1 only. That means you can’t connect to the database instance from a remote host, even if that host is Windows. This is not a bug. This is a security feature.

To change this setting, we need to:

1. edit postgresql.conf;
2. uncomment (sic!) listen_address line;
3. change it to listen_address = '*' for every available IP address or comma-separated list of addresses;
4. restart the PostgreSQL instance, so the new settings take effect.

Depending on your distro, the location of the postgresql.conf file may differ. The easiest way to know where it is is to ask the server itself. However, there is one catch here.

Right now, there is only one user available in our fresh PostgreSQL installation: postgres. Peer authentication is the only method available for establishing a connection to the instance.

That means the operating system (Ubuntu on WSL2) should get a user name from the kernel and use it as the allowed database user name:

sudo -u postgres psql -c "SHOW config_file" config_file ----------------------------------------- /etc/postgresql/14/main/postgresql.conf (1 row)

I highly recommend checking out the fantastic explainshell.com website if you’re having trouble understanding what this command does.

Let’s do something enjoyable now! You can run GUI Linux applications thanks to the cool new WSL2! So instead of using a TUI editor like nano or vim, we will use Gedit!

 $ sudo apt install gedit -y $ sudo gedit /etc/postgresql/14/main/postgresql.conf $ sudo service postgresql restart

To a PostgreSQL cluster, how do I add users?

As I said, by default, there is only one user available: postgres . I strongly recommend creating a separate user.

Here we will use the same trick to connect to PostgreSQL with psql, and execute the CREATE USER command:

$ sudo -u postgres psql psql (14.0 (Ubuntu 14.0-1.pgdg20.04+1)) Type "help" for help. postgres=# CREATE USER dev PASSWORD 'strongone' CREATEDB; CREATE ROLE postgres=# \q

Now we can specify our newly created user dev and connect to PostgreSQL using password authentication. Please note that I explicitly used the-h 127.0.0.1 parameter to force password authentication instead of peer authentication.

$ psql -U dev -h 127.0.0.1 -d Postgres Password for user dev: psql (14.0 (Ubuntu 14.0-1.pgdg20.04+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=>\q

How can I enable remote host authentication for PostgreSQL on WSL2?

The easiest way would be to add additional lines to the pg_hba.conf file:

... host all all 0.0.0.0/0 scram-sha-256 host all all ::/0 scram-sha-256

This change will apply scram-sha-256 password authentication to all IPv4 and IPv6 connections.

$ sudo -u postgres psql -c "SHOW" hba_file' hba_file ------------------------------ /etc/postgresql/14/main/pg_hba.conf (1 row) $ sudo gedit /etc/postgresql/14/main/pg_hba.conf $ sudo service postgresql restart

What steps must be taken in order to connect to PostgreSQL on WSL2 using a Windows host?

With the latest WSL2 version, you can access PostgreSQL from a Windows app (like psql or pgAdmin) using localhost (just like you usually would):

PS> psql -U dev -d postgres Password for user dev: psql (13.0, server 14.0 (Ubuntu 14.0-1.pgdg20.04+1)) WARNING: psql major version 13, server major version 14. Some psql features might not work. WARNING: Console code page (65001) differs from Windows code page (1251) 8-bit characters might not work correctly. See the psql reference page "Notes for Windows users" for details. SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=>\q

However, if there are conflicts with something else, such as a local (Windows) PostgreSQL installation, you might want to use the specific WSL2 IP address. If your version of Windows is earlier than Build 18945, the same applies to you.

As I said before, the WSL2 system is a virtual machine that works on its own and has its own IP address. Therefore, before connecting, we must know the IP address. There are many options for doing this. Pick whichever appeals to you.

In the WSL2 session, you can issue the following command:

 $ ip addr show eth0 6: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> MTU 1500 QDisc MQ State UP default group qlen 1000 link/ether 015:00:5d:19:85:ca brd ff:ff:ff:ff:ff:ff inet 192.168.176.181/20 brd 192.168.191.255 scope global eth0 valid_left forever preferred_left forever inet6 fe80::215:5d fe80::215:5d ff:fe19:85ca/64 scope link valid_lft forever preferred_lft forever

Or, if you don’t need all those specifics, even shorter:

 $ hostname -I 192.168.176.181

You can also use PowerShell or the Command Prompt on the Windows host to run one of the following commands:

PS> bash -c "hostname -I" 192.168.176.181 PS> wsl -- hostname -I 192.168.176.181

Now that we know the IP address, we can connect to PostgreSQL on WSL2 withpsql:

PS> psql -U dev -d postgres -h 192.168.176.181 Password for user dev: psql (13.0, server 14.0 (Ubuntu 14.0-1.pgdg20.04+1)) WARNING: psql major version 13, server major version 14. Some psql features might not work. WARNING: Console code page (65001) differs from Windows code page (1251) 8-bit characters might not work correctly. See the psql reference page "Notes for Windows users" for details. SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=> \q

You could also connect using any GUI you like, like HeidiSQL.

The only drawback is that the WSL2 machine’s IP address cannot be made static. You will need to either set up a startup script to add the current IP address to a system environment variable that points to a file or check the IP address after every restart. Since there is no all-encompassing answer, I will leave that as the reader’s responsibility.

Conclusion

This article taught us:

• how to set up WSL 2
• how to install PostgreSQL on Ubuntu, which is the default WSL2 distribution;
• how to configure PostgreSQL so that it can listen on all IP addresses;
• how to configure PostgreSQL to allow user authentication from any IP address;
• a few tools, programs, and services.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​