search

ZmnSCPxj / clboss

Automated Core Lightning Node Manager
MIT License
211 stars 32 forks source link

Support postgresql as an alternative to sqlite #65

Open 3nprob opened 3 years ago

3nprob commented 3 years ago

If clightning is already configured to use a postgres database, it would be great to create a table there, that rather than creating a new sqlite3 database on disk

Especially for setups where the datadir for clightning is on a network-attached volume/disributed filesystem, as sqlite is known to be prone to corruption in those scenarios

vincenzopalazzo commented 2 years ago

If clightning is already configured to use a postgres database, it would be great to create a table there, that rather than creating a new sqlite3 database on disk

Writing in a DB of c-lightning should be illegal IMO. clboss needs to use a different DB at all because make different thighs from c-lightning. However, c-lightning 0.10.2 supports datastore plugin that gives the possibility to store data on lightning db from a plugin, but IMO it is not good for clboss.

Especially for setups where the datadir for clightning is on a network-attached volume/disributed filesystem, as sqlite is known to be prone to corruption in those scenarios

In this case, it is possible to use postgress but with a different DB.

3nprob commented 2 years ago

@vincenzopalazzo Makes sense to separate the dbs. But as of today, clboss only works with sqlite, correct?

I renamed the issue accordingly.

ZmnSCPxj commented 2 years ago

I would prefer not to support this, or at least defer it to much much much later. C-Lightning actually has a fairly large amount of code to translate a bastardized version of the SQLITE3 dialect to the PostgreSQL dialect, it is fairly heavy.

Especially for setups where the datadir for clightning is on a network-attached volume/disributed filesystem, as sqlite is known to be prone to corruption in those scenarios

I believe this is true only if you actually access the DB from multiple computers and you are in WAL mode? SQLITE3 uses shared memory in that case, which is not possible across multiple computers. It should work perfectly fine if there is only one DB accessor on one machine, which you should really ensure by other means, since data.clboss is right next to hsm_secret.... you should not be running two C-lightning instances with the same hsm_secret.

On the other hand, C-Lightning can be compiled without SQLITE3 as long as with PostgreSQL, and a minimalist sysad would prefer to keep the number of packages needed low, so there is certainly an argument towards supporting a PostgreSL-only CLBOSS.

3nprob commented 2 years ago

C-Lightning actually has a fairly large amount of code to translate a bastardized version of the SQLITE3 dialect to the PostgreSQL dialect, it is fairly heavy.

panik

I believe this is true only if you actually access the DB from multiple computers and you are in WAL mode?

This is correct. In practice, I have seen issues with WAL even with only access from a single host/process at a time. However am I right in understanding that clboss will by itself not enable the WAL? If so that particular concern is not relevant :)

kalm

ZmnSCPxj commented 2 years ago

However am I right in understanding that clboss will by itself not enable the WAL?

No, you would have to manually do this explicitly.

ZmnSCPxj commented 2 years ago

Another note is that while lightningd.sqlite3 is important in the "lose this you lose your funds" sense, data.clboss is nowhere near as important --- it is mostly statistics. You can delete your data.clboss file completely without loss of funds, though CLBOSS will lose its statistics and will not manage your node quite as effectively, at least until it operates for long enough again to regenerate the statistics. So the ability of PostgreSQL to more reliably replicate the data is not as important for CLBOSS as it is for C-Lightning itself.