PostgreSQL im Betrieb: Konfiguration, Wartung und Replikation

PostgreSQL gilt als die funktionsreichste freie relationale Datenbank. Sie ist für Anwendungen mit hohen Ansprüchen an Datenintegrität, komplexe Abfragen und Erweiterbarkeit die erste Wahl. Dieser Artikel zeigt, was sie auszeichnet und wie man sie im Betrieb führt.

Was PostgreSQL besonders macht

Echte Transaktionssicherheit: Vollständiges MVCC (mehrere Versionen gleichzeitig, Leser blockieren Schreiber nicht). Die Isolationsstufe serializable ist tatsächlich nutzbar, nicht nur nominell vorhanden.
Erweiterbarkeit: Eigene Datentypen, Funktionen und Indexverfahren lassen sich über Erweiterungen nachrüsten – PostGIS für Geodaten, pg_trgm für Textähnlichkeit, TimescaleDB für Zeitreihen.
JSONB: Dokumentendaten mit Indexunterstützung. Damit ist eine relationale Datenbank ausreichend für viele Fälle, in denen sonst eine Dokumentendatenbank nötig wäre.
Volltextsuche: Eingebaut, mit Stemming und Sprachunterstützung – für viele Anwendungen genügt das ohne zusätzliche Suchmaschine.
Fensterfunktionen und CTEs: Sehr mächtige Abfragemittel, konsequent umgesetzt.
Strenge Typisierung: Ein Tippfehler führt zu einem Fehler zur Ausführungszeit statt zu einem stillschweigend falschen Ergebnis.

Verbindung und Grundeinrichtung

# Verbinden
psql -h db.example.org -U app -d appdb
psql "postgresql://app:Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein.:5432/appdb"

# Passwort ohne Klartext in der Prozessliste
export PGPASSWORD='geheim'
psql -h db.example.org -U app -d appdb
# oder besser: ~/.pgpass mit chmod 600
#   db.example.org:5432:appdb:app:*geheim*

# Wichtige Einstellungen in postgresql.conf
#   listen_addresses = 'localhost'         nur lokal (Standard ist sicher!)
#   max_connections = 100                   Verbindungen begrenzen
#   shared_buffers = 25% des RAM           Zwischenspeicher
#   effective_cache_size = 50-75% des RAM  Hinweis fuer den Planer
#   work_mem = 16MB                        je Sortier-/Hashvorgang!
#   maintenance_work_mem = 256MB           fuer VACUUM und Indizes
#   wal_level = replica                    fuer Replikation
#   max_wal_size = 2GB                     haeufige Pruefpunkte vermeiden

# In pg_hba.conf: Wer darf sich wie verbinden
# local   all   postgres            peer
# host    all   app     10.0.0.0/24 md5
# hostnossl all all    0.0.0.0/0    reject

Betrieb im Alltag

# Zustand und Aktivitaet
SELECT version();
SELECT * FROM pg_stat_activity WHERE state != 'idle';
SELECT pid, usename, application_name, state, wait_event_type, wait_event,
       now() - query_start AS laufzeit, left(query, 80)
FROM pg_stat_activity
WHERE state = 'active' ORDER BY laufzeit DESC LIMIT 10;

# Blockierungen finden (Deadlocks und Warteketten)
SELECT blocked.pid AS blockiert,
       blocking.pid AS blockierend,
       left(blocked.query, 60)  AS blockierte_abfrage,
       left(blocking.query, 60) AS blockierende_abfrage
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

# Eine Abfrage abbrechen
SELECT pg_cancel_backend(12345);      # sanft: nur die Abfrage
SELECT pg_terminate_backend(12345);   # hart: die ganze Verbindung

# Wartung
VACUUM (ANALYZE, VERBOSE) tabellenname;
VACUUM (FULL, ANALYZE) tabellenname;   # sperrt die Tabelle! Nur im Wartungsfenster
ANALYZE tabellenname;                  # nur Statistiken
REINDEX INDEX CONCURRENTLY idx_name;   # ohne Sperre (seit 12)
CLUSTER tabellenname USING idx_name;   # physisch neu sortieren

# Groessen und Bloat
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS groesse
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
SELECT pg_size_pretty(pg_database_size('appdb'));

# Aktive Verbindungen je Anwendung
SELECT application_name, count(*) FROM pg_stat_activity
GROUP BY 1 ORDER BY 2 DESC;

# Autovacuum-Beobachtung
SELECT relname, last_vacuum, last_autovacuum, n_dead_tup, n_live_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

Autovacuum ist kein Luxus, sondern notwendig. PostgreSQL speichert bei jedem Update eine neue Zeilenversion; die alte bleibt liegen, bis sie eingesammelt wird. Ohne Autovacuum wächst die Datenbank unbegrenzt und Abfragen werden langsam. Bei Tabellen mit sehr vielen Änderungen muss man die Einstellungen je Tabelle nachschärfen:

ALTER TABLE anfragen SET (
  autovacuum_vacuum_scale_factor = 0.01,     # statt 0.2
  autovacuum_analyze_scale_factor = 0.005
);

JSONB: Dokumente in der relationalen Welt

CREATE TABLE ereignisse (
  id         bigserial PRIMARY KEY,
  zeitpunkt  timestamptz NOT NULL DEFAULT now(),
  typ        text NOT NULL,
  daten      jsonb NOT NULL
);

