How to Connect to Server: Postgresql on Ubuntu in Windows Subsystem for Linux

How to connect to windows postgres Database from WSL

Specify your host, port, and username explicitly
For example:

psql -h 127.0.0.1 -p 5432 -U postgres

Windows Subsystem for Linux 2: Ubuntu, connect to external PostgreSQL database

So it turns out I was pointing the finger in the wrong direction.

I logged into the PostgreSQL server box with a colleague, and issued this command:

ps -ef --sort=start_time | fgrep [db host name] | more

It turns out, the existing connections to the server were fine, but something is jammed up. I have a bunch of idle processes, then a slew of ones saying "startup waiting" - over 100. Here's the output from the command:

[...about 100 idle processes, truncated...]
postgres 26815 48821 0 Aug16 ? 00:00:00 postgres: my-pg-server: web_user web 192.168.9.187(55972) idle
postgres 27525 48821 0 Aug16 ? 00:00:00 postgres: my-pg-server: web_user web 192.168.9.187(55976) idle
postgres 14781 48821 0 00:00 ? 00:00:00 postgres: my-pg-server: postgres jsmith_d [local] VACUUM waiting
postgres 22738 48821 0 00:01 ? 00:00:00 postgres: my-pg-server: other_user other_db 192.168.9.187(57692) startup waiting
postgres 7683 48821 0 00:15 ? 00:00:00 postgres: my-pg-server: yetanother_user yetanother_db 192.168.9.187(57694) startup waiting
postgres 15951 48821 0 00:30 ? 00:00:00 postgres: my-pg-server: yetanother_user yetanother_db 192.168.9.187(57696) startup waiting
[...and about another 100 startup waiting processes, truncated...]

Aha! The culprit it found:

postgres 14781 48821 0 00:00 ? 00:00:00 postgres: my-pg-server: postgres jsmith_d [local] VACUUM waiting

It appears something is jammed up in the VACUUM process, which is causing the new connections to just hang, without failing. Time to dig a little deeper and clean it up, but there's the answer to this behavior.

Can't start postgresql on WSL Ubuntu

The answer of why you cannot use systemd is quite simple: WSL doesn't support it from day 1 and till now.This is a known issue, and you may refer to this issue opened in 2018 and this issue using a similar configuration of Windows and WSL for more information.

The problem of postgresql is another known issue. It is fixed in WSL 2. To solve this issue on WSL 1, this post tells you to set fsync=on and data_sync_retry=true. An alternative way to solve the problem is to set fsync=off. Refer to this issue for more information about fsync problem in WSL.

Edit: on 9/21/2022, Microsoft announced that systemd support has been added to WSL so with latest update systemd should be usable now. (source)

PgAdmin on Windows 10 with Postgres when installed via Bash on Ubuntu on Windows

Here's what I did to connect Postgres DB installed in WSL Ubuntu from Windows pgAdmin.

  1. Launch Ubuntu in Windows.
  2. Start postgres in Ubuntu terminal: sudo service postgresql start
  3. Download the latest pgAdmin and install in Windows.
  4. Launch pgAdmin, a new tab in browser opens; click on Add New Server link.
  5. In the popup Create - Server window in the browser:

    1. General tab: I set Name to localhost
    2. Connection tab: I set Host name/address to localhost, set Password to postgres, which is the default, click on Save password?
    3. I save the setting, leaving the rest of the fields as is
  6. That's it, I can see the DB created in Postgres immediately.

Browser screenshot on adding a server



Related Topics



Leave a reply



Submit