Prof. Dr.-Ing. Oliver Radfelder
Informatik / Wirtschaftsinformatik
Hochschule Bremerhaven
Kurz und Knapp: postgresql

In den Docker-Containern ist das Datenbank-Management System postgresql installiert. Anders aber als bei der zentralen Instanz der mariadb müssen Sie sich selbst um das Verwalten (Datenbank anlegen, User anlegen, Hochfahren, Herunterfahren, ...) kümmern.

Aktivieren Sie den Datenbankserver innerhalb des Docker-Containers mit:

sudo /etc/init.d/postgresql start

Sie können diese Zeile in das Skript atreboot.sh in dem Home-Verzeichnis Ihres Docker-Containers aufnehmen, das bei jedem hbv_dockeraktivieren aufgerufen wird. Passend dazu sollten Sie dann auch in atstop.sh

sudo /etc/init.d/postgresql stop

aufnehmen.

Legen Sie als User postgres eine Datenbank und einen User an und geben Sie dem User die Rechte für die Datenbank:

sudo -u postgres psql -c "create database mypgdb"
sudo -u postgres psql -c "create user mypguser with password 'einsicherespasswort'"
sudo -u postgres psql -c "grant all privileges on database mypgdb to mypguser"
sudo -u postgres psql -d mypgdb -c "grant all on schema public to mypguser"

Stellen Sie sicher, dass Sie tatsächlich ein sicheres Passwort wählen!

Nun können Sie sich als User mypguser bei dem Server anmelden:

PGPASSWORD=einsicherespasswort psql -h localhost mypgdb mypguser

Tragen Sie die Zeile:

localhost:5432:mypgdb:mypguser:einsicherespasswort

in die Datei .pgpass in dem Homeverzeichnis des Docker-Containers ein und ändern Sie die Zugriffsrechte so, dass nur Sie Zugriff darauf haben. Dann können Sie sich das Setzen der Variable PGPASSWORD sparen.

Nun können Sie mit SQL Tabellen erzeugen:

create table demo (id serial primary key not null,name text);
insert into demo values (1,'moin');

Der Jdbc-Treiber für Postgresql ist in dem tomcat in Ihrem Container bereits enthalten. Sie müssen in Ihrer Web-Anwendung lediglich dafür sorgen, dass in der Datei META-INF/context.xml ein Verweis hinterlegt wird, über den Sie dann im Servlet darauf zugreifen können.

<Context>
  <Resource name="jdbc/mypgdb"
    auth="Container"
    type="javax.sql.DataSource"
    initialSize="3"
    maxTotal="40"
    maxWaitMillis="5000"
    username="mypguser"
    password="einsicherespasswort"
    driverClassName="org.postgresql.Driver"
    url="jdbc:postgresql://localhost:5432/mypgdb"
 />
</Context>

In dem Javacode sieht es dann wie gewohnt aus:

    Context initCtx = new InitialContext();
    Context envCtx = (Context) initCtx.lookup("java:/comp/env");
    DataSource ds = (DataSource)envCtx.lookup("jdbc/mypgdb");
    Connection connection = ds.getConnection();

In php wiederum sieht es wie folgt aus:

<?php
$dbconn = pg_connect("host=localhost dbname=mypgdb user=mypguser password=einsicherespasswort")
    or die('Verbindungsaufbau fehlgeschlagen: ' . pg_last_error());
$query = 'SELECT * FROM demo';
$result = pg_query($query) 
  or die('Abfrage fehlgeschlagen: ' . pg_last_error());
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
    foreach ($line as $col_value) {
        echo "$col_value ";
    }
    echo "\n";
}

pg_free_result($result);

pg_close($dbconn);

Oder eher mit PDO:

<?php
$sql = 'SELECT * from demo';
$conn = new PDO("pgsql:host=localhost dbname=mypgdb", 'mypguser', 'einsicherespasswort');
$q = $conn->query($sql);
while($r = $q->fetch(PDO::FETCH_ASSOC)) {
  echo $r['id']. ' ' . $r['msg'] . "\n";
}

Wenn Sie ein wenig SQL üben wollen, laden Sie sich den Inhalt der dvdrentals-Datenbank in PostgreSQL. In /home/common/data/ auf hopper finden Sie die Datei dvdrentals-postgres.tar, die Sie für das Tutorial unter postgresqltutorial.com nutzen können.

scp /home/common/data/dvdrental-postgres.tar mydocker:
ssh mydocker
sudo -u postgres psql -c 'create database dvdrental;'
sudo -u postgres pg_restore -d dvdrental dvdrental-postgres.tar
sudo -u postgres psql dvdrental
dvdrental=# 
select * from actor where last_name = 'Akroyd';
┌──────────┬────────────┬───────────┬────────────────────────┐
│ actor_id │ first_name │ last_name │      last_update       │
├──────────┼────────────┼───────────┼────────────────────────┤
│       58 │ Christian  │ Akroyd    │ 2013-05-26 14:47:57.62 │
│       92 │ Kirsten    │ Akroyd    │ 2013-05-26 14:47:57.62 │
│      182 │ Debbie     │ Akroyd    │ 2013-05-26 14:47:57.62 │
└──────────┴────────────┴───────────┴────────────────────────┘

Wenn wir Daten exportieren wollen, dann können wir entweder mit dem Schalter --csv arbeiten, aber sauberer scheint es mit copy zu werden:

