pg2sqlite

Overview ¶

Command pg2sqlite migrates cloud-api's dbx-managed Postgres database to a SQLite file. cloud-api uses raw dbx queries (not ORM struct sync), so the canonical schema lives in postgres only. This tool translates the live postgres schema + data into a SQLite file the cloud-api binary can open with driverName=sqlite (modernc.org/sqlite, already linked since v1.784.2).

Usage:

pg2sqlite -src "user=hanzo password=... host=postgres port=5432 dbname=hanzo_cloud sslmode=disable" \
          -dst /tmp/cloud-staging.db

Strategy:

1. Open source postgres via lib/pq, destination sqlite via modernc.org/sqlite.
2. Discover schema from information_schema (no pg_dump dependency).
3. Translate per-column types to SQLite equivalents.
4. Per-table CREATE TABLE on destination using composite PK from information_schema.table_constraints (the cloud-api convention is PRIMARY KEY (owner, name) on every table except `record` which uses a synthetic INTEGER id).
5. Stream rows: SELECT * FROM source → INSERT into dest with parameter binding (nil → NULL, []byte → BLOB, time.Time → RFC3339).
6. Emit a per-table row-count report so the operator can verify parity before flipping driverName.

Mirrors the IAM pg2sqlite design (~/work/hanzo/iam/cmd/pg2sqlite/main.go) but works without xorm because cloud-api never adopted xorm.