PostgreSQL replication: streaming, hot standby and failover

PostgreSQL streaming replication continuously copies the transaction logs (WAL) from the primary to one or more standbys. The standby can serve as a hot standby (real-time reads) and take over in case of failure through a failover (promote). This guide builds a two-node cluster from end to end, from the primary configuration to the promotion of the standby, with all the commands tested on PostgreSQL 18.

What you will learn

• Configure a primary for streaming replication (wal_level, dedicated role, slot, pg_hba)
• Initialize a standby with pg_basebackup -R and understand standby.signal
• Monitor replication: pg_stat_replication, pg_stat_wal_receiver, pg_wal_lsn_diff()
• Use the hot standby for reads and understand its limits
• Run a manual failover with pg_promote() and check the timeline change
• Enable synchronous replication and measure what it costs in availability
• Tell physical and logical replication apart to choose the right approach

In what context?

Streaming replication answers concrete administration needs:

• You administer a production PostgreSQL and want to eliminate the single point of failure (SPOF)
• You need a read standby to offload reporting queries from the primary
• You are preparing a planned maintenance (version upgrade, migration) with a controlled switchover
• You must guarantee a fast recovery (short RTO, that is, a low time to restore service) if the main server crashes
• You want to understand what the orchestration tools (Patroni, repmgr) automate before entrusting them with a failover

What this guide does not cover

Three neighbouring topics systematically come up as soon as replication is discussed, and mixing them leads to lopsided architectures. Here is where they are covered:

• Backup and PITR (Point-In-Time Recovery) → see the backup and restore guide (in French)
• Automatic orchestration of failover (Patroni, repmgr, pg_auto_failover) → mentioned at the end of the guide
• Detailed logical replication → only an overview at the end of the guide, the detail is out of scope here

pg_basebackup appears here as the tool that initializes the standby, not as a backup tool.

Streaming only = a lab simplification

This guide configures the standby in pure streaming (without continuous WAL archiving) to simplify learning. In production, the PostgreSQL documentation recommends combining restore_command (WAL archiving) with streaming: the standby automatically loops between archive → pg_wal → streaming. The archive must remain accessible even when the primary is down. Without archiving, the primary may recycle WAL files before the standby has received them.

See the backup and restore guide (in French) to set up continuous WAL archiving.

Prerequisites

• Two servers with PostgreSQL installed (same major version)
• A network allowing a TCP connection between the two nodes (port 5432)
• The basics from the installation guide and the configuration guide
• Superuser access (postgres) or a role with REPLICATION

Environment of this guide's examples (the lab at the end of the page uses db1.lab and db2.lab):

Role	Hostname	IP	OS	PostgreSQL
Primary	pg-primary	192.168.122.61	Debian 12	18.3
Standby	pg-standby	192.168.122.62	Debian 12	18.3

Swipe to see more

Replication is not a backup

This is a costly confusion in operations, and it comes from an understandable shortcut: "my data is on two machines, so it is protected". Replication protects against the loss of a machine, not against data loss. The two risks call for two different mechanisms, and one never replaces the other.

Replication ≠ backup

Replication propagates everything from the primary to the standby: the INSERTs but also the DELETEs, the DROP TABLEs and human errors. A DROP DATABASE on the primary is immediately replicated.

To protect yourself against human errors and corruption, you need backups with WAL archiving and PITR. See the backup and restore guide (in French).

Physical vs logical replication

PostgreSQL offers two types of replication. This guide focuses on physical replication (streaming), the most common one for high availability.

Physical: bit-for-bit copy of the WAL

Physical replication (streaming) copies the WAL logs byte by byte. The standby is an exact copy of the primary: same structure, same databases, same data, same state.

Characteristics:

• Copies the whole cluster (all databases, all roles)
• The standby is read-only (hot standby)
• Very low latency (near real time)
• The standby can serve as the basis for a failover

Logical: selective replication by publication/subscription

Logical replication transmits changes at the row level (INSERT, UPDATE, DELETE) for chosen tables. It uses the PUBLICATION / SUBSCRIPTION mechanism introduced in PostgreSQL 10.

Characteristics:

• Replicates specific tables, not the whole cluster
• The subscriber is an independent server (read-write)
• Allows replication between different versions of PostgreSQL
• Useful for migration, data aggregation or selective sharing

When to use which

The Main objective row is enough to decide in the vast majority of cases: if you want to survive the loss of a server, use physical replication; if you want to move or share data, use logical replication. The Different versions row is the one that decides in a particular but frequent case, the major version upgrade: physical replication requires the same major version on both sides, so it cannot be used to migrate from a PostgreSQL 16 to an 18.

