Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 0: Trainingsgelände

Ziel: PostgreSQL 18 aus dem PGDG-Repository läuft auf pg1 und pg2, pg1 hat die Datenbank notes mit Testdaten und nimmt Verbindungen aus dem Labornetz an. · Dauer: ca. 60 min · Voraussetzung: zwei VMs mit Ubuntu 24.04

Briefing

Ubuntu 24.04 bringt PostgreSQL 16 mit. Wir nehmen das offizielle Repository des Projekts (PGDG, apt.postgresql.org): Es liefert jede unterstützte Major-Version mit allen Minor-Updates. pg1 wird der Primary, pg2 ab Tag 9 Backup-Ziel und ab Tag 10 Standby. Heute ist pg2 vor allem Client.

  pg1 .182 (argo)                         pg2 .123 (prod)
  PostgreSQL 18, Cluster 18/main          PostgreSQL 18, Cluster 18/main
  Datenbank notes, 10 000 Zeilen          psql als Client  ---- 5432/tcp ---->  pg1
  4 vCPU / 8 GB                           2 vCPU / 4 GB
        |_______________________________________|
              172.16.0.0/24 · SSH postgres@pg1 <-> postgres@pg2

Ersetze die IPs durch deine eigenen. Zwischen den VMs gibt es keine Namensauflösung, deshalb stehen überall IPs. Drill 1 und 2 laufen auf pg1 und pg2, Drill 3 bis 6 auf pg1.

Drill 1: PGDG-Repository einbinden

Paketquellen im Buch fragen die Architektur ab (dpkg --print-architecture). Auf einem Mac mit Apple Silicon (etwa VMs per multipass) ist das arm64 – PGDG baut für die LTS-Versionen von Ubuntu auch arm64-Pakete, die Befehle bleiben gleich, nur die Ausgaben im Buch zeigen amd64.

Erst den Signaturschlüssel holen und seinen Fingerabdruck prüfen:

sudo apt-get update
sudo apt-get install -y curl ca-certificates gpg
sudo install -d /usr/share/postgresql-common/pgdg
K=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
sudo curl -fsSL -o $K https://www.postgresql.org/media/keys/ACCC4CF8.asc
gpg --show-key $K

Erwartet (gekürzt):

pub   rsa4096 2011-10-13 [SC]
      B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8
uid                      PostgreSQL Debian Repository

Der Fingerabdruck muss mit dem auf postgresql.org/download/linux/ubuntu übereinstimmen. Dann die Quelle im deb822-Format. Codename und Architektur setzt die Shell ein:

cat <<EOF | sudo tee /etc/apt/sources.list.d/pgdg.sources
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: $(lsb_release -cs)-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: $K
EOF
sudo apt-get update
apt-cache policy postgresql-18 postgresql-19 | grep -E '^post|Candidate'

Erwartet:

Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: noble-pgdg
Architectures: amd64
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
postgresql-18:
  Candidate: 18.7-1.pgdg24.04+2
postgresql-19:
  Candidate: (none)

main enthält nur freigegebene Versionen. Vorabversionen (Beta, Release Candidate) liegen in eigenen Komponenten wie 19 – nichts für einen Server, den du betreiben willst.

Tipp: Die Doku nennt auch ein fertiges Skript: sudo apt install -y postgresql-common und dann sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh. Es macht dasselbe wie die Befehle oben – hier siehst du, was passiert.

Fortschritt speichern? Kostenlos registrieren

Drill 2: PostgreSQL 18 installieren

Weiter auf beiden VMs:

sudo apt-get install -y postgresql-18 postgresql-client-18
pg_lsclusters
systemctl list-units 'postgresql*' --no-legend
psql --version

Erwartet (gekürzt):

Creating new PostgreSQL cluster 18/main ...
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main --auth-local peer \
  --auth-host scram-sha-256 --no-instructions
...
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...
  postgresql.service         loaded active exited  PostgreSQL RDBMS
  [email protected] loaded active running PostgreSQL Cluster 18-main
psql (PostgreSQL) 18.7 (Ubuntu 18.7-1.pgdg24.04+2)

Das Paket legt gleich einen Cluster an: Version 18, Name main, Port 5432. Debian/Ubuntu trennen dabei Konfiguration (/etc/postgresql/18/main) und Daten (/var/lib/postgresql/18/main). Jeder Cluster ist eine eigene systemd-Unit postgresql@<version>-<name>; postgresql.service ist nur der Sammelschalter.

