Database Restore Runbook

Restore a Finaisse .bkp (custom-format pg_restore dump of the finance DB) into a Postgres target — local dev seeding or Railway staging.

Driven by fin-infra/railway/scripts/restore.sh, which handles the gotchas below automatically. You rarely need the manual steps.

TL;DR

cd fin-infra/railway/scripts

# Railway staging (drops & replaces the finance DB — prompts for typed confirmation):
./restore.sh /path/to/finance_<date>.bkp \
  --url 'postgresql://postgres:<password>@shortline.proxy.rlwy.net:37585/finance'

# Local dev (defaults: db=finance host=localhost port=5432 user=$USER, password via PGPASSWORD):
./restore.sh /path/to/finance_<date>.bkp

The script drops & recreates the target DB, restores, then verifies by real row counts. Success looks like:

==> Schemas with data: 10 / 10 expected
==> ✅ Restore verified: NNNNN rows across 10 schema(s) in finance on <host>

If it prints PARTIAL restore: X/10 schemas or 0 rows, it failed — re-run (see Troubleshooting).

Before you start

• Get the dump onto local disk first. If it lives in Zoho WorkDrive TrueSync (~/Library/CloudStorage/ZohoWorkDriveTrueSync-Finaisse/...), the file may be a cloud placeholder (metadata only, no bytes — stat -f %b <file> shows blocks=0). Restoring straight from a placeholder stalls mid-read. Copy it to a real local path to force hydration, then verify:

  cp "<truesync-path>/finance_<date>.bkp" /tmp/finance.bkp
  stat -f "blocks=%b" /tmp/finance.bkp          # must be > 0
  pg_restore -l /tmp/finance.bkp | grep -c "TABLE DATA"   # sanity: lists data entries

• Client tools: needs pg_restore / psql (brew install libpq; PG18 client is fine).
• Confirm the target. The script DROPS the target DB. Remote targets require you to type the DB name to confirm (set RESTORE_ASSUME_YES=1 only for automation).

Why this isn't a plain pg_restore (the gotchas)

Gotcha	What the script does
Dumps come from PostgreSQL 18 and use DEFAULT uuidv7() on PK columns. uuidv7() is a PG18-only built-in — Railway staging is PG17, RDS is planned PG16.	Installs a uuidv7() shim into pg_catalog only when the server is < 18. Without it, every CREATE TABLE fails with function uuidv7() does not exist.
pg_restore --exit-on-error aborts on the first benign "already exists" collision, leaving a schema with 0 rows.	Runs without --exit-on-error; collisions are expected and harmless.
"pg_restore finished" is not proof of success — a uuidv7-aborted run leaves a complete-looking schema with no data.	Verifies by real row counts and checks schemas-with-data == schemas the dump should load (catches partial loads).
Railway's public TCP proxy (*.rlwy.net) silently drops a briefly-idle connection mid-restore.	Sets libpq TCP keepalives so the socket survives.

Verify a restore manually

export PGPASSWORD=<password>
PSQL="psql -h shortline.proxy.rlwy.net -p 37585 -U postgres -d finance"

$PSQL -c "SELECT count(*) FILTER (WHERE n_live_tup>0) AS with_rows,
                 count(DISTINCT schemaname) FILTER (WHERE n_live_tup>0) AS schemas_with_data,
                 sum(n_live_tup) AS total_rows
          FROM pg_stat_user_tables;"

Expect 10 schemas with data (agent, cash, closehub, coresystem, finbase, invoice, journal, public, reconciliation, sys). Row estimates are populated by ANALYZE, which the script runs; a fresh manual restore may show 0 until you ANALYZE;.

Troubleshooting

Symptom	Cause / fix
function uuidv7() does not exist	Target is PG<18 and the shim wasn't installed. Confirm the script printed Server major version: 17 (not 1700 — that was a fixed bug) and installing uuidv7() shim.
PARTIAL restore: X/10 schemas	Connection dropped mid-restore (Railway proxy). Keepalives are on by default — just re-run. If it keeps dropping, restore from inside the Railway network (see below).
Restore stalls reading the file	Dump is an un-hydrated TrueSync placeholder — copy it local first (see Before you start).
Hangs on DROP DATABASE	An open session (e.g. pgAdmin tab) holds the DB. The script terminates other sessions, but close stray clients if it blocks.
Restore looks huge (1.5 MB dump → ~30 MB DB)	Normal — the dump is compressed; the live DB adds indexes, FK structures, and page padding.

Notes

• Sizing: a finance dump is ~1.5–1.6 MB; restored DB ~30 MB; ~35k rows (dominated by sys.calendarday). A restore over the proxy takes a few minutes.
• Password rotation: the staging postgres superuser password is long-lived. Rotate it in the Railway dashboard if it has been shared; the new value only needs to go into the --url arg / PGPASSWORD.
• Faster/robust restores (future): for large dumps or the AWS RDS migration, run the restore from inside the target's private network (a runner co-located with Postgres), not from a laptop over the internet. Railway private networking is per-project, so a staging refresh runner must live in finaisse-stage (can reuse its RustFS/S3 to stage the file).