PostgreSQL 14 unter Ubuntu-Server

PostgreSQL ist eines der populärsten und mächtigsten Datenbankmanagementsysteme und zu dem noch Open Source. Dieser Blog-Artikel erklärt die Installation, Konfiguration und das Aktualisieren von PostgreSQL 14 unter Ubuntu Server 20.04 LTS.

Einführung

PostgreSQL ist ein freies, objektrelationales Datenbankmanagementsystem, das den größten Teil des SQL-Standards abbildet und diesen um einige Erweiterungen ergänzt. Die Open-Source-Lizenz von PostgreSQL ist sehr liberal und entspricht in etwa der MIT-Lizenz. PostgreSQL läuft unter Windows, Linux, macOS sowie weiteren Unix-Derivaten.

Version 14 wurde am 30. September 2021 veröffentlicht und wird das letzte Update am 12. November 2026 erhalten. Die Lebenszyklus beträgt also wie bei älteren Versionen auch fünf Jahre.

In der Tat haben wir nur positive Erfahrungen mit PostgreSQL gemacht und setzen diese Datenbank daher auch in unserem Open-Source-Projekt ENBREA ein. ENBREA ist kompatibel mit PostgreSQL in den Versionen 10 bis 14.

Installieren

Als erstes wollen wir das apt-Repository von PostgreSQL in unserem Ubuntu-Server registrieren:

$ echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee  /etc/apt/sources.list.d/pgdg.list

Anschließend wird der PostgreSQL-Signaturschlüssel importiert. Dieser wird für die Überprüfung des Installationspakets aus dem soeben registrierten apt-Repository benötigt.

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

Ist es auch der richtige Signaturschlüssel?

$ apt-key fingerprint B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8

Das Ergebnis sollte in etwa so aussehen:

pub   rsa4096 2011-10-13 [SC]
      B97B 0AFC AA1A 47F0 44F2  44A0 7FCC 7D46 ACCC 4CF8
uid           [ unknown] PostgreSQL Debian Repository

So, jetzt sind die Vorbereitungen abgeschlossen. Wir können die eigentliche Installation von PostgreSQL starten:

$ sudo apt update && sudo apt install postgresql-14 postgresql-client-14

Das dauert jetzt ein Weilchen. Aber am Ende sollte der PostgreSQL-Dienst erfolgreich gestartet sein.

Wir können dies wie folgt überprüfen:

$ systemctl status postgresql.service 

Die Antwort sollte in etwa so aussehen:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2021-10-14 21:47:07 CEST; 3min 24s ago
   Main PID: 54563 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 60)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Möchtest Du die exakte Version Deines PostgreSQl-Servers ermitteln, dann tippe folgendes ein:

$ pg_config --version

Die Antwort sollte in etwa so aussehen:

PostgreSQL 14.0 (Ubuntu 14.0-1.pgdg20.04+1)

Ein erster Überblick

Schauen wir uns die Installation von PostgreSQL genauer an:

• Die Installation hat einen neuen Ubuntu-Benutzer postgres angelegt. Dieser Benutzer ist Eigentümer (owner) des Serverprozesses und hat standardmäßig kein Kennwort (man kann sich also nicht direkt mit postgres unter Ubuntu anmelden).

• Die Installation hat automatisch einen neuen Cluster unter dem Verzeichnis /etc/postgres/14/main angelegt. Ein Datenbank-Cluster umfasst alle Datenbanken, die von einer einzelnen PostgreSQL-Server-Instanz verwaltet werden.

• Der superuser dieses Clusters heißt ebenfalls postgres. Hierbei handelt es sich aber um einen PostgreSQL-Benutzer, der nichts mit dem Ubuntu-User postgres zu tun hat.

• Das Binärverzeichnis liegt unter /usr/lib/postgresql/14/bin.

• Das Datenverzeichnis liegt unter /var/lib/postgresql/14/main.

• Die Log-Datei liegt unter /var/log/postgresql/postgresql-14-main.log.

• Die Standardkonfigurationsdatei für den Cluster lautet /etc/postgresql/14/main/postgresql.conf.

