Install PostgreSQL on Windows

Before you start

You installed MySQL in the previous chapter; a real PostgreSQL server is what makes MySQL vs PostgreSQL useful. Install PostgreSQL 18, minor release 18.6. PostgreSQL 19 is still a beta, so it is a preview for testing and never for work.

The Windows route is the interactive installer built by EDB, about 394 MB, named postgresql-18.6-4-windows-x64.exe. It asks for a directory, a password, a port and a locale, then runs initdb and registers a Windows service. MySQL needs a separate configurator; PostgreSQL does it all in one wizard.

Install step by step

1. Download the installer.

   Open postgresql.org/download/windows and click "Download the installer". On EDB's page, click the Windows x86-64 icon in the 18.6 row.

2. Run it as Administrator.

   Right-click the file, choose "Run as administrator", click Next.

3. Installation directory.

   Keep C:\Program Files\PostgreSQL\18. The programs land in its bin subfolder.

4. Select components.

   Keep PostgreSQL Server, pgAdmin 4 and Command Line Tools ticked. Untick Stack Builder.

5. Data directory.

   Keep C:\Program Files\PostgreSQL\18\data. Write the path down; configuration files and logs live there.

6. Password for the superuser.

   This is the password of the database role postgres. Put it in your password manager now.

7. Port.

   Keep 5432. If the installer proposes another number, something already listens on 5432, so note whatever it picks.

8. Locale.

   Keep "[Default locale]". It decides how text sorts, and it is permanent for the cluster, so every database you create later inherits it.

9. Summary, then install.

   Click Next twice and wait. The installer runs initdb (UTF8 encoding, scram-sha-256 passwords, data checksums) and registers the service postgresql-x64-18, running as NT AUTHORITY\NetworkService.

10. Finish.

    Untick "Launch Stack Builder at exit" and click Finish.

11. Check the service.

    Open PowerShell.

    # Is the server running? The wildcard saves you the exact name.
    Get-Service postgresql*

    Status	Name	DisplayName
    Running	postgresql-x64-18	postgresql-x64-18 - PostgreSQL Server 18

12. Add the tools to PATH.

    The installer never touches PATH, so psql is unknown to every terminal until you add the bin folder.

    # This window only
    $env:Path += ';C:\Program Files\PostgreSQL\18\bin'
    
    # Permanently, for your user account (open a new terminal afterwards)
    [Environment]::SetEnvironmentVariable('Path',
      [Environment]::GetEnvironmentVariable('Path', 'User') + ';C:\Program Files\PostgreSQL\18\bin',
      'User')

13. Connect with psql.

    Open a new terminal. There is no postgres operating-system user on Windows, so sudo -u postgres psql never applies; you name the role with -U. Run chcp 1252 first so accented text displays correctly.

    chcp 1252
    psql -U postgres
    # Password for user postgres: ********
    # postgres=#

14. Load the shop dataset.

    Run the shared script from the course folder. It recreates shop and the tables every chapter uses.

    cd C:\path\to\the\course
    psql -U postgres -f assets/sample-shop.postgres.sql

Your first ten minutes

Create a role and a database

Working as postgres all day is like working as root, so create a login role for yourself. At work the same statement creates an application's account, usually without CREATEDB. More in Users, roles and security.

-- A role is users and groups in one concept; LOGIN makes it a MySQL-style user
CREATE ROLE yourname WITH LOGIN PASSWORD 'pick-a-real-one' CREATEDB;

-- One cluster holds many databases; OWNER makes a role responsible for one
CREATE DATABASE playground OWNER yourname;

Then connect: psql -U yourname -d playground. Without -d, psql assumes a database named after the role and fails. To stop retyping passwords, put one line per server in %APPDATA%\postgresql\pgpass.conf: hostname:port:database:username:password. Treat that file like a password.

Find your way around psql

A backslash command is handled by the client and takes no semicolon. Anything else is SQL.

The list includes template0 and template1. Templates are what CREATE DATABASE copies, which is why the install-time locale sticks.

The two files a DBA edits

Both live in the data directory. postgresql.conf is the settings file, the counterpart of my.ini: port, listen_addresses, memory, logging. pg_hba.conf is the gatekeeper: one line per rule, saying which address may reach which database as which role, with which method. If you lose them, ask the server.

SHOW data_directory;   -- C:/Program Files/PostgreSQL/18/data
SHOW config_file;      -- .../data/postgresql.conf
SHOW hba_file;         -- .../data/pg_hba.conf
SELECT pg_reload_conf();  -- re-read both files without a restart

On Windows there are no Unix sockets, so psql always uses TCP to localhost and only these two lines matter.

# pg_hba.conf as the installer wrote it (comments trimmed)
# TYPE  DATABASE  USER  ADDRESS       METHOD
host    all       all   127.0.0.1/32  scram-sha-256
host    all       all   ::1/128       scram-sha-256

Changes to pg_hba.conf take effect on reload. Changes to port or listen_addresses need a full restart. Tuning both files is covered in A DBA's working week.

Start and stop the service

The service is automatic, so it returns after every reboot. Control it from an elevated PowerShell; keep pg_ctl for status and reload.

# From an elevated PowerShell (Run as administrator)
Stop-Service postgresql-x64-18
Start-Service postgresql-x64-18
Restart-Service postgresql-x64-18

# Reload configuration files without dropping connections
pg_ctl reload -D "C:\Program Files\PostgreSQL\18\data"

pgAdmin's first launch

pgAdmin 4 is in the Start menu under PostgreSQL 18. Its master password only encrypts the server passwords pgAdmin saves; it has nothing to do with the database. Expand Servers, click "PostgreSQL 18", enter the postgres password, then open the Query Tool on shop. Clients and tools compares the clients.

Other ways to install

winget and Chocolatey run the same EDB installer with fewer clicks, and end with the same service name and data directory. In winget's unattended mode the superuser password defaults to postgres, so change it immediately.

winget install -e --id PostgreSQL.PostgreSQL.18
choco install postgresql18 --params "/Password:YourPassword /Port:5432"

When it goes wrong

Every one of these has a boring cause.

Resetting a forgotten postgres password

Trust local connections for a minute. Back up pg_hba.conf first, then edit it as Administrator.

1. Open trust.

   In C:\Program Files\PostgreSQL\18\data\pg_hba.conf, change the METHOD on the 127.0.0.1/32 and ::1/128 lines to trust.

2. Reload.

   Restart-Service postgresql-x64-18 from an elevated PowerShell.

3. Set a new password.

   psql -U postgres now lets you in without a password.

   ALTER USER postgres WITH PASSWORD 'a-new-strong-one';

4. Close the door.

   Put scram-sha-256 back and reload. Test with psql -U postgres; it must ask for the password.

Uninstall cleanly

Settings, Apps, Installed apps, PostgreSQL 18, Uninstall. The uninstaller leaves the data directory behind, so delete C:\Program Files\PostgreSQL\18\data yourself for a clean slate; a reinstall into a non-empty path fails. Remove the bin entry from your PATH, and take a pg_dumpall backup first if the server holds anything you care about (Backup, restore and replication).

Where to go deeper

• EDB: Installing PostgreSQL on Windows

  The wizard, screen by screen.

• The pg_hba.conf file

  Every field and method.

• psql reference

  Options and backslash commands.

• Locale support

  Why the locale is permanent.