How to Set Up DHCP Failover on Windows Server 2022

How to Install PostgreSQL on Windows Server 2022

PostgreSQL is one of the most powerful open-source relational database management systems available. Installing it on Windows Server 2022 provides a robust, enterprise-grade database platform suitable for production workloads. This guide walks through the complete installation process using the EnterpriseDB installer, configuring the service, securing the instance, and performing essential administrative tasks.

Downloading the PostgreSQL Installer

The recommended way to install PostgreSQL on Windows is through the EnterpriseDB (EDB) interactive installer, which bundles PostgreSQL, pgAdmin 4, the command-line tools, and the Stack Builder utility. Navigate to the official PostgreSQL download page at https://www.postgresql.org/download/windows/ and click the link to download the installer from EDB.

Select your target version — PostgreSQL 14 or 16 are common production choices as of 2024. Download the Windows x86-64 installer executable. The file will be named something like postgresql-16.2-1-windows-x64.exe. Once downloaded, transfer it to your server via file share, RDP clipboard, or a package staging path.

Running the Interactive Installer

Right-click the installer and choose Run as administrator. The EnterpriseDB setup wizard will launch. Work through the steps:

On the Installation Directory screen, the default is C:Program FilesPostgreSQL16. Accept this or choose a drive with sufficient space. On the Select Components screen, keep PostgreSQL Server, pgAdmin 4, Stack Builder, and Command Line Tools all selected. On the Data Directory screen, the default is C:Program FilesPostgreSQL16data — this is where postgresql.conf, pg_hba.conf, and all data files will reside. You can relocate this to a dedicated data volume for production environments, for example D:pgdata.

Set a strong password for the postgres superuser account. This password is critical — store it in a secrets manager. Set the port to 5432 (the default). On the Advanced Options screen, set the locale to match your server locale (e.g., English, United States). Click Next to begin the installation. The installer will initialize the database cluster and register the PostgreSQL Windows service automatically.

Verifying the Windows Service

After installation, the PostgreSQL server runs as a Windows service named postgresql-x64-16 (or postgresql-x64-14 for version 14). Open a PowerShell session as Administrator and check its status:

Get-Service -Name "postgresql-x64-16"

You should see the service in Running status. To start, stop, or restart it:

Stop-Service -Name "postgresql-x64-16"
Start-Service -Name "postgresql-x64-16"
Restart-Service -Name "postgresql-x64-16"

The service is configured to start automatically at boot. You can confirm this in Services (services.msc) where the startup type should be Automatic.

Using the psql Command-Line Interface

The psql CLI is installed at C:Program FilesPostgreSQL16binpsql.exe. Add this directory to your system PATH so you can run it from any prompt. In PowerShell as Administrator:

[System.Environment]::SetEnvironmentVariable("Path", $env:Path + ";C:Program FilesPostgreSQL16bin", [System.EnvironmentVariableTarget]::Machine)

Open a new terminal and connect to the local PostgreSQL instance as the postgres superuser:

psql -U postgres -h localhost

Enter the password you set during installation. You will see the psql prompt: postgres=#. From here you can run SQL commands and administrative queries.

Check the server version and list existing databases:

SELECT version();
l

Creating Databases and Users

Always create application-specific users rather than using the postgres superuser for application connections. Inside psql:

CREATE USER appuser WITH PASSWORD 'SecurePass123!';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
q

Connect as the new user to verify access:

psql -U appuser -h localhost -d appdb

Configuring postgresql.conf

The primary configuration file is located at C:Program FilesPostgreSQL16datapostgresql.conf. Open it with a text editor (run Notepad or VS Code as Administrator). Key parameters to review and tune for production:

# Memory
shared_buffers = 256MB          # ~25% of system RAM for dedicated DB servers
effective_cache_size = 1GB      # estimate of OS cache available
work_mem = 4MB                  # per sort/hash operation
maintenance_work_mem = 64MB     # for VACUUM, CREATE INDEX

# Connections
max_connections = 100           # reduce if using a connection pooler like pgBouncer
listen_addresses = 'localhost'  # change to '*' for remote access

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000   # log queries taking over 1 second

After editing postgresql.conf, restart the service for changes to take effect:

Restart-Service -Name "postgresql-x64-16"

Configuring pg_hba.conf for Remote Connections