-- Index auf dem gesamten Dokument
CREATE INDEX idx_ereignisse_daten ON ereignisse USING gin (daten);

-- GIN-Index nur auf bestimmte Pfade (schlanker)
CREATE INDEX idx_ereignisse_host
  ON ereignisse ((daten ->> 'host'));

-- Abfragen
INSERT INTO ereignisse (typ, daten)
VALUES ('login', '{"user":"max","host":"web01","ok":true}'::jsonb);

SELECT * FROM ereignisse WHERE daten ->> 'host' = 'web01';
SELECT * FROM ereignisse WHERE daten @> '{"ok": true}'::jsonb;
SELECT daten ->> 'user' AS benutzer, count(*)
FROM ereignisse GROUP BY 1 ORDER BY 2 DESC;

-- Typischer Fehler: falscher Index fuer den Zugriffspfad
--   GIN (daten)              hilft bei @>, ?, ?|
--   B-Tree ((daten->>'host')) hilft bei Gleichheit auf einem Feld
--   Fuer <, > auf einem Zahlenfeld: (daten->>'x')::int indizieren

Sicherung und Wiederherstellung

# Logischer Dump (portabel, gut fuer einzelne Datenbanken)
pg_dump -Fc -h db -U app appdb > appdb_$(date +%F).dump     # eigenes Format
pg_dump -Fp -h db -U app appdb > appdb.sql                  # reines SQL

# Alles inklusive Rollen und Tablespaces
pg_dumpall -h db -U postgres > alle_$(date +%F).sql

# Wiederherstellen
pg_restore -h db -U app -d appdb_neu -j 4 appdb.dump
pg_restore -l appdb.dump | head -30          # Inhaltsverzeichnis ansehen
pg_restore -h db -U app -d appdb -t server appdb.dump   # nur eine Tabelle

# Wiederherstellung VORHER pruefen - immer in eine Testdatenbank!
createdb -h db -U app appdb_probe
pg_restore -h db -U app -d appdb_probe -j 4 appdb.dump
psql -h db -U app -d appdb_probe -c "SELECT count(*) FROM kunden;"
dropdb -h db -U app appdb_probe

# Physische Sicherung (fuer grosse Datenmengen und Point-in-Time)
pg_basebackup -h db -D /srv/backup/base -Ft -z -P
#   Ermoeglicht Wiederherstellung auf einen beliebigen Zeitpunkt,
#   wenn das WAL-Archiv mitgesichert wird.

Replikation

# Auf dem Primaer: Replikationsbenutzer und WAL-Versand
CREATE ROLE replik LOGIN REPLICATION PASSWORD 'geheim';
# postgresql.conf:
#   wal_level = replica
#   max_wal_senders = 5
#   wal_keep_size = 1GB
# pg_hba.conf:
#   host replication replik 10.0.0.12/32 scram-sha-256

# Auf dem Sekundaer: Basis-Sicherung holen und starten
pg_basebackup -h primary -D /var/lib/postgresql/16/main -U replik -Fp -Xs -P -R
#   -R erzeugt die noetige Konfiguration (standby.signal + primary_conninfo)
systemctl start postgresql

# Zustand pruefen (auf BEIDEN Seiten!)
psql -c "SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication;"
psql -c "SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn();"

# Wie weit hinkt der Sekundaer nach?
psql -c "SELECT now() - pg_last_xact_replay_timestamp() AS rueckstand;"

Sicherheit

Kein direkter Zugriff aus dem Internet. Datenbanken gehören in ein internes Netz. Wenn Zugriff von außen nötig ist, nur über einen festen Tunnel oder einen Verbindungsbroker.
Nicht als Datenbank-Superuser arbeiten. Die Anwendung bekommt einen eigenen Benutzer mit Rechten nur auf ihre Tabellen.
Rechte einzeln vergeben. Statt für alles GRANT ALL zu setzen, nur was gebraucht wird – und bei Tabellen zwischen Lesen und Schreiben unterscheiden.
Row Level Security nutzen, wo mehrere Mandanten dieselbe Tabelle verwenden. Die Durchsetzung erfolgt dann in der Datenbank, nicht nur im Anwendungscode.
Verbindungen verschlüsseln. In pg_hba.conf hostssl statt host verwenden und Zertifikatsprüfung aktivieren.
Protokollieren, was auffällt. log_min_duration_statement = 500 schreibt langsame Abfragen ins Protokoll – die Grundlage jeder Optimierung.

Prüfliste im Betrieb

Läuft Autovacuum und hat es Rückstand? n_dead_tup beobachten.
Gibt es Abfragen, die länger als eine Minute laufen? pg_stat_activity.
Sind Indizes noch vollständig? pg_stat_user_indexes mit idx_scan = 0 zeigt ungenutzte Indizes als Aufräumkandidaten.
Ist die Sicherung innerhalb der letzten 24 Stunden gelaufen – und wurde sie einmal wiederhergestellt?
Läuft die Replikation ohne Rückstand und wie weit darf sie zurückfallen?
Reicht der Speicher? Datenbanken wachsen, und VACUUM braucht Platz für die neue Version.
Hängen noch Verbindungen nicht geschlossener Anwendungen? Ein Verbindungsleck ist ein häufiger Grund für too many connections.