Raspiblitz: BTCpay: use postgres database by default

[openoms]

Postgres is better supported database format for for BTCPayServer as described in: https://github.com/btcpayserver/btcpayserver/issues/1996

Will need to install postgres in the install script and have it running on BTCpay install to be used by default.

[openoms]

Will try to make this work asap as restoring my sqllite database did not work no the second time. BTCpay cannot generate invoices any more despite all data was preserved.

logs when attempting to create an invoice: https://termbin.com/6tbc

Nov 10 21:04:59 raspberrypi dotnet[5703]: fail: Microsoft.AspNetCore.Server.Kestrel: Connection id "0HM45N8F199LP", Request id "0HM45N8F199LP:00000001": An unhandled exception was thrown by the application.
Nov 10 21:04:59 raspberrypi dotnet[5703]: Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
Nov 10 21:04:59 raspberrypi dotnet[5703]:  ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'UNIQUE constraint failed: AddressInvoices.Address'.
Nov 10 21:04:59 raspberrypi dotnet[5703]:    at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
Nov 10 21:04:59 raspberrypi dotnet[5703]:    at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
Nov 10 21:04:59 raspberrypi dotnet[5703]:    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
Nov 10 21:04:59 raspberrypi dotnet[5703]:    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
Nov 10 21:04:59 raspberrypi dotnet[5703]:    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
Nov 10 21:04:59 raspberrypi dotnet[5703]:    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Nov 10 21:04:59 raspberrypi dotnet[5703]:    at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
Nov 10 21:04:59 raspberrypi dotnet[5703]:    --- End of inner exception stack trace ---

