Setting Up PostgreSQL Locally on WSL

A local PostgreSQL instance gives a project a database without depending on a remote development service.

This guide installs PostgreSQL from Ubuntu's repositories inside WSL 2. It creates a separate application role and database, then connects through the local network interface.

Start with a working Ubuntu distribution. The WSL setup guide covers that part. Package versions vary with the Ubuntu release; the commands below show how to inspect your installation.

1. Install PostgreSQL

Inside Ubuntu, update the package lists and install the server and contributed modules:

sudo apt update
sudo apt install postgresql postgresql-contrib

Installing extension files does not enable every extension in every database. Individual extensions can require CREATE EXTENSION and additional server configuration.

2. Start the service

Check which process manages the Linux environment:

ps -p 1 -o comm=

If it reports systemd, start PostgreSQL and enable it when the distribution starts:

sudo systemctl enable --now postgresql

For an environment without systemd, start it manually:

sudo service postgresql start

Microsoft documents systemd support in WSL. Service management belongs there; a privileged startup command in each shell configuration is unnecessary.

Check the client and server versions:

psql --version
sudo -u postgres psql -c 'SHOW server_version;'

3. Create an application role and database

Open the PostgreSQL command-line client as the administrative role:

sudo -u postgres psql

Run these commands inside psql. The password command prompts without putting the password in the SQL text.

SET password_encryption = 'scram-sha-256';
CREATE ROLE app_user LOGIN;
\password app_user
CREATE DATABASE app_db OWNER app_user;
\q

The application role can log in and owns its database. It does not need superuser privileges.

The session setting chooses SCRAM when the password is set. Existing passwords need to be reset when migrating from an older password format.

4. Test a local TCP connection

From the Ubuntu shell, connect using the application role:

psql -h 127.0.0.1 -U app_user -d app_db -W

Check the connected role and database:

SELECT current_user, current_database();
\q

Using -h 127.0.0.1 selects a TCP connection. A connection without a host normally uses a Unix socket instead. PostgreSQL matches these against different authentication rules.

5. Inspect authentication rules if the connection fails

Ask the running server for its configuration paths:

sudo -u postgres psql -c 'SHOW hba_file;'
sudo -u postgres psql -c 'SHOW config_file;'

Open the reported pg_hba.conf file. A rule for this application's loopback connection can use:

host    app_db    app_user    127.0.0.1/32    scram-sha-256

Rules are checked in order. Place the intended rule before a broader matching rule. Keep administrative socket rules, such as local all postgres peer, separate.

host rules cover TCP connections. local rules cover Unix sockets. Changing a local rule will not change the authentication method for the TCP command above. See the authentication file reference.

Use scram-sha-256 for this example. PostgreSQL now deprecates MD5-encrypted passwords; its password documentation explains migration and client compatibility.

Reload the authentication configuration, then retry the connection:

sudo -u postgres psql -c 'SELECT pg_reload_conf();'

6. Connect from a Windows database client

First confirm the Ubuntu connection works. Then try these settings in your Windows database client:

1. Host: 127.0.0.1
2. Port: 5432, unless your installation uses another port.
3. Database: app_db
4. User: app_user
5. Password: the password set through \password.

WSL supports access to Linux network applications from Windows through localhost. If that fails, check the WSL networking configuration and the server's listener:

sudo -u postgres psql -c 'SHOW listen_addresses;'
sudo -u postgres psql -c 'SHOW port;'

A local setup can keep listen_addresses = 'localhost'. Changing that setting requires a server restart, rather than an authentication reload.

Keep the setup reproducible

Record the Ubuntu, WSL, and PostgreSQL versions alongside your project's setup notes. Use schema migrations to reproduce the application database, rather than relying on manual changes.

This guide covers local development. It does not configure a database for remote clients.