Padreug
40639ee437
Some checks failed
ci.yml / docs: add satmachineadmin -> spirekeeper data-migration runbook (push) Failing after 0s
SQLite (cp the ext DB file) and Postgres (rename/copy schema) recipes. Key trap documented: dbversions is keyed by ext id and m001 drops the legacy v1 tables, so the new install must seed dbversions['spirekeeper'] >= 1 to skip m001 and preserve copied data. Validated on the regtest dev instance (10 tables, 52 dca_payments etc. preserved across boot). Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
105 lines
4 KiB
Markdown
105 lines
4 KiB
Markdown
# Migrating data: `satmachineadmin` → `spirekeeper`
|
||
|
||
`spirekeeper` is the v2-bitspire line of the old `satmachineadmin` extension
|
||
split into its own repo, with **both identifiers renamed**:
|
||
|
||
| Concern | old (`satmachineadmin`) | new (`spirekeeper`) |
|
||
|----------------|-------------------------|-----------------------|
|
||
| extension id | `satmachineadmin` | `spirekeeper` |
|
||
| DB name/schema | `satoshimachine` | `spirekeeper` |
|
||
| SQLite file | `ext_satoshimachine.sqlite3` | `ext_spirekeeper.sqlite3` |
|
||
|
||
The **table layout is identical** — only the schema *alias* changed. So
|
||
migrating an existing install is a data move, not a schema change.
|
||
|
||
## The one trap: `dbversions` + `m001`'s `DROP TABLE`
|
||
|
||
`migrations.py:m001` begins by dropping the legacy v1 tables
|
||
(`dca_clients`, `dca_deposits`, `dca_payments`, `lamassu_*`) before
|
||
recreating the v2 schema. On a **fresh** `spirekeeper` install the
|
||
`dbversions` row starts at 0, so the runner executes `m001` — which would
|
||
**drop three tables of any data you just copied in**.
|
||
|
||
`dbversions` is keyed by **extension id**. An existing `satmachineadmin`
|
||
install that ran the pre-squash staged migrations sits at version **9**
|
||
(`m001`–`m009`), which is why the later squash to a single `m001` never
|
||
re-dropped its data (9 ≥ 1 → skip).
|
||
|
||
**Therefore: after copying the data, seed `dbversions['spirekeeper']` to
|
||
the old install's version (≥ 1) so the runner skips `m001`.** Match the
|
||
old value (commonly `9`) for fidelity with future migrations.
|
||
|
||
> `dbversions` lives in the **core** DB (`database.sqlite3` / the core
|
||
> Postgres schema), NOT in the extension DB.
|
||
|
||
---
|
||
|
||
## SQLite (default dev + small deployments)
|
||
|
||
Run with LNbits **stopped** (so neither DB file is open):
|
||
|
||
```bash
|
||
DATA=/path/to/lnbits/data # dir holding database.sqlite3 + ext_*.sqlite3
|
||
|
||
# 1. Copy the extension data file (tables inside are identical).
|
||
cp "$DATA/ext_satoshimachine.sqlite3" "$DATA/ext_spirekeeper.sqlite3"
|
||
|
||
# 2. Read the old version, seed the new row (upsert) in the CORE db.
|
||
OLDVER=$(sqlite3 "$DATA/database.sqlite3" \
|
||
"SELECT version FROM dbversions WHERE db='satmachineadmin';")
|
||
sqlite3 "$DATA/database.sqlite3" \
|
||
"INSERT INTO dbversions (db, version) VALUES ('spirekeeper', $OLDVER)
|
||
ON CONFLICT(db) DO UPDATE SET version=excluded.version;"
|
||
```
|
||
|
||
Start LNbits, enable the `spirekeeper` extension — it loads at version
|
||
`$OLDVER`, runs no migration, and sees all rows.
|
||
|
||
**Rollback:** `rm "$DATA/ext_spirekeeper.sqlite3"` and
|
||
`DELETE FROM dbversions WHERE db='spirekeeper';`. The old install is
|
||
untouched throughout.
|
||
|
||
---
|
||
|
||
## Postgres (production)
|
||
|
||
Both schemas live in the same database. With LNbits stopped:
|
||
|
||
**Option A — cut over (old extension removed on this instance):**
|
||
|
||
```sql
|
||
ALTER SCHEMA satoshimachine RENAME TO spirekeeper;
|
||
INSERT INTO dbversions (db, version)
|
||
SELECT 'spirekeeper', version FROM dbversions WHERE db='satmachineadmin'
|
||
ON CONFLICT (db) DO UPDATE SET version = excluded.version;
|
||
-- optional: DELETE FROM dbversions WHERE db='satmachineadmin';
|
||
```
|
||
|
||
**Option B — coexist (both extensions stay enabled):** copy table-by-table
|
||
into a fresh schema instead of renaming:
|
||
|
||
```sql
|
||
CREATE SCHEMA spirekeeper;
|
||
-- for each table: super_config, dca_machines, dca_clients, dca_lp,
|
||
-- dca_deposits, dca_settlements, dca_commission_splits, dca_payments,
|
||
-- dca_telemetry, cassette_configs
|
||
CREATE TABLE spirekeeper.<t> (LIKE satoshimachine.<t> INCLUDING ALL);
|
||
INSERT INTO spirekeeper.<t> SELECT * FROM satoshimachine.<t>;
|
||
-- then the same dbversions upsert as Option A
|
||
```
|
||
|
||
Start LNbits, enable `spirekeeper`; `m001` is skipped, data intact.
|
||
|
||
---
|
||
|
||
## Verify after migration
|
||
|
||
Row counts in `ext_spirekeeper` must equal the old install's, and the
|
||
LNbits log must show no `DROP TABLE` for `spirekeeper.*` on boot:
|
||
|
||
```bash
|
||
sqlite3 "$DATA/ext_spirekeeper.sqlite3" \
|
||
"SELECT 'dca_payments', COUNT(*) FROM dca_payments
|
||
UNION ALL SELECT 'dca_settlements', COUNT(*) FROM dca_settlements
|
||
UNION ALL SELECT 'dca_clients', COUNT(*) FROM dca_clients;"
|
||
```
|