Dump and Import

Sometimes the right fix for an aging install is not another migration — it is a clean rebuild: take the data out, provision a fresh database from the schema tree, and load the data back minus the garbage. dump and import make that a repeatable workflow with a defined archive format, instead of a pile of ad-hoc pg_dump and psql invocations that nobody can reproduce a year later.

What the archive contains and what it means for an adopter is on the hub at Data Ownership and Export. This page is the format, the semantics, and the edges.

The archive

A dump directory is two things — a manifest and one file per table:

manifest.json                    dump time, source database, schema fingerprint,
                                 and per table: schema, name, column list, row
                                 count, byte count, SHA-256, relative file path
tables/<schema>.<table>.copy     PostgreSQL COPY text, one file per table

COPY text rather than a binary format, deliberately. It is stable across PostgreSQL versions, diffable when something needs a forensic look, and readable by anything that can read a tab-delimited file. The cost is size; the benefit is that a dump taken today still loads after a server upgrade, and that a human can open one.

The manifest is the contract between the two verbs, and the column list is the load-bearing field: import loads the intersection of the dumped columns and the target's current columns. Columns dropped since the dump fall away, columns added since take their defaults. That is what lets an archive survive modest schema evolution between the install it came from and the install it lands in. When the target has lost a column, rows are re-projected on the way in — COPY text escapes in-value tabs as \t, never emitting a literal one, so splitting on unescaped tabs is exact rather than heuristic.

Three things are excluded from a dump, mirroring the reconcile's own exclusions, because they re-create themselves: the hangfire schema, the harness's forge_db bookkeeping schema, and EF's __EFMigrationsHistory table. Generated columns are omitted from every column list — they cannot be COPYed back in.

The manifest also carries a schema fingerprint: a hash of the assembled desired state at dump time. Import compares it and warns when the tree has moved since, which is informational rather than fatal — the column intersection usually absorbs the drift. Running dump outside a forge-db checkout leaves the fingerprint null; the dump is still perfectly usable, the skew check simply cannot run, and dump says so.

Interchangeable with the app's own export

Forge's Admin → Database Transfer screen is an in-app port of these two verbs. Its archive is this exact directory layout, zipped — same manifest.json at the zip root, same tables/<schema>.<table>.copy entries, same camelCase serialization. So the two are interchangeable in both directions: unzip a UI export and hand it to forge-db import --from, or zip a CLI dump and upload it through the browser.

They are ports of one design, not one implementation, and they diverge in three places worth knowing:

	CLI	In-app
Cleanup rules	scrub/ scripts from this repo, run every import	An optional purge of soft-deleted rows (deleted_at IS NOT NULL)
Schema fingerprint	Stamped from the local schema/ tree	Always null — the running app has no repo to hash
Target	Any database, including a fresh one on another server	Necessarily the install it is running in

That last row is the reason the CLI still matters. A cross-database rebuild — dump the old, provision a new one, load into it — can only be done from the CLI, because an in-app import has nowhere else to point.

The clean rebuild

forge-db dump   --db postgres://…/old --out ./dump                    # 1. data out, read-only
createdb forge_clean
forge-db apply  --db postgres://…/forge_clean                         # 2. fresh schema
forge-db import --db postgres://…/forge_clean --from ./dump \
                --exclude 'audit_*,*_log'                             # 3. data back, minus garbage

dump is strictly read-only against the source and refuses to write into a non-empty directory (exit 2), so a second dump cannot quietly interleave with a first.

import is where the semantics live. Garbage leaves at three points, in order:

1. Exclude globs. Whole tables that do not come along — event logs, dead features. Patterns match against schema.table; * spans any run of characters including the dot, so *_log catches a _log table in any schema, and a pattern with no dot is also matched against the bare table name so audit_* reads the way an operator expects. Matching is case-insensitive.

2. scrub/ scripts. Version-controlled cleanup SQL, run after the load and before validation: purge soft-deleted rows, expired tokens, orphaned attachments, dead-feature leftovers that an exclude cannot express because they share a table with live rows. This is where "garbage" gets defined once, in review, instead of as somebody's ad-hoc DELETE.

Scrub scripts are the one deliberate divergence from every other script directory here: they are NOT applied-once. There is no ledger entry, and every import runs the full set, so each must be idempotent by authoring — a plain DELETE/UPDATE with a WHERE guard naturally is. Each runs in its own transaction; a failure rolls that script back and stops the import. --skip-scrub bypasses the directory entirely for a faithful one-to-one restore. If a scrub script deletes a parent row, it must delete or re-point that row's children in the same script, children first — the validation pass that follows exists to catch exactly what it leaves behind.

3. Foreign-key orphan validation. The load runs with FK triggers suspended, so nothing enforced them on the way in. A final pass re-checks every foreign key involving a loaded table and counts child rows whose parent did not make the trip. Orphans fail the import with exit 4 unless --allow-fk-orphans. They are precisely the garbage this workflow exists to surface, not a nuisance to paper over: an orphan means either an exclude went too far, or the source database was already inconsistent.

What the load actually does

The load is one transaction: TRUNCATE … RESTART IDENTITY CASCADE across every selected table, then a COPY … FROM STDIN per table. A failure anywhere in it rolls the whole thing back and leaves the target exactly as apply provisioned it. Import is therefore a replacement, not a merge — there is no reconciliation of rows, no upsert, no partial load.

Afterwards, in order: sequences are bumped past max(id) for every serial or identity column (the truncate reset them and COPY does not advance them), scrub runs, foreign keys are validated, and ANALYZE refreshes the planner's statistics. A JSON receipt of what was loaded, excluded, missing, scrubbed and orphaned lands in history/ beside apply's plan captures — an output, never replayed.

Two prerequisites bite in practice:

• Import needs a superuser connection. Suspending FK triggers is a superuser-only operation, and the harness turns the permission error into a clear message rather than a raw SQL state. The self-hosted stack's default database user qualifies; a hardened install that has demoted it will need the connection pointed at one that has not.
• Import truncates, so it is destructive on the target. It sits behind the same posture as a schema apply: a non-dev --env requires --yes --backup-taken, and is blocked with exit 3 otherwise.

A table that is in the dump but no longer in the target schema is skipped with a warning rather than failing the import — the same tolerance that the column intersection provides, one level up.

When to reach for this

A clean rebuild is not a routine operation, and it is not a substitute for the reconcile. Ordinary version-to-version schema movement is The Reconcile Harness; ordinary disaster recovery is a PostgreSQL snapshot, covered on the hub at Backup and Restore. This pair is for the cases where the database's contents are the problem: years of accumulated log rows nobody will ever read, a dead feature's tables, an install whose referential integrity you want proven rather than assumed, or a move onto a fresh server where you would rather not carry the old one's sediment along.

The FK validation pass is the part that repays the effort even when nothing else does. It is the only place in Forge that will tell you, exhaustively and with counts, where your data is already inconsistent.

Related

• The Reconcile Harness — provisioning the fresh database that step 2 imports into.
• Schema Ownership — what "provisioned from the schema tree" means.
• Data Ownership and Export — the export in the app, and what owning your data means here.
• forge-api wiki — the in-app port of these verbs.