• Die Konfigurationsdatei zur Client-Authentifizierung liegt unter /etc/postgresql/14/main/pg_hba.conf. Hier wird festgelegt, welche Clients sich von woher mit welcher Authentifizierungsmethode an diesem PostgreSQL-Server anmelden können.

Die wesentlichen Daten zu unserem Cluster können wir jederzeit per pg_lsclusters abfragen:

$ pg_lsclusters 

Die Antwort sollte in etwa so aussehen:

Ver Cluster Port Status Owner    Data directory              Log file
14  main    5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

Authentifizierung

Standardmäßig sind in der Konfigurationsdatei pg_hba.conf folgende Authentifizierungsmethoden definiert:

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Nur lokale Zugriffe sind erlaubt, d.h. nur lokale Dienste oder Anwendungen, die sich per SSH mit dem Ubuntu-Server verbinden, können auf PostgreSQL zugreifen. Umgekehrt heißt dies, ein Remote-Zugriff via TCP/IP von außen ist standardmäßig unterbunden. Dies ist auch gut so und sollte nur mit Bedacht geändert werden.

Der Inhalt dieser Datei wird ausführlich in der PostgreSQL-Dokumentation beschrieben.

Lokalisierung

In der PostgreSQL-Konfigurationsdatei kannst Du die Sektion CLIENT CONNECTION DEFAULTS an Deine Bedürfnisse anpassen.

$ sudo nano /etc/postgresql/14/main/postgresql.conf

Zwei interessante Parameter sind datestyle und timezone. Ist Dein Ubuntu-Server beispielsweise auf Englisch eingestellt, kannst Du beide Parameter auf Deutsch umstellen:

datestyle = 'iso, dmy'
timezone = 'CET'

Vergiss bitte nicht, den PostgreSQL-Server nach jeder Änderung in der PostgreSQL-Konfigurationsdatei neu zu starten.

$ sudo systemctl restart postgresql.service 

Cluster neu erstellen

Die apt-Installation von PostgreSQL richtet automatisch einen neuen Datenbank-Cluster ein. Wem das nicht gefällt, der kann diesen löschen und anschließend einen neuen Datenbank-Cluster nach seinen eigenen Wünschen erstellen. Die Werkzeuge dafür heißen pg_dropcluster und pg_createcluster.

Ein Beispiel:

1. PostgreSQL-Dienst stoppen:

   $ sudo systemctl stop postgresql.service
   

2. Als Benutzer postgres agieren:

   $ sudo -u postgres -i
   

3. Aktuellen Datenbank-Cluster löschen:

   $ pg_dropcluster --stop 14 main
   

4. Neuen Datenbank-Cluster erzeugen:

   $ pg_createcluster 14 mycluster -- -U postgres -W -A scram-sha-256 -E utf8 
   

5. Die Sitzung für Benutzer postgres wieder verlassen:

   $ exit
   

6. PostgreSQL-Dienst wieder starten:

   $ sudo systemctl start postgresql.service
   

7. Abschließende Kontrolle:

   $ pg_lsclusters 
   

Cluster umbenennen

Manchmal möchte man einen Cluster nicht gleich neu erstellen, sondern lediglich umbenennen. Auch dafür gibt es ein Werkzeug namens pg_renamecluster.

Ein Beispiel:

$ sudo pg_renamecluster 14 main mein_cluster

Der Cluster wird zunächst gestoppt, dann von main nach mein_cluster umbenannt und anschließend wieder gestartet.

Updates einspielen

Die Versionsnummern von PostgreSQL bestehen seit Version 10 immer aus einer Major-Nummer und einer Minor-Nummer.

Beispiel: In der Versionsnummer 14.1 ist 14 die Major-Nummer und 1 die Minor-Nummer.

Es gilt dabei folgende Regel:

• PostgreSQL-Versionen mit derselben Major-Nummer sind untereinander kompatibel, was das interne Datenbankspeicherformat angeht. Da bedeutet, Updates innerhalb einer Major-Version (z.B. von 14.0 auf 14.2) bedürfen keiner Datenbankmigration.

