db_migration_guide.md
1 # `LND Database Upgrade Guide`: A Two-Stage Migration for Node Operators 2 3 *Table of Contents* 4 - [Overview](#overview) 5 - [Operation Modes](#operation-modes) 6 - [Stage 1: Migration from bbolt to SQLite/Postgres (kvdb)](#stage-1-migration-from-bbolt-to-sqlitepostgres-kvdb) 7 - [Choosing Your Target Backend](#choosing-your-target-backend) 8 - [Postgres kvdb Migration](#postgres-kvdb-migration) 9 - [SQLite kvdb Migration](#sqlite-kvdb-migration) 10 - [Stage 2: Migration from kvdb to Relational Database](#stage-2-migration-from-kvdb-to-relational-database) 11 - [Subsystem Readiness](#subsystem-readiness) 12 - [Known Limitations and Edge Cases](#known-limitations-and-edge-cases) 13 - [Best Practices for Node Operators](#best-practices-for-node-operators) 14 - [Choosing the Right Path](#choosing-the-right-path) 15 - [Timing Your Migration](#timing-your-migration) 16 - [Validation](#validation) 17 - [Implementation Examples](#implementation-examples) 18 - [Migrating to SQLite kvdb](#migrating-to-sqlite-kvdb) 19 - [Migrating Invoices to Relational Backend](#migrating-invoices-to-relational-backend) 20 - [Future Improvements](#future-improvements) 21 - [Conclusion](#conclusion) 22 23 --- 24 25 ## Overview 26 27 The `LND Database Upgrade` process enables node operators to migrate from the legacy **bbolt** key-value store to modern **SQLite** or **PostgreSQL** backends—first in **kvdb mode**, then ultimately to a **relational database** format. This two-stage migration addresses long-standing performance bottlenecks, and lays the foundation for scalable, maintainable node operations. 28 29 This guide explains the migration path, backend trade-offs, subsystem readiness, and best practices for safely upgrading your LND database. 30 31 --- 32 33 ## Operation Modes 34 35 The LND database upgrade operates in two sequential stages, each with distinct tooling and implications: 36 37 ```mermaid 38 flowchart TD 39 %% 1. Define all nodes 40 Bbolt["Bbolt (kvdb)"] 41 SQLite["SQLite (kvdb)"] 42 Postgres["Postgres (kvdb)"] 43 44 M1_Sqlite["Migration #1 (lnd v0.19)<br>Invoices"] 45 M2_Sqlite["Migration #2 (lnd v0.20)<br>Graph"] 46 M3_Sqlite["Migration #3 (lnd v0.21)<br>Payments"] 47 48 M1_Postgres["Migration #1 (lnd v0.19)<br>Invoices"] 49 M2_Postgres["Migration #2 (lnd v0.20)<br>Graph"] 50 M3_Postgres["Migration #3 (lnd v0.21)<br>Payments"] 51 52 %% 2. Define all links (within and between graphs) 53 Bbolt --> SQLite 54 Bbolt --> Postgres 55 56 SQLite --> M1_Sqlite 57 M1_Sqlite --> M2_Sqlite 58 M2_Sqlite --> M3_Sqlite 59 60 Postgres --> M1_Postgres 61 M1_Postgres --> M2_Postgres 62 M2_Postgres --> M3_Postgres 63 64 %% 3. Group nodes into subgraphs 65 subgraph "Step 1: Migration via lndinit" 66 Bbolt 67 SQLite 68 Postgres 69 end 70 71 subgraph "Step 2: Migration within lnd" 72 M1_Sqlite 73 M2_Sqlite 74 M3_Sqlite 75 M1_Postgres 76 M2_Postgres 77 M3_Postgres 78 end 79 80 %% 4. Apply Styles 81 %% Define classes with explicit dark text color (#333) 82 classDef bboltNode fill:#e2e3e5,stroke:#383d41,color:#333 83 classDef sqliteNode fill:#d4edda,stroke:#155724,color:#333 84 classDef postgresNode fill:#cce5ff,stroke:#004085,color:#333 85 86 %% Apply classes to nodes 87 class Bbolt bboltNode 88 class SQLite,M1_Sqlite,M2_Sqlite,M3_Sqlite sqliteNode 89 class Postgres,M1_Postgres,M2_Postgres,M3_Postgres postgresNode 90 ``` 91 92 - **Stage 1**: Migrate from bbolt to a SQL-based **kvdb** backend using the [lndinit](https://github.com/lightninglabs/lndinit/blob/main/docs/data-migration.md) tool. 93 - **Stage 2**: Incrementally migrate subsystem data (invoices, graph, payments, etc.) from **kvdb** to SQL native **relational tables** as support becomes available. 94 95 --- 96 97 ## Stage 1: Migration from bbolt to SQLite/Postgres (kvdb) 98 99 LND cannot run with mixed backends, so all users must first leave bbolt behind. This stage uses the `lndinit` utility to perform an offline migration. 100 101 ### Choosing Your Target Backend 102 103 | Backend | Performance (in kvdb mode) | Default in LND? | Long-Term Viability | 104 |-----------|--------------------------|------------------|----------------------| 105 | Postgres | Mediocre | No | ✅ | 106 | SQLite | Good | **Yes (future)** | ✅ | 107 108 > 💡 **Recommendation**: Unless you require Postgres for infrastructure reasons, 109 **migrate to SQLite kvdb** as your backend. 110 111 ### Postgres kvdb Migration 112 113 A migration script is available via `lndinit`: 114 115 - [Postgres migration script](https://github.com/lightninglabs/lndinit/blob/main/docs/data-migration.md#using-postgres-as-the-destination-remote-database) 116 - **Caveat**: Users report degraded performance in kvdb mode. Only proceed if you plan to **immediately follow with Stage 2** to migrate the available data stores to relational DB. 117 This will mitigate the poor Postgres performance on kvdb. 118 119 ### SQLite kvdb Migration 120 121 - [SQLite migration script](https://github.com/lightninglabs/lndinit/blob/main/docs/data-migration.md#using-sqlite-as-the-destination-remote-database) 122 - **Advantage**: Maintains good performance while waiting for full relational migration. 123 124 --- 125 126 ## Stage 2: Migration from kvdb to Relational Database 127 128 This stage unlocks true SQL performance by restructuring data into relational tables. Migration is **per-subsystem** and **incremental**. 129 130 The migration steps are automatically applied when LND is restarted after step 1 was successfully completed and the config value db.use-native-sql=true is set. 131 You will see log lines from the `SQLD` subsystem about the migration, such as `Starting migration of invoices from KV to SQL`. 132 133 ### Subsystem Readiness 134 135 | Subsystem | Relational Backend | Migration Script | Status | 136 |---------------------|--------------------|------------------|--------| 137 | Invoices | ✅ Available | ✅ | Available with **v0.19** | 138 | Graph | ✅ Available | ✅ | Available with **v0.20** | 139 | Payments | 🚧 In Progress | Planned | Targeted with **v0.21**| 140 | Btcwallet | 🚧 In Progress | Planned | Targeted with **v0.21**| 141 | Forwarding History | ❌ TBD | ❌ TBD | Future work | 142 143 --- 144 145 ## Known Limitations and Edge Cases 146 147 - **Single database engine required**: LND requires a single consistent backend. 148 You cannot run invoices in relational mode while graph remain in kvdb *unless* 149 both are on the same SQL engine (e.g., SQLite). 150 - **Data loss risk**: Always **back up your `data/` directory** before migration. 151 - **Downtime required**: Stage 1 requires LND to be offline. Stage 2 is done at startup, requiring a LND restart. 152 - **Postgres kvdb performance**: Postgres performance on kvdb is sub-optimal. It is 153 recommended to make the stage 2 migration immediately to avoid performance bottlenecks. Certain RPCs like `listpayments` may not perform well on Postgres if the node has a lot of payments data. If your node operation is heavy 154 on payments and `listpayments` performance is critical for you, we'd recommend 155 not doing any migration and wait till version 0.21.0 is released. 156 - **No migration path between SQL backend**: Once migrated to either Postgres or 157 SQLite, it is not possible to switch to the other, so choose your target backend carefully. 158 --- 159 160 ## Best Practices for Node Operators 161 162 ### Choosing the Right Path 163 164 - **For most users**: Choose SQLite, then migrate. Later, adopt relational backends subsystem-by-subsystem. 165 - **Enterprise/Postgres users**: It is recommened to wait to start the migration 166 until **payments relational backend** is ready, then perform **Stage 1 + Stage 2 in quick succession**. 167 168 ### Timing Your Migration 169 170 - Perform migrations during **low-activity periods**. 171 - Monitor LND release notes for relational DB support of different subsystems. 172 173 ### Validation 174 175 1. Stop LND. 176 2. Run migration with `lndinit`. 177 3. Start LND with new backend flags, to execute stage 2 migrations. 178 4. Validate node health: channels, balance, invoice/payment history. 179 180 --- 181 182 ## Implementation Examples 183 184 ### Migrating to SQLite kvdb 185 186 ```bash 187 # Stop LND 188 lnd --shutdown 189 190 # Backup 191 cp -r ~/.lnd ~/lnd-backup-$(date +%Y%m%d) 192 193 # Run migration (e.g. sqlite) 194 lndinit --debuglevel info migrate-db \ 195 --source.bolt.data-dir ~/.lnd/data \ 196 --dest.backend sqlite \ 197 --dest.sqlite.data-dir ~/.lnd/data --network mainnet 198 199 # Start LND with SQLite backend 200 lnd --db.backend=sqlite 201 ``` 202 203 > 📝 Add `db.backend=sqlite` to your `lnd.conf` to make it persistent. 204 205 ### Migrating Invoices to Relational Backend 206 207 Once on LND v0.19+ with SQLite/Postgres: 208 209 ```bash 210 # Ensure backend is set 211 echo "db.backend=sqlite" >> ~/.lnd/lnd.conf 212 213 # Start LND — invoice migration runs automatically 214 lnd 215 ``` 216 217 Check logs for: 218 ``` 219 Migrating invoices from kvdb to relational format... 220 Invoice migration completed successfully. 221 ``` 222 223 --- 224 225 ## Future Improvements 226 227 The LND team is actively working on: 228 229 - **Payments relational backend** and migration tooling (Stage 2) 230 - **Btcwallet relational backend** and migration tooling (Stage 2) 231 - **Forwarding history** relational schema (long-term) 232 - **Automatic detection** of migration readiness in `lnd` 233 234 Node operators should monitor: 235 - [LND GitHub Releases](https://github.com/lightningnetwork/lnd/releases) 236 - [lndinit repository](https://github.com/lightninglabs/lndinit) 237 238 --- 239 240 ## Conclusion 241 242 The LND database upgrade is a strategic two-stage process designed to eliminate bbolt performance limitations while ensuring data integrity and operational continuity. 243 244 By **first migrating to SQLite/Postgres kvdb** and **then adopting relational backends incrementally**, node operators can achieve significant performance gains—especially for payment-heavy workloads—without rushing into unstable configurations. 245 246 Choose your path wisely, back up rigorously, and stay informed. The future of LND is relational, and this guide ensures you get there safely.