> ## Documentation Index
> Fetch the complete documentation index at: https://xata.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/docs/opensource/pgstream/docs/snapshots#️-the-data-snapshot-source-must-be-a-single-instance);
> `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

```bash theme={null}
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](/docs/opensource/pgstream/docs/cli#init).

If you already created one, drop it:

```bash theme={null}
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

```bash theme={null}
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](/docs/opensource/pgstream/docs/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**:
>
> ```sql theme={null}
> 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.

```yaml theme={null}
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](/docs/opensource/pgstream/docs/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:

  ```sql theme={null}
  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.