pgimport

Overview ¶

Command pgimport performs a one-shot, FK-ordered copy of an encode-system SQLite database into a Postgres database. It is the migration path for the Phase 2 SQLite→Postgres store workstream.

Usage:

pgimport -sqlite <path> -pg <dsn> [-force] [-dry-run]

-sqlite   path to the source SQLite database (required)
-pg       target Postgres DSN, e.g. postgres://encode@host:port/db?sslmode=disable (required)
-force    allow import into a non-empty target: TRUNCATEs every data table CASCADE first
-dry-run  count + validate only; write nothing to the target

Design notes:

• Tables are enumerated from the SQLite file (sqlite_master) and cross- checked against the PG catalog after connect. A SQLite table with no PG counterpart is a schema-drift bug → fail loudly, except a documented skip list (schema_migrations is owned by the target's migratePG; sqlite_sequence is a SQLite internal).
• Copy is generic: per table, read column names from SQLite PRAGMA table_info, SELECT *, batch INSERT into PG with an explicit column list ($n placeholders). Values are copied as-is — TEXT timestamps stay TEXT strings, INTEGER 0/1 bools stay integers. Explicit ids are preserved (BIGSERIAL accepts explicit inserts).
• After each table, the id sequence is advanced via SELECT setval(pg_get_serial_sequence('t','id'), COALESCE(MAX(id),1)) so future inserts don't collide with the imported ids.
• A verification pass (always, even dry-run/force) compares per-table row counts SQLite vs PG side by side, plus key aggregates via the store package (CountJobsByStatus, ListNodes/CountNodes). Mismatch → non-zero exit. A final "IMPORT OK: <N> tables, <M> rows" line is printed on success.
• No secrets are ever logged: share password ciphertext/plaintext and settings JSON values are never printed — only counts.