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

• Import the repository signing key

Expected output:

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  4812  100  4812    0     0   5453      0 --:--:-- --:--:-- --:--:--  5449

• Create the repository configuration file

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

• Check the correct installation of the PostgreSQL

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

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

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

Example of expected output:

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

Create data folder

• Create the dedicated PostgreSQL data folder

• Assign as the owner to the postgres user

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

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

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_US.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/postgresdb17 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
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/17/bin/pg_ctl -D /data/postgresdb/17 -l logfile start

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

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

data_directory = '/data/postgresdb/17'

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

• 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

Expected output:

• 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

Example of the expected output:

• You can check if the cluster is on status "online" by

Expected output:

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

Expected output:

• Ensure PostgreSQL is listening on the default relational database port

Expected output:

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

Expected output:

Congrats! You have PostgreSQL ready to use as a database backend by another software

Extras (optional)

Some useful PostgreSQL commands

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

Example of expected output:

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

Example of expected output:

List the global existing databases

• Type the next command and enter

Example of expected output:

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=#

Replace <NAMEOFDATABASE> to the specific name of the database

Example:

Expected output:

• List tables

Example of expected output:

View the size of a specific database

• Type the next command and enter

Replace <NAMEOFDATABASE> to the specific name of the database

Example:

Example of expected output:

View the size of a specific table inside a database

• Enter a specific database with

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

• 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

Port reference

Port	Protocol	Use