Replication
Streaming Replication
Streaming replication is Postgres's built-in mechanism for maintaining an exact copy of your entire database cluster. The primary server continuously streams Write-Ahead Log (WAL) records to one or more standby servers, which replay those records to stay in sync. This gives you a byte-for-byte replica that's ready for failover or serving read queries.
I've run streaming replication in production for years, and it remains the foundation of most Postgres high-availability setups. If your goal is "keep a hot standby ready to take over," this is your 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. This is enormously useful for offloading reporting or analytics traffic from your primary.
Synchronous vs. Asynchronous
You have two modes to choose from:
- Asynchronous (default): The primary doesn't wait for the standby to confirm writes. Lower commit latency, but if the primary crashes, recent transactions may not have reached the standby yet.
- Synchronous: The primary waits for at least one standby to confirm the write before acknowledging the commit. Zero data loss guarantee, but commits take longer because of the network round trip.
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 will begin streaming from the primary.
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 tools like Patroni or pg_auto_failover 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. This opens up capabilities that streaming replication simply can't provide.
I find logical replication particularly powerful for migrations. When I need to move a database between major Postgres versions with minimal downtime, logical replication is what I reach for every time.
Publisher/Subscriber Model
Logical replication uses a publisher/subscriber architecture:
- The publisher defines which tables (and optionally which rows/columns) to share
- The subscriber connects and receives changes in real time
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 Makes Logical Replication Special
- Cross-version replication: Replicate from Postgres 12 to Postgres 17. This is how you do zero-downtime major version upgrades.
- Selective tables: Only replicate what you need.
- Writable subscriber: The subscriber can have its own tables, indexes, and even receive writes on replicated tables (though be careful with conflicts).
- Different schemas: The subscriber can have additional columns, different indexes, or even different table names (with some configuration).
Practical Example: Zero-Downtime Upgrade
Here's the workflow I use for major version upgrades:
-- On old server (publisher, PG 14): CREATE PUBLICATION upgrade_pub FOR ALL TABLES; -- On new server (subscriber, PG 17): -- (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
aside positive 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 replication methods stream changes from WAL, but they serve different purposes. Here's how I think about the decision:
| 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 need to feed a separate analytics database via logical replication. These are not mutually exclusive — I've run setups with streaming to a failover standby and logical to an analytics subscriber simultaneously.
Monitoring Replication
Replication that you don't monitor is replication that will surprise you. I've seen production outages from unmonitored replication slots filling up disk. Make these queries part of your operational toolkit.
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 for errors in the subscription worker:
SELECT subname, worker_type, last_error_message, last_error_time FROM pg_stat_subscription_stats;
Replication Slot Health
Replication slots prevent the primary from discarding WAL that the subscriber hasn't consumed yet. This is important for reliability, but an inactive slot will accumulate WAL until your disk fills up.
-- 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 I use in monitoring systems 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
Replication is fundamental to running Postgres in production. Streaming replication gives you the safety net of a hot standby ready for failover, while logical replication gives you the flexibility to move data between versions, selectively sync tables, and perform zero-downtime migrations. Understanding both and knowing when to reach for each is what separates a development setup from a production-ready architecture.
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
