🪧New PostgreSQL 🐘 bonus guide + other related 🔄

By ⚡️2FakTor⚡️ June 4, 2024
A new bonus guide about a great database backend and others!
🪧New PostgreSQL 🐘 bonus guide + other related 🔄

### What's changed

- [New bonus guide dedicated to install/upgrade/uninstall PostgreSQL](https://minibolt.minibolt.info/bonus-guides/system/postgresql)

- [Modified the LND guide to use PostgreSQL instead of bbolt](https://minibolt.minibolt.info/lightning/lightning/lightning-client)

- [Modified the Nostr relay guide to use PostgreSQL instead of SQLite (experimental)](https://minibolt.minibolt.info/bonus-guides/nostr/nostr-relay)

- [Modified the BTCPay Server bonus guide according to these changes](https://minibolt.minibolt.info/bonus-guides/bitcoin/btcpay-server)

- Used the [lndinit MiniBolt organization fork](extra section to migrate an existing LND bbolt database to PostgreSQL(Experimental(https://github.com/lightninglabs/lndinit/pull/21) - use it behind your responsibility⚠️🚨)

- [New Golang bonus guide](lndinit compilation(https://minibolt.minibolt.info/lightning/lightning/lightning-client#install-lndinit)

- [Updated LND to v0.18](https://minibolt.minibolt.info/lightning/lightning/lightning-client#download-binaries)

- [New Bitcoin Core extra section to renovate Tor & I2P addresses](https://minibolt.minibolt.info/bitcoin/bitcoin/bitcoin-client#renovate-your-bitcoin-core-tor-and-i2p-addresses)

- [New Bitcoin Core extra section to generate a full bitcoin.conf file](https://minibolt.minibolt.info/bitcoin/bitcoin/bitcoin-client#generate-a-full-bitcoin.conf-example-file)

- [Rebuilt some homepage sections and general structure](https://minibolt.minibolt.info//)

- 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](https://minibolt.minibolt.info/system/system/security)

🪧 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](https://minibolt.minibolt.info/lightning/lightning/lightning-client#use-the-default-bbolt-database-backend)

- [Use the default SQLite database backend for the Nostr relay](https://minibolt.minibolt.info/bonus-guides/bitcoin/nostr-relay#use-the-default-sqlite-database-backend)

⚠️Attention⚠️: [The migration process](according to the developer(https://github.com/lightninglabs/lndinit/pull/21), 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](HERE(https://habla.news/u/twofaktor@twofaktor.github.io/1735902560910) <

If you installed [NBXplorer + BTCPay Server](https://minibolt.minibolt.info/bonus-guides/bitcoin/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

    pgwal/archivestatus/

    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! 💙

    #minibolt #bitcoin #noderunner #guide #sovereign #diy #lightning #zerotrust #database #postgresql #ramix #selfhost #migrate #install #download #renovate #generate #use
    No comments yet.