PostgreSQL in WSL: A Reliable Local Development Service

A PostgreSQL instance inside WSL is useful for local development, integration tests, and disposable data sets. It is not automatically a production database just because PostgreSQL itself is a mature server. WSL distributions can stop, update, move between machines, or be exported; those workstation lifecycle events are outside PostgreSQL’s durability and availability guarantees.

Treat the database as a local service with explicit ownership: one Linux distribution owns the server process and data directory, clients use a documented connection path, and backups are validated independently of the WSL distro archive. This avoids confusing a running service with durable storage or a successful connection with a sound recovery plan.

Decide which environment owns the server

For a Linux application under development in WSL, placing both the app and PostgreSQL in the same distribution can keep traffic on a local Unix-domain socket and avoid unnecessary network configuration. Microsoft’s WSL database tutorial documents installing PostgreSQL inside a distro through that distribution’s package manager.

If the application runs on Windows while the database runs in WSL, the connection crosses an additional host/guest boundary. Keep the bind address, authentication method, Windows firewall, WSL networking mode, and exposure scope deliberate. Do not open PostgreSQL to every interface simply to make one Windows client connect. The required path depends on the current WSL networking mode and local policy.

Use a separate WSL distro for the database only if there is a clear ownership or test-isolation benefit. PostgreSQL data clusters and configuration should not be edited by multiple distro registrations that share private VHDX state. Never attach or copy a live PostgreSQL data directory between distributions as an alternative to PostgreSQL’s own backup tools.

Install with the distro package manager

On Ubuntu, Microsoft’s setup guide uses APT to install the distribution-provided PostgreSQL packages. Review the current Ubuntu and PostgreSQL support policy before selecting a major version; package names and major-version availability are distro-release dependent.

sudo apt update
sudo apt install postgresql postgresql-contrib
psql --version

The package manager owns installation and service integration. Do not download an arbitrary server binary into /usr/local while also letting APT manage the same files. Record the distro release and PostgreSQL package version so a teammate can reproduce the environment.

If systemd is enabled, inspect the service state and start the distro’s PostgreSQL unit:

systemctl is-enabled postgresql
systemctl status postgresql --no-pager
sudo systemctl start postgresql
pg_isready

On a distro without systemd, follow the package’s documented service command and WSL’s current guidance. A running PostgreSQL process is not guaranteed to start merely because the distro is installed. WSL lifecycle and service enablement determine when the service can run; do not assume a stopped distribution continues to host a database.

Create an application role and database explicitly

The initial cluster administrator is commonly a local operating-system account such as postgres, but exact package defaults are distribution-specific. Use the local administrative path for initial provisioning and create an application role with only the privileges the development app requires:

sudo -u postgres psql

Inside psql, create a dedicated role and database. Replace the example names and password strategy with local policy; do not paste real credentials into a source-controlled file or shell history:

CREATE ROLE app_dev LOGIN;
CREATE DATABASE app_dev OWNER app_dev;
\du
\l
\password app_dev
\q

The psql password meta-command prompts for the new password rather than requiring it in shell history. For a password-authenticated client, configure a strong local secret through an approved local secret store or environment mechanism and confirm PostgreSQL’s host-based authentication rules. Do not change pg_hba.conf to trust as a shortcut on a machine where other local users or processes are not trusted.

Prefer a distinct database and role per project or test suite rather than connecting applications as the superuser. A development role should not have cluster-wide administrative attributes unless a test specifically exercises that behavior.

Verify readiness and client identity

Use PostgreSQL’s own client and readiness tools to test the connection. A local Unix socket test does not prove that a Windows TCP client can connect, and a successful TCP connection does not prove that the application is using the intended database:

pg_isready
psql --host 127.0.0.1 --username app_dev --dbname app_dev --command 'SELECT current_database(), current_user, version();'

The TCP example requires a host-based rule that accepts the local connection using the project’s approved authentication method; inspect the active pg_hba.conf rather than assuming a package default. If local peer authentication is the chosen policy, test with the matching Linux account and explain that identity mapping explicitly. Use a prompt, a permissions-restricted password file, or an approved secret injection path for passwords. Avoid putting secrets directly in a command line because process listings, logs, and shell history can expose them.

Confirm the server’s data directory and port through a query or package configuration before investigating duplicate instances. Ubuntu packages may manage versioned clusters, so the service name and cluster state are not always represented by a single process. Use distribution tools and PostgreSQL utilities rather than terminating an arbitrary PID.

Keep schema setup and test data repeatable

Schema migrations belong to the application repository and should be applied by its migration tool. Avoid using a developer’s hand-edited database as a substitute for migrations. A clean database creation should be sufficient to run the project’s schema setup and test suite.

For automated integration tests, create a dedicated database or disposable cluster, use a unique role, run migrations, execute tests, and remove only the test data that the harness created. Never point a cleanup script at the default cluster without validating the target name and connection.

Track test fixtures as source-controlled SQL or application-level seed data. Keep production-like data out of a developer workstation unless there is an approved anonymization and access process. WSL does not change the privacy obligations of the data placed in the database.

Back up with PostgreSQL tools, not only WSL export

A WSL distro export can be useful for migration, but it is not a replacement for an application-consistent PostgreSQL backup and restore test. Use logical backup tools for project data:

sudo -u postgres pg_dump --format=custom --file=/var/tmp/app_dev.dump app_dev
sudo -u postgres createdb app_dev_restore_check
sudo -u postgres pg_restore --dbname=app_dev_restore_check --exit-on-error /var/tmp/app_dev.dump
sudo -u postgres psql --dbname=app_dev_restore_check --command 'SELECT count(*) FROM information_schema.tables;'

Choose backup scope and ownership intentionally. pg_dump exports one database; global roles and tablespaces need separate treatment when required. A restore check should run in an isolated target so it cannot overwrite the working database.

For important local work, quiesce write activity or use the supported PostgreSQL backup procedures for the required consistency level. Do not copy a live data directory while PostgreSQL is writing and call the result a verified backup. Store a backup outside the distro’s VHDX if it must survive a damaged or removed distro.

Respect WSL lifecycle and local-service boundaries

PostgreSQL can be active only while its WSL environment is running. A Windows Task Scheduler task or systemd timer cannot make a stopped instance available unless the external mechanism explicitly starts the distro, and the database’s recovery behavior must be tested after that start. This is a local development service, not a reliable always-on endpoint.

Avoid setting up automatic exposure beyond the development machine. If a team needs shared or production PostgreSQL, deploy it on an environment designed for service availability, network controls, monitoring, backups, upgrades, and recovery. A workstation database can help test client behavior, but it does not establish an HA or disaster-recovery architecture.

Troubleshooting and acceptance checks

When a client cannot connect, split the diagnosis into server process, readiness, authentication, database/role existence, socket or network route, and client configuration. Run pg_isready and a local psql query in the owning distro first. Only then test the Windows-to-WSL network path if required.

When PostgreSQL does not start, inspect the unit or service status and the database server log before changing data-directory permissions. Never recursively change ownership on a guessed directory. Confirm the active cluster path and package version, then use PostgreSQL’s documented recovery steps.

A local environment is accepted when a clean install can start the intended server, a least-privilege role can connect to its named database, the application’s migrations and tests pass, a logical dump restores into a separate database, and stopping/restarting WSL does not lead to silent data loss. Record what is local-only and what has actually been backed up.

Related:

Sources: