PostgreSQL Point-in-Time Recovery on Windows Server

When a database rollback must be exact

PostgreSQL point-in-time recovery lets you roll a database back to the minute before a bad deploy, a dropped table, or a broken migration. On Windows Server 2022 and Windows Server 2025, that makes it a practical recovery method for teams running PostgreSQL beside IIS apps, internal tools, or line-of-business systems.

This tutorial walks through a working recovery setup on a fresh Windows Server host: install PostgreSQL, enable WAL archiving, create an on-demand base backup, verify the archive, and perform a test restore into a separate data directory. If you are sizing the server for production, a Hostperl VPS is usually the better fit for staging, while a dedicated server hosting plan makes more sense when WAL growth, memory pressure, or restore speed matter more than price.

Windows Server is the focus here because it is the least-covered track for this workflow, and PostgreSQL is fully supported there. You will not need Linux shell commands in this guide. Every step shows the exact PowerShell or PostgreSQL command, what it changes, and how to confirm it worked.

What this recovery design does

Point-in-time recovery depends on two pieces:

• a base backup of the database cluster
• a continuous stream of WAL files, which PostgreSQL replays up to a chosen timestamp

For a small business or agency, that means you can recover after a failed import, a bad plugin migration, or an accidental delete without restoring more data than necessary. For a support team, it also gives you a recovery path you can test instead of a backup that only looks good on paper.

There is a tradeoff. WAL archiving uses disk space, and frequent backups need retention management. If you want broader context on restore planning and retention decisions, Hostperl’s PostgreSQL backup strategy for safer restores in 2026 covers the design side, while the kernel maintenance guide helps when the same server also runs other services that need disciplined patch windows.

Prerequisites on Windows Server

You need:

• Windows Server 2022 or 2025 with local administrator access
• PowerShell 5.1 or later
• At least one data disk with room for base backups and WAL archives
• A test database name you can safely modify, such as appdb

Open PowerShell as Administrator. If you are connecting remotely, keep your original admin session open until the new setup is verified. That avoids lockouts if you change service accounts or firewall rules later.

Confirm the Windows Server version

Get-ComputerInfo | Select-Object WindowsProductName, WindowsVersion, OsHardwareAbstractionLayer

This confirms you are on Windows Server 2022 or 2025 and shows the current platform details. You should see the server edition in the output before you continue.

Install PostgreSQL and check the service

Use the official PostgreSQL installer for Windows Server and choose the major version you plan to support. In production, keep the version the same on both the primary and recovery hosts.

winget install --id PostgreSQL.PostgreSQL -e

This installs PostgreSQL through the Windows package manager if your image includes it. If winget is unavailable on your server build, use the PostgreSQL Windows installer from the official source and install the server service during setup. After installation, confirm the service exists.

Get-Service | Where-Object { $_.Name -match 'postgres' }

You should see a PostgreSQL service such as postgresql-x64-16 or similar, depending on the installed version.

Find the data directory and open the config files

PostgreSQL on Windows usually stores its cluster in a versioned data directory. Check the service command line to find the exact path.

Get-CimInstance Win32_Service | Where-Object { $_.Name -match 'postgres' } | Select-Object Name, State, StartName, PathName

Look for the -D argument in the service path. That is your data directory. The files you will edit are usually postgresql.conf and pg_hba.conf in that directory.

Enable WAL archiving for PostgreSQL point-in-time recovery

Open postgresql.conf in Notepad as Administrator. Replace the data directory in the command below if your server uses a different version or path.

notepad.exe 'C:\Program Files\PostgreSQL\16\data\postgresql.conf'

Add or adjust these settings. They send WAL files into a separate archive folder and keep enough history for recovery testing.

archive_mode = on
archive_command = 'powershell -NoProfile -ExecutionPolicy Bypass -Command "Copy-Item ''%p'' ''C:\\pgarchive\\%f'' -Force"'
wal_level = replica
max_wal_senders = 3
archive_timeout = 60

Save the file. The archive command copies each completed WAL segment into C:\pgarchive. If the folder does not exist yet, create it next.

Create the WAL archive folder with restricted access

Run these commands in PowerShell as Administrator. They create the archive directory and give the PostgreSQL service account write access without opening it to everyone.

New-Item -ItemType Directory -Path 'C:\pgarchive' -Force | Out-Null
icacls 'C:\pgarchive' /inheritance:r
icacls 'C:\pgarchive' /grant 'Administrators:(OI)(CI)F'

If your PostgreSQL service runs under a dedicated local user, grant that account modify rights too. You can see the service account in the earlier service command output. A correct setup keeps archive files writable for PostgreSQL and protected from ordinary users.

Restart PostgreSQL and confirm WAL archiving

Use the service name from your server. The example below assumes the common PostgreSQL service naming pattern on Windows.

Restart-Service -Name 'postgresql-x64-16'
Get-Service -Name 'postgresql-x64-16'

After the restart, PostgreSQL should be running again. Next, connect with psql and force a new WAL segment so you can confirm archiving.

& 'C:\Program Files\PostgreSQL\16\bin\psql.exe' -U postgres -d postgres -c "SELECT pg_switch_wal();"

You should receive a WAL switch result. Then check the archive directory for a newly written file.

Get-ChildItem C:\pgarchive | Sort-Object LastWriteTime -Descending | Select-Object -First 5 Name, Length, LastWriteTime

If the archive folder stays empty, check the PostgreSQL log files and the archive command path before moving on.

Create a base backup for recovery

Now create a full backup of the cluster. This gives you the starting point for any point-in-time restore.

New-Item -ItemType Directory -Path 'C:\pgbackups' -Force | Out-Null
& 'C:\Program Files\PostgreSQL\16\bin\pg_basebackup.exe' -U postgres -D 'C:\pgbackups\base-2026-01-01' -Fp -Xs -P -R

The -P flag shows progress, and -R writes recovery settings for streaming-style setups. For this tutorial, the important part is that the base backup completes cleanly and the target folder contains cluster files.

If your policy requires a dedicated backup account, create one with limited rights instead of using postgres. For smaller environments, the service account may already be acceptable, but keep credentials out of scripts and scheduled tasks.

Test a controlled data change

Create a sample table and record a before-state so you have something to recover from. This simulates a real mistake such as a bad deploy or a mistaken delete.

& 'C:\Program Files\PostgreSQL\16\bin\psql.exe' -U postgres -d postgres -c "CREATE DATABASE appdb;"
& 'C:\Program Files\PostgreSQL\16\bin\psql.exe' -U postgres -d appdb -c "CREATE TABLE recovery_test(id int PRIMARY KEY, note text); INSERT INTO recovery_test VALUES (1, 'before change'); SELECT * FROM recovery_test;"

Confirm that the row exists. Then make a destructive change you can roll back from.

& 'C:\Program Files\PostgreSQL\16\bin\psql.exe' -U postgres -d appdb -c "DELETE FROM recovery_test; SELECT * FROM recovery_test;"

That leaves the table empty. Note the current time, because point-in-time recovery uses a timestamp target.

Prepare the recovery target directory

Do not restore over the active cluster. Use a separate directory so you can test the recovered copy without risking the running service.

New-Item -ItemType Directory -Path 'C:\pgrestore\test' -Force | Out-Null

Stop PostgreSQL only if you are testing an in-place disaster recovery scenario. For this tutorial, keep the original cluster online and restore into a new location.

Restore to a point in time

Copy the base backup into the recovery directory, then create the recovery signal files needed for replay. Replace the timestamp with a moment just before the delete command.

Copy-Item 'C:\pgbackups\base-2026-01-01\*' 'C:\pgrestore\test' -Recurse -Force

Now create a recovery configuration file in the restored cluster. Open the data directory for the restored copy and add the recovery target.

notepad.exe 'C:\pgrestore\test\postgresql.conf'

Add or confirm these lines near the bottom of the file:

restore_command = 'powershell -NoProfile -ExecutionPolicy Bypass -Command "Copy-Item ''C:\\pgarchive\\%f'' ''%p'' -Force"'
recovery_target_time = '2026-01-01 15:40:00'
recovery_target_action = 'pause'

Adjust recovery_target_time to the exact time before the bad change. Save the file, then create the recovery signal file expected by newer PostgreSQL versions.

New-Item -ItemType File -Path 'C:\pgrestore\test\recovery.signal' -Force | Out-Null

Start a separate temporary PostgreSQL instance only if your installation method supports it. On many Windows servers, the safest test is to restore the files and inspect them in an isolated environment. If you want a fully active replay test, launch a second instance on a different port with a separate data directory and service name.

Verify the recovered data

Connect to the restored cluster and confirm the deleted row returns. If the recovery target time was set correctly, you should see the row that existed before the change.

& 'C:\Program Files\PostgreSQL\16\bin\psql.exe' -U postgres -d appdb -c "SELECT * FROM recovery_test;"

Successful output should show the row containing before change. If it does not, your target time was too late, your WAL archive is incomplete, or the restore command cannot reach the archive directory.

Check logs and diagnose common failures

Three failures account for most restore problems: missing WAL files, bad archive permissions, and the wrong recovery timestamp. Start with the logs, then fix the cause.

Check the PostgreSQL event and application logs:

Get-WinEvent -LogName Application -MaxEvents 30 | Where-Object { $_.ProviderName -match 'PostgreSQL' } | Select-Object TimeCreated, Id, LevelDisplayName, Message

If you see archive command failures, re-check the folder path in postgresql.conf and the ACLs on C:\pgarchive. If recovery lands on the wrong point, move the target time earlier by one to two minutes and test again.

Check whether the archive folder contains enough WAL segments:

Get-ChildItem C:\pgarchive | Sort-Object Name | Select-Object Name, Length

If the archive looks sparse, your workload may not have generated enough WAL yet. Run another pg_switch_wal() and repeat the backup cycle.

Rollback plan if the test restore fails

If the restore test does not succeed, do not overwrite the original cluster. Keep the production data directory intact, fix the archive path or permissions, then create a fresh base backup. That sequence keeps a partial recovery from becoming a bigger problem.

If your server hosts a live application, schedule the restore test during a maintenance window. For mixed workloads, Hostperl’s Ubuntu vs Debian on VPS practical admin differences can help if you are planning a Linux-based secondary environment later, and the Windows Server network troubleshooting guide is useful if remote access to the recovery host is unstable.

Final verification checklist

Before you call the recovery plan complete, confirm all of the following:

• PostgreSQL service starts cleanly after restart
• WAL files appear in C:\pgarchive
• A base backup exists in C:\pgbackups
• The restored cluster opens in psql
• The deleted row reappears at the chosen timestamp
• The original production cluster remains untouched

For a client-side smoke test, open a terminal on your admin workstation and run a read query against the restored database. If the row appears exactly as expected, your recovery process is working.

FAQ

Does point-in-time recovery replace regular backups?
No. You still need scheduled base backups and archived WAL files. PITR only works when both pieces are available.

Can I test this without taking production offline?
Yes. Restore into a separate directory or a second PostgreSQL instance on another port. Never test over the live data directory first.

How far back can I recover?
Only as far back as your retained base backup and WAL archive. If you delete old archives, older recovery points disappear too.

What if the archive folder fills the disk?
Increase storage, shorten retention safely, or move archives to a separate volume. Watch growth before the backup volume reaches capacity.

Is this guide compatible with Linux?
The recovery method is the same in concept, but the commands and service management differ. This tutorial is intentionally Windows Server-specific.