Streaming and logical replication in PostgreSQL for real failover

Failover is the moment a database architecture stops being a diagram and becomes either a working promotion or an outage with a root cause report. For PostgreSQL that moment is built on streaming replication, the physical mechanism that ships write ahead logs from a primary to standbys and keeps a byte level copy of the entire cluster ready to take over. Logical replication, the row level publish and subscribe mechanism, solves a different set of problems, and the teams that reach for it as a failover tool usually discover its gaps during the incident they bought it to prevent. This article builds the working failover setup: the streaming mechanics, the primary and standby configuration, the lag and retention protection, the synchronous quorum option, the promotion and routing procedure, and the narrow space where logical replication is the correct complexity.

What streaming replication moves from primary to standby

Streaming replication is physical. The primary writes every change into the write ahead log, the append only record of everything the server has done, and a set of background senders streams those log segments to each standby, which replays them in order. The copy is of disk blocks, not rows: there is no interpretation, no filtering and no choice of what to replicate. When the standby has caught up, it is the same data directory byte for byte, which is exactly what a failover wants, a replacement that is indistinguishable from the original.

The whole cluster rides the stream. Every database in the installation, every index, every statistic, even the bloat that autovacuum has not reclaimed yet, all of it replicates, because the log is the story of the entire server. That is the strength and the budget item in one: nothing is forgotten, but nothing is skipped either, so a standby needs the same storage, the same memory for the shared buffers that matter, and the patience to replay every operation the primary performed.

The mechanism runs on the write ahead log level set in postgresql.conf. The default of replica is the level streaming needs, while the minimal setting, chosen by some write heavy setups to shrink logging, cannot support replication at all. The first step of any replication project is confirming that single line:

wal_level = replica

The primary side configuration and the slot that guards the WAL

The primary needs senders, permissions and a retention guard. The senders are the processes that stream to standbys, sized to the number of replicas planned plus headroom:

max_wal_senders = 10

The permission is a line in pg_hba.conf that grants replication to the dedicated replication user, distinct from application roles:

host  replication  replicator  10.0.2.0/24  scram-sha-256

The retention guard is the replication slot, the structure that tells the primary which standby has consumed which log position. Without a slot, the primary recycles old segments when it needs space, and a standby that falls behind loses the log it needs, forcing a fresh base backup. With a slot, the primary holds every segment the standby has not yet replayed:

SELECT * FROM pg_create_physical_replication_slot('standby_a');

The slot is loyalty with a bite: a detached standby leaves its slot behind, and the primary holds WAL forever, filling the disk. PostgreSQL 13 added the ceiling that turns that failure from an outage into an alarm:

max_slot_wal_keep_size = 40GB

The slot now becomes invalid rather than filling the volume, the standby needs a rebuild, and the monitoring saw it coming. Any production deployment without that setting is holding a disk full future.

Streaming answers failover, and archiving answers history. The archive_command ships finished log segments to durable storage, the same write ahead log the standbys consume, and a base backup plus that archive gives point in time recovery to any moment the retention covers:

archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

The archive is the reason a dropped table from Tuesday is a restore job rather than a career event, and it is configured once and forgotten until the day it is the only copy of the truth.

The standby side and the moment it becomes readable

A standby starts as a copy of the primary's data directory, and pg_basebackup takes that copy while the primary keeps serving:

pg_basebackup -h primary.internal -U replicator     -D /var/lib/postgresql/standby -X stream -P -R

The X stream flag ships the log generated during the backup, and the R flag writes the startup configuration automatically. Since PostgreSQL 12 the marker of a standby is a single empty file named standby.signal in the data directory, which replaced the old recovery.conf mechanism, and the connection back to the primary lives in postgresql.conf:

primary_conninfo = 'host=primary.internal port=5432 user=replicator'
restore_command = 'cp /archive/%f %p'

With hot_standby on, its default, the standby opens for read only queries while it replays, which makes it both a failover target and a reporting server. The standby state is observable from inside:

SELECT status, sender_host, latest_end_lsn
FROM pg_stat_wal_receiver;

and from the primary side the same pair of servers appears with the lag numbers that matter for every operational decision:

SELECT client_addr, state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

The three lag columns, measured since PostgreSQL 10, separate how far the standby trails in accepting, flushing and replaying the log. A replay lag that grows while write lag stays flat is a standby too slow to keep up with queries it is running, and the fix is tuning the load on the standby, not the primary.

Synchronous commit and the latency that buys certainty

Asynchronous replication answers the question of durability for the primary alone: a transaction commits when the local flush finishes, and the standby follows at network speed. In a failure the last milliseconds of commits can be lost, a window that finance teams measure and usually accept in exchange for the zero latency cost. Synchronous commit closes that window by making the primary wait for standbys before acknowledging, configured through synchronous_standby_names:

synchronous_standby_names = 'ANY 1 (standby_a, standby_b)'

The ANY form, available since PostgreSQL 9.6, waits for any one of the listed standbys to flush, a quorum that tolerates one slow or gone replica. The cost is written into every commit: the round trip to the standby joins the transaction path, and applications feel it as added latency on every write. The honest configuration exercise is measuring that latency in staging and deciding whether the recovery point objective is worth it, because synchronous replication that nobody benchmarked tends to be discovered by the first latency complaint, traced back, and quietly switched off.

Failover promotion and the client routing problem

Promotion is the moment the standby stops replaying and becomes a primary. The command is deliberately small, one function call or one pg_ctl invocation:

SELECT pg_promote();

The moment after promotion is where architectures succeed or fall apart, because the old primary still believes it is a primary. If it comes back and accepts writes, the cluster has two divergent histories, the split brain scenario that no database repairs after the fact. The protection is procedural: fence the old primary before promoting, keep it unreachable until it can be rebuilt as a standby, and never let automation promote a standby while the old primary still answers on the application network.

The old primary has a road back. The pg_rewind tool moves the data directory of a formerly promoted primary backwards through its own log to the divergence point, after which the server can start as a standby of the new primary without a fresh base backup. It requires the full_page_writes and wal_log_hints settings to have been on, which is one more reason to leave both at their defaults, and it turns a failover from a rebuild into a rejoin, minutes instead of hours.

The client side of failover is routing, and PostgreSQL ships the answer in the connection string. The target_session_attrs parameter, available since libpq 10, sends writes to whichever host currently accepts them:

host=standby_a,standby_b,old_primary
target_session_attrs=read-write

The driver walks the host list, finds the server whose session is read write, and connects there. Applications that hardcode a single host name rediscover this after their first failover, when the DNS change that was supposed to move traffic lags behind the promotion. Managed high availability tools such as Patroni, with its consensus store handling leader election, or repmgr, automate the promotion and the fencing, and the teams that use them still rehearse the manual promotion, because the automation is also a failure surface.

Where logical replication earns its complexity

Logical replication, native since PostgreSQL 10, moves rows instead of disk blocks. A publication names tables on one database, a subscription on another database pulls the changes, and the receiving side applies them as ordinary inserts and updates. That indirection enables what physical replication cannot: selected tables instead of a whole cluster, different major versions on each side, which makes it the standard zero downtime major upgrade path, and consolidation of several primaries into one warehouse.

The costs are the mirror of streaming. DDL does not replicate, so a schema change on the publication needs the same change applied by hand on the subscriber. Sequences do not replicate, so identity columns diverge and failovers need manual sequence alignment. Tables without a primary key or a declared REPLICA IDENTITY cannot carry updates and deletes, only inserts. Every one of those gaps is a missing piece during a failover, which is why logical setups are for selective distribution and version migrations, while the failover architecture stands on physical streaming with its boring, complete, block level copy. The decision list for a failover design is short:

1. Stand up physical streaming replication with slots and max_slot_wal_keep_size sized;
2. Monitor the three lag columns and alert on growth, not on absolute values;
3. Choose synchronous quorum only after benchmarking its latency in staging;
4. Automate promotion with fencing, then rehearse the manual pg_promote anyway;
5. Put target_session_attrs in every application connection string before the first incident;
6. Keep logical replication for migrations and selective feeds, not for the failover path.

The replication that carries a failover is the one whose failure modes have been rehearsed. A streaming standby with a guarded WAL, a promotion that has been run by hand on a Sunday, and a connection string that already knows how to find the read write host together form an architecture that survives the loss of a primary as a paragraph in a status page, not as a line in a postmortem.

Cascading standbys and the reporting tier

A standby can itself stream to further standbys, and the cascade is the standard shape for reporting tiers. The primary keeps one or two local standbys for failover, and each of those feeds a fan of reporting replicas, so the analytics load, the heavy scans and the export jobs, never touches the primary's network or disk, and never multiplies the primary's sender count. The configuration is the same primary_conninfo on the cascaded machines, pointing at the intermediate standby instead of the primary, and the intermediate's hot_standby must stay on, which is its default.

The cascade has one honesty rule that monitoring enforces: lag compounds. The end of a two hop chain trails the primary by the first hop plus the second, and the reporting tier at the end serves data that old. Teams that need fresher reporting move the reader closer to the primary and accept the sender cost, and teams that need isolation put the whole cascade in another room entirely, which is the poor man's geography that a single rack failure cannot interrupt. The lag that matters is measured at the reader, the replay_lag at the end of the chain, and the dashboard that shows it belongs to the analysts who depend on the number, because they are the ones who know how stale is too stale for the morning report.