# Migrate a PostgreSQL database to pgrun.dev

This file is a complete migration runbook written for a coding agent.
Drop it into your repository and tell your agent:

> "Migrate my database to pgrun.dev following pgrun-migrate.md"

It works with Claude Code, Cursor, Codex, or any agent that can run shell
commands. A human can follow it too -- every step is a plain command.

Questions at any point: support@postgresrun.com (you get the person who runs
the databases, not a ticket queue).

---

## Rules for the agent (non-negotiable)

1. **Never modify the source database's data.** No INSERT, UPDATE, DELETE,
   TRUNCATE, DROP, or ALTER on any user table. The only writes ever allowed
   on the source are the replication objects in Path B/C (a publication, a
   replication slot, and where needed `ALTER TABLE ... REPLICA IDENTITY FULL`),
   and only after your human has approved that path.
2. **Never cut over without explicit human confirmation.** Repointing the
   application is the human's decision, made after the verification step
   passes. Ask; do not infer.
3. **The source stays running and intact after migration.** Nothing is
   deleted from it, ever. Rollback = simply don't cut over.
4. **If a step fails twice, or the situation doesn't match this document,
   stop** and show your human what happened -- or have them email
   support@postgresrun.com. Do not improvise around a failed migration step.
5. **Never use `--clean` or `--create` with pg_dump/pg_restore**, and never
   run `pg_restore` or a schema load with `$SOURCE_URL`. In this document the
   source URL appears only in read-only commands plus the two approved
   replication writes -- keep it that way.
6. **A live-sync migration (Path B/C) creates a replication slot on the
   source.** An abandoned slot forces the source to retain WAL forever and
   can fill its disk -- a production outage. If you abandon or abort a
   live-sync migration for any reason, ALWAYS run the cleanup in
   "Aborting a live-sync migration" below, and monitor slot lag while the
   migration runs.
7. Store credentials in environment variables, never in files you commit.

**Nervous? Rehearse first.** Restore a recent backup of the source into a
scratch database, point `SOURCE_URL` at the scratch copy, and run this whole
document against it. A successful rehearsal is the strongest possible
evidence -- and costs one afternoon.

## What you need before starting

- `SOURCE_URL` -- the current database (`postgres://...`). Read access to
  pg_catalog; for Path B/C also a user with REPLICATION.
- `TARGET_URL` -- a pgrun.dev database. Your human creates one at
  https://app.pgrun.dev (pick region/size/version, ~2 minutes) and gives you
  the connection string. Target storage should be at least the source's
  database size, ideally 150%.
- `psql` and `pg_dump`/`pg_restore` installed, **matching the target's major
  version or newer** (`pg_dump --version`).
- Your human's answer to one question: *"is a maintenance window acceptable,
  and how long?"*

## Step 0 -- preflight

```sh
psql "$SOURCE_URL" -Atc "SELECT version();"
psql "$TARGET_URL" -Atc "SELECT version();"
# The two URLs must NOT point at the same cluster -- compare start times:
psql "$SOURCE_URL" -Atc "SELECT pg_postmaster_start_time();"
psql "$TARGET_URL" -Atc "SELECT pg_postmaster_start_time();"
# Target must be empty (a fresh pgrun database is):
psql "$TARGET_URL" -Atc "SELECT count(*) FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE c.relkind='r' AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast');"
```

If the two start times are identical, both URLs point at one cluster --
**stop**. If the target count is not 0, **stop** -- never restore into a
non-empty database. Then show your human both hostnames, state plainly which
is source and which is target and which path you chose, and wait for their
confirmation before continuing.

## Step 1 -- run discovery

```sh
curl -O https://pgrun.dev/discovery.sql
curl -sO https://pgrun.dev/discovery.sql.sha256 && shasum -a 256 -c discovery.sql.sha256
psql -q -f discovery.sql "$SOURCE_URL"
```

This writes `pgrun-discovery.json` -- read-only SELECTs over the catalogs; it
never reads a row of table data. Read the JSON. The fields that drive every
decision below:

| field | meaning |
|---|---|
| `database.size_bytes` | total size; "small" below means < 50 GB |
| `settings.wal_level` | must be `logical` for Path B/C |
| `settings.max_replication_slots` / `max_wal_senders` | must be >= 1 for Path B/C |
| `tables.top[]` | biggest tables; per table: `has_usable_identity`, `relreplident`, `n_upd`, `n_del` |
| `sequences.total_count` | all must be synced at cutover (Step 5) |
| `large_objects.count` | > 0 excludes Path B |
| `matviews[]` | need REFRESH on the target at cutover |
| `extensions[]` | recreate on target in Step 3 |

A table **blocks CDC** when `has_usable_identity` is false, `relreplident`
is not `"f"`, and it has updates or deletes (`n_upd > 0 or n_del > 0`).