• PostgreSQL-Versionen mit unterschiedlichen Major-Nummern sind untereinander inkompatibel, was das interne Datenbankspeicherformat angeht. Da bedeutet, Updates zwischen Major-Versionen (z.B. von 12.8 auf 14.0) bedürfen einer Datenbankmigration. Der Aufwand für solche Updates ist also höher.

Aktualisierung zwischen Minor-Versionen

Bei einer Aktualisierung von PostgreSQL zwischen zwei Minor-Versionen, kannst Du einfach die Packages per apt aktualisieren.

sudo apt update && sudo apt upgrade

Die Datenbankdateien bleiben unverändert. Grundsätzlich sollte man regelmäßig auf die neueste Minor-Version aktualisieren.

Aktualisierung zwischen Major-Versionen

Bei einer Aktualisierung von PostgreSQL zwischen zwei Major-Versionen ist es etwas komplizierter, da alle Datenbanken migriert werden müssen.

Die Vorgehensweise besteht ganz grob aus vier Schritten:

1. Installiere die neue PostgreSQL-Version parallel zur bisherigen PostgreSQL-Version.

2. Lösche den automatisch erzeugten Cluster der neuen PostgreSQL-Version mittels pg_dropcluster.

3. Führe eine Migration der Datenbanken mittels pg_upgradecluster durch.

4. Deinstalliere die alte PostgreSQL-Version.

Beispielhafte Aktualisierung von 12.8 auf 14.0:

1. Stoppe PostgreSQL 12.8:

   $ sudo systemctl stop postgresql@12-main
   

2. Installiere und konfiguriere PostgreSQL 14.0.

3. Stoppe PostgreSQL 14.0 und lösche den durch die Installation automatisch eingerichteten Cluster:

   $ sudo systemctl stop postgresql@14-main
   $ sudo pg_dropcluster 14 main --stop
   

4. Starte die Migration von 12.8 auf 14.0.

   $ sudo pg_upgradecluster -v 14 12 main -m=upgrade
   

Es wird automatisch ein neuer Cluster für PostgreSQL 14 erstellt. Das kann je nach Anzahl und Größe der Datenbanken etwas dauern.

Wenn erfolgreich beendet, kannst Du den neuen PostgreSQL-Server starten und mit ihm arbeiten.

$ sudo systemctl start postgresql@14-main

Wenn Du Dir absolut sicher bist, dass alles in Ordnung ist, kannst Du den alten PostgreSQL-Server komplett löschen.

$ sudo pg_dropcluster 12 main
$ sudo apt --purge remove postgresql-12 postgresql-client-12

Administrationswerkzeuge

psql

Wer mal eben schnell einen SQL-Befehl loswerden möchte, kann sich lokal per psql (PostgreSQL interactive terminal) anmelden.

Dazu solltest Du zunächst in die Rolle des Benutzers postgres schlüpfen:

$ sudo -u postgres -i

Und dann:

$ psql

Es erscheint folgende Eingabeaufforderung:

psql (14.0 (Ubuntu 14.0-1.pgdg20.04+1))
Geben Sie »help« für Hilfe ein.

postgres=# 

Als kleines Beispiel wollen wir jetzt die aktuelle Version von PostgreSQL per SQL erfragen:

postgres=# SELECT version();

Um psql wieder zu verlassen, gibt man \q ein und bestätigt.

pgAdmin

pgAdmin ist eine freie Open-Source-Software mit einer grafischen Oberfläche, um PostgreSQL-Datenbanken zu entwickeln und zu administrieren. pgAdmin 4 unterstützt PostgreSQL ab Version 9.5 und läuft unter Windows, Linux, macOS sowie weiteren Unix-Derivaten. Du kannst pgAdmin auf Deinem lokalen Computer installieren und per SSH auf die PostgreSQL-Instanz Deines Ubuntu-Server zugreifen.

Eine ausführliche Installationsanleitung für Windows findest Du in diesem Blog-Artikel.