Cloning Production with pg_basebackup for Upgrade Rehearsal

How to build a byte-faithful, disposable clone of a production cluster with pg_basebackup so an extension upgrade can be rehearsed against real data cardinality, real catalog state, and the real shared-library set before it touches the live fleet.

Up: Test Environment Routing — this page is the concrete cloning technique that routing depends on, and it feeds the wider Extension Upgrade Planning & Compatibility Validation pipeline.

Context & When This Applies

A rehearsal is only trustworthy if the clone matches production where it matters: the same extension versions, the same catalog rows, the same shared_preload_libraries, and enough data to reproduce real lock windows. pg_basebackup produces exactly that — a physical, block-level copy of the running cluster including every extension’s installed state and on-disk files — which is why it is the right cloning tool for an upgrade rehearsal, as opposed to a logical pg_dump that would rebuild the schema and lose per-node artifact fidelity. This applies to PostgreSQL 12–17 wherever you need a throwaway clone to validate a promotion, and it is the substrate for the async upgrade simulation that captures locks and catalog diffs.

The clone must be disposable and isolated: it should never connect back to production, and it should carry a clear marker so automation cannot mistake it for a real node. A rehearsal cluster that accidentally joins replication or gets promoted is worse than no rehearsal at all.

Cloning production for a disposable upgrade rehearsal A production primary is copied block for block by pg_basebackup into a disposable clone. The clone inherits the identical extension version set, catalog state, and shared_preload_libraries, plus real data cardinality. The upgrade is rehearsed on the clone, capturing lock windows and catalog diffs, and the clone is then destroyed. The clone is isolated: it never connects back to production and carries a marker so automation cannot mistake it for a live node. Production primary serving traffic do not disrupt Disposable clone same exts + libs real cardinality isolated · marked Rehearse upgrade capture locks capture catalog diffs Destroy clone no residue, no cost pg_basebackup

Concept: Physical Fidelity Beats Logical Reconstruction

The value of pg_basebackup for rehearsal is physical fidelity. It copies the data directory at the block level while the primary streams WAL, producing a clone whose pg_extension, pg_available_extension_versions, and on-disk .control/.so files are identical to the source node. That fidelity matters because the failure modes an upgrade rehearsal is meant to catch — a missing update path on one node, an ABI mismatch, a lock window proportional to real row counts — are invisible to a logical dump that rebuilds the schema fresh and normalizes away per-node drift. The clone reproduces the exact reachability graph the dependency tree analysis pre-flight reads.

The second concept is WAL consistency. pg_basebackup takes a base copy plus the WAL needed to reach a consistent recovery point, so the clone starts in exactly the state the primary was in when the backup completed — no torn pages, no half-applied transactions. Starting the clone with recovery configured and then promoting it standalone gives you a writable, production-faithful cluster you can safely mutate, which is the substrate test environment routing routes candidates onto.

Runnable Implementation

The script below takes a streaming base backup, configures the copy as an isolated standalone cluster on a throwaway port, and marks it so automation cannot confuse it with production.

#!/usr/bin/env bash
# Clone a production primary into a disposable, isolated rehearsal cluster.
set -euo pipefail

SRC_HOST="${1:?usage: clone-for-rehearsal.sh <src_host> <clone_dir> <port>}"
CLONE_DIR="${2:?clone data directory}"
PORT="${3:?throwaway port, e.g. 55432}"
REPL_USER="${PGREPLUSER:-replicator}"

# 1. Stream a block-level base backup including required WAL. -R writes a
#    standby.signal + primary_conninfo; we override it to standalone below.
pg_basebackup \
  --host="$SRC_HOST" --username="$REPL_USER" \
  --pgdata="$CLONE_DIR" \
  --wal-method=stream \
  --checkpoint=fast \
  --progress --verbose

# 2. Make the clone STANDALONE and ISOLATED: no primary_conninfo, so it can
#    never stream from or reconnect to production. Remove any standby signal.
rm -f "$CLONE_DIR/standby.signal" "$CLONE_DIR/recovery.signal"

# 3. Mark the clone unmistakably and move it off the production port.
cat >> "$CLONE_DIR/postgresql.auto.conf" <<EOF
port = ${PORT}
cluster_name = 'REHEARSAL-DISPOSABLE'
# never let this node be mistaken for production automation targets
EOF

# 4. Start it. It comes up as a normal writable cluster in the primary's state.
pg_ctl -D "$CLONE_DIR" -o "-p ${PORT}" -w start

echo "Rehearsal clone up on port ${PORT}; extensions match source node."

Once up, run the candidate upgrade against this clone exactly as the pipeline would, then feed the captured lock timings into threshold tuning for downtime windows and destroy the clone. Automate the promotion rehearsal itself with ALTER EXTENSION automation.

Expected Output & Verification

pg_basebackup reports progress and a clean finish:

24576/24576 kB (100%), 1/1 tablespace
pg_basebackup: base backup completed

Verify the clone is faithful and isolated before rehearsing on it — a clone still pointed at production is a hazard:

-- On the CLONE: extensions must match the source node exactly.
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- And it must be standalone: recovery off, no upstream primary.
SELECT pg_is_in_recovery() AS still_a_standby,
       current_setting('cluster_name') AS marker;

still_a_standby must be false and marker must read REHEARSAL-DISPOSABLE. Compare the extension list against the production node recorded in compatibility matrix synchronization to confirm nothing drifted during the copy.

Edge Cases & Gotchas

A clone left in recovery cannot be mutated. If you forget to remove standby.signal, the clone stays read-only and every ALTER EXTENSION fails:

ERROR:  cannot execute ALTER EXTENSION in a read-only transaction

Remove the standby/recovery signals (step 2) so the clone promotes to a writable standalone before rehearsing.

shared_preload_libraries must match or preload extensions will not load. pg_basebackup copies the data directory, but if you start the clone with a different postgresql.conf that omits timescaledb or pg_partman_bgw, those extensions behave differently than in production. Copy the preload configuration verbatim, per TimescaleDB extension upgrade quirks and preload ordering.

Replication slots and connection info can leak back to production. The -R flag writes primary_conninfo pointing at the source; leaving it in place risks the clone streaming from — or being confused with — production. Always strip it and assign a distinct cluster_name.

Insufficient WAL retention aborts the backup. On a busy primary, if WAL is recycled faster than the backup streams, pg_basebackup fails with a missing-WAL error. Use --wal-method=stream (as above) so a dedicated connection ships WAL alongside the base copy, and confirm a replication slot or adequate wal_keep_size on the source.