Integration of PostgreSQL replacing SQLite3

[Kexkey]

Integration of PostgreSQL replacing SQLite3.

• PostgreSQL is in its own container, potentially useful for any other containers
• Automatic ETL/migration of SQLite3 data to the new PostgreSQL data types
• Added several tests in proxy_docker/app/tests
• Upgraded Bitcoin Core to v22.0
• Upgrade C-Lightning to v0.10.2
• Fixed library changes in mosquitto
• Removed rawtx field from the database
• Added indexes to the SQLite3 database... even if we are replacing it...
• createbatcher will update the batcher if it already exists
• Optimized tx confirmation processing
• Several small fixes and improvements here and there

[Kexkey]

Trying to add @phillamy as reviewer but you're not showing up in the list of reviewers, why?

[jimbojw]

Curious, what motivated the switch to Postgres?

[Kexkey]

Hi @jimbojw

Main driver was a performance problem we got at Bull Bitcoin with the tx table. By default, sqlite3 locks the whole database when writing and that caused timeout failures on some writes.

Also, we want to be able to implement replicas. Distributed redundancy is something relatively easy with PostgreSQL.

We also wanted to use PostgreSQL with c-lightning to secure our channel states remotely. We tried a lot of alternatives (backup plugin, lsyncd with sshfs, etc.) but we think this setup is more reliable.

Finally, we've been thinking for a while to have a separate container running PostgreSQL that would also be useful for any other container in the Cyphernode ecosystem, instead of duplicating databases for each component.

Of course, all that would be possible with sqlite3, but with a lot of work.