PostgreSQL Failover on Windows Server 2025

What you will build

This tutorial shows you how to set up PostgreSQL failover on Windows Server 2025 with one primary server and one standby server. You will enable streaming replication, check replication lag, test a controlled switchover, and keep a rollback path ready if promotion fails.

This setup suits Windows-based application stacks that need a second PostgreSQL node for maintenance windows, patching, or host failure. It also works well for IIS deployments, internal tools, and business apps that need a real recovery plan, not just a backup file on disk. If you are planning the surrounding hosting environment on Hostperl, a Hostperl VPS is suitable for small production replicas, while larger database workloads are better placed on dedicated capacity.

For a quick operational reference, this guide uses two Windows Server 2025 machines with the reserved documentation IP 203.0.113.10 as the example address. Replace it with the real public IP assigned to your server. If your provider gives you a default non-root or alternate admin account, use that account name in the same connection pattern.

Prerequisites and design choices

• Two Windows Server 2025 hosts: one primary and one standby.
• PostgreSQL 16 or newer installed on both servers.
• TCP access on port 5432 between the two servers.
• A domain or hostname for each server, such as server.example.com.
• Administrator access to Windows PowerShell on both machines.

We are using built-in PostgreSQL streaming replication because it is predictable and easy to validate. It does not replace backups. It complements them. You should still keep point-in-time backups and test restores separately, especially before any switchover in production.

If you want more context on data-layer planning before you start, see PostgreSQL logical replication on Ubuntu Server 24.04 and safe upgrade cleanup practices. The platform differs here, but the operational discipline is the same.

Connect to the first server and confirm the platform

On your local computer, open a terminal or SSH client and connect to the first Windows Server host using your administrator account. The example below is a reserved documentation address.

ssh root@203.0.113.10

Replace 203.0.113.10 with the real public IP of your Windows Server host. If your environment uses a different initial admin account, connect with that account name instead.

Once you are on the server, open PowerShell and identify the operating system.

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

You should see Windows Server 2025 details. If the host is not Windows Server 2025, stop here and match the commands to the actual platform before continuing.

Install PostgreSQL and confirm the service

On the VPS as root or an equivalent administrator, install PostgreSQL on the primary server. If you already have PostgreSQL installed, verify the version and skip the installer.

winget install -e --id PostgreSQL.PostgreSQL --version 16.4

This example installs PostgreSQL 16.4 with winget. If your packaging source offers a different supported build, use the current PostgreSQL 16 line on both servers. The key point is that both nodes match.

psql --version

Successful output should show PostgreSQL 16.x on both hosts. If psql is not in PATH, use the PostgreSQL installation directory to run it directly.

Confirm the Windows service state.

Get-Service | Where-Object { $_.Name -like 'postgres*' } | Format-Table Name, Status, StartType

You want the PostgreSQL service listed and running. If it is stopped, start it after configuration is complete.

Set up the primary server for PostgreSQL failover on Windows Server 2025

On the VPS as root, edit the PostgreSQL configuration files on the primary server. The exact path depends on your install path, but the default Windows installer usually keeps data files under the PostgreSQL data directory. Open the main configuration file with Notepad or your preferred editor.

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

Add or confirm these settings. They allow the server to accept replication traffic and retain enough WAL to keep a standby current.

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
wal_keep_size = '512MB'
hot_standby = on

Save the file and close the editor. Then update client authentication so the standby can connect with a dedicated replication user.

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

Add a rule similar to this, replacing the standby address with your real standby IP.

host  replication  replicator  203.0.113.11/32  scram-sha-256

This example uses 203.0.113.11 for the standby node. Replace it with the actual standby IP. Save the file, then restart PostgreSQL so the new settings take effect.

Restart-Service postgresql-x64-16

Check that the service returned cleanly.

Get-Service postgresql-x64-16

Create the replication user and slot

On the VPS as root, connect to PostgreSQL on the primary and create a restricted replication account. Use a strong password and store it in your secret manager, not in a plain text note.

psql -U postgres -d postgres

At the PostgreSQL prompt, run the following SQL.

CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'ChangeThisToAStrongPassword';
SELECT pg_create_physical_replication_slot('standby01');

Exit with \q. The replication slot helps prevent WAL files from disappearing before the standby has replayed them. That is useful during brief network interruptions, but do not rely on it as a substitute for monitoring.

Prepare the standby server

On your local computer, open a second terminal and connect to the standby host.

ssh root@203.0.113.11

Replace 203.0.113.11 with the standby server IP. Then confirm the platform.

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

Stop PostgreSQL on the standby before copying data files.

Stop-Service postgresql-x64-16

Remove or move the existing data directory if it was initialized already. Be careful here. If this server contains data you need, back it up first.

Rename-Item 'C:\Program Files\PostgreSQL\16\data' 'data.old'

Now clone the primary server using pg_basebackup. Run this from the standby server.

& 'C:\Program Files\PostgreSQL\16\bin\pg_basebackup.exe' -h 203.0.113.10 -p 5432 -D 'C:\Program Files\PostgreSQL\16\data' -U replicator -Fp -Xs -P -R -S standby01

Replace 203.0.113.10 with the primary server IP. You will be prompted for the replication password. The -R flag writes the standby connection settings into postgresql.auto.conf, and -S binds the clone to the replication slot you created earlier.

Confirm standby recovery settings

After base backup completes, check the generated recovery settings. PostgreSQL on Windows uses the same physical standby logic as Linux, so the file names may vary slightly depending on version, but the goal is the same: the standby must know where to fetch WAL from the primary.

Get-Content 'C:\Program Files\PostgreSQL\16\data\postgresql.auto.conf'

You should see a primary_conninfo entry and the replication slot name. If it is missing, rerun pg_basebackup with -R.

Start PostgreSQL on the standby.

Start-Service postgresql-x64-16

Then confirm the service is running.

Get-Service postgresql-x64-16

Open the firewall for database replication

On the VPS as root, allow PostgreSQL traffic only between the two servers. Windows Firewall rules should be narrow, not open to the world.

New-NetFirewallRule -DisplayName 'PostgreSQL 5432 Inbound' -Direction Inbound -Protocol TCP -LocalPort 5432 -Action Allow -RemoteAddress 203.0.113.11

Run the same rule on the standby, but point it back at the primary address.

New-NetFirewallRule -DisplayName 'PostgreSQL 5432 Inbound from Primary' -Direction Inbound -Protocol TCP -LocalPort 5432 -Action Allow -RemoteAddress 203.0.113.10

These rules permit only the other node to connect on port 5432. If your app servers connect directly to PostgreSQL, add separate rules for those addresses later.

Test replication from both servers

On the primary server, open PostgreSQL and confirm the standby is connected.

psql -U postgres -d postgres -c "SELECT client_addr, state, sync_state, write_lag, replay_lag FROM pg_stat_replication;"

Expected result: one row for the standby, with a state of streaming. The lag fields may be blank or close to zero on a healthy local network.

On the standby server, confirm recovery mode is active and the server is read-only.

psql -U postgres -d postgres -c "SELECT pg_is_in_recovery();"

The result should be t. That means the server is correctly acting as a standby.

Create a small test table on the primary and confirm it reaches the standby.

psql -U postgres -d postgres -c "CREATE TABLE IF NOT EXISTS failover_test(id int PRIMARY KEY, note text); INSERT INTO failover_test VALUES (1, 'replication works') ON CONFLICT DO NOTHING;"

Then check it from the standby.

psql -U postgres -d postgres -c "SELECT * FROM failover_test;"

If the standby returns the inserted row, replication is working end to end.

Perform a controlled switchover

A switchover is safer than an emergency failover because you choose the timing. Use it when you want to move production traffic during maintenance.

On the primary server, make sure no heavy writes are in flight, then pause your application if needed. Next, stop new writes and check the replication state one more time.

psql -U postgres -d postgres -c "SELECT pg_current_wal_lsn(); SELECT client_addr, state FROM pg_stat_replication;"

Once you are ready, promote the standby.