Achtung: Nicht postgresql-contrib installieren. Das Ubuntu-Metapaket zieht PostgreSQL 16 nach (apt-cache policy postgresql-contrib zeigt 16+257build1.1). Die contrib-Erweiterungen wie pg_stat_statements stecken seit PostgreSQL 10 schon in postgresql-18.

sudo -u postgres psql -c "SELECT name, default_version FROM pg_available_extensions
  WHERE name IN ('pg_stat_statements', 'amcheck', 'pgcrypto') ORDER BY name;"

Erwartet:

        name        | default_version
--------------------+-----------------
 amcheck            | 1.5
 pg_stat_statements | 1.12
 pgcrypto           | 1.4
(3 rows)

Fortschritt speichern? Kostenlos registrieren

Drill 3: Erster Login als postgres

Ab hier auf pg1. Nach der Installation gibt es genau eine Rolle: postgres. Lokal gilt peer: PostgreSQL glaubt dem Betriebssystem, wer am Unix-Socket anklopft. Wer Linux-Benutzer postgres ist, ist auch Datenbank-Rolle postgres – ohne Passwort.

sudo -u postgres psql -Atc 'SELECT version()'
sudo -u postgres psql -Atc 'SHOW data_directory' -c 'SHOW config_file' \
  -c 'SHOW hba_file' -c 'SHOW data_checksums' -c 'SHOW server_encoding'
sudo -u postgres psql -c 'SELECT datname FROM pg_database;'
psql

Erwartet (gekürzt):

