cln migrate

1 files changed, 74 insertions, 0 deletions

diff --git a/scripts/migrate.py b/scripts/migrate.py
new file mode 100644
index 00000000..de7ff922
--- /dev/null
+++ b/scripts/migrate.py
@@ -0,0 +1,74 @@
+#!/usr/bin/env python3
+"""Migrate CLN SQLite database to Postgres.
+
+Reads from /tmp/lightningd.sqlite3, outputs SQL to stdout.
+Run inside the pgcl container:
+  python3 /tmp/migrate.py | psql -U lightning -d lightning
+"""
+import sqlite3
+import subprocess
+import sys
+
+sq = sqlite3.connect("/tmp/lightningd.sqlite3")
+sq.row_factory = sqlite3.Row
+
+# Get Postgres tables
+r = subprocess.run(
+    ["psql", "-U", "lightning", "-d", "lightning", "-tAc",
+     "SELECT tablename FROM pg_tables WHERE schemaname='public';"],
+    capture_output=True, text=True
+)
+pg_tables = set(r.stdout.strip().split("\n"))
+
+# Get SQLite tables
+sq_tables = [row[0] for row in sq.execute(
+    "SELECT name FROM sqlite_master WHERE type='table'").fetchall()]
+
+sql_parts = [
+    "BEGIN;",
+    "SET CONSTRAINTS ALL DEFERRED;",
+]
+total = 0
+
+# Insert tables with foreign key targets first
+priority = ["version", "blocks", "shachains", "peers", "channels",
+            "channel_configs", "offers", "invoicerequests", "move_accounts"]
+ordered = priority + [t for t in sq_tables if t not in priority]
+
+for tbl in ordered:
+    if tbl not in pg_tables:
+        print(f"  SKIP {tbl}", file=sys.stderr)
+        continue
+
+    rows = sq.execute(f'SELECT * FROM [{tbl}]').fetchall()
+    if not rows:
+        print(f"  {tbl}: 0 rows", file=sys.stderr)
+        continue
+
+    cols = rows[0].keys()
+    col_list = ", ".join(f'"{c}"' for c in cols)
+    count = 0
+
+    for row in rows:
+        values = []
+        for col in cols:
+            val = row[col]
+            if val is None:
+                values.append("NULL")
+            elif isinstance(val, bytes):
+                values.append(f"'\\x{val.hex()}'")
+            elif isinstance(val, (int, float)):
+                values.append(str(val))
+            else:
+                escaped = str(val).replace("'", "''")
+                values.append(f"'{escaped}'")
+        val_list = ", ".join(values)
+        sql_parts.append(f'INSERT INTO "{tbl}" ({col_list}) VALUES ({val_list});')
+        count += 1
+
+    total += count
+    print(f"  {tbl}: {count} rows", file=sys.stderr)
+
+sql_parts.append("COMMIT;")
+print(f"\nTotal: {total} rows to migrate", file=sys.stderr)
+print("\n".join(sql_parts))