psql -U postgres -d postgres -c "SELECT pg_promote();"

Watch the standby transition out of recovery.

psql -U postgres -d postgres -c "SELECT pg_is_in_recovery();"

The value should change to f. That means the standby is now the writable primary.

At this point, your application connection string must point to the new primary. If you use a DNS record, update it and keep the old record TTL low before the change. For database access planning and connection stability, Hostperl clients often pair this with a nearby compute host on managed VPS hosting or a dedicated database node when write volume grows.

Rollback if promotion fails

If the standby does not promote cleanly, stop and keep the original primary intact. Do not force both nodes to accept writes. Split-brain is the failure you want to avoid.

On the standby, check the Windows event log for PostgreSQL errors.

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

If promotion failed because of a bad config change, revert the last edit in postgresql.conf or pg_hba.conf, then restart the service and retry the base backup if necessary.

If the standby promoted but the application layer is not ready, point traffic back to the original primary only if it never accepted writes during the test. Otherwise, use your backup and recovery plan instead of guessing. That is the safe decision.

Secure the pair after the cutover

After the first successful switchover, tighten the environment. Confirm that only replication traffic and application traffic are allowed, and that the replication account cannot do more than it should.

psql -U postgres -d postgres -c "ALTER ROLE replicator NOINHERIT;"

You should also store the password outside the server and rotate it if the password was ever exposed during testing. Then keep at least one offline backup of the old primary before rejoining it as a standby.

Final verification and smoke test

On the server side, confirm the active database node, service status, and firewall state.

Get-Service postgresql-x64-16; Get-NetFirewallRule -DisplayName 'PostgreSQL 5432 Inbound*' | Format-Table DisplayName, Enabled, Direction, Action

On the client side, connect through your normal application path and run a simple read/write test.

psql -h 203.0.113.10 -U postgres -d postgres -c "INSERT INTO failover_test VALUES (2, 'smoke test after failover') ON CONFLICT DO NOTHING; SELECT * FROM failover_test ORDER BY id;"

Replace 203.0.113.10 with the current primary address or DNS name used by your application. Successful output should show both rows and no connection errors.

Reboot both Windows Server hosts during a maintenance window and confirm PostgreSQL starts automatically after reboot.

Restart-Computer

After the servers return, verify the service again with Get-Service postgresql-x64-16. If the service is stopped after reboot, set the startup type to Automatic.

Set-Service postgresql-x64-16 -StartupType Automatic

Troubleshooting the most likely failures

Replication does not start. Check the PostgreSQL log files for authentication or network errors.

Get-ChildItem 'C:\Program Files\PostgreSQL\16\data\log' | Sort-Object LastWriteTime -Descending | Select-Object -First 3

If you see password or host-based auth failures, fix pg_hba.conf, confirm the standby IP, and retry pg_basebackup.

The standby is connected but never catches up. Run a lag query on the primary.

psql -U postgres -d postgres -c "SELECT application_name, state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"

If the lag grows steadily, check disk performance, antivirus exclusions for the PostgreSQL data directory, and network latency between the servers.

Promote works, but the application cannot connect. Check Windows Firewall and the app connection string.

Get-NetTCPConnection -LocalPort 5432

If nothing is listening, restart PostgreSQL. If the port is listening but remote access still fails, review the firewall rule source addresses and any upstream security group rules in your hosting platform.

FAQ

Can I use this setup for automatic failover?

Not by itself. This guide gives you physical streaming replication and a manual switchover process. Automatic failover needs an orchestration layer such as a witness, health checks, and a promotion controller.

Do I need identical hardware on both servers?

No, but close CPU, RAM, and disk performance makes failover behavior more predictable. If the standby is weaker, it may lag under load even though replication is technically working.

Can I use a domain name instead of a raw IP?

Yes. A DNS name is often better for application cutover. Keep the TTL short before the migration and test the new address from the client side after the change.

What should I monitor after failover?

Monitor PostgreSQL service state, replication lag, disk space, event logs, and whether your app can still write to the new primary. Those checks catch most production issues early.