Client authentication is controlled by pg_hba.conf in the same data directory. By default, connections are restricted to localhost. To allow remote connections from a specific subnet, add a line like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.0/24          scram-sha-256

Replace 192.168.1.0/24 with your application server subnet. Using scram-sha-256 is the modern, secure authentication method. Avoid md5 in new deployments. Reload the configuration after editing pg_hba.conf:

psql -U postgres -c "SELECT pg_reload_conf();"

Also change listen_addresses in postgresql.conf to ‘*’ or to the specific server IP so PostgreSQL binds on the network interface.

Opening the Firewall Port

Windows Firewall must be configured to allow inbound TCP connections on port 5432. Run this in an elevated PowerShell session:

New-NetFirewallRule -DisplayName "PostgreSQL 5432" `
  -Direction Inbound `
  -Protocol TCP `
  -LocalPort 5432 `
  -Action Allow `
  -Profile Domain,Private

If your server is behind a hardware firewall or security group, also open port 5432 there. Test connectivity from a remote machine using psql or a tool like telnet or Test-NetConnection:

Test-NetConnection -ComputerName 192.168.1.50 -Port 5432

Using pgAdmin 4

pgAdmin 4 is installed alongside PostgreSQL and provides a full graphical management interface. Launch it from the Start menu under PostgreSQL 16. On first run, set a master password for pgAdmin itself. Then register your server by right-clicking Servers in the object browser, choosing Register > Server, and entering the connection details: host (localhost or IP), port 5432, maintenance database postgres, username postgres, and the postgres user password.

From pgAdmin you can create databases, tables, and users; run queries in the Query Tool; view table data; analyze query execution plans; and monitor server activity. The Dashboard tab shows active connections, transactions per second, and block I/O metrics in real time.

Backing Up with pg_dump

For logical backups of individual databases, use pg_dump. This produces a SQL script or custom-format dump that can be restored with pg_restore or psql:

# SQL format backup
pg_dump -U postgres -h localhost -d appdb -F p -f C:Backupsappdb_backup.sql

# Custom format (compressed, supports parallel restore)
pg_dump -U postgres -h localhost -d appdb -F c -f C:Backupsappdb_backup.dump

Restore a custom-format dump:

pg_restore -U postgres -h localhost -d appdb_new -F c C:Backupsappdb_backup.dump

For a full cluster backup including all databases, roles, and tablespaces, use pg_dumpall:

pg_dumpall -U postgres -h localhost -f C:Backupsfull_cluster_backup.sql

Physical Backup with pg_basebackup

For point-in-time recovery and streaming replication setup, pg_basebackup creates a binary copy of the entire PostgreSQL data directory. First, enable WAL archiving in postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'copy "%p" "C:\WALArchive\%f"'
max_wal_senders = 3

Then run pg_basebackup:

pg_basebackup -U postgres -h localhost -D C:Backupsbasebackup -Ft -z -P

The -Ft flag outputs a tar archive, -z compresses it, and -P shows progress. This produces a base.tar.gz file suitable for recovery or replication standby setup.

Scheduling Automated Backups

Use Windows Task Scheduler to run pg_dump on a schedule. Create a PowerShell script at C:Scriptspg_backup.ps1:

$date = Get-Date -Format "yyyyMMdd_HHmm"
$backupFile = "C:Backupsappdb_$date.dump"
& "C:Program FilesPostgreSQL16binpg_dump.exe" `
  -U postgres -h localhost -d appdb -F c -f $backupFile
Write-Host "Backup completed: $backupFile"

Set the PGPASSWORD environment variable or use a .pgpass file (pgpass.conf on Windows, located at %APPDATA%postgresqlpgpass.conf) to avoid interactive password prompts. Then register the script with Task Scheduler to run nightly.

Summary

Installing PostgreSQL on Windows Server 2022 with the EnterpriseDB installer provides a complete, production-ready database stack including the server, pgAdmin 4 GUI, and all command-line utilities. After installation, key tasks include configuring postgresql.conf for performance, editing pg_hba.conf to control remote access, opening Windows Firewall port 5432, creating application-specific database users, and establishing regular backup routines using pg_dump and pg_basebackup. PostgreSQL’s reliability, standards compliance, and rich feature set make it an excellent choice for Windows Server environments.