Criterion	Physical (streaming)	Logical
Main objective	High availability, failover	Migration, selective replication
Granularity	Whole cluster	Chosen tables
Read standby	Yes (hot standby)	The subscriber is read-write
Failover	Yes (pg_promote)	Not designed for it
Different versions	No (same major version)	Yes
Latency	Very low	Low to moderate
Complexity	Simple	More complex (conflicts possible)

Swipe to see more

Configure the primary

Configuring the primary takes four steps: replication parameters, a dedicated role, a replication slot and network authorization.

Replication parameters

The essential parameters are in postgresql.conf. On Debian, we use ALTER SYSTEM to modify them cleanly:

-- WAL level required for replication.
-- This is already the default value: this line is there to make sure of it,
-- notably if the server was switched to 'minimal' for a bulk load.
ALTER SYSTEM SET wal_level = 'replica';

-- Maximum number of simultaneous replication connections
ALTER SYSTEM SET max_wal_senders = 5;

-- Maximum number of replication slots
ALTER SYSTEM SET max_replication_slots = 10;

-- Amount of WAL kept for standbys that fall behind
ALTER SYSTEM SET wal_keep_size = '256MB';

-- Listen on all interfaces (not only localhost)
ALTER SYSTEM SET listen_addresses = '*';

Restart required

The wal_level, max_wal_senders, max_replication_slots and listen_addresses parameters require a PostgreSQL restart to take effect:

sudo systemctl restart postgresql

Before reloading or restarting, read the context of each parameter in pg_settings: the pending_restart column tells you whether a written value is still waiting for a restart. The guide Configure PostgreSQL details this reading and the WAL tuning that replication depends on.

After the restart, check that the parameters are applied:

SELECT name, setting
FROM pg_settings
WHERE name IN (
    'wal_level', 'max_wal_senders', 'max_replication_slots',
    'wal_keep_size', 'listen_addresses'
);

          name          | setting
------------------------+---------
 listen_addresses       | *
 max_replication_slots  | 10
 max_wal_senders        | 5
 wal_keep_size          | 256
 wal_level              | replica

Create a dedicated REPLICATION role

The standby must authenticate with the primary to open its WAL stream. Using the postgres role (superuser) for this connection is a bad practice: the password ends up stored on the standby side, and it would open full access to the primary if the standby were compromised. Create a dedicated role with only the LOGIN and REPLICATION privileges:

CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'motdepasse_fort';

By default, this role has no rights on your tables: it cannot create databases, nor modify anything in SQL. It remains sensitive, though, because it can receive the WAL stream and run pg_basebackup, and therefore obtain a complete copy of the cluster. Treat its password like that of a read access to all your data.

Create a physical replication slot

A replication slot guarantees that the primary keeps the WAL files the standby needs, even if the standby is temporarily disconnected. Without a slot, a standby disconnected for too long can lose synchronization if the WAL files have been recycled (deleted to free up space).

SELECT pg_create_physical_replication_slot('standby_slot');

Check:

SELECT slot_name, slot_type, active FROM pg_replication_slots;

  slot_name   | slot_type | active
--------------+-----------+--------
 standby_slot | physical  | f

The slot is inactive (active = f) as long as no standby uses it.

Beware of orphan slots

A slot whose standby never reconnects prevents the recycling of WAL files. The primary's disk space will grow indefinitely. Watch the inactive slots with pg_replication_slots and drop the orphans:

SELECT pg_drop_replication_slot('standby_slot');

Dropping an orphan slot by hand assumes you notice it in time. PostgreSQL provides two safeguards that make this monitoring much less critical.

Safeguards on slots in production

By default, a slot keeps the WAL files without limit. Two parameters protect the primary against uncontrolled accumulation:

• max_slot_wal_keep_size: caps the volume of WAL retained per slot (default: -1 = unlimited). Example: '10GB'
• idle_replication_slot_timeout: automatically invalidates an inactive slot after a delay (default: 0 = disabled). Example: '60min'

ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
ALTER SYSTEM SET idle_replication_slot_timeout = '60min';
SELECT pg_reload_conf();

Without these safeguards, an orphan slot consumes the disk without limit.

Allow replication in pg_hba.conf

The pg_hba.conf file controls which connections are allowed. Add a line to allow the replicator role from the standby's IP:

# TYPE  DATABASE        USER          ADDRESS              METHOD
host    replication     replicator    192.168.122.62/32    scram-sha-256

Reload the configuration:

sudo systemctl reload postgresql

Check: the active rules are visible in pg_hba_file_rules:

SELECT line_number, type, database, user_name, address, netmask, auth_method
FROM pg_hba_file_rules
WHERE 'replication' = ANY(database);

 line_number | type |   database    |  user_name   |    address     |    netmask    |  auth_method
