PostgreSQL Client-Programme Schulung: psql, pg_dump, pg_restore

PostgreSQL-Werkzeuge einrichten

• Warum die Kommandozeile: wiederholbar, skriptbar, auch über SSH verfügbar
• Die Client-Programme im Überblick: psql, pg_dump, pg_restore, pg_isready und Co.
• Client-Programme unter Windows: EDB-Installer nur mit Kommandozeilenwerkzeugen
• Client-Programme unter macOS: Homebrew-Paket libpq oder Postgres.app
• Linux: Paket postgresql-client aus den PGDG-Paketquellen in passender Version
• Programmpfad und Umgebungsvariablen unter Windows dauerhaft setzen
• PATH unter macOS in .zprofile erweitern, mehrere Versionen parallel nutzen
• Übungsserver als Docker-Container starten und Client-Version zum Server passend wählen

Das interaktive Terminal psql

• Verbinden mit Host, Port, Benutzer und Datenbank oder als Verbindungs-URI
• Datenbank anlegen mit CREATE DATABASE oder dem Hilfsprogramm createdb
• SQL-Befehle mehrzeilig ausführen, Abfragepuffer mit \e im Editor bearbeiten
• Aufbau der Backslash-Befehle: Muster, + für Details, S für Systemobjekte
• Hilfe zu SQL-Befehlen mit \h und zu Meta-Befehlen mit \?
• Skripte ausführen mit \i, \ir und psql -f, Fehler mit ON_ERROR_STOP abbrechen
• Datenbankinhalte auflisten: \l, \dn, \dt, \dv, \df und \du
• Objekte beschreiben mit \d+: Spalten, Indizes, Constraints, Trigger
• Verbindung und Server abfragen: \conninfo, \encoding, Systemkataloge
• Erweiterte Anzeige mit \x auto, Laufzeiten mit \timing, Abfragen wiederholen mit \watch

psql anpassen: Zugangsdaten, Prompt und .psqlrc

• Passwörter aus der Datei .pgpass statt PGPASSWORD im Skript
• Verbindungsprofile in pg_service.conf und Aufruf mit service=
• Umgebungsvariablen PGHOST, PGUSER, PGDATABASE und ihr Vorrang
• Prompt mit PROMPT1 gestalten: Datenbank, Benutzer, Transaktionsstatus, Farben
• Eigene .psqlrc: Standardeinstellungen, \pset null, Verlauf je Datenbank
• Kurzbefehle als Variablen hinterlegen und mit :name aufrufen

Ein- und Ausgabe auf der Kommandozeile

• Abfragen direkt übergeben mit psql -c und Ergebnisse unformatiert mit -At ausgeben
• Variablen von außen setzen mit -v und in SQL als :'name' sicher einsetzen
• Ausgerichtete Textausgabe steuern: \pset border, linestyle, format wrapped
• Ergebnisse in Dateien umleiten mit \o und \g datei
• Tabellen als HTML, LaTeX oder AsciiDoc ausgeben mit \pset format
• CSV-Ausgabe mit --csv bzw. \pset format csv für Excel und Skripte
• Datentabelle exportieren und importieren mit \copy – Unterschied zu serverseitigem COPY
• Kreuztabellen mit \crosstabview direkt im Terminal
• Ergebnisse weiterverwenden: \gset in Variablen, \gexec für erzeugtes SQL
• Bedingte Skriptlogik mit \if, \elif, \else und \endif

Sichern mit pg_dump und pg_dumpall

• Logische Sicherung erstellen: was pg_dump sichert und was nicht
• Ausgabeformate wählen: Nur-Text, Custom (-Fc), Verzeichnis (-Fd), Tar
• Sicherungsoptionen: Kompression, --no-owner, --no-privileges, --clean --if-exists
• Nur Schema mit --schema-only, nur Daten mit --data-only
• Einzelne Tabellen und Schemas sichern mit -t, -n und Ausschlüssen per -T
• Parallele Sicherung großer Datenbanken im Verzeichnisformat mit -j
• Rollen und Tablespaces mit pg_dumpall --globals-only mitsichern
• Physische Kopie mit pg_basebackup im Überblick, Abgrenzung zum logischen Dump

Wiederherstellen mit psql und pg_restore

• Nur-Text-Sicherungen mit psql -f und --single-transaction einspielen
• Archive mit pg_restore in eine neue oder bestehende Datenbank laden
• Archivinhalt mit pg_restore -l ansehen und als Inhaltsliste bearbeiten
• Partielle Wiederherstellung: einzelne Tabellen, nur Daten, mit -L gefilterter Liste
• Parallel wiederherstellen mit -j, Fehlerverhalten mit --exit-on-error
• Sicherung als SQL-Datei aus einem Archiv erzeugen mit pg_restore -f

Client-Programme in Skripten und Automatisierung

• Erreichbarkeit prüfen mit pg_isready und Exit-Codes auswerten
• Shell-Skripte mit set -euo pipefail, Service-Namen und Protokolldatei
• Zeitgesteuerte Sicherungen mit cron oder systemd-Timer, Aufbewahrung und Rotation
• Restore-Test als Skript: Archiv in Wegwerf-Datenbank zurückspielen, Zeilen zählen
• Hilfsprogramme vacuumdb, reindexdb und clusterdb im Wartungsskript

PostgreSQL-Client-Programme mit Coding-Agenten

• Agent verbindet sich nur über einen Service-Eintrag mit Leserolle, nie mit Superuser-Zugang
• psql -X -v ON_ERROR_STOP=1 in generierten Skripten, damit Fehler als Exit-Code ankommen
• Agent liest \d+- und pg_restore -l-Ausgaben, statt Tabellen und Abhängigkeiten zu raten
• Vorgeschlagene pg_dump-Optionen gegen --help der installierten Version prüfen lassen
• Sicherungsskript gilt erst als fertig, wenn der automatische Restore-Test grün ist

Praxisprojekt: Sicherungs- und Exportwerkzeug für eine Vereinsdatenbank

• Shell-Skript sichert nächtlich Datenbank und Rollen im Custom-Format mit Zeitstempel
• Monatsexport der Mitgliederstatistik per psql --csv und Variablen für den Stichtag
• Restore-Test holt eine einzelne Tabelle aus dem Archiv und vergleicht die Zeilenzahlen

Lernziele

• Sie richten die Client-Programme auf Windows, macOS oder Linux ein und verbinden sich ohne Passwort im Skript
• Sie erkunden Datenbanken mit psql-Meta-Befehlen und steuern Ausgabeformate für Terminal, CSV und HTML
• Sie schreiben psql-Skripte mit Variablen, Bedingungen und sauberem Fehlerabbruch
• Sie sichern Datenbanken mit pg_dump und pg_dumpall in passenden Formaten
• Sie stellen ganze Datenbanken oder einzelne Tabellen mit pg_restore gezielt wieder her

Zielgruppe

• Datenbank- und Systemadministrator:innen, die PostgreSQL betreiben
• DevOps-Teams, die Sicherungen und Wartung automatisieren
• Datenanalyst:innen, die Abfragen und Exporte auf der Kommandozeile erledigen