need a lot more clarity of databases in LND

[ZZiigguurraatt]

Summary

In LND there are a variety of databases and options for what type of database to use for them. It is very confusing all the options. Here I give a summary of what I think the current state is and also leave some open questions. I think all of this needs to be clearly defined in one consolidated place. As even more changes come in terms of the databases, this becomes an even bigger deal so that people can understand what they have, what is changing, and what choices they have.

Databases

• macaroons
• invoices/payments received
• forwarded payments
• outbound payments
• wallet
• channel state
• sphinxreplay
• watchtower
• network graph
• chain state
• ?

Database Types

• bbolt
• etcd
• SQLite
• Postgres

History

Initially LND supported only bbolt, which is a key/value database.

Later, with v0.11.0 (https://lightning.engineering/posts/2020-08-20-lnd-v0.11/ , https://github.com/lightningnetwork/lnd/releases/tag/v0.11.0-beta) etcd was supported (https://github.com/lightningnetwork/lnd/blob/master/docs/etcd.md). etcd is also a key/value database.

Later, with v0.14.0 (https://lightning.engineering/posts/2021-11-18-lnd-v0.14/, https://github.com/lightningnetwork/lnd/blob/master/docs/release-notes/release-notes-0.14.0.md#postgres-database-support) Postgres was supported (https://github.com/lightningnetwork/lnd/blob/master/docs/postgres.md) in a key-value mode (https://github.com/lightningnetwork/lnd/blob/master/docs/postgres.md#what-is-in-the-database) rather than working as a relational database.

Later, with v0.16.0 (https://lightning.engineering/posts/2023-03-29-lnd-0.16-launch/, https://github.com/lightningnetwork/lnd/blob/master/docs/release-notes/release-notes-0.16.0.md#db) SQLite was supported (https://github.com/lightningnetwork/lnd/blob/master/docs/sqlite.md). I think this support is similar to the Postgres support in that it is using a key-value mode rather than a relational database.

Later, with v0.18.0 (https://lightning.engineering/posts/2024-05-30-lnd-0.18-launch/, https://github.com/lightningnetwork/lnd/blob/master/docs/release-notes/release-notes-0.18.0.md#database) the invoices database added an optional relational database mode if SQLite is used. The documentation is vague whether Postgres can also be used. Also, this change to a relational database only happens for invoices. I think everything else stays as a key/value database.

All of the above options were only selectable on initial startup of the node. They could not be changed after a node was initialized.

Future

Slated to launch before LND v0.19.0 is a migration tool (lightninglabs/lndinit#21) to allow a transition for bbolt (but not etcd) users to Postgres or SQLite.

Upcoming with v0.19.0 we will have the option for nodes that were created before v0.18.0 with a Postgress or SQlite database (or bbolt after using the migration tool) to convert their invoice database in a key/value format to the same optional relational database format that was introduced in v0.18.0 (lightningnetwork/lnd#9646, https://github.com/lightningnetwork/lnd/blob/master/docs/release-notes/release-notes-0.19.0.md#database).

Other unknowns

What files are required for each database type? For etcd and Postgres, those are separate servers that we need to point to in lnd.conf, so the files are not really defined by LND. For bbolt I've found some different database files in LND:

• watchtower.db
• channel.db
• sphinxreplay.db: seems to just be related to onion routing messaging
• macaroons.db
• wallet.db

I'm not sure where bbolt stores

• invoices/payments received
• forwarded payments
• outbound payments
• network graph
• chain state

Maybe those are in channels.db and wallet.db??

Also, for the SQLite option, what files are generated? Is everything stored in a single file or is there a separate database file for each?

Do any of the above .db files still exist if not using bbolt?

We need a document somewhere that says what file is what. Existence of a file or files (and lack of existence of others) may allow a user to identify what type of database they are using so that they can more easily determine if they need do do any database migration or not.

Confusions in documentation that does exist

• At https://docs.lightning.engineering/lightning-network-tools/lnd we have a link in the sidebar to https://docs.lightning.engineering/lightning-network-tools/lnd/etcd which appears to be a clone of https://github.com/lightningnetwork/lnd/blob/master/docs/etcd.md . I'm getting the vibe that etcd is not a popular database to use. However, we don't have SQLite (https://github.com/lightningnetwork/lnd/blob/master/docs/sqlite.md) or Postgres (https://github.com/lightningnetwork/lnd/blob/master/docs/postgres.md) mentioned at all, which appear to the "first class" database types that we are moving towards.
• If we don't use bbolt, how do we clear our macaroon database if we want to revoke all macaroons?

[ZZiigguurraatt]

Also, we have some config settings here for the different database options:

https://github.com/lightningnetwork/lnd/blob/eb822a5e117f738b0889e341a2be2bf8bbdbc75a/sample-lnd.conf#L1452-L1657

Throwing this in here as it is another reference that may help to write some clearer documentation.

[ZZiigguurraatt]

Might want to also add some notes like is shown here https://github.com/blckbx/lnd_postgres for installing and setting up postgres.

[saubyk]

Hi @ZZiigguurraatt thanks for the detailed feedback. Agree with you that we are not currently presenting a clear picture of the various db option LND has, as well as how we are planning to move to the future state where we want LND to be at.

I will address the questions you've raised above by proposing a documentation plan for the site.

• We should create a LND Backend Options section under the LND page
• List all the db options currently available with LND: bbolt(current default), etcd, sqlite, postgres and how to configure them
• List the db options which LND will eventually end up with: sqlite(future default), postgres
• Provide a path which will be taken to migrate present LND nodes from bbolt (and possibly etcd) to future options:
  • Step1: Migrate from bbolt (kvdb) to sqlite/postgres (kvdb) [performed with lndinit]
  • Step2: Migrate from sqlite/postgres (kvdb) to sqlite/postgres (native sql) [performed within lnd]
• Create sub sections/pages for configuration option of various dbs
  • Recommended config settings for optimal performance of Postgres: https://gist.github.com/djkazic/526fa3e032aea9578997f88b45b91fb9
• Create a page/section for 'Post Migration Housekeeping': This section should list the database files which can be safely backed up and deleted after the migration operation is successful

Regarding the details of files which are created with different db options, I am a little hesitant to get into that, because we run into the danger of providing too much information which can generate unnecessary questions from users. E.g. when we moved from bbolt(kvdb) to sqlite(kvdb) the bbolt db file wallet.db was renamed to chain.sqlite. The user doesn't need to know such details IMO.

As far as the awareness of the type of DB which the user has running is concerned, it can be determined by examining the config file. Bbolt is the only default option currently, for all other options user has to make corresponding config entries.

Let me know what you think.

[ZZiigguurraatt]

A new doc has emerged: https://github.com/lightninglabs/lndinit/blob/migrate-db/docs/data-migration.md .

[ZZiigguurraatt]

Hi @ZZiigguurraatt thanks for the detailed feedback. Agree with you that we are not currently presenting a clear picture of the various db option LND has, as well as how we are planning to move to the future state where we want LND to be at.

I will address the questions you've raised above by proposing a documentation plan for the site.

* We should create a `LND Backend Options` section under the LND page

* List all the db options currently available with LND: `bbolt`(current default), `etcd`, `sqlite`, `postgres` and how to configure them

* List the db options which LND will eventually end up with: `sqlite`(future default), `postgres`

* Provide a path which will be taken to migrate present LND nodes from `bbolt` (and possibly `etcd`) to future options:
  
  * Step1: Migrate from bbolt (kvdb) to sqlite/postgres (kvdb) [performed with lndinit]
  * Step2: Migrate from sqlite/postgres (kvdb) to sqlite/postgres (native sql) [performed within lnd]

* Create sub sections/pages for configuration option of various dbs
  
  * Recommended config settings for optimal performance of Postgres: https://gist.github.com/djkazic/526fa3e032aea9578997f88b45b91fb9

Sounds good.

Regarding the details of files which are created with different db options, I am a little hesitant to get into that, because we run into the danger of providing too much information which can generate unnecessary questions from users. E.g. when we moved from bbolt(kvdb) to sqlite(kvdb) the bbolt db file wallet.db was renamed to chain.sqlite. The user doesn't need to know such details IMO.

Yeah, only thing is I might be concerned why there is no wallet.sqlite appeared after the conversion for example. Also, what if the user needs to delete old files if they are really big? How do they know what can be deleted if we don't have them clearly scoped out? Maybe just say everything that ends in .db is for bbolt and everything that ends in .sqlite is SQLite? That's not obvious to me going into a conversion, but after conversion I can guess it.

As far as the awareness of the type of DB which the user has running is concerned, it can be determined by examining the config file. Bbolt is the only default option currently, for all other options user has to make corresponding config entries.

I guess they can also look at the log file to know their config file is right.

[saubyk]

Also, what if the user needs to delete old files if they are really big?

This is definitely a good point, which we should address as 'Post Migration Housekeeping' and should include the list of database files which can be safely deleted after.

[ZZiigguurraatt]

Also, what if the user needs to delete old files if they are really big?

This is definitely a good point, which we should address as 'Post Migration Housekeeping' and should include the list of database files which can be safely deleted after.

I think that is a good place to put it.

[ZZiigguurraatt]

More database notes related to running a postgres server well: https://gist.github.com/djkazic/526fa3e032aea9578997f88b45b91fb9 . Hopefully we can find a good place for this in the docs too.