-------------+------+---------------+--------------+----------------+---------------+---------------
         117 | host | {replication} | {replicator} | 192.168.122.62 | 255.255.255.255 | scram-sha-256

The host line above lets the client decide whether to encrypt or not: the WAL stream, which contains all your data, can therefore travel in cleartext between the two nodes. The primary_conninfo generated further down also shows sslmode=prefer, which accepts a fallback without TLS. On a network you do not control, replace host with hostssl on the primary side and set sslmode=verify-full in primary_conninfo on the standby side. The guide Control access to PostgreSQL shows how to enforce TLS with hostssl and a dedicated certificate.

Initialize the standby with pg_basebackup

The standby is initialized by copying the entire cluster from the primary with pg_basebackup. This command runs on the standby.

Prepare the standby

Before running pg_basebackup, the standby's data directory must be empty. If PostgreSQL was installed and initialized automatically, empty it:

# Stop PostgreSQL on the standby
sudo systemctl stop postgresql

# Empty the data directory
sudo -u postgres find /var/lib/postgresql/18/main -mindepth 1 -delete

Copy the cluster with pg_basebackup -R

The -R option is crucial: it automatically generates standby.signal and writes the primary_conninfo into postgresql.auto.conf.

sudo -u postgres pg_basebackup \
    -h 192.168.122.61 \
    -U replicator \
    -D /var/lib/postgresql/18/main \
    -Fp -Xs -P -R

Option	Role
-h	Address of the primary
-U	Replication role
-D	Target data directory
-Fp	Plain format (files)
-Xs	Stream the WAL during the copy
-P	Show progress
-R	Generate standby.signal + primary_conninfo

Swipe to see more

walsenders and pg_basebackup -Xs

With the -Xs option, pg_basebackup consumes two walsenders in parallel: one for the base copy and one for the WAL streaming during the copy. Make sure max_wal_senders is sized accordingly (the connected standbys + 2 for the backup).

One last point before running the command: pg_basebackup runs with the rights of the local system account and trusts what the primary sends it. That trust has already been exploited.

Keep PostgreSQL up to date (CVE-2026-6475)

The plain format (-Fp) of pg_basebackup and the pg_rewind tool were affected by CVE-2026-6475 (CVSS 8.8): a malicious superuser on the source could, through symbolic links, overwrite local files on the machine running the copy and hijack the system account. Two reflexes: only replicate from a trusted primary, and apply the minor versions of May 2026 (PostgreSQL 18.4, 17.10 and the equivalents for the branches still supported). See the PostgreSQL security advisories.

What -R generates

Two elements are created automatically by the -R option:

1. The standby.signal file, an empty file that tells PostgreSQL to start in standby mode:

ls -la /var/lib/postgresql/18/main/standby.signal

-rw------- 1 postgres postgres 0 Apr 13 11:44 standby.signal

2. The primary_conninfo parameter in postgresql.auto.conf, the connection string to the primary:

grep primary_conninfo /var/lib/postgresql/18/main/postgresql.auto.conf

primary_conninfo = 'user=replicator password=motdepasse_fort channel_binding=prefer
 host=192.168.122.61 port=5432 sslmode=prefer sslnegotiation=postgres
 sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2
 gssencmode=prefer krbsrvname=postgres gssdelegation=0
 target_session_attrs=any load_balance_hosts=disable'

pg_basebackup writes all the libpq parameters (PostgreSQL's client library) with their effective value, including those you did not provide. The line is therefore long, but only user, host and password come from what you provided; the rest, port included, takes the client's default values.

Cleartext password in postgresql.auto.conf

The -R option of pg_basebackup writes the password in cleartext into postgresql.auto.conf. In production, prefer a ~/.pgpass file on the standby (owner postgres, permissions 0600) with replication as the database name:

192.168.122.61:5432:replication:replicator:motdepasse_fort

Then remove password=… from primary_conninfo in postgresql.auto.conf.

One parameter remains to add, which pg_basebackup -R does not generate: the slot name. Without it, the standby will connect to the primary just fine, but without reserving any WAL, and the slot created earlier will stay unused. Set primary_slot_name before the first start:

echo "primary_slot_name = 'standby_slot'" | \
    sudo -u postgres tee -a /var/lib/postgresql/18/main/postgresql.auto.conf

Since PostgreSQL 12: no more recovery.conf

Before PostgreSQL 12, the standby configuration went through a separate recovery.conf file. Since PostgreSQL 12, this file no longer exists. Everything goes through the normal parameters (primary_conninfo, primary_slot_name) and the standby.signal file. If you follow old tutorials that mention recovery.conf, they no longer apply.

Start the standby

At this point, the data directory contains a complete copy of the primary, the standby.signal file and the connection string. PostgreSQL detects standby.signal at startup and goes straight into recovery mode, where it replays WAL files, instead of opening the database read-write. The startup may take a few seconds longer than a normal start, the time to replay the WAL accumulated during the copy.

sudo systemctl start postgresql

Check right away that the standby is in recovery mode:

SELECT pg_is_in_recovery();

 pg_is_in_recovery
-------------------
 t

t (true) confirms that PostgreSQL is running in standby mode.

Check the replication

Once the standby has started, you need to check that streaming replication is active and working. The two key views are pg_stat_replication (on the primary) and pg_stat_wal_receiver (on the standby).

On the primary: pg_stat_replication

This view shows the active replication connections:

SELECT pid, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

 pid  |   state   | sync_state | sent_lsn  | write_lsn | flush_lsn | replay_lsn
------+-----------+------------+-----------+-----------+-----------+------------
 4536 | streaming | async      | 0/30067F0 | 0/30067F0 | 0/30067F0 | 0/30067F0

Column	Meaning
state	streaming = replication active in real time
sync_state	async = asynchronous replication (default)
sent_lsn	Last WAL position sent to the standby
write_lsn	Last position written by the standby (handed to the file system, not necessarily flushed to disk)
flush_lsn	Last position flushed to disk (flush) by the standby
replay_lsn	Last position replayed by the standby

Swipe to see more

When the four LSN columns (Log Sequence Number, the position in the WAL stream) are identical, the standby is perfectly synchronized.

On the standby: pg_stat_wal_receiver

This view shows the active replication connection on the standby side:

SELECT status, sender_host, sender_port,
       slot_name, flushed_lsn
FROM pg_stat_wal_receiver;

  status   |  sender_host   | sender_port |  slot_name   | flushed_lsn
-----------+----------------+-------------+--------------+-------------
 streaming | 192.168.122.61 |        5432 | standby_slot | 0/30067F0

status = streaming confirms that the standby receives the WAL continuously. Also check the slot_name column: if it is empty, the standby is not using the slot you created on the primary, because primary_slot_name was not set. Replication works anyway, but you lose the WAL retention guarantee and the slot will stay active = f on the primary.

Compute the lag with pg_wal_lsn_diff()

To measure the replication lag, that is, how far the standby is behind the primary, in bytes:

-- On the primary
SELECT
    client_addr,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

  client_addr    | replay_lag_bytes
-----------------+------------------
 192.168.122.62  |                0

A lag of 0 bytes means that the standby has replayed everything the primary sent it. In production under load, a slight lag (a few KB) is normal.

Continuous lag monitoring

Replication lag is a critical indicator. Include it in your monitoring (Prometheus, Zabbix, etc.). The other views to watch on an instance (pg_stat_activity, pg_stat_statements, autovacuum) are in the guide Monitor and maintain PostgreSQL. PostgreSQL also exposes the write_lag, flush_lag and replay_lag columns (as a time interval) in pg_stat_replication for finer granularity.

Test the replication in real time

The most telling test: write on the primary, read on the standby.

On the primary, insert a row:

INSERT INTO app.servers (hostname, ip_address, os, role, env, cpu_cores, ram_gb)
VALUES ('srv-repl-test', '10.0.0.42', 'Debian 12', 'test', 'dev', 2, 4);

On the standby, check immediately:

SELECT hostname, ip_address FROM app.servers
WHERE hostname = 'srv-repl-test';

   hostname    | ip_address
---------------+------------
 srv-repl-test | 10.0.0.42

The row is visible almost instantly. The standby receives and replays the WAL continuously.

Hot standby: reads on the replica

The hot standby lets you run read queries on a standby while it keeps receiving and replaying the WAL. The hot_standby parameter controls this feature, it is on by default since PostgreSQL 10 and only has an effect on a server in recovery/standby. No configuration is needed on the primary.

What you can do

The hot standby accepts read-only operations:

-- SELECT queries
SELECT * FROM app.servers WHERE env = 'prod';

-- Statistics views
SELECT * FROM pg_stat_user_tables;

-- Execution plans
EXPLAIN ANALYZE SELECT * FROM app.servers WHERE hostname LIKE 'srv%';

It is ideal for:

• Reporting and long analytical queries
• Distributed reads (connect the read applications to the standby)
• Debugging and performance analysis without impacting the primary

What you cannot do

Any write operation is refused:

-- INSERT → error
INSERT INTO app.servers (hostname) VALUES ('test');

ERROR:  cannot execute INSERT in a read-only transaction

-- DDL → error
CREATE TABLE app.test (id serial);

ERROR:  cannot execute CREATE TABLE in a read-only transaction

This is normal and expected behaviour: the standby is a read-only copy of the primary.

Hot standby conflicts

Sometimes, the replay of the WAL on the standby comes into conflict with long read queries. For example, if the primary runs a VACUUM that removes dead rows (the old versions of rows that have become useless; the role of VACUUM is detailed in the guide Monitor and maintain PostgreSQL), the standby must replay this operation, but a SELECT query may be reading those rows.

The max_standby_streaming_delay parameter controls how long the standby waits before cancelling the conflicting query:

SHOW max_standby_streaming_delay;

 max_standby_streaming_delay
-----------------------------
 30s

By default, the standby waits 30 seconds before cancelling the conflicting query. Raise this value if your reporting queries are long:

ALTER SYSTEM SET max_standby_streaming_delay = '120s';
SELECT pg_reload_conf();

Performance vs latency trade-off

Raising max_standby_streaming_delay lets long queries finish, but delays the replay of the WAL. The standby falls behind for the duration of the conflict. It is a trade-off between data freshness and the stability of read queries.

Manual failover

Failover means promoting the standby to be the new primary when the old primary is unavailable (failure, maintenance, migration).

Failover is irreversible

Once promoted, the standby becomes an autonomous server on a new timeline (a WAL history number, incremented at each promotion so that two servers do not mix their logs). The old primary can no longer follow it. The only way to restore replication is to rebuild the old primary as a new standby (with pg_basebackup or pg_rewind).

Check the state before the promote

Before promoting, make sure the standby is properly synchronized:

-- On the standby: check that we are in recovery
SELECT pg_is_in_recovery();
-- → t

-- Check the current timeline
SELECT timeline_id FROM pg_control_checkpoint();
-- → 1

-- Check that standby.signal exists
-- ls -la /var/lib/postgresql/18/main/standby.signal

Promote with pg_promote()

The promotion is done with a single SQL command, run on the standby:

SELECT pg_promote();

 pg_promote
------------
 t

Alternatives to pg_promote()

You can also promote from the command line:

sudo -u postgres pg_ctl promote -D /var/lib/postgresql/18/main

In old tutorials you will come across a third method, the trigger file designated by promote_trigger_file. This parameter was removed in PostgreSQL 16: on an installation of 16 or later, it no longer appears in pg_settings and dropping it into the configuration causes a startup error. That leaves pg_promote() in SQL and pg_ctl promote on the command line.

Check the promote

After the promote, several changes are immediately observable on the former standby:

1. No longer in recovery:

SELECT pg_is_in_recovery();

 pg_is_in_recovery
-------------------
 f

f (false): the server is no longer a standby, it accepts writes.

2. New timeline:

SELECT timeline_id FROM pg_control_checkpoint();

 timeline_id
-------------
           2

The timeline goes from 1 to 2. Each promote creates a new timeline to avoid any confusion with the WAL of the old one.

3. standby.signal removed:

ls /var/lib/postgresql/18/main/standby.signal

ls: cannot access '/var/lib/postgresql/18/main/standby.signal': No such file or directory

PostgreSQL automatically deleted the file.

4. pg_stat_wal_receiver empty:

SELECT * FROM pg_stat_wal_receiver;

(0 rows)

The standby no longer receives WAL, it is now an autonomous primary.

5. Writes work:

INSERT INTO app.servers (hostname, ip_address, os, role, env, cpu_cores, ram_gb)
VALUES ('srv-after-promote', '10.0.0.99', 'Debian 12', 'test', 'dev', 2, 4);

INSERT 0 1

The INSERT goes through, and so does DDL. The server is fully operational in read-write mode.

What to do with the old primary?

After the promote, the old primary is still on timeline 1 and does not know the standby was promoted. There are three options:

Option	When to use it
Full pg_basebackup	The old primary has diverged a lot or is corrupted
pg_rewind	The old primary has diverged little (recommended)
Rebuild completely	Change of version or configuration

Swipe to see more

pg_rewind is the most efficient option when the old primary accepted only a few writes after the standby disconnected:

# Stop the old primary
sudo systemctl stop postgresql

# Resynchronize with the new primary
sudo -u postgres pg_rewind \
    --target-pgdata=/var/lib/postgresql/18/main \
    --source-server="host=192.168.122.62 user=postgres dbname=postgres"

pg_rewind prerequisites

pg_rewind requires the target server to have been initialized with data checksums enabled (this is the default since PostgreSQL 18) or wal_log_hints = on to be configured. The full_page_writes parameter must also be on (this is the default). If none of these conditions is met, pg_rewind will fail.

Then, create a standby.signal and configure primary_conninfo so that the old primary becomes the new standby and points to the new primary.

Synchronous replication

So far, everything that has been set up works in asynchronous mode, PostgreSQL's default behaviour. This section describes the mode that guarantees that no committed transaction is lost, at the price of a tight coupling between the primary and its standby. It is a choice of RPO (Recovery Point Objective: the amount of transactions you accept to lose in case of failure), not of architecture: the setup stays exactly the same, a single parameter changes.

Reminder: asynchronous is the default

By default, streaming replication is asynchronous: the primary does not wait for the standby's confirmation before validating a COMMIT. It is the best-performing mode, but it can lose a few transactions if the primary crashes.

Synchronous replication guarantees that a COMMIT is only acknowledged to the client once the standby has confirmed that it received and flushed to disk (flush) the corresponding WAL (with synchronous_commit = on). Depending on the value of this parameter, the confirmation awaited is lighter (remote_write: written to the file system) or heavier (remote_apply: replayed and visible to reads). It is the safest mode for the RPO (zero data loss).

Enable synchronous replication

One point often misleads: synchronous_commit is already on by default, and yet replication stays asynchronous. The reason is that synchronous_commit = on only guarantees the local write of the WAL to disk before returning control to the client, as long as no standby is designated. Synchronous mode proper is enabled by designating at least one standby in synchronous_standby_names, which is empty by default. It is this parameter, and it alone, that turns sync_state from async to sync.

Before writing this parameter, you need to know under what name the standby presents itself to the primary. This name is the application_name of its replication connection, and you observe it on the primary:

-- On the primary
SELECT application_name, state, sync_state FROM pg_stat_replication;

Without an application_name in its primary_conninfo, the standby takes the value of its cluster_name, and walreceiver only if that parameter is empty. Yet a Debian installed from a package does not leave this parameter empty: pg_createcluster writes cluster_name = '18/main' into postgresql.conf. The postgresql.conf of this guide's standby lives in /etc/postgresql/18/main/, which pg_basebackup does not copy: it keeps its own cluster_name, and the column therefore shows 18/main, like on the primary. With a cluster named standby, it showed 18/standby, and walreceiver only came back after emptying cluster_name. That last case is the one of the official Docker image, which explains why many tutorials copy walreceiver.

A tutorial can therefore be right on its author's machine and wrong on yours. The trap is silent: a name that matches no connected standby triggers no error, pg_stat_replication keeps showing async, and every COMMIT waits for nobody. You think you have RPO = 0, you have nothing.

The safest approach is to not depend on this default value: set an explicit application_name in the standby's primary_conninfo. ALTER SYSTEM SET primary_conninfo replaces the whole string, so you must put the host, the role and the password back in (or use ~/.pgpass, see above). A simple reload is enough since PostgreSQL 13: the WAL receiver process reconnects on its own.

-- On the standby
ALTER SYSTEM SET primary_conninfo =
  'host=192.168.122.61 port=5432 user=replicator password=motdepasse_fort application_name=standby1';
SELECT pg_reload_conf();

Run the check query again on the primary: the column should now show standby1. Only then, declare this exact name:

-- On the primary
-- synchronous_commit = on is already the default value
-- It is synchronous_standby_names that enables synchronous mode
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (standby1)';
SELECT pg_reload_conf();

Parameter	Role
synchronous_commit = on	Default value. synchronous_standby_names empty: the COMMIT waits for the local WAL flush. A named standby: it also waits for that standby to have received and flushed to disk the WAL
synchronous_standby_names	The real enabler: which standbys are synchronous (by application_name)

Swipe to see more

Avoid the wildcard *, which would make any standby that connects synchronous, including an unexpected server. After configuration, pg_stat_replication shows sync instead of async:

SELECT application_name, sync_state FROM pg_stat_replication;

 application_name | sync_state
------------------+------------
 standby1         | sync

If the sync_state column stays at async even though the parameter is set, the declared name does not match application_name: reread it character by character, 18/main included.

Trade-offs of synchronous replication

Read this table line by line, as pairs: each advantage has a direct price opposite it. The second line has the heaviest consequences, and it often surprises people in production: the availability of the primary becomes dependent on that of the standby. You have moved the single point of failure, you have not removed it. This is why synchronous replication is generally deployed with at least two standbys.

Advantage	Drawback
RPO = 0 (zero data loss)	Increased latency on every COMMIT
Consistency guarantee	If the standby goes down, the primary blocks the COMMITs
Confidence for critical data	Requires a reliable and fast network

Swipe to see more

Standby down = primary blocked

In synchronous mode, if the standby becomes unavailable, the primary blocks on every COMMIT while waiting for the confirmation. This is why synchronous replication is often used with at least two standbys and the FIRST 1 (standby1, standby2) clause to switch over automatically.

Logical replication: an overview

Logical replication is a topic in its own right. Here is a quick overview of the mechanism to understand when to consider it.

Principle

The primary creates a publication on chosen tables. Another PostgreSQL server creates a subscription to receive the changes:

-- On the primary (publisher)
CREATE PUBLICATION ma_publication FOR TABLE app.servers, app.deployments;

-- On the subscriber (an independent server)
CREATE SUBSCRIPTION ma_souscription
    CONNECTION 'host=192.168.122.61 dbname=labdb user=replicator password=motdepasse_fort'
    PUBLICATION ma_publication;

When to use logical replication

The first three cases have one thing in common: the source and the destination are two autonomous servers that pursue different goals. The fourth point is a limit to remember before going down this path by mistake, because a subscriber looks like a standby from a distance: it receives data continuously and stays up to date. But it has neither a common timeline with the source, nor any notion of promotion, and nothing guarantees that it contains the entire cluster.

• Version migration: replicate from a PostgreSQL 16 to an 18
• Selective replication: transmit only some tables
• Aggregation: collect data from several sources
• Not for failover: the subscriber is not a standby (no timeline, no promote)

Automatic orchestration tools

The manual failover described above assumes that a human notices the failure, checks the state of the standby and starts the promotion. In practice, this often means several minutes of unavailability, and a much worse risk: split-brain, when the old primary becomes reachable again and accepts writes while the standby has already been promoted. Automating failover is above all about acquiring a consensus mechanism able to designate a single legitimate primary.

Beyond manual failover

Manual failover works, but it is not suited to production where the reaction time must be a few seconds. Three main tools automate PostgreSQL failover:

Tool	Description
Patroni	The most widely used. Uses a DCS (etcd, Consul, ZooKeeper) for consensus. Handles promote, fencing and automatic reconfiguration.
repmgr	Mature PostgreSQL extension. Failure detection and automatic promote with repmgrd.
pg_auto_failover	Developed by Citus/Microsoft. Simple architecture with a "monitor" node that coordinates the failover.

Swipe to see more

These tools also handle the automatic rebuild of the old primary as a new standby after a failover.

Troubleshooting

The standby does not connect

Follow these four checks in order, from the network to the application: each one rules out a layer. Step 3 is the most discriminating, because it reproduces exactly what the standby does: a connection in replication mode, which uses a different pg_hba.conf line than an ordinary psql connection. A regular psql that works while replication fails almost always points to a missing or misplaced replication line.

1. Check network connectivity:

   # From the standby
   pg_isready -h 192.168.122.61 -p 5432

2. Check pg_hba.conf on the primary, the replication role must be allowed from the standby's IP:

   SELECT user_name, address, auth_method
   FROM pg_hba_file_rules
   WHERE 'replication' = ANY(database);

3. Check the password, test the connection manually:

   psql "host=192.168.122.61 user=replicator replication=true" -c "IDENTIFY_SYSTEM;"

   A line with systemid, timeline and xlogpos confirms that the replication connection succeeds: password and pg_hba.conf rule are correct.

4. Check the logs of the standby:

   sudo tail -20 /var/log/postgresql/postgresql-18-main.log

The lag keeps increasing

A lag that keeps growing without ever going back down is not handled like a one-off spike: it means that the standby receives or replays the WAL more slowly than the primary produces it, and the gap will not close by itself. Before going through this table, isolate the faulty layer by comparing the columns of pg_stat_replication: if sent_lsn is far ahead of flush_lsn, the problem is network or disk on the standby side; if flush_lsn is close to sent_lsn but replay_lsn lags, the replay is what is blocking, often because of a conflicting read query.

Possible cause	Solution
Very high write load on the primary	Increase wal_keep_size, check the standby's disk IOPS
Long queries on the standby (conflicts)	Increase max_standby_streaming_delay or move the queries elsewhere
Saturated network	Check the bandwidth between primary and standby
Unused replication slot	Check pg_replication_slots, drop the orphan slots

Swipe to see more

The promote fails

pg_promote() returns f rather than an error when the promotion does not complete within the allotted time, which gives a poor diagnosis. The three checks below cover the real causes, and the last one is the most rewarding: the server log generally says why the switchover was refused.

• Check that pg_is_in_recovery() returns t before the promote
• Check that the user has the rights (superuser, or a role that has been granted EXECUTE on the pg_promote function)
• Look at the logs: sudo tail -30 /var/log/postgresql/postgresql-18-main.log

An orphan slot is filling the disk

The symptom shows up on the primary, not on the standby: pg_wal grows without limit while the activity has not changed. The query below quantifies the problem before you act, by translating the LSN gap retained by each inactive slot into a readable volume. Only drop a slot after confirming that the corresponding standby will not come back: the drop is definitive, and a standby that reconnected afterwards would have to be rebuilt.

-- Identify the inactive slots
SELECT slot_name, active, pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS retained_wal
FROM pg_replication_slots
WHERE NOT active;

-- Drop an orphan slot
SELECT pg_drop_replication_slot('standby_slot');

Put it into practice

The lab has you stream a standby on db2.lab from a primary, db1.lab, which holds the facturation database, then prove the data flows. Note that the lab brings up two virtual machines, so dsoxlab provision creates two of them: plan your host's resources accordingly.

The validation first checks the configuration, with this guide's names: a replicator role that can log in and replicate without being a superuser or being able to create databases; a physical slot standby_slot, which must be active, that is, consumed by the standby thanks to primary_slot_name; in the primary's pg_hba.conf, a host or hostssl line for the replication database and an address other than the loopback, whose method is neither trust nor password (the guide uses scram-sha-256); finally a standby in recovery and a streaming state in pg_stat_replication. It does not check the encryption of the stream: host passes, whereas the guide recommends hostssl outside a lab.

The test that matters is the only one that cannot be wrong: it writes a row into the primary's appli.sonde table, laid down empty by the lab, and looks for it on the standby for 20 seconds. A correct configuration that carries nothing does not pass, and the copy method matters little. The last check compares the report /home/student/rapport-replication.txt with the primary: number of streaming standbys, state of the slot, and the fact that replicator is not a superuser.

Guided lab Practice

Learn how to do it

Stream a standby from a primary, and prove the data flows

$ dsoxlab run pg-09-repliquer

Environment: VM·Duration: ~50 min

How it is checked: the state of the system is checked after a reboot, not the command you typed.

First time? What DSOXLab is and how to install it

DSOXLab is the command line that prepares a disposable environment (an isolated shell or a VM), runs the lab scenario, then checks the real state of the system and computes a score. Nothing is installed or broken on your workstation.

It needs Python 3.11 or later, uv or pipx, and git. The installation guide details these prerequisites; on Windows, or on a Mac without Python, use the appliance instead.

This lab starts virtual machines: it also needs ansible-playbook, a hypervisor (KVM or Incus), Terraform and hardware virtualization (/dev/kvm), which inside a VirtualBox VM means nested virtualization. The first time, dsoxlab start pg-09-repliquer checks all of it before creating the machines. Prepare your machine for vm labs.

Install the CLI and add the catalogue, once:

uv tool install dsoxlab   # or: pipx install dsoxlab
dsoxlab catalog add https://github.com/stephrobert/bdd-dsoxlab-training

Before you run the lab, check:

• DSOXLab installed
• catalogue available
• compatible environment (isolated shell or VM)

To check, nothing is probed from this page: run dsoxlab doctor. Read the diagnosis

Run this lab, then validate your work:

dsoxlab run pg-09-repliquer
dsoxlab check pg-09-repliquer

See every lab: dsoxlab list-labs. Track your progress: dsoxlab progress. The full dsoxlab guide covers scoring and the pitfalls.

Key takeaways

1. Streaming replication continuously copies the WAL from the primary to the standby; in production, combine it with WAL archiving (restore_command) to cover prolonged disconnections
2. Replication is not a backup: a DROP TABLE is replicated immediately
3. pg_basebackup -R initializes the standby and automatically generates standby.signal + primary_conninfo
4. Since PostgreSQL 12, recovery.conf no longer exists, everything goes through the normal parameters
5. Replication slots prevent premature recycling of the WAL; configure max_slot_wal_keep_size and idle_replication_slot_timeout to avoid a full disk
6. The hot standby accepts reads but refuses all writes (INSERT, UPDATE, DELETE, DDL)
7. pg_promote() turns the standby into an autonomous primary on a new timeline, it is irreversible
8. Synchronous replication is enabled through synchronous_standby_names, it guarantees zero data loss but blocks if the standby goes down; the declared name must be the real application_name (18/main or walreceiver depending on cluster_name), observed in pg_stat_replication, otherwise nothing is synchronous and no error says so
9. In production, use an automatic orchestration tool (Patroni, repmgr) for the failover

Knowledge check

Eight questions on what a replication does not protect against, the role of the slot and of primary_slot_name, what makes replication synchronous, the choice between physical and logical replication, the conflicts of the hot standby and the fate of the old primary after a promote.

Knowledge check

Check what you have learned with this interactive quiz

8 questions

8 min

70% to pass

How it works

• The timer starts when you click Start
• Multiple choice, true/false and short answer questions
• You can move between questions
• Detailed results are shown at the end

Start the quiz

Starts the quiz and the timer

×

See in the glossary →