Migrating a Database

Move a PostgreSQL database to ClickHouse with EKOS Migrate: evidence-backed design, risk-gated human approval and validation to the cent.

EKOS Migrate (RFC 0154–0168) is an opt-in command group that migrates a live PostgreSQL source to a ClickHouse target. Every step it takes is compiled into the ledger as an evidenced fact: the live catalog, the profiles, the findings, the type mapping, each approval and each validation run. The migration report is compiled from those facts, not written by hand.

Migrate uses the rest of EKOS too. If the source's repository is already indexed, discover records the drift between the live catalog and the repository's DDL, and the blast radius of a table (how many compiled objects depend on it) feeds its risk class.

Status

1. Configure

[migrate]
enabled = true
default-environment = "sandbox"          # never "production"
policy = "migrate.policy.toml"           # approval policy, RFC 0161
source-named-collection = "lsmb_source"  # ClickHouse named collection holding the source credential
chunk-rows = 100000

[migrate.connections.src-pg]
host = "127.0.0.1"
port = 5432
user = "ekos"
secret-env = "SRC_PG_PASSWORD"           # the NAME of an env var; there is no password key

[migrate.connections.dst-ch]
host = "127.0.0.1"
port = 8123
user = "ekos"
secret-env = "DST_CH_PASSWORD"

No credential ever appears in ekos.toml, in the ledger or in a generated statement. Connections are aliases. A DSN that carries a password is refused. Loads read the source through a ClickHouse named collection that the ClickHouse administrator creates in the server config, so a generated statement only names it: INSERT INTO … SELECT … FROM postgresql(lsmb_source, table = 'acc_trans', schema = 'public'). Every statement is hashed and pinned to an approval, so it has to be safe to print.

The approval policy sets the thresholds and the approver groups:

scan-budget-rows = 50000000

[thresholds]
blast-radius = 10     # more compiled dependents than this escalates one risk class
affected-rows = 1000  # a load touching more rows than this escalates one risk class

[approvers]
r2 = ["data-engineering"]
r3 = ["data-engineering", "finance-controller"]
r4 = ["finance-controller", "cfo"]

The policy's content hash is recorded on every approval, so each decision can be read against the rules that were in force when it was made.

2. Run

ekos migrate init --name erp-to-ch --source postgres://src-pg/erp --target clickhouse://dst-ch/erp_raw \
     --source-secret-env SRC_PG_PASSWORD --target-secret-env DST_CH_PASSWORD
ekos migrate discover                 # live catalog → one unit per table, plus live-vs-repo drift
ekos migrate profile --tier p1        # p0 catalog only · p1 bounded sample · p2 exact, budgeted
ekos migrate assess                   # data-quality and target-compatibility findings
ekos migrate map --emit ddl.sql       # target types and table design per unit, with reasoning
ekos migrate load --unit public.orders --dry-run   # gate every statement and print it, execute nothing
ekos migrate load --unit public.orders             # refused if its computed risk needs an approval
ekos migrate validate --unit public.orders --tier v3
ekos migrate report --out migration_report.md      # compiled from ledger facts, groundedness-gated
ekos migrate status                   # units by state, and what is blocking

Run ANALYZE on the source before profile --tier p1. P1 estimates come from planner statistics, and a freshly loaded database has none.

3. Approve

When a unit's computed risk class needs an approval, load refuses it. You then raise a request, and a different person approves it:

ekos migrate review --unit public.orders --env sandbox          # raise REQ:public.orders:sandbox
ekos migrate approve REQ:public.orders:sandbox --as alice --show-evidence
ekos migrate reject  REQ:public.orders:sandbox --as alice --reason "wrong key"

4. Validate

Values are canonicalised identically on both sides: unconstrained numeric keeps its real scale, NULL booleans are the same on both sides, and lengths are counted in characters. validate exits non-zero on any failure, so you can use it as a CI gate.

Worked example: LedgerSMB → ClickHouse

The LedgerSMB analytics demo (presentation) runs the whole flow on a real open-source ERP: 168 tables and 503 functions in PostgreSQL, with 18 months of synthetic activity posted through LedgerSMB's own procedures.

Each validation tier was also shown to fail on a planted defect before being trusted. A check that has only ever passed proves nothing.