PostgreSQL Database Server

Introduction

PostgreSQL is a relational database management system (RDBMS) known for its extensibility, SQL standards compliance, robustness and advanced features. PostgreSQL is often preferred for applications that require strict transaction control and data integrity, and FreeBSD provides an excellent base for running a PostgreSQL database using a licence similar to BSD.

Goals

PostgreSQL forms the database server and therefore the basis for all articles based on it.
This can be done either separately as a separate database jail or together with the application without a separate jail. Especially if several PostgreSQL databases are already in use, a central server is worthwhile.

NEW: For the very impatient I have a console only section. There are only commands, no explanations.

Last update:

  • 03.03.2024: Initial document

Precondition

  • TrueNAS Core oder reiner FreeBSD Server mit installiertem iocage
  • IP Adresse des PostgreSQL Jails ist bekannt
  • Hostname des PostgreSQL Jails ist bekannt (z.B. git.domain.local) und per internem DNS erreichbar

Diagramm

The setup, including all optional possibilities, looks like this:

                   ┌───────────────────────────────────────────────┐
                   │  TrueNAS                  Optional:           │
                   │ ┌──────────────────────┐ ┌──────────────────┐ │
                   │ │ jail/pgsql           │ │ jails_data/pgsql │ │
LAN: 0.0.0.0:5432 ─┼─┼─► postgresql-server ─┼─┼─► db             │ │
                   │ │                      │ │   backup         │ │
                   │ └──────────────────────┘ └──────────────────┘ │
                   └───────────────────────────────────────────────┘

Create jail

A separate jail is required if PostgreSQL should also serve as a central database server for other applications. Here we take pgsql as the jail name and the option allow_sysvipc is required in any case.

Optional: Data directories

This is more for advanced users who already have some experience.

How certain data directories are stored outside the jail is explained here The following directories are required: 

└── /mnt/tank/jails_data
    └── pgsql
        ├── backup # Ablage für Backups (im Jail: /mnt/backup)
        └── db     # Ablage für die PostgreSQL Datenbank (im Jail: /var/db/postgres with id:770/postgres)

Optional: Create User

If the data is to be stored outside the jail, the authorisations must be appropriate and are explained in a separate article. The user and group name: postgres with the ID: 770 is required here.

Set up jail

Login to the jail via SSH: ssh USERNAME@IP or ssh USERNAME@HOSTNAME to gain root rights with su.

Customise package source

Package sources should be customised, see separate article.

Install packages & activate services

Now update the package source with pkg update and then install the required packages: pkg install -y postgresql15-server. Activate services and start them automatically when starting the jail: service postgresql enable.

PostgreSQL

Initialise PostgreSQL with service postgresql initdb and start with service postgresql start.
Set the PostreSQL root password with su -m postgres -c "createuser -s root --pwprompt".

Regular maintenance

Databases should be maintained regularly and preferably automatically:

  • vacuumdb is a utility for cleaning up a PostgreSQL database
  • reindexdb is a utility for rebuilding indices in a PostgreSQL database

Use mkdir /var/db/postgres/bin to create a directory for the following script:

cat > /var/db/postgres/bin/vacuum.sh << 'EOF'
#! /bin/sh
/usr/local/bin/vacuumdb -az 1> /dev/null 2> /dev/null
/usr/local/bin/reindexdb -a 1> /dev/null 2> /dev/null
'EOF'

and then run it every evening at midnight:

echo "# PostgreSQL house keeping" >> /etc/crontab
echo "0       0       *       *       *       postgres /var/db/postgres/bin/vacuum.sh" >> /etc/crontab

Network access

If it is a separate PostgreSQL jail, the other jails can use it to access the database(s), but must be permitted first:

ee /var/db/postgres/data13/pg_hba.conf
+ # Datenbank fuer lokales Netzwerk erreichbar
+ host    *     *     192.168.xxx.0/24         trust

This also means that the service must not be bound only to localhost, otherwise it cannot be reached via the network IP address.

ee /var/db/postgres/data13/postgresql.conf
- #listen_addresses = 'localhost'
+ listen_addresses = '*'

Create databases and users

su -m postgres -c "createuser -s DATENBANKBENUTZER --pwprompt" # Erstellt den Benutzer
su -m postgres -c "createdb -O DATENBANKBENUTZER -E Unicode -T template1 DATENBANKNAME" # Erstellt die Datenbank für den Benutzer

Backup

pg_dump -Fc -h "DATENBANKHOST" -p "DATENBANKPORT" -d "DATENBANKNAME" -U "DATENBANKBENUTZER" -f "ZIELPFAD"

Console

pkg update
pkg install -y postgresql15-server
service postgresql enable
service postgresql initdb
mkdir /var/db/postgres/bin
fetch https://raw.githubusercontent.com/marzlberger/bsdbox/main/pgsql/vacuum.sh -o /var/db/postgres/bin/vacuum.sh
service postgresql start
echo "0 0 * * * postgres /var/db/postgres/bin/vacuum.sh" >> /etc/crontab
su -m postgres -c "createuser -s root --pwprompt"

Voilá