Replication
Streaming Replication
Streaming replication is Postgres's built-in way to keep an exact copy of your entire database cluster. The primary continuously streams Write-Ahead Log (WAL) records to one or more standbys, which replay them to stay in sync. You get a byte-for-byte replica that's ready for failover or for serving read queries.
We've run streaming replication in production for years, and it's still the foundation of most Postgres high-availability setups. If your goal is "keep a hot standby ready to take over," this is the tool.
How It Works
The primary writes every change to the WAL. Standby servers connect and receive those WAL records over a streaming connection, then replay them locally. The standby is always slightly behind the primary (even if just by milliseconds in async mode).
Hot standby means your replica can serve read-only queries while it's replicating. That's handy for moving reporting or analytics traffic off your primary.
Synchronous vs. Asynchronous
You have two modes to choose from:
With asynchronous replication (the default), the primary doesn't wait for the standby to confirm writes. Commits are faster, but if the primary crashes, the last few transactions may not have reached the standby.
With synchronous replication, the primary waits for at least one standby to confirm before acknowledging the commit. You don't lose committed data on failover, but every commit pays for a network round trip. Most teams run async and accept a tiny RPO. Go synchronous when losing even one committed transaction is unacceptable.
Primary Configuration
On the primary server, configure postgresql.conf:
# postgresql.conf on primary wal_level = replica max_wal_senders = 5 wal_keep_size = '1GB' # For synchronous replication (optional): synchronous_standby_names = 'standby1' synchronous_commit = on
Create a replication user:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password_here';
Add the standby to pg_hba.conf:
# pg_hba.conf host replication replicator standby_ip/32 scram-sha-256
Standby Configuration
Take a base backup from the primary:
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/data -Fp -Xs -P
Then configure postgresql.conf on the standby:
# postgresql.conf on standby hot_standby = on primary_conninfo = 'host=primary_host port=5432 user=replicator password=strong_password_here'
Create the standby signal file:
touch /var/lib/postgresql/data/standby.signal
Start the standby and it begins streaming from the primary. (pg_basebackup -R will write standby.signal and primary_conninfo for you, if you'd rather skip those two steps.)
Failover
When the primary goes down, you promote the standby:
SELECT pg_promote();
Or from the command line:
pg_ctl promote -D /var/lib/postgresql/data
In production, most teams use Patroni or a similar tool to handle promotion automatically. Manual failover is fine for development, but you don't want to be SSHing into servers at 3 AM.
Logical Replication
Logical replication works at a different level than streaming. Instead of shipping raw WAL bytes, it decodes the WAL into logical changes (the actual INSERT, UPDATE, and DELETE operations) and sends those to subscribers. That makes a few things possible that streaming replication can't do.
Logical replication really shines for migrations. When we need to move a database between major Postgres versions with minimal downtime, this is what we reach for.
Publisher/Subscriber Model
Logical replication uses a publisher/subscriber architecture:
- The publisher defines which tables (and optionally which rows and columns) to share.
- The subscriber connects and receives changes as they happen.
The subscriber first does an initial table sync (copying existing data), then switches to streaming ongoing changes.
Setting Up the Publisher
On the source database, set wal_level and create a publication:
# postgresql.conf on publisher wal_level = logical max_replication_slots = 4 max_wal_senders = 4
-- Publish specific tables CREATE PUBLICATION app_pub FOR TABLE users, orders, products; -- Or publish everything CREATE PUBLICATION full_pub FOR ALL TABLES; -- Row filtering (PostgreSQL 15+) CREATE PUBLICATION active_users_pub FOR TABLE users WHERE (active = true); -- Column lists (PostgreSQL 15+) CREATE PUBLICATION limited_pub FOR TABLE users (id, name, email);
Setting Up the Subscriber
On the destination database, create matching table structures, then subscribe:
-- Tables must exist on subscriber (create them or use pg_dump --schema-only) CREATE SUBSCRIPTION app_sub CONNECTION 'host=publisher_host port=5432 dbname=myapp user=replicator password=secret' PUBLICATION app_pub;
The subscription will:
- Copy all existing data from published tables (initial sync)
- Begin streaming ongoing changes in real time
What Logical Replication Gets You
It works across major versions, so you can replicate from Postgres 14 to Postgres 18. That's how you do near-zero-downtime major version upgrades. You can replicate only the tables you need. The subscriber is a normal, writable database: it can have its own tables and indexes, and it can even take writes on replicated tables, though you're on your own for conflicts. Subscriber tables can also have extra columns and different indexes than the publisher. Table names (schema-qualified) do need to match.
A few things it does not replicate: DDL (schema changes), sequence values, and large objects. Apply schema changes to the subscriber yourself, and sync sequences right before cutover.
Practical Example: Zero-Downtime Upgrade
Here's the workflow we use for major version upgrades:
-- On old server (publisher, PG 14): CREATE PUBLICATION upgrade_pub FOR ALL TABLES; -- On new server (subscriber, PG 18): -- (after creating schema with pg_dump --schema-only) CREATE SUBSCRIPTION upgrade_sub CONNECTION 'host=old_server port=5432 dbname=myapp user=replicator' PUBLICATION upgrade_pub; -- Wait for initial sync to complete, monitor lag... -- When caught up: stop writes to old server, verify subscriber is current, cut over
Logical replication is the recommended path for migrating to Snowflake Postgres. Set up your existing database as the publisher, create a subscription on your Snowflake Postgres instance, and cut over with minimal downtime. See the Migrate to Snowflake Postgres guide for the complete workflow.
Choosing Between Streaming and Logical
Both methods stream changes out of WAL, but they're for different jobs. Here's how we think about it:
| Criteria | Streaming | Logical |
|---|---|---|
| Scope | Entire cluster | Selected tables |
| Version requirement | Same major version | Cross-version OK |
| Standby writes | Read-only | Writable |
| Failover speed | Fast (seconds) | Manual cutover |
| Schema changes | Automatic | Manual on subscriber |
| Use case | HA / DR | Migrations, selective sync |
| Overhead | Lower | Higher (decoding) |
| DDL replication | Yes (via WAL) | No |
Decision Framework
Use streaming replication when:
- You need a hot standby for automatic failover
- You want read replicas that mirror the full database
- Disaster recovery with minimal RPO (especially synchronous mode)
- You're running the same Postgres major version on all nodes
Use logical replication when:
- Migrating between major Postgres versions
- Replicating a subset of tables to another system
- The subscriber needs its own independent tables or writes
- Consolidating data from multiple sources into one database
- Feeding an analytics or reporting database
Use both together when:
- You want HA via streaming replication and you also need to feed a separate analytics database via logical replication. They aren't mutually exclusive. We've run setups with streaming to a failover standby and logical to an analytics subscriber at the same time. Before Postgres 17 the catch was that logical slots didn't survive a failover. See the next section.
Logical Slots and Failover (Postgres 17+)
Before Postgres 17, logical replication slots lived only on the primary. If you failed over to a physical standby, your logical subscribers (CDC pipelines, analytics feeds) were broken and had to be resynced. Postgres 17 fixed this with failover slots, which get synchronized to the standby.
On the subscriber, mark the subscription's slot as a failover slot:
CREATE SUBSCRIPTION app_sub CONNECTION 'host=publisher_host port=5432 dbname=myapp user=replicator' PUBLICATION app_pub WITH (failover = true);
On the publisher, tell Postgres which physical standby has to confirm WAL before logical consumers get it, so a subscriber can never get ahead of the standby you'd fail over to:
# postgresql.conf on primary synchronized_standby_slots = 'standby1_slot'
On the standby, turn on slot sync. It needs hot_standby_feedback, a physical slot on the primary, and a dbname in primary_conninfo:
# postgresql.conf on standby sync_replication_slots = on hot_standby_feedback = on primary_slot_name = 'standby1_slot' primary_conninfo = 'host=primary_host port=5432 user=replicator dbname=postgres'
Check that it's working on the standby. Synced slots show synced = true:
SELECT slot_name, failover, synced FROM pg_replication_slots;
Postgres 17 also added pg_createsubscriber, which turns an existing physical standby into a logical subscriber. For a large database that skips the slow initial table copy.
Monitoring Replication
Replication you don't monitor is replication that will surprise you. We've seen production outages caused by forgotten replication slots filling up the disk. Keep these queries handy.
Monitoring Streaming Replication (on Primary)
Check connected standbys and their lag:
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;
The replay_lag column tells you how far behind the standby is. In a healthy async setup, this should be milliseconds. If it's climbing, your standby can't keep up.
Monitoring Logical Replication (on Subscriber)
Check subscription status:
SELECT subname, pid, received_lsn, latest_end_lsn, latest_end_time FROM pg_stat_subscription;
Check error counts for each subscription (Postgres 15+). Any non-zero count means it's time to read the subscriber's log, which has the actual error message:
SELECT subname, apply_error_count, sync_error_count FROM pg_stat_subscription_stats;
Replication Slot Health
Replication slots stop the primary from removing WAL that a consumer hasn't received yet. That's what makes them reliable, and it's also why an inactive slot will pile up WAL until your disk fills. Setting max_slot_wal_keep_size puts a cap on it: past that limit Postgres invalidates the slot instead of taking the server down.
-- Check slot status and retained WAL SELECT slot_name, slot_type, active, pg_size_pretty( pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) ) AS retained_wal FROM pg_replication_slots;
If you see an inactive slot with gigabytes of retained WAL, either reactivate the subscriber or drop the slot:
-- Drop an inactive slot (make sure the subscriber is truly gone) SELECT pg_drop_replication_slot('dead_slot_name');
Lag Alert Query
Here's a query we use in monitoring to alert when replication falls behind:
-- Alert if any standby has replay lag over 30 seconds SELECT client_addr, replay_lag FROM pg_stat_replication WHERE replay_lag > interval '30 seconds';
Clean Up After Migration
When you're done with a logical replication setup (like after a migration cutover):
-- On subscriber: drop subscription first DROP SUBSCRIPTION app_sub; -- On publisher: drop publication DROP PUBLICATION app_pub;
Always drop the subscription before the publication. The subscription drop will also clean up the replication slot on the publisher.
Conclusion
Streaming replication gives you a hot standby ready for failover. Logical replication lets you move data between versions, sync selected tables, and do near-zero-downtime migrations. Most production setups end up using both.
Related Resources
Complete walkthrough of using logical replication for database migrations.
Step-by-step migration to Snowflake Postgres with minimal downtime.
Official docs on publications, subscriptions, and row filtering.
Foundational concepts of logical replication and use cases.
This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances
