migratekvdb: add Postgres bulk migration path

[ziggie1984]

Summary

This PR wires lndinit to the migration-only SQL KV bulk migration API from
lightningnetwork/lnd#10965 and adds a Postgres-only bulk migration path.

It keeps lndinit as orchestration code:

• updates the dependency stack to the lnd PR head and btcd split modules
• records interrupted bulk attempts in the migration meta DB
• calls the lnd-owned SQL KV bulk writer/verifier APIs
• adds --bulk-writes for fresh Postgres migrations
• fails closed when interrupted recovery finds a non-empty target
• adds --reset-bulk-target for explicitly authorized destructive recovery
• isolates the bulk implementation behind the kvdb_postgres build constraint
• documents the flags and recovery behavior

An interrupted migration whose transaction left the target empty restarts
automatically. If the target contains rows, recovery refuses to modify it unless
the operator explicitly supplies --reset-bulk-target, which truncates the
target before restarting.

Dependency

This PR depends on lightningnetwork/lnd#10965.

The temporary replace directives in go.mod should be removed once that PR
lands and lndinit can depend on upstream lnd module versions.

Verification

go test ./migratekvdb
go test -tags "kvdb_etcd kvdb_postgres kvdb_sqlite" ./migratekvdb
go test -tags "kvdb_etcd kvdb_postgres kvdb_sqlite" . \
  -run '^TestValidateBulkResetFlags$'
make lint
git diff --check

[ziggie1984]

/gateway review

[ziggie1984]

/gateway review

[lightninglabs-gateway]

Gateway review — 9 findings

🔴 0 Blocker · 🟠 4 Major · 🟡 5 Minor · 🔵 0 Nit

Summary

This PR wires lndinit to lnd's new Postgres-only bulk SQL-KV migration API, adding a MigrateBulk/VerifyMigrationBulk path behind a --bulk-writes flag plus a BulkInProgress recovery marker. The orchestration code is clean and the new logic is unit-tested for recovery, fresh-target rejection, and target-corruption detection.

The central risk is in the verification path, which is the gate that lets an operator tombstone a live source DB. VerifyMigrationBulk compares the bolt source cursor positionally against the rows returned by the lnd-owned FetchChildren, so its soundness rests on an ordering contract that is defined only in the (currently vendored-by-replace) fork and cannot be audited here. Separately, the interrupted-run recovery path unconditionally truncates the destination based on source-side state alone, and an explicitly-requested --bulk-writes is silently downgraded on non-Postgres destinations. Verdict is REQUEST_CHANGES.

Bot commands

• /gateway re-review — re-run after pushing changes
• /gateway dismiss <id> — silence a finding (maintainers)
• /gateway explain <id> — elaborate on a finding (anyone)

[lightninglabs-gateway]

🤖 gateway audit metadata for this PR — auto-generated, please don't edit.

[ziggie1984]

/gateway re-review

[lightninglabs-gateway]

🔁 gateway re-review starting…

[ziggie1984]

A benchmark I did so far locally just for info

  ### Initial results

   Path             Time    Throughput          Key rate        Memory          Allocations
  ━━━━━━━━━━  ━━━━━━━━━━━  ━━━━━━━━━━━━  ━━━━━━━━━━━━━━━━  ━━━━━━━━━━━━  ━━━━━━━━━━━━━━━━━━━
   Standard    779 ms/op     6.78 MB/s     25,664 keys/s    80.8 MB/op    622,755 allocs/op
  ──────────  ───────────  ────────────  ────────────────  ────────────  ───────────────────
   Bulk        131 ms/op    40.31 MB/s    152,698 keys/s    16.6 MB/op    152,103 allocs/op

  For this dataset and machine, the bulk path was approximately 5.9× faster, used about 4.9× less allocated memory, and performed about 4.1×
  fewer allocations.

  These are exploratory numbers rather than a stable performance guarantee. This was a single iteration on one machine using a synthetic,
  leaf-heavy dataset. A more rigorous comparison would use repeated runs with benchstat, alternate execution order, warmed PostgreSQL state,
  and multiple dataset shapes and sizes. Nevertheless, the initial result confirms that the bulk implementation moves performance in the
  intended direction.

[ziggie1984]

/gateway re-review

[djkazic]

Version check passed (not a version issue). The migrate phase was flying (~114k keys/sec) then postgres ran out of shared memory.

The likely root cause: the upstream BeginBulk opens the whole migration as one SERIALIZABLE transaction (migration_bulk_postgres.go:91-92), and a serializable transaction inserting millions of rows accumulates predicate locks until it exhausts shared memory on default postgres settings. The original path I laid out used the default READ COMMITTED and completed on the same vanilla config.

[djkazic]

No-tag build breaks: cmd_migrate_db.go is untagged but references sqlbase.MigrationBackend, postgres.NewMigrationBackend, and the kvdb_postgres-only MigrateBulk/VerifyMigrationBulk. go build ./... without tags fails.

Fix: move the bulk wiring into a //go:build kvdb_postgres file with a small non-postgres stub. (Mine avoided this by using database/sql generically in the untagged cmd.)

[djkazic]

tACK. Migration of our large test DB took 8m27s.