Tag 0: Trainingsgelände
Ziel: PostgreSQL 18 aus dem PGDG-Repository läuft auf
pg1undpg2,pg1hat die Datenbanknotesmit 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 $KErwartet (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-commonund dannsudo /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 --versionErwartet (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-contribinstallieren. Das Ubuntu-Metapaket zieht PostgreSQL 16 nach (apt-cache policy postgresql-contribzeigt16+257build1.1). Die contrib-Erweiterungen wiepg_stat_statementsstecken seit PostgreSQL 10 schon inpostgresql-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;'
psqlErwartet (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 PGPASSWORDErwartet (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_ed25519Erwartet (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_keysTest 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
- Zeige die Version des laufenden Servers per SQL. Zeitziel: 15 s
- Zeige alle Cluster dieser VM mit Port und Status. Zeitziel: 5 s
- Zeige, woher der aktuelle Wert von
listen_addresseskommt. Zeitziel: 30 s - 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_lsclustersErwartet: 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 5432Erwartet: 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] hostnameErwartet: 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_lsclustersErwartet (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 mitpg_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?“ –
peerprü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_addressesauf konkrete Interfaces,pg_hba.confpro Anwendung und Netz, dazu eine Firewall –'*'plus offene Regeln ist ein Laborwert.- TLS mit Zertifikaten einer internen CA und
hostsslstatthost; Clients mitsslmode=verify-full. - Daten auf eigenem Volume, getrennt von WAL und Logs, mit Monitoring auf Füllstand.