Optionally drop the JSON on https://pgrun.dev/migrate/assessment to see this
analysis rendered.

## Step 2 -- choose the path

Apply the first rule that matches:

1. `wal_level != "logical"` (or slots/senders are 0) and your human cannot
   change it -> **Path A** (dump/restore, maintenance window). On managed
   sources this is often changeable: RDS/Aurora parameter
   `rds.logical_replication=1`, Cloud SQL flag
   `cloudsql.logical_decoding=on`, self-hosted `wal_level=logical` +
   restart. Heroku Postgres cannot -- Path A, or email us about trigger-based
   sync.
2. Any table blocks CDC -> fix each one first (add a primary key or unique
   NOT NULL index; where impossible:
   `ALTER TABLE <t> REPLICA IDENTITY FULL;` -- needs human approval, and note
   it takes a brief ACCESS EXCLUSIVE lock: run it at a quiet moment, never
   mid-peak), re-run discovery, then continue. If the database is small and
   a window is acceptable, Path A skips the fixes entirely.
3. Small + `wal_level=logical` + no large objects -> **Path B** (native
   logical replication, brief cutover).
4. Otherwise -> **Path C** (pgcopydb --follow, near-zero downtime).

## Step 3 -- extensions on the target

For each entry in `extensions[]` except `plpgsql`:

```sh
psql "$TARGET_URL" -c 'CREATE EXTENSION IF NOT EXISTS "<name>";'
```

If any `CREATE EXTENSION` fails as unavailable, **stop** -- email
support@postgresrun.com with the extension name before migrating anything.

## Path A -- pg_dump / pg_restore (maintenance window)

```sh
# 1. Human confirms the window has started; application writes are stopped.
# 2. Export (custom format keeps it restartable and parallel-restorable):
pg_dump -Fc --no-owner --no-privileges "$SOURCE_URL" -f pgrun-migration.dump
# 3. Restore with parallel jobs:
pg_restore --no-owner --no-privileges -j 4 -d "$TARGET_URL" pgrun-migration.dump
```

`--no-owner --no-privileges` matters: role names differ between providers,
and without these flags a managed-to-managed restore fails on GRANTs.
Sequences and matview contents are included in the dump -- skip the sequence
step in the cutover checklist, keep the rest. Then go to Step 5.

## Path B -- native logical replication (brief cutover)

Requires the pgrun target to be able to reach the source over the network
(public hostname or allowlisted). If the source is unreachable from outside,
use Path C from a host that can see both.

```sh
# 1. Schema only, first:
pg_dump --schema-only --no-owner --no-privileges "$SOURCE_URL" | psql "$TARGET_URL"
# 2. On the SOURCE (the one approved write):
psql "$SOURCE_URL" -c "CREATE PUBLICATION pgrun_migration FOR ALL TABLES;"
# 3. On the TARGET:
psql "$TARGET_URL" -c "CREATE SUBSCRIPTION pgrun_migration CONNECTION '$SOURCE_URL' PUBLICATION pgrun_migration;"
# 4. Watch the initial sync until every table is ready (state 'r'):
psql "$TARGET_URL" -c "SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel;"
# 5. Watch lag until it is near zero:
psql "$SOURCE_URL" -c "SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag FROM pg_replication_slots;"
```

Then the cutover checklist (Step 5). After cutover, clean up:
`DROP SUBSCRIPTION pgrun_migration;` on the target -- this also removes the
slot on the source.

## Path C -- pgcopydb --follow (near-zero downtime)

Run from a host that can reach both databases. Install pgcopydb
(`apt install pgcopydb` / `brew install pgcopydb`); if it can't be
installed, fall back to Path A or B or email us.

```sh
export PGCOPYDB_SOURCE_PGURI="$SOURCE_URL"
export PGCOPYDB_TARGET_PGURI="$TARGET_URL"
# Parallel copy + continuous change streaming:
pgcopydb clone --follow --table-jobs 4 --index-jobs 4
```

`clone --follow` copies schema and data in parallel, then keeps applying
changes. When the human is ready to cut over:

```sh
# Stop application writes briefly, then let pgcopydb finish applying:
pgcopydb stream sentinel set endpos --current
# It exits when everything up to that point is applied.
```

pgcopydb copies sequences and large objects itself -- in the cutover
checklist you still verify sequences, but usually nothing to fix. Continue
to Step 5.

## Step 5 -- cutover checklist (every path)

Application writes must be stopped (Path A: they already are) before this
checklist. Work top to bottom; all commands are safe to re-run.

**1. Sequences** (skip on Path A -- the dump carried them):

