PostgreSQL on WSL2 for Windows: Install and setup

UPDATE on 23.02.2023: This post explains how to install PostgreSQL on WSL2 for Windows, apply the necessary changes to PostgreSQL settings, and access the database from the Windows host. Even though this knowledge can be found in different bits and pieces spread out all over the internet, I want to compile a short and straightforward how-to article. I want you to be able to complete all the steps from scratch, without having to skip all over the place.

Why do I need PostgreSQL on WSL2?

Although there is a strong feeling that a true programmer uses Linux in their work, this statement is not really close to the truth. At least according to this Stack Overflow survey 2022:

Even more, it seems like Windows' popularity increases with time. Take a look at this Stack Overflow survey from 2021:

There are a ton of reasons why a developer might want to use WSL2 with PostgreSQL onboard, but let's name 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.
• 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. Since WSL2 is a standalone virtual machine under the hood, using it might be the easiest way to achieve this.
• WSL2 will provide the environment for advanced developers to build and test different PostgreSQL extensions not available in binary form or created exclusively for Linux, e.g., pg_squeeze, pg_show_plans, pg_crash, pg_partman, etc.

Install WSL2

To install WSL2 from PowerShell or the Windows Command Prompt, just run:

From the manual:

• This command will enable the required optional components, download the latest Linux kernel, set WSL2 as your default, and install a Ubuntu distribution for you by default.
• The first time you launch a newly installed Linux distribution, a console window will open and you'll be asked to wait for files to de-compress and be stored on your machine. All future launches should take less than a second.

Supposing you prefer to change the distribution installed, you have the option to choose among those available. To list the known distros, run:

After that, you can install the chosen Linux distribution on WSL2 by running the command:

Here in this post, I will use the Ubuntu distribution for demonstration purposes.

⚠️ All further commands are supposed to be executed in the Ubuntu WSL2 session.

I strongly suggest using Windows Terminal to work with console sessions.

Install PostgreSQL on WSL2 Ubuntu

Please follow the instructions on the official site:

Please take note: we are not using systemctl because WSL2 doesn't use systemd to operate:

Set up PostgreSQL on WSL2

Now we need to set up PostgreSQL so it will:

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

By the way, let me recommend my friend Lætitia Avrot's blog to you, where all these topics are covered.

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

🔔 I'm aware that the newest WSL2 version allows localhost forwarding, but I think this topic is essential to know, especially in constructing a development environment!

By default, every PostgreSQL installation listens on 127.0.0.1 only. That means you cannot access the database instance from a remote host, including the Windows host. 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. And there is only one way to connect to the instance: peer authentication.

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

🔔 If you are struggling to understand what this command does, I suggest you visit the fantastic explainshell.com site!

Now let's do something fun!

The latest WSL2 is so cool that it allows you to run GUI Linux applications! So instead of using a TUI editor like nano or vim, we will use Gedit!

How do I add users to a PostgreSQL cluster?

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:

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.

How can I allow authentication from remote hosts for PostgreSQL on WSL2?

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

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

How do I connect to PostgreSQL on WSL2 from 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):

⚠️ But if you have conflicts with, for example, a local (Windows) PostgreSQL installation, you might want to use the specific WSL2 IP address. The same applies if you are running an older version of Windows 10 (Build 18945 or less).

As I mentioned earlier, the WSL2 system is a standalone virtual machine with its own IP address. So first, we need to know the IP address to connect. There are several ways to do so. Choose whatever you prefer.

You can run such a command in the WSL2 session:

Or even shorter-- if you don't need all those details:

Alternatively, you can run one of these commands from PowerShell, or from the Command Prompt session in the Windows host:

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

Or connect with any GUI you prefer, for example, with HeidiSQL:

The only drawback is that the WSL2 machine IP address cannot be made static! That means you will need to check the IP address after each restart or set up some startup script to update the system environment variable of some file content with the current IP. Since there is no universal solution, I will leave that as homework for the reader. 😉

Conclusion

In this post, we learned:

• how to install WSL2;
• the way to install PostgreSQL on the default WSL2 distro Ubuntu;
• how to set up PostgreSQL to listen on all IP addresses;
• how to set up PostgreSQL to authenticate users from all IP addresses;
• some tricks, software, and services.

Let me know if this topic is interesting for you and the issues we should highlight in the follow-up articles.

Here's where you can find more Windows-specific posts you may find helpful.

In conclusion, I wish you all the best! ♥️
Look forward to meeting you in person at one of the conferences, meetups, or training sessions!

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.