Currently a new BTCPay install is starting with sqllite:
sudo journalctl -u btcpayserver
```
Nov 11 13:13:38 raspberrypi systemd[1]: Started BtcPayServer daemon.
Nov 11 13:13:41 raspberrypi dotnet[32200]: info: Configuration: Data Directory: /home/btcpay/.btcpayserver/Main
Nov 11 13:13:41 raspberrypi dotnet[32200]: info: Configuration: Configuration File: /home/btcpay/.btcpayserver/Main/settings.config
Nov 11 13:13:41 raspberrypi dotnet[32200]: info: Configuration: Creating configuration file
Nov 11 13:13:41 raspberrypi dotnet[32200]: info: Configuration: Network: Mainnet
Nov 11 13:13:41 raspberrypi dotnet[32200]: info: Configuration: Supported chains: BTC
Nov 11 13:13:43 raspberrypi dotnet[32200]: info: Configuration: Network: Mainnet
Nov 11 13:13:43 raspberrypi dotnet[32200]: info: Configuration: Supported chains: BTC
Nov 11 13:13:43 raspberrypi dotnet[32200]: info: Configuration: SQLite DB used (Data Source=/home/btcpay/.btcpayserver/Main/sqllite.db)
Nov 11 13:13:43 raspberrypi dotnet[32200]: warn: Configuration: SQLite is not widely tested and should be considered experimental, we advise you to use postgres instead.
````

[openoms]

Not exactly sure how to go on configuring postgres.
so far found the compatible package:
$ sudo apt install postgresql-common

Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  postgresql-client-common ssl-cert
Suggested packages:
  libjson-perl openssl-blacklist
The following NEW packages will be installed:
  postgresql-client-common postgresql-common ssl-cert
0 upgraded, 3 newly installed, 0 to remove and 123 not upgraded.
Need to get 331 kB of archives.
After this operation, 911 kB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://archive-bm.raspbian.org/raspbian buster/main armhf postgresql-client-common all 200+deb10u4 [85.1 kB]
Get:2 http://archive-bm.raspbian.org/raspbian buster/main armhf ssl-cert all 1.0.39 [20.8 kB]
Get:3 http://archive-bm.raspbian.org/raspbian buster/main armhf postgresql-common all 200+deb10u4 [225 kB]
Fetched 331 kB in 1s (450 kB/s)            
Preconfiguring packages ...
Selecting previously unselected package postgresql-client-common.
(Reading database ... 100123 files and directories currently installed.)
Preparing to unpack .../postgresql-client-common_200+deb10u4_all.deb ...
Unpacking postgresql-client-common (200+deb10u4) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../ssl-cert_1.0.39_all.deb ...
Unpacking ssl-cert (1.0.39) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../postgresql-common_200+deb10u4_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (200+deb10u4) ...
Setting up postgresql-client-common (200+deb10u4) ...
Setting up ssl-cert (1.0.39) ...
Setting up postgresql-common (200+deb10u4) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_gb
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Processing triggers for man-db (2.8.5-2) ...
Processing triggers for systemd (241-7~deb10u4+rpi1) ...

BTCpayserver should pick it up when it is running as per https://github.com/btcpayserver/btcpayserver/issues/1996#issuecomment-711172039:

We cannot really create the postgres like we did with sqlite as you need postgres running as a service, configured and ready to be connected. We do create the database once we connect to the postgres server though.

[frennkie]

warn: Configuration: SQLite is not widely tested and should be considered experimental, we advise you to use postgres instead.

I assume they mean "not widely tested in BTCPay" - right?! (not sure whether that is actually true..)

Not exactly sure how to go on configuring postgres.

The plan is to run the PostgreSQL server, right?

sudo apt update
sudo apt install -y postgresql postgresql-11 postgresql-contrib

Check version:

sudo -u postgres psql --version
psql (PostgreSQL) 11.9 (Raspbian 11.9-0+deb10u1)

Open DB shell

sudo -u postgres psql
\l  <- lists the databases
\q  <- quit

Where are you stuck?

[frennkie]

I would assume that (as usually with this stuff) a database needs to be created for BTCPay and a user (which also needs to be assigned access to the DB).

sudo -u postgres createuser --pwprompt btcpay
sudo -u postgres createdb --encoding=UTF8 --owner=btcpay btcpay

[frennkie]

Nov 10 21:04:59 raspberrypi dotnet[5703]: ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'UNIQUE constraint failed: AddressInvoices.Address'.

You can manually go into your sqlite file.. maybe you are able to fix the problem.

btcpay@raspberrypi:~ $ sqlite3 ~/.btcpayserver/Main/sqllite.db
.tables  <- list tables
select * from AddressInvoices limit 3;

The UNIQUE constraint failed on AddressInvoices.Address error indicates that it tries to create a new address entry for an address that already exists. It's very likely that this issue would be exactly the same after switching from SQLite to PostgreSQL.

[frennkie]

Currently the sqlite file for BTCPay is stored on the external disk. If we move the PostgreSQL we would either need to put the database on the sd card (not a good idea) or on to the external disk (also not such a good idea).

SD-Card:

• reads and writes
• gone on update (requires robust back&restore)
• ...?

External Disk:

• might cause issues when disk is missing and service doesn't come up
• ...?

We should clearly outline what the benefit of this change for RaspiBlitz would be!

[openoms]

Thank you for all the input @frennkie!

I assume they mean "not widely tested in BTCPay" - right?! (not sure whether that is actually true..)
yes, it iso nly about btcpay. they say, that there is a lot of conversion needed to make some features work.

First of all will try to fix my database and would probably make sense to put off the issue to v1.7.

I think we should continue keeping the app-data on the disk including the postgres database.
The main reason to do this is the recommendation from the BTCpay devs.

[openoms]

Nov 10 21:04:59 raspberrypi dotnet[5703]: ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'UNIQUE constraint failed: AddressInvoices.Address'.

You can manually go into your sqlite file.. maybe you are able to fix the problem.

btcpay@raspberrypi:~ $ sqlite3 ~/.btcpayserver/Main/sqllite.db
.tables  <- list tables
select * from AddressInvoices limit 3;

The UNIQUE constraint failed on AddressInvoices.Address error indicates that it tries to create a new address entry for an address that already exists. It's very likely that this issue would be exactly the same after switching from SQLite to PostgreSQL.

@frennkie thanks so much you pointed me to the right direction, was able to fix the issue pretty quickly by deleting all the previous invoices from the database. Good thing is my shop settings, API keys etc did all remain intact.

what I did:

$ sudo apt install sqlite3
$ sudo su - btcpay
$ sqlite3 ~/.btcpayserver/Main/sqllite.db
sqlite> select * from AddressInvoices;
 # shows all the invoices
sqlite> delete from AddressInvoices;
sqlite> vacuum;
sqlite> select * from AddressInvoices;
 # all invoices gone

now have no errors on generating invoices.

[openoms]

Installation of postgres covered here: https://docs.btcpayserver.org/ManualDeploymentExtended/#-install
and https://docs.btcpayserver.org/ManualDeploymentExtended/#2-create-postgresql-database

[rootzoll]

so is this also an issue vor v1.6.3 or do we keep it on 1.7?

[openoms]

Can be kept for a future release. BTCPay is fully functional with sqllite.