PostgreSQL 18.7 (Ubuntu 18.7-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by ...
/var/lib/postgresql/18/main
/etc/postgresql/18/main/postgresql.conf
/etc/postgresql/18/main/pg_hba.conf
on
UTF8
  datname
-----------
 postgres
 template1
 template0
(3 rows)
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: FATAL:  role "ubuntu" does not exist

data_checksums ist ab PostgreSQL 18 standardmäßig an: Jede Datenseite trägt eine Prüfsumme, kaputte Blöcke fallen beim Lesen auf statt still falsche Daten zu liefern. Das letzte psql scheitert, weil es dich als Rolle ubuntu anmeldet – die gibt es noch nicht.

Fortschritt speichern? Kostenlos registrieren

Drill 4: Eigene Rolle und Datenbanken

Im Labor bekommt ubuntu Superuser-Rechte, damit du nicht jedes Mal sudo -u postgres tippen musst. psql ohne -d verbindet sich mit der Datenbank, die wie die Rolle heißt – deshalb legt das nackte createdb die Datenbank ubuntu an:

sudo -u postgres createuser --superuser ubuntu
createdb
createdb notes
psql -c "SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolname !~ '^pg_';"
psql -d notes -c '\conninfo' | grep -E 'Database|Client User|Socket|Password|SSL Conn'

Erwartet:

 rolname  | rolsuper | rolcanlogin
----------+----------+-------------
 postgres | t        | t
 ubuntu   | t        | t
(2 rows)

 Database             | notes
 Client User          | ubuntu
 Socket Directory     | /var/run/postgresql
 Password Used        | false
 SSL Connection       | false

Über den Socket gibt es kein Passwort und kein TLS – das ist peer. Tag 2 baut Rollen mit genau den Rechten, die sie brauchen.

Fortschritt speichern? Kostenlos registrieren

Drill 5: Tabelle notes und Testdaten

Die Tabelle entspricht dem Schema, das notes-api mit notes-api migrate anlegt (Teil 2 bindet die Anwendung an). gen_random_uuid() ist seit PostgreSQL 13 eingebaut:

psql -d notes <<'EOF'
CREATE TABLE notes (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  text       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO notes (text) VALUES
  ('Tag 0: PostgreSQL 18 läuft'),
  ('Backup ist erst ein Backup, wenn der Restore geklappt hat'),
  ('Der Standby kommt an Tag 10');
INSERT INTO notes (text, created_at)
SELECT 'Testnotiz ' || i, now() - i * interval '1 minute'
FROM generate_series(1, 10000) AS i;
EOF
psql -d notes -c '\d notes'
psql -d notes -c "SELECT count(*), pg_size_pretty(pg_total_relation_size('notes'))
  FROM notes;"

Erwartet:

CREATE TABLE
INSERT 0 3
INSERT 0 10000
                               Table "public.notes"
   Column   |           Type           | Collation | Nullable |      Default
------------+--------------------------+-----------+----------+-------------------
 id         | uuid                     |           | not null | gen_random_uuid()
 text       | text                     |           | not null |
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)

 count | pg_size_pretty
-------+----------------
 10003 | 1120 kB
(1 row)

generate_series erzeugt die 10 000 Zeilen in einem Rutsch. Mehr Last bauen Tag 5 (generate_series) und Tag 7 (pgbench).

Fortschritt speichern? Kostenlos registrieren

Drill 6: Netzwerkzugang öffnen

Zuerst eine Rolle für Verbindungen übers Netz, mit Passwort und nur den nötigen Rechten. Das Passwort ist ein Lab-Wert:

psql -d notes <<'EOF'
CREATE ROLE notes_app LOGIN PASSWORD 'lab-notes-123';
GRANT SELECT, INSERT ON notes TO notes_app;
EOF
psql -Atc "SELECT rolname, left(rolpassword, 14) FROM pg_authid
  WHERE rolname = 'notes_app';"

Erwartet:

CREATE ROLE
GRANT
notes_app|SCRAM-SHA-256$

PostgreSQL speichert nur einen SCRAM-Hash, nie das Passwort. Interaktiv ist \password notes_app besser: Dann landet das Passwort weder in der Shell-History noch im Log.

Jetzt hört der Server nur auf localhost. Zwei Stellschrauben öffnen ihn: listen_addresses (auf welchen Adressen er lauscht) und pg_hba.conf (wer sich von wo wie anmelden darf):

sudo ss -tlnp | grep 5432
psql -c "ALTER SYSTEM SET listen_addresses = '*';"
echo 'host  notes  notes_app  172.16.0.0/24  scram-sha-256' \
  | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
sudo systemctl restart postgresql@18-main
sudo ss -tlnp | grep 5432
psql -c "SELECT line_number AS nr, database, user_name, address, auth_method
  FROM pg_hba_file_rules WHERE type = 'host';"

Erwartet (gekürzt):

LISTEN 0  200   127.0.0.1:5432   0.0.0.0:*  users:(("postgres",pid=4095,fd=7))
LISTEN 0  200       [::1]:5432      [::]:*  users:(("postgres",pid=4095,fd=6))
ALTER SYSTEM
host  notes  notes_app  172.16.0.0/24  scram-sha-256
LISTEN 0  200     0.0.0.0:5432   0.0.0.0:*  users:(("postgres",pid=4704,fd=6))
LISTEN 0  200        [::]:5432      [::]:*  users:(("postgres",pid=4704,fd=7))
 nr  |   database    |  user_name  |  address   |  auth_method
-----+---------------+-------------+------------+---------------
 125 | {all}         | {all}       | 127.0.0.1  | scram-sha-256
 127 | {all}         | {all}       | ::1        | scram-sha-256
 131 | {replication} | {all}       | 127.0.0.1  | scram-sha-256
 132 | {replication} | {all}       | ::1        | scram-sha-256
 133 | {notes}       | {notes_app} | 172.16.0.0 | scram-sha-256
(5 rows)

ALTER SYSTEM schreibt in postgresql.auto.conf im Datenverzeichnis, nicht in /etc/postgresql. listen_addresses braucht einen Neustart, eine Änderung an pg_hba.conf nur ein Reload. Die Regel lässt genau eine Rolle in genau eine Datenbank, und nur aus dem Labornetz. Tag 3 vertieft beides.

Fortschritt speichern? Kostenlos registrieren

Drill 7: Verbindung von pg2 nachweisen

Auf pg2:

psql -h 172.16.0.182 -U notes_app -d notes -c 'SELECT count(*) FROM notes;'

Erwartet:

Password for user notes_app:
 count
-------
 10003
(1 row)

Das Passwort ist lab-notes-123. Damit es nicht bei jedem Befehl fragt, kommt es für diesen Drill in die Umgebung (Tag 1 zeigt mit .pgpass den sauberen Weg):

export PGPASSWORD=lab-notes-123
C="host=172.16.0.182 dbname=notes user=notes_app"
psql "$C" -c '\conninfo' | grep -E 'Host|Client User|Password|SSL (Conn|Proto)'
psql "$C" -c "INSERT INTO notes (text) VALUES ('Gruß von pg2') RETURNING id;"
psql "$C" -c 'DELETE FROM notes;'
psql -h 172.16.0.182 -U notes_app -d postgres -c 'SELECT 1;'
psql -h 172.16.0.182 -U postgres -d notes -c 'SELECT 1;'
unset PGPASSWORD

Erwartet (gekürzt):

 Client User          | notes_app
 Host                 | 172.16.0.182
 Password Used        | true
 SSL Connection       | true
 SSL Protocol         | TLSv1.3
                  id
--------------------------------------
 ed030bbc-f4d5-43ae-be17-06cdd6801dd7
(1 row)

INSERT 0 1
ERROR:  permission denied for table notes
psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:  no
pg_hba.conf entry for host "172.16.0.123", user "notes_app", database "postgres",
SSL encryption
...
psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:  no
pg_hba.conf entry for host "172.16.0.123", user "postgres", database "notes", ...

Drei Nachweise: Die Verbindung läuft verschlüsselt (Ubuntu richtet ssl = on mit dem selbst signierten „snakeoil“-Zertifikat ein, Tag 3 ersetzt es durch eine eigene CA). notes_app darf lesen und schreiben, aber nicht löschen. Und pg_hba.conf weist jede andere Kombination ab. Jede Ablehnung steht doppelt da, weil psql es nach dem TLS-Versuch noch unverschlüsselt probiert (sslmode=prefer).

Fortschritt speichern? Kostenlos registrieren

Drill 8: SSH zwischen pg1 und pg2 für postgres

pgBackRest (Tag 9) spricht per SSH mit dem jeweils anderen Server, und ein archive_command mit rsync oder scp schiebt WAL-Dateien auf pg2. Beides läuft als Linux-Benutzer postgres. (pg_basebackup und pg_rewind brauchen kein SSH, sie nutzen eine normale Datenbankverbindung.) Auf beiden VMs einen Lab-Schlüssel ohne Passphrase erzeugen:

sudo -u postgres ssh-keygen -t ed25519 -N '' -C postgres@$(hostname) \
  -f /var/lib/postgresql/.ssh/id_ed25519

Erwartet (gekürzt):

Created directory '/var/lib/postgresql/.ssh'.
Your identification has been saved in /var/lib/postgresql/.ssh/id_ed25519

ssh-copy-id hilft nicht: postgres hat kein Passwort (!* in /etc/shadow). Die Public Keys verteilt dein eigener Rechner, der per Schlüssel als ubuntu auf beide VMs kommt:

ssh [email protected] sudo cat /var/lib/postgresql/.ssh/id_ed25519.pub \
  | ssh [email protected] \
  'sudo -u postgres tee -a /var/lib/postgresql/.ssh/authorized_keys'
ssh [email protected] sudo cat /var/lib/postgresql/.ssh/id_ed25519.pub \
  | ssh [email protected] \
  'sudo -u postgres tee -a /var/lib/postgresql/.ssh/authorized_keys'

Erwartet (gekürzt):

ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIITG4bCuO5YItznoqwPrfs4KIVTL1dgP5CVcBl8Y4R3u ...
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIGJC8fo7nzZ7fT3xixy5xXz7xJIiACatIjyrWJafHwf0 ...

tee legt die Datei mit der Umask von sudo an (rw-rw-r--). Auf beiden VMs auf 600 setzen:

sudo -u postgres chmod 600 /var/lib/postgresql/.ssh/authorized_keys

Test von pg1 zu pg2 und zurück; accept-new speichert den Host-Key beim ersten Kontakt:

sudo -u postgres ssh -o StrictHostKeyChecking=accept-new [email protected] \
  'hostname; whoami; pg_lsclusters'

Erwartet (gekürzt):

Warning: Permanently added '172.16.0.123' (ED25519) to the list of known hosts.
prod
postgres
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...

Auf pg2:

sudo -u postgres ssh -o StrictHostKeyChecking=accept-new [email protected] \
  'hostname; whoami'

Erwartet:

Warning: Permanently added '172.16.0.182' (ED25519) to the list of known hosts.
argo
postgres

Die VM für pg1 heißt im Labor argo, die für pg2 heißt prod – im Buch stehen immer die Rollen. Ab jetzt laufen alle Befehle auf pg1, sofern nichts anderes dasteht.

Fortschritt speichern? Kostenlos registrieren

Übung: Speed-Drills

  1. Zeige die Version des laufenden Servers per SQL. Zeitziel: 15 s
  2. Zeige alle Cluster dieser VM mit Port und Status. Zeitziel: 5 s
  3. Zeige, woher der aktuelle Wert von listen_addresses kommt. Zeitziel: 30 s
  4. Lade die Konfiguration neu, ohne Verbindungen zu trennen. Zeitziel: 10 s

Lösung

psql -Atc 'SHOW server_version'
pg_lsclusters
psql -Atc "SELECT setting, sourcefile FROM pg_settings
  WHERE name = 'listen_addresses';"
psql -Atc 'SELECT pg_reload_conf()'

Erwartet (gekürzt):

18.7 (Ubuntu 18.7-1.pgdg24.04+2)
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...
*|/var/lib/postgresql/18/main/postgresql.auto.conf
t

sourcefile zeigt auf postgresql.auto.conf: ALTER SYSTEM schlägt jede Zeile in postgresql.conf. Gleichwertig zum letzten Befehl: sudo systemctl reload postgresql@18-main.

Fortschritt speichern? Kostenlos registrieren

Kontrollpunkt

pg_lsclusters

Erwartet: Cluster 18 main auf Port 5432, online – auf pg1 und auf pg2.

psql -Atc 'SHOW server_version'

Erwartet: 18.7 (Ubuntu 18.7-1.pgdg24.04+2) oder eine neuere 18.x.

psql -d notes -Atc 'SELECT count(*) FROM notes'

Erwartet: 10004 (10 003 aus Drill 5 plus die Notiz von pg2).

sudo ss -tlnp | grep 5432

Erwartet: 0.0.0.0:5432 und [::]:5432.

psql -Atc 'SHOW data_checksums'

Erwartet: on

Auf pg2:

psql -h 172.16.0.182 -U notes_app -d notes -Atc 'SELECT count(*) FROM notes'

Erwartet: Passwortabfrage, dann 10004.

sudo -u postgres ssh [email protected] hostname

Erwartet: argo – ohne Rückfrage nach Passwort oder Host-Key.

Challenge

Lege auf pg2 neben 18/main einen zweiten Cluster 18/test an, starte ihn, melde dich an und zeige Port und Datenverzeichnis. Danach entferne ihn wieder vollständig.

Hinweis: Die Debian-Werkzeuge heißen pg_createcluster und pg_dropcluster; den Port wählt pg_createcluster selbst.

Lösung

sudo pg_createcluster 18 test --start
pg_lsclusters
sudo -u postgres psql -p 5433 -Atc 'SHOW port' -c 'SHOW data_directory'
systemctl is-active postgresql@18-test
sudo pg_dropcluster --stop 18 test
pg_lsclusters

Erwartet (gekürzt):

Ver Cluster Port Status Owner    Data directory              Log file
18  test    5433 online postgres /var/lib/postgresql/18/test ...
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...
18  test    5433 online postgres /var/lib/postgresql/18/test ...
5433
/var/lib/postgresql/18/test
active
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...

Ein Cluster ist eine Server-Instanz mit eigenem Datenverzeichnis und Port – nicht mehrere Maschinen. pg_dropcluster löscht Konfiguration, Daten und Log. Genau so entsteht in Tag 13 der neue Cluster für das Upgrade.

Fortschritt speichern? Kostenlos registrieren

Interview-Notiz

  • „Was ist ein Cluster in PostgreSQL?“ – Eine Server-Instanz: ein Datenverzeichnis, ein Port, mehrere Datenbanken. Auf Debian/Ubuntu <version>/<name>, verwaltet mit pg_lsclusters, pg_ctlcluster, pg_createcluster.
  • „Warum PGDG statt Distributionspaket?“ – Freie Wahl der Major-Version, alle Minor-Releases zeitnah, mehrere Versionen parallel installierbar (Basis für pg_upgrade).
  • „peer vs. scram-sha-256?“ – peer prüft lokal den Linux-Benutzer am Socket; übers Netz meldet man sich per Passwort an, und SCRAM überträgt es nie im Klartext.

Enterprise-Notiz

  • Pakete kommen aus einem internen Spiegel (Artifactory, Nexus, aptly) mit geprüftem Schlüssel; die Major-Version steht im Paketnamen, Minor-Updates laufen geplant.
  • Kein Superuser für Menschen im Alltag: persönliche Rollen mit Gruppenrechten, Anmeldung über LDAP/Kerberos oder Zertifikate, Superuser nur im Notfall.
  • listen_addresses auf konkrete Interfaces, pg_hba.conf pro Anwendung und Netz, dazu eine Firewall – '*' plus offene Regeln ist ein Laborwert.
  • TLS mit Zertifikaten einer internen CA und hostssl statt host; Clients mit sslmode=verify-full.
  • Daten auf eigenem Volume, getrennt von WAL und Logs, mit Monitoring auf Füllstand.