pg_sandbox

disposable PostgreSQL, on demand

Spin up any installed PG version in seconds
Full topologies — replication & clusters — with one command
Every sandbox is a throwaway directory under ~/postgresql-sandboxes/

On this box: PG 16.5 · 17.1 · 17.2 · 17.4 · 18.3 — pg_sandbox --version → v1.0.1

Single instance lifecycle core flow

deploy → connect → load data → inspect → destroy


export PGS_BIN_DIR=/opt/postgresql/18.3
cd ~/postgresql-sandboxes

pg_sandbox deploy  -s pg18
pg_sandbox status  -s pg18
pg_sandbox use     -s pg18 -- -c "SELECT version();"
pg_sandbox run     -s pg18 -- pgbench -i      # -d auto-injected
pg_sandbox run     -s pg18 -- pgbench -T 3    # ~6k tps locally
pg_sandbox config  show -s pg18               # KEY / VALUE / SOURCE
pg_sandbox destroy -s pg18 --force

One binary wraps initdb, pg_ctl, psql and every client — connection params injected for you.

Streaming replication primary + standby

A primary and an async standby on its own port — then promote.


pg_sandbox deploy -s primary
pg_sandbox deploy -s standby1 \
    --replicate-from primary --slot primary_standby1_slot

sleep 2
pg_sandbox status -s primary    # replicas[0]=walreceiver state=streaming

pg_sandbox use -s primary  -- -c "CREATE TABLE t(id int); INSERT INTO t VALUES (1);"
pg_sandbox use -s standby1 -- -c "SELECT * FROM t;"   # → the row, replicated

pg_sandbox promote -s standby1  # standby1 role → primary

pg_sandbox destroy -s standby1 --force
pg_sandbox destroy -s primary  --force

Replica gets its own port automatically. status reports live write/flush/replay lag.

One-shot cluster 1 command

A whole primary + N-standby topology, managed as a unit.


pg_sandbox cluster deploy -s rep -N 2   # rep_p + rep_s1 + rep_s2

sleep 2
pg_sandbox cluster status -s rep        # primary shows BOTH replicas streaming
pg_sandbox global_status                # grouped fleet view

pg_sandbox cluster destroy -s rep --force   # tears down all 3 members

Members auto-named rep_p / rep_s1 / rep_s2, ports 65432–65434. --sync-count is accepted but currently deferred (still async today).

Logical replication PG 17 → 18

Table-level pub/sub across major versions — the classic upgrade path.


pg_sandbox deploy  -s pub -b /opt/postgresql/17.4         # publisher on PG 17
pg_sandbox use     -s pub -- -c "CREATE TABLE t(id int primary key); INSERT INTO t VALUES (1);"
pg_sandbox publish -s pub --pub-name my_pub --all-tables   # auto wal_level=logical + restart

pg_sandbox deploy    -s sub -b /opt/postgresql/18.3        # subscriber on PG 18
pg_sandbox subscribe -s sub --from pub --pub-name my_pub --copy-schema

sleep 2
pg_sandbox use -s sub -- -c "SELECT * FROM t;"   # → the row, replicated

pg_sandbox destroy -s sub --force
pg_sandbox destroy -s pub --force

Per-sandbox -b picks the major. publish raises wal_level=logical & restarts for you; --copy-schema dumps the schema from 17 into 18 first.

pg_gather report diagnostics

Turn a captured out.txt into an HTML diagnostics report.


# capture from any sandbox, then render
pg_sandbox use -s pg18 -- -X -f "$GATHER/gather.sql" > out.txt
pg_sandbox report --pg-gather-dir "$GATHER" \
                  --input ~/postgresql-sandboxes/out.txt \
                  --output ~/postgresql-sandboxes/gather.html
open ~/postgresql-sandboxes/gather.html

Auto-resolves bin-dir; ingests the dump in a throwaway sandbox that then self-destructs. Input is pre-scanned — psql meta-commands in a hostile out.txt are rejected.

Also: global_status (fleet view) · build <ver> (compile from source) — everything reproducible & disposable.