```sh
psql "$SOURCE_URL" -Atc "SELECT format('SELECT setval(%L, %s, %s);', schemaname||'.'||sequencename, coalesce(last_value,1), (last_value IS NOT NULL)::text) FROM pg_sequences;" > /tmp/pgrun-seqs.sql
psql "$TARGET_URL" -v ON_ERROR_STOP=1 -f /tmp/pgrun-seqs.sql
```

(The `::text` matters -- without it the boolean renders as bare `t`, which is
not valid SQL. `ON_ERROR_STOP` makes any failure loud instead of silent.)

Skipping this causes duplicate-key errors on the first inserts after
cutover -- it is the classic logical-migration failure.

**2. Materialized views** -- for each entry in the discovery `matviews[]`,
on the target, in dependency order:

```sh
psql "$TARGET_URL" -c 'REFRESH MATERIALIZED VIEW <schema>.<name>;'
```

**3. Verify row counts** -- generate per-table counts from the source,
run the generated statements against both sides, diff:

```sh
psql "$SOURCE_URL" -Atc "SELECT format('SELECT %L, count(*) FROM %I.%I;', n.nspname||'.'||c.relname, n.nspname, c.relname) FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE c.relkind='r' AND NOT c.relispartition AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast');" > /tmp/pgrun-counts.sql
psql "$SOURCE_URL" -Atf /tmp/pgrun-counts.sql > /tmp/counts-source.txt
psql "$TARGET_URL" -Atf /tmp/pgrun-counts.sql > /tmp/counts-target.txt
diff /tmp/counts-source.txt /tmp/counts-target.txt && echo "COUNTS MATCH"
```

Any difference -> **stop, do not cut over**, show your human.

**4. Fresh statistics on the target:**

```sh
psql "$TARGET_URL" -c "ANALYZE;"
```

**5. Human confirmation** -- show your human: counts matched, sequences
synced, matviews refreshed, extensions present. They say the word; only
then update the application's `DATABASE_URL` to the pgrun connection string
and restart the application.

**6. Afterwards** -- leave the source untouched and running for at least a
few days. Path B: drop the subscription (see Path B). Rollback at any point
before the source receives no new reads/writes = point the application back.

## While a live-sync migration runs (Path B/C)

Check both of these roughly every 30 minutes until cutover; more often on a
write-heavy source:

```sh
# Slot lag on the source -- should trend toward zero, never grow unbounded:
psql "$SOURCE_URL" -Atc "SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) FROM pg_replication_slots;"
# Free disk on the source (managed providers: check the provider's console).
```

If lag grows for hours or source free disk is shrinking fast, pause: tell
your human, and if the migration cannot proceed promptly, abort cleanly
(next section) rather than leaving the slot in place overnight.

## Aborting a live-sync migration (Path B/C)

Aborting is always safe for your data -- the source was never modified. What
MUST be cleaned up is the replication slot, or the source will retain WAL
until its disk fills:

```sh
# Path B -- dropping the subscription on the target also drops the slot on the source:
psql "$TARGET_URL" -c "DROP SUBSCRIPTION IF EXISTS pgrun_migration;"
# If that errors (half-created state), detach first, then drop the slot manually:
psql "$TARGET_URL" -c "ALTER SUBSCRIPTION pgrun_migration DISABLE;"
psql "$TARGET_URL" -c "ALTER SUBSCRIPTION pgrun_migration SET (slot_name = NONE);"
psql "$TARGET_URL" -c "DROP SUBSCRIPTION pgrun_migration;"
psql "$SOURCE_URL" -Atc "SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE slot_name = 'pgrun_migration';"

# Path C -- pgcopydb cleans up its own slot and origin:
pgcopydb stream cleanup
# Manual fallback if pgcopydb is gone:
psql "$SOURCE_URL" -Atc "SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE slot_name LIKE 'pgcopydb%';"

# Verify nothing is left -- this must return zero rows:
psql "$SOURCE_URL" -Atc "SELECT slot_name FROM pg_replication_slots;"
```

Also drop the publication on the source (Path B): 
`psql "$SOURCE_URL" -c "DROP PUBLICATION IF EXISTS pgrun_migration;"` -- 
publications are harmless but tidy. The target database can simply be
deleted from the pgrun dashboard and recreated for the next attempt.

## Troubleshooting

- `permission denied` during discovery or dump -> the user lacks read access
  to some schemas; run as the owner or grant SELECT.
- Restore errors mentioning roles or GRANTs -> you forgot
  `--no-owner --no-privileges`.
- Subscription stuck in state `d` (data sync) -> check
  `max_logical_replication_workers` on the target and table sizes; large
  tables just take time.
- Anything else, twice -> stop. support@postgresrun.com -- include the
  command, the full error, and `pgrun-discovery.json` if your human agrees
  to share it (it contains schema shape, never data).