PostgreSQL Datenbank Server

Einleitung

PostgreSQL ist ein relationales Datenbankmanagementsystem (RDBMS), das für seine Erweiterbarkeit, SQL-Standardkonformität, Robustheit und erweiterten Funktionen bekannt ist. PostgreSQL wird oft für Anwendungen bevorzugt, die eine strenge Transaktionskontrolle und Datenintegrität erfordern und FreeBSD bietet eine hervorragende Basis für den Betrieb einer PostgreSQL-Datenbank, die diese eine ähnliche Lizenz wie BSD verwendet.

Ziele

PostgreSQL bildet den Datenbankserver und damit die Basis für alle darauf aufbauenden Artikel.
Dies kann entweder separat als eigene Datenbank Jail oder zusammen mit der Applikation ohne separate Jail erfolgen. Gerade wenn ohnehin mehrere PostgreSQL-Datenbanken im Einsatz sind, lohnt sich ein zentraler Server.

NEU: Für ganz ungeduldige habe ich einen Konsole only Abschnitt. Da gibts nur Befehle, keine Erklärungen.

Letzte Aktualisierung:

  • 03.03.2024: Initiales Dokument

Voraussetzungen

  • 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

Damit sieht das Setup inkl. aller optionalen Möglichkeiten so aus:

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

Jail erstellen

Es wird ein eigenes Jail benötigt sollte PostgreSQL als zentraler Datenbank Server auch für andere Anwendungen dienen. Hier nehmen wir pgsql als Jail Namen und es wird auf jeden Fall die Option allow_sysvipc benötigt.

Optional: Datenverzeichnisse

Dies ist eher für fortgeschrittenen Anwender gedacht, die schon etwas Erfahrung besitzen.

Wie bestimmte Datenverzeichnisse außerhalb des Jails abgelegt werden, wird hier erklärt. Es werden folgende Verzeichnisse benötigt: 

└── /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 mit id:770/postgres)

Optional: Benutzer erstellen

Sollten die Daten außerhalb des Jails gespeichert werden, dann müssen die Berechtigungen passen und wird in einem separaten Artikel erklärt. Hier wird der Benutzer- und Gruppenname: postgres mit der ID: 770 benötigt.

Jail einrichten

Login per SSH in das Jail: ssh USERNAME@IP oder ssh USERNAME@HOSTNAME, um mit su root Rechte zu erlangen.

Paketquelle anpassen

Paketquellen sollten angepasst werden, siehe separater Artikel.

Pakete installieren & Dienste aktivieren

Nun die Paketquelle mit pkg update aktualisieren und dann die benötigten Pakete installieren: pkg install -y postgresql15-server Dienste aktivieren und beim Start des Jails automatisch mit starten: service postgresql enable

PostgreSQL

PostgreSQL mit service postgresql initdb initialisieren und mit service postgresql start starten.
PostreSQL root Passwort mit su -m postgres -c "createuser -s root --pwprompt" setzen.

Regelmäßige Wartung

Datenbanken sollten regelmäßig und am besten automatisch gewartet werden:

  • vacuumdb ist ein Dienstprogramm zum Bereinigen einer PostgreSQL-Datenbank
  • reindexdb ist ein Dienstprogramm zum Wiederaufbau von Indizes in einer PostgreSQL-Datenbank

Mit mkdir /var/db/postgres/bin eine Verzeichnis für das folgende Skript anlegen:

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'

und dann jeden Abend um 0:00 Uhr ausführen lassen:

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

Netzwerkzugriff

Falls es ein separates PostgreSQL Jail ist, dürfen damit die anderen Jails auf die Datenbank(en) zugreifen, muss aber erst vorher erlaubt werden:

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

Dies bedeutet auch, dass der Dienst nicht nur an localhost gebunden sein darf, da er sonst nicht über die Netzwerk-IP-Adresse erreichbar ist.

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

Datenbanken und Benutzer erstellen

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"

Konsole

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á