psql -c "\copy (select * from demo where name like 'a%') to STDOUT delimiter ';' csv header;"
psql -c "\copy (select * from demo d, demo e where d.name like 'a%') to STDOUT WITH (HEADER TRUE,FORMAT csv,FORCE_QUOTE *);"

Alternativ reicht der Schalter --csv, dann lässt sich der Delimiter mit -P festlegen:

psql -P csv_fieldsep=';' --csv -c "select * from demo where name like 'a%'"

Besonders bekannt ist PostgreSQL für die Erweiterung postgis - eine Geodatenbank-Erweiterung. Die zugehörigen Module sind in dem Docker-Container bereits installiert, sie müssen lediglich für Ihre Datenbank akiviert werden.

sudo -u postgres psql -d mypgdb
mypgdb=# create extension postgis;

Weiteres finden Sie in der Dokumentation zu postgis. Andere interessante Erweiterungen sind file_fdw (ein schönes Beispiel unter rustprooflabs), plperl, pltcl, plpython3u oder postgres_fdw, die alle installiert sind. Und wer ein bisschen mehr Buzzwords braucht: Wie wäre es mit PostgreSQL und Machine Learning?

Der Postgres-Server ist nur von innerhalb des Docker-Containers zu erreichen. Sollten Sie ihn von außen ansprechen wollen, heisst es zu recherchieren und man-Pages zu lesen. Sie müssen dann den Port des Servers auf 6000 umbiegen (/etc/postgresql/15/main/postgresql.conf), dafür sorgen, dass der Server auf allen Interfaces lauscht (gleiche Datei) und in /etc/postgresql/15/main/pg_hba.conf noch einstellen, wie die Authentifizierung funktionieren soll (host all all 0.0.0.0/0 scram-sha-256). Das alles können Sie als User postgres durchführen. Allerdings sind all diese Einstellungen nach dem Aktivieren mit hbv_dockeraktivieren wieder verschwunden. Also hilft es nichts: da müssen Sie dann mit sed ran und diese Dateien beim Hochfahren in atreboot.sh umschreiben vor dem Starten der Datenbank. Alternativ können Sie sich auch die Konfigurationsdateien in Ihr Homeverzeichnis kopieren, editieren und dann beim Hochfahren in atreboot.sh an die entsprechende Stelle schieben.

In PostgreSQL programmiert man stored procedures mit plpgsql - einer recht mächtigen Sprache, die an Oracles plsql angelehnt ist.

CREATE OR REPLACE FUNCTION sum_of_two_numbers(m integer, n integer) 
RETURNS integer AS $$
        BEGIN
                RETURN m + n;
        END;
$$ LANGUAGE plpgsql;

plpgsql ist eine Erweiterung, die in den dockern automatisch aktiviert ist. Eine weitere Extension, die interessant ist, ist plsh - eine sogenannte unsichere Erweiterung. Im Produktivbetrieb sicher mit Vorsicht zu genießen, aber für eine schnelle Kommunikation mit dem umgebenden Host eine unschätzbare kleine Hilfe.

create extension plsh; -- einmalig aktivieren
CREATE OR REPLACE FUNCTION dolog(text) RETURNS text AS $$
#!/bin/bash
echo "$1" >/tmp/postgres.log
echo "done"
$$ LANGUAGE plsh;

select dolog('moin');

Damit lassen sich recht interessante Trigger bauen:

CREATE OR REPLACE FUNCTION dolog() RETURNS trigger AS $$
#!/bin/bash
echo "$1:$2" >> /tmp/postgresql.log
$$ LANGUAGE plsh;

CREATE OR REPLACE TRIGGER plshtrigger AFTER INSERT ON plshdemo
    FOR EACH ROW EXECUTE PROCEDURE dolog();

insert into plshdemo values(1,'Moin');

Ein solches Log ergibt zunächst wenig Sinn - dafür gibt es Werkzeuge in PostgreSQL. Wofür es aber sonst keine Werkzeuge gibt, ist beispielsweise einen Webhook einzubauen, der einen lokalen curl-Request absetzt, wann immer ein Eintrag in einer Tabelle (z.B. User) sich ändert. Das ginge sicher auch mit einem Polling-Ansatz, aber ich finde diesen Ansatz reizvoll. Eine andere Variante wäre, das listen/notify-Konzept von PostgreSQL zu nutzen.

CREATE OR REPLACE FUNCTION notify_insertion()
  RETURNS trigger AS $$
DECLARE
BEGIN
  PERFORM pg_notify('insertedevents',row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER plshtriggernotify AFTER INSERT ON plshdemo
    FOR EACH ROW EXECUTE PROCEDURE notify_insertion();

Ein entsprechender Listener lässt sich dann beispielsweise mit php - allerdings nur pollend - realisieren:

<?php
require_once 'config.php';

$conn=pg_connect("host=$host port=$port user=$user password=$password dbname=$db");

pg_query($conn, 'LISTEN insertedevents;');

while(true){
  $notify = pg_get_notify($conn,PGSQL_ASSOC);
  if (!$notify) {
    usleep(10000);
  } else {
    print_r($notify);
  }
}

Mit dieser Kombination lassen sich ohne Probleme selbst in unserer stark beschränkten Docker-Umgebung ohne deutliche Mehrbelastung 50.000 Nachrichten (also inserts+notify) weitergeben (oder doch nicht? testen mit tomcat...)- z.B. an eine Redis-Instanz. Das ist effizienter und sicherer als ein externes Skript aufzurufen, allerdings sind dann natürlich mehr Komponenten zu verwalten und zu monitoren.

Links