Welcome to our latest blog!! In the real world, not every PostgreSQL deployment gives you the freedom to choose your operating system. Many OEM software products ship with PostgreSQL on Windows as a hard dependency — the database is bundled, the OS is prescribed, and migrating to Linux simply is not an option. You work with what the vendor gives you.
This is more common than it might seem. Enterprise applications in sectors like insurance, banking, and manufacturing frequently bundle PostgreSQL on Windows as part of their stack. The DBA’s job is still to ensure high availability — just within the constraints of that environment.
PostgreSQL’s native logical replication works just as well on Windows as it does on Linux, and it is the most practical HA tool available in this scenario. It streams row-level changes from a publisher (Primary) to a subscriber (Standby) without the overhead of physical replication, and unlike streaming replication, it lets you replicate selectively at the database level — so you can target just the application database without touching everything else on the instance.
In this blog we will walk through the complete process of setting up logical replication for an application database on PostgreSQL running on Windows — from prerequisites and schema migration to post-verification.
Note: Writes on the Standby’s application database are strongly discouraged. Any direct writes to the subscriber will cause data inconsistency that logical replication cannot automatically resolve.
Prerequisites
Ensure the following are configured on the Primary before starting. These settings require a PostgreSQL service restart to take effect.
#postgresql.conf — Primary wal_level = logical # must be 'logical' for pub/sub max_replication_slots = 10 # at least 1 slot per subscription max_wal_senders = 10 # at least 2 required per subscriber #pg_hba.conf — Primary (allow replication connection from Standby) host replication <replication_user> <standby_IP>/32 scram-sha-256
After editing postgresql.conf and pg_hba.conf, restart the PostgreSQL service on the Primary:
#Replace with version we are at.
sc stop postgresql-x64-<version>
sc start postgresql-x64-<version>Step by step guide:
Step 1: Create the target database on Standby
The subscriber database must exist before the schema can be restored into it. If the database name is case-sensitive or mixed-case, wrap it in double quotes.
#Connecting to the database
"C:\Program Files\PostgreSQL\<version>\bin\psql.exe" -h <IP_addr> -U <user> -d <dbname> -p <port>
#Create database in standby
CREATE DATABASE <database_name>;
-- Verify it was created:
\l+
-- Expected: database appears in the list.
Step 2: Take a schema-only dump from the Primary
Logical replication streams data changes — not the schema. You must manually copy the schema from Primary to Standby before creating the subscription. Run this from the Standby machine.
"C:\Program Files\PostgreSQL\<version>\bin\pg_dump.exe" ^ --schema-only ^ -h <primary_IP> ^ -p <port> ^ -U <user> ^ -d <database_name> ^ -Fc ^ 2>error_schema.txt ^ 1>app_schema.dump
Verify the dump was successful before proceeding:
-- error_schema.txt must be empty (no errors during dump) type error_schema.txt -- app_schema.dump must not be empty (schema was captured) dir app_schema.dump
If error_schema.txt is not empty, resolve the errors before proceeding. Restoring a partial schema will cause the subscription to fail on tables that are missing on the Standby.
Step 3: Restore the schema dump on Standby
Restore the schema into the target database on the Standby. This creates all tables, indexes, sequences, and constraints — without copying any data. The data will flow through logical replication in the next steps.
"C:\Program Files\PostgreSQL\<version>\bin\pg_restore.exe" ^ -h <standby_IP> ^ -p <port> ^ -U <user> ^ -d <database_name> ^ -Fc app_schema.dump
Verify the table count matches the Primary:
\c <database_name> SELECT count(*) FROM pg_tables WHERE schemaname = 'public'; -- Expected: matches the count noted from Primary in the pre-check.
Step 4: Create the publication on Primary
A publication defines what the Primary will make available for replication. Connect to the target database on the Primary before running this command.
\c <database_name> -- Publish all tables in the database: CREATE PUBLICATION <publication_name> FOR ALL TABLES; -- Verify publication was created: SELECT * FROM pg_publication; -- Expected: publication appears with puballtables = t
Using FOR ALL TABLES ensures any new tables added to the database in the future are automatically included in replication without needing to alter the publication.
Step 5: Create the subscription on Standby
The subscription connects the Standby to the Primary’s publication and triggers the initial full data copy. Connect to the target database on the Standby before running this.
\c <database_name>
CREATE SUBSCRIPTION <subscription_name>
CONNECTION 'host=<primary_IP> port=<port>
user=<user> dbname=<database_name>
password=<password>'
PUBLICATION <publication_name>
WITH (copy_data = true);
-- Expected output: CREATE SUBSCRIPTION
-- Initial data copy begins immediately after this command.
The copy_data = true option instructs PostgreSQL to copy all existing rows from the Primary before streaming live changes. For larger databases this may take several minutes — do not interrupt the process.
Post-check
Once the subscription is created, verify that replication is active and the initial sync has completed cleanly.
Primary — verify slot is active and streaming
SELECT slot_name, active, slot_type FROM pg_replication_slots; -- Expected: active = t for the new subscription slot SELECT application_name, state, sent_lsn, write_lsn, replay_lsn FROM pg_stat_replication; -- Expected: state = streaming
Standby — verify zero lag
SELECT pg_size_pretty( pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn() ) AS replay_lag_bytes; -- Expected: 0 bytes
Standby — verify all tables are fully synced
SELECT s.subname, r.srrelid::regclass AS table_name, r.srsubstate FROM pg_subscription_rel r JOIN pg_subscription s ON s.oid = r.srsubid; -- Expected: srsubstate = 'r' (ready) for every table. -- During initial copy you may see 'd' (data copy in progress).
When all rows show srsubstate = 'r', the initial data copy is complete and logical replication is fully operational. Live changes on the Primary now stream to the Standby in real time.
Conclusion
Setting up logical replication on PostgreSQL Windows is straightforward when executed in the right sequence: create the target database on the Standby, copy the schema across, create the publication on the Primary, and create the subscription on the Standby. The key to a smooth setup is verifying each step — an empty schema dump error file, matching table counts after restore, and ultimately srsubstate = 'r' across all tables.
This setup gives you a live, low-latency replica of any application database that can serve as a warm standby — ready to take over with minimal downtime if the Primary fails.
Thank you and stay tuned for more PostgreSQL production runbooks!!