🪧New PostgreSQL 🐘 bonus guide + other related 🔄

What's changed#

---

• New bonus guide dedicated to install/upgrade/uninstall PostgreSQL
• Modified the LND guide to use PostgreSQL instead of bbolt
• Modified the Nostr relay guide to use PostgreSQL instead of SQLite (experimental)
• Modified the BTCPay Server bonus guide according to these changes
• Used the lndinit MiniBolt organization fork, to add an extra section to migrate an existing LND bbolt database to PostgreSQL (🚨⚠️Experimental - use it behind your responsibility⚠️🚨)
• New Golang bonus guide as a common language for the lndinit compilation
• Updated LND to v0.18
• New Bitcoin Core extra section to renovate Tor & I2P addresses
• New Bitcoin Core extra section to generate a full bitcoin.conf file
• Rebuilt some homepage sections and general structure
• Deleted the $ symbol of the commands to easy copy-paste to the terminal
• Deleted the initial incoming and the outgoing rules configuration of UFW, due to it being by default

---

🪧 PD: If you want to use the old database backend of the LND or Nostr relay, follow the next extra sections:

• Use the default bbolt database backend for the LND
• Use the default SQLite database backend for the Nostr relay

⚠️Attention⚠️: The migration process was tested on testnet mode from an existing bbolt database backend to a new PostgreSQL database using lndinit and the results were successful. However, It wasn't tested on mainnet, according to the developer, it is in experimental status which could damage your existing LND database.🚨 Use it behind your responsibility 🧼

---

🔧 PR related: https://github.com/minibolt-guide/minibolt/pull/93

---

♻️ Migrate the PostgreSQL database location

Attention!! These instructions are invalid since the latest changes applied to the PostgreSQL bonus guide. See more information on the recent associated post by clicking ~ > HERE < ~

If you installed NBXplorer + BTCPay Server before 05/06/2024, it is probably you have the database of the PostgreSQL cluster on the default path (/var/lib/postgresql/16/main/), follow the next instructions to migrate it to the new dedicated location on /data/postgresdb folder:

• With user admin create the dedicated PostgreSQL data folder

sudo mkdir /data/postgresdb

• Assign as the owner to the postgres user

sudo chown postgres:postgres /data/postgresdb

• Assign permissions of the data folder only to the postgres user

sudo chmod -R 700 /data/postgresdb

• Stop NBXplorer and BTCPay Server

sudo systemctl stop nbxplorer && sudo systemctl stop btcpayserver

• Stop PostgreSQL

sudo systemctl stop postgresql

• Use the rsync command to copy all files from the existing database on (/var/lib/postgresql/16/main) to the new destination directory (/data/postgresdb)

sudo rsync -av /var/lib/postgresql/16/main/ /data/postgresdb/

Expected output:

sending incremental file list
./
PG_VERSION
postgresql.auto.conf
postmaster.opts
postmaster.pid
base/
base/1/
base/1/112
base/1/113
base/1/1247
base/1/1247_fsm
base/1/1247_vm
base/1/1249
base/1/1249_fsm
base/1/1249_vm
[...]
pg_wal/000000010000000000000009
pg_wal/archive_status/
pg_xact/
pg_xact/0000

sent 164,483,875 bytes  received 42,341 bytes  36,561,381.33 bytes/sec
total size is 164,311,368  speedup is 1.00

• Edit the PostgreSQL data directory on configuration, to redirect the store to the new location

sudo nano /etc/postgresql/16/main/postgresql.conf --linenumbers

• Replace the line 42 to this. Save and exit

data_directory = '/data/postgresdb'

• Start PostgreSQL to apply changes and monitor the correct status of the main instance and sub-instance monitoring sessions before

sudo systemctl start postgresql

• You can monitor the PostgreSQL main instance by the systemd journal and check the log output to ensure all is correct. You can exit the monitoring at any time with Ctrl-C

journalctl -fu postgresql

Example of the expected output:

Nov 08 11:51:10 minibolt systemd[1]: Stopped PostgreSQL RDBMS.
Nov 08 11:51:10 minibolt systemd[1]: Stopping PostgreSQL RDBMS...
Nov 08 11:51:13 minibolt systemd[1]: Starting PostgreSQL RDBMS...
Nov 08 11:51:13 minibolt systemd[1]: Finished PostgreSQL RDBMS.

• You can monitor the PostgreSQL sub-instance by the systemd journal and check log output to ensure all is correct. You can exit monitoring at any time with Ctrl-C

journalctl -fu postgresql@16-main

Example of the expected output:

Nov 08 11:51:10 minibolt systemd[1]: Stopping PostgreSQL Cluster 16-main...
Nov 08 11:51:11 minibolt systemd[1]: postgresql@16-main.service: Succeeded.
Nov 08 11:51:11 minibolt systemd[1]: Stopped PostgreSQL Cluster 16-main.
Nov 08 11:51:11 minibolt systemd[1]: postgresql@16-main.service: Consumed 1h 10min 8.677s CPU time.
Nov 08 11:51:11 minibolt systemd[1]: Starting PostgreSQL Cluster 16-main...
Nov 08 11:51:13 minibolt systemd[1]: Started PostgreSQL Cluster 16-main.

• Start NBXplorer and BTCPay Server again

sudo systemctl start nbxplorer && sudo systemctl start btcpayserver

• Monitor to make sure everything is as you left it. You can exit monitoring at any time by pressing Ctrl+C

journalctl -fu nbxplorer

---

journalctl -fu btcpayserver

---

Enjoy it MiniBolter! 💙