Installing PostgreSQL for development and testing on Microsoft Windows

Prerequesites

• Windows 10 build 16299 or later
• An account with Administrator rights

Installing

For a development machine, we'll use WinGet to download and install PostgreSQL:

winget install PostgreSQL.PostgreSQL.16

Found PostgreSQL 16 [PostgreSQL.PostgreSQL.16] Version 16.4-1
This application is licensed to you by its owner.
Microsoft is not responsible for, nor does it grant any licenses to, third-party packages.
Downloading https://get.enterprisedb.com/postgresql/postgresql-16.4-1-windows-x64.exe
  ██████████████████████████████   356 MB /  356 MB
Successfully verified installer hash
Starting package install...
Successfully installed

...where 16 is the major version of PostgreSQL we wish to install. This will install PostgreSQL in unattended mode (it won't ask you questions on what components to install, where to install them, or what the initial configuration should look like... Although you may be prompted by Windows to approve admin access for the installer). The installer will use the default location (C:\Program Files\PostgreSQL\) with the default password (postgres) and also install both StackBuilder and pgAdmin.

winget search PostgreSQL.PostgreSQL

Name          Id                       Version Source
------------------------------------------------------
PostgreSQL 10 PostgreSQL.PostgreSQL.10 10      winget
PostgreSQL 11 PostgreSQL.PostgreSQL.11 11      winget
PostgreSQL 12 PostgreSQL.PostgreSQL.12 12.20-1 winget
PostgreSQL 13 PostgreSQL.PostgreSQL.13 13.16-1 winget
PostgreSQL 14 PostgreSQL.PostgreSQL.14 14.13-1 winget
PostgreSQL 15 PostgreSQL.PostgreSQL.15 15.8-1  winget
PostgreSQL 16 PostgreSQL.PostgreSQL.16 16.4-1  winget
PostgreSQL 9  PostgreSQL.PostgreSQL.9  9       winget

Add PostgreSQL commands to your path

PostgreSQL comes with several useful command-line tools for working with your databases. For convenience, you'll probably want to add their location to your path.

1. Open the System Properties control panel and select the Advanced tab (or run SystemPropertiesAdvanced.exe)
2. Activate the "Environment Variables..." button to open the envornment variables editor
3. Find the Path variable under the "System variables" heading, and click "Edit..."
4. Add the path to the bin directory of the PostgreSQL version you installed, e.g. C:\Program Files\postgresql\16\bin\ (where 16 is the version of PostgreSQL that you installed).

Verifying your installation

If the steps above completed successfully, you'll now have a PostgreSQL service running with the default database and superuser account. Let's verify this by connecting, creating a new user and database, and then connecting using that user.

Connect with psql

Open a new command prompt, and run

psql -U postgres

Password for user postgres:

psql (16.4)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

When prompted, enter the default password (postgres).

It's a good practice to develop with a user and database other than the default (postgres database and postgres superuser) - this allows us to apply the principle of least privilege and helps avoid issues later on when you go to deploy: since the superuser is allowed to modify anything, you'll never encounter permissions errors even if your app's configuration has you reading or writing to the wrong table, schema or database... Yet that's certainly not the sort of bug you'd wish to ship! So let's start off right by creating an app user and giving it its own database to operate on:

create user myapp with password 'app-password';
create database appdb with owner myapp;

CREATE ROLE
CREATE DATABASE

Now let's quit psql and connect with pgAdmin as our new user. Run,

\q

Connect with pgAdmin

Launch pgAdmin via the Start menu (you can find it under "PostgreSQL 16", or just type "pgAdmin").

If you poke around a bit (by expanding the Servers group on the left), you'll see that pgAdmin comes with a default connection configured for the postgres user to the postgres database in our local PostgreSQL server.

Let's add a new connection for our app user to our app database.

1. Right-click the Servers entry on the left, select Register and click Server:

   

2. On the General tab, give it a descriptive name like "myapp db"

3. Switch to the Connection tab:

   enter connection information, using the new role and database we created in psql above:

   • localhost for Host name/address
   • appdb for Maintenance database
   • myapp for Username
   • app-password for Password

   ...and check the Save password? option and click the Save button.

   

Note that some areas that require system-level permissions (e.g., logs) are unavailable, as you're not connected as a superuser. For these, you can use the default superuser connection.

Conclusion

By following these steps, you've created a local environment for developing against PostgreSQL. You've created your own database and limited-access user to own it, and can proceed to create a schema, connect application frameworks, run tests, etc.