PostgreSQL

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

Difficulty: Medium

Installation

Install PostgreSQL using the apt package manager

• With user admin, update and upgrade your OS

sudo apt update && sudo apt full-upgrade

• create the file repository configuration

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

• Import the repository signing key

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Expected output:

> Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).
OK

You can ignore the W: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)) message

• Update the package lists and install the latest version of PostgreSQL. Press "y" and enter or directly enter when the prompt asks you

sudo apt update && sudo apt install postgresql postgresql-contrib

• Check the correct installation of the PostgreSQL

psql -V

Example of expected output:

> psql (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)

• Ensure PostgreSQL is running and listening on the default port 5432

sudo ss -tulpn | grep LISTEN | grep postgres

Expected output:

> tcp   LISTEN 0      200        127.0.0.1:5432       0.0.0.0:*    users:(("postgres",pid=2532748,fd=7))
> tcp   LISTEN 0      200            [::1]:5432          [::]:*    users:(("postgres",pid=2532748,fd=6))

• You can monitor general logs by the systemd journal. You can exit monitoring at any time with Ctrl-C

journalctl -fu postgresql

Example of expected output:

May 31 13:51:11 minibolt systemd[1]: Finished PostgreSQL RDBMS.

• And the sub-instance and specific cluster logs. You can exit monitoring at any time with Ctrl-C

journalctl -fu postgresql@16-main

Example of expected output:

May 31 13:51:18 minibolt systemd[1]: Starting PostgreSQL Cluster 16-main...
May 31 13:51:21 minibolt systemd[1]: Started PostgreSQL Cluster 16-main.

Create data folder

• 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

• With user postgres, create a new cluster on the dedicated folder

sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /data/postgresdb

Example of expected output ⬇️

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_GB.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/postgresdb ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Madrid
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/16/bin/pg_ctl -D /data/postgresdb -l logfile start

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

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

• Replace the line 42 with /var/lib/postgresql/16/main to the next. Save and exit

data_directory = '/data/postgresdb'

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

sudo systemctl restart postgresql

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

journalctl -fu postgresql

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. 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]: [email protected]: Succeeded.
Nov 08 11:51:11 minibolt systemd[1]: Stopped PostgreSQL Cluster 16-main.
Nov 08 11:51:11 minibolt systemd[1]: [email protected]: 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.

(Optional) -> If you want, you can disable the autoboot option for PostgreSQL (not recommended) using:

sudo systemctl disable postgresql

Expected output:

Synchronizing state of postgresql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install disable postgresql
Removed /etc/systemd/system/multi-user.target.wants/postgresql.service.

Create a PostgreSQL user account

• Create a new database admin user and assign the password "admin" with the automatically created user for the PostgreSQL installation, called postgres

sudo -u postgres psql -c "CREATE ROLE admin WITH LOGIN CREATEDB PASSWORD 'admin';"

Extras (optional)

Some useful PostgreSQL commands

• With user admin, enter the PostgreSQL CLI with the user postgres. The prompt should change to postgres=#

sudo -u postgres psql

Example of expected output:

psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
Type "help" for help.

postgres=#

Type \q command and enter to exit PostgreSQL CLI and exit to come back to the admin user

List the global existing users and roles associated

• Type the next command and enter

\du

Example of expected output:

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 admin     | Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

List the global existing databases

• Type the next command and enter

\l

Example of expected output:

     Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
--------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 btcpay       | admin    | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 lndb         | admin    | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 nbxplorer    | admin    | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 nostrelay    | admin    | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 postgres     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 template0    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
              |          |          |                 |             |             |            |           | postgres=CTc/postgres
 template1    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
              |          |          |                 |             |             |            |           | postgres=CTc/postgres
(8 rows)

List tables inside of a specific database

• Connect to a specific database, type the next command, and enter. The prompt should change to the name of the database. Example: lndb=#

\c <NAMEOFDATABASE>

Replace <NAMEOFDATABASE> to the specific name of the database

Example:

\c lndb

Expected output:

> You are now connected to database "lndb" as user "postgres".

• List tables

\dt

Example of expected output:

             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+-------
 public | channeldb_kv     | table | admin
 public | decayedlogdb_kv  | table | admin
 public | macaroondb_kv    | table | admin
 public | towerclientdb_kv | table | admin
 public | towerserverdb_kv | table | admin
 public | walletdb_kv      | table | admin
(6 rows)

View the size of a specific database

• Type the next command and enter

SELECT pg_size_pretty(pg_database_size('<NAMEOFDATABASE>'));

Replace <NAMEOFDATABASE> to the specific name of the database

Example:

SELECT pg_size_pretty(pg_database_size('lndb'));

Example of expected output:

 pg_size_pretty
----------------
 546 MB
(1 row)

View the size of a specific table inside a database

• Enter a specific database with

\c <NAMEOFDATABASE>

Replace <NAMEOFDATABASE> to the specific name of the database

Example:

\c lndb

• View the size of a specific table

SELECT pg_size_pretty(pg_total_relation_size('<NAMEOFTABLE>'));

Replace <NAMEOFTABLE> to the specific name of the database

Example:

SELECT pg_size_pretty(pg_total_relation_size('channeldb_kv'));

Example of expected output:

 pg_size_pretty
----------------
 457 MB
(1 row)

Detele a specific database

• Type the next command and enter

DROP DATABASE <NAMEOFDATABASE>;

Replace <NAMEOFTABLE> to the specific name of the table

Example:

DROP DATABASE lndb;

Expected output:

> DROP DATABASE

Delete a table inside of a specific database

Stop the service related to this database before the action, i.e: sudo systemctl stop lnd

• Enter a specific database with

\c <NAMEOFDATABASE>

Replace <NAMEOFDATABASE> to the specific name of the database

Example:

\c lndb

• Delete a specific table

Stop the service related to this table and database before the action, i.e: sudo systemctl stop lnd

DROP TABLE <NAMEOFTABLE>;

Replace <NAMEOFTABLE> to the specific name of the table

Example:

DROP TABLE towerclientdb_kv;

Upgrade

The latest release can be found on the official PostgreSQL web page.

• To upgrade, type this command

sudo apt update && sudo apt full-upgrade

Uninstall

Uninstall PostgreSQL package and configuration

• With user admin, stop and disable the postgres service

sudo systemctl stop postgresql && sudo systemctl disable postgresql

• Uninstall PostgreSQL using the apt package manager

sudo apt remove postgresql postgresql-* --purge

• Uninstall possible unnecessary dependencies

sudo apt autoremove

• Delete configuration files and data

sudo rm -rf /etc/postgresql/ && sudo rm -rf /etc/postgresql-common/ && sudo rm -rf /var/lib/postgresql/ && sudo rm -rf /var/log/postgresql/ && sudo rm -rf /usr/lib/postgresql/ && sudo rm -rf /usr/share/postgresql/

Uninstall postgres user

• Delete the postgres user. Don't worry about userdel: bitcoind mail spool (/var/mail/bitcoind) not found output, the uninstall has been successful

sudo userdel -rf postgres

• Delete postgres group

sudo groupdel postgres

• Delete the complete postgresdb directory

sudo rm -rf /data/postgresdb