Replicas

This guide explains how to run pgstream — both the snapshot and the replication phases — against a physical streaming replica instead of the primary. This reduces load on the primary and also avoids locking issues due to the long-running snapshots created by pgstream.

Requirements

• PostgreSQL 16 or later. Logical decoding on a standby does not exist before 16.
• wal_level = logical on the primary and on the replica. See the warning below — the replica does not inherit this.
• hot_standby_feedback = on on the replica.
• The wal2json output plugin installed on the replica’s host. It is a shared library, not a SQL extension, so it does not arrive through replication — it must be present on that machine.
• A single-instance endpoint for the replica, not a load-balanced reader endpoint.
• Superuser-equivalent access on the primary for the one-time pgstream init.

⚠️ wal_level = logical is not inherited by the replica. If the primary sets it with a command line flag, the setting never reaches postgresql.conf and so is not copied by pg_basebackup. The replica comes up at wal_level = replica and slot creation fails with logical decoding requires "wal_level" >= "logical". Set it explicitly on the replica.

⚠️ Point the source URL at a specific replica instance. The parallel data snapshot exports a transaction snapshot and imports it on sibling connections, and an exported snapshot is instance-local. An Aurora reader (cluster-ro) endpoint, an RDS reader endpoint, or a pooler spanning instances will fail nondeterministically. See Snapshots; pgstream check flags this before a snapshot runs.

How streaming DDL works

pgstream’s DDL replication is stateless. pgstream init installs an event trigger (pgstream.emit_ddl) that calls pg_logical_emit_message(), so DDL travels inline in the WAL stream as a logical message rather than through a table that has to be read back.

You cannot install that event trigger on the replica because a standby is read only. But it’s not needed. The event trigger runs on the primary, where DDL actually executes, and pg_logical_emit_message() writes an ordinary WAL record. The replica replays that record like any other, so a logical slot on the replica decodes it for free. No pgstream state of any kind is required on the replica.

Setup

1. Initialise pgstream on the primary — without a replication slot

pgstream init --postgres-url <primary-url> --migrations-only

This installs the pgstream schema, functions and the emit_ddl event trigger, all of which reach the replica through physical replication.

--migrations-only is important: without it, init also creates a logical replication slot on the primary. In this setup nothing ever consumes that slot, so it would pin WAL on the primary indefinitely — the exact disk pressure you are trying to avoid. See CLI: init.

If you already created one, drop it:

pgstream destroy --postgres-url <primary-url> --slot-only

⚠️ Use --slot-only. A plain pgstream destroy would also remove the pgstream schema and the emit_ddl event trigger, which must stay on the primary.

⚠️ Two different slots live on the primary and only the slot_type column tells them apart. Drop the unused logical slot; keep the replica’s own physical slot, which is what stops the primary from discarding WAL the replica still needs.

2. Configure the replica

Both settings are required, and neither is inherited from the primary:

wal_level = logical
hot_standby_feedback = on

3. Create the logical replication slot on the replica

pgstream init --postgres-url <replica-url> --slot-only

--slot-only creates the replication slot and skips the schema and migrations, which is what makes this work against a read only standby. See CLI: init.

The slot name only has to match what pgstream looks for on the source URL. If --replication-slot and source.postgres.replication.replication_slot are both left empty, pgstream derives pgstream_<dbname>_slot from the URL’s database name; since the replica is a physical copy its database name is the same, so the default resolves identically on both hosts.

⚠️ If this command does not return promptly, it is waiting, not failing. Creating a logical slot on a standby blocks until the primary emits an xl_running_xacts record, which is what lets the standby build a consistent catalog snapshot. An idle primary may never emit one. Unblock it by running this on the primary:

SELECT pg_log_standby_snapshot();

4. Run pgstream against the replica

Point source.postgres.url at the replica and run as normal. pgstream run never creates a slot, it only connects to an existing one, so a missing or invalidated slot is a startup failure rather than something that silently recreates itself.

Configuration

Nothing here is replica-specific except the URL: the source simply points at the replica.

source:
  postgres:
    url: "postgres://user:pass@replica-host:5432/mydb"
    mode: snapshot_and_replication
    replication:
      replication_slot: "pgstream_mydb_slot" # the slot created on the replica
    snapshot:
      mode: full
      tables:
        - "*"
      schema:
        pgdump_pgrestore:
          clean_target_db: false

target:
  postgres:
    url: "postgres://user:pass@target-host:5432/targetdb"

See Configuration for the full set of options.

Operational considerations

• Slot invalidation is the main risk. A logical slot on a standby can be invalidated by recovery conflicts, or by the primary’s max_slot_wal_keep_size. Invalidation means a full resnapshot, not a resume — a slot on the primary does not have this failure mode. Monitor it:

  SELECT slot_name, conflicting, invalidation_reason  -- invalidation_reason is PG17+
  FROM pg_replication_slots WHERE slot_name = 'pgstream_<dbname>_slot';
  

• hot_standby_feedback holds back vacuum on the primary. It takes no locks there — it only moves the primary’s vacuum horizon — so it cannot cause the DDL lock contention that motivates moving the snapshot off the primary in the first place. What it does cost is bloat, and the cost differs sharply by phase:

  • Steady-state replication is cheap. A logical slot needs only catalog_xmin, so the retained rows are in the system catalogs rather than user tables.
  • The snapshot phase is not. The parallel data snapshot holds a REPEATABLE READ transaction open on the replica for as long as the snapshot runs, and that is a real reader with a data xmin. While it is open the primary cannot vacuum the tables being snapshotted.

  Measured on a primary/standby pair with a snapshot-style transaction open on the standby:

  VACUUM (VERBOSE) on the primary:
    tuples: 0 removed, 6000 remain, 5000 are dead but not yet removable
    removable cutoff: 742    <- pinned at the xmin fed back by the standby
  

  So budget for the primary not vacuuming the snapshotted tables for the duration of the snapshot, and let autovacuum catch up afterwards. Watch n_dead_tup on the largest tables. In the extreme — a horizon pinned long enough to trigger anti-wraparound autovacuum — DDL can end up blocked, because that flavour of autovacuum holds SHARE UPDATE EXCLUSIVE and is not cancelled when a conflicting lock request arrives.

• max_standby_streaming_delay is the complementary lever. It protects long-running queries on the replica by pausing WAL replay instead of touching the primary’s horizon, trading replication lag for query survival. It does not replace hot_standby_feedback for a long-lived slot: delaying the conflicting WAL only defers the invalidation.

• Failover. The slot is local to the replica, so promoting that node keeps it. Failing over to a different node, or rebuilding the replica, loses the slot and requires a new snapshot.

• The primary is still required for DDL. DDL continues to execute on the primary and the event trigger must remain installed there. Re-run pgstream init --postgres-url <primary-url> --migrations-only after any upgrade that adds migrations.