PostgreSQL-Datenbanken über Bash sichern und wiederherstellen #

Ich bin ein großer Freund von Datenbanken. Warum? Vielleicht weil ich gerne auch abgebe. In diesem Falle gebe ich meine Nutzdaten an ein System ab, für das sich viele hundert schlaue Köpfe unter Einsatz von Koffein die Nächte um die Ohren geschlagen haben.

Den Zynismus bei Seite: Datenbanksysteme (nein, ich meine hier nicht Access!) sind in der Regel verlässliche Datenspeicher mit starker Typisierung und einem gewissen Determinismus - was mich als Safety-Entwickler natürlich fast schon sexuell anzieht. Sei es drum, ich habe irgendwann angefangen sie zu nutzen. Und ich liebe sie, kann ich doch über ein definiertes Interface mit einer definierten Sprache wohldefinierte Dinge tun, für die ich sonst selbst komplizierte Algorithmen zaubern müsste, die natürlich, man mag es mir ob meiner menschlichen Natur nachsehen, nicht immer 100% fehlerfrei sind. Denn Audits bekomme ich privat mancher Tags nur von meiner Partnerin, wenn ich wieder Werkzeug verteilt habe im gemeinsamen Wohnbereich - sie erkennt eben den Projektbezug und die stille Schönheit der männlichen Dekoration nicht immer gleich… Sei es drum. Werkzeug: genau! Das Datenbanksystem ist ein Werkzeug, was irgendwo im Netz (ganz unbemerkt durch kritische Blicke) mit oder ohne Projektbezug parkt und sehnlichst auf Traffic wartet (anm.d.Red.: engl. “Traffic” ist hier weniger verfänglich, als dt. “Verkehr”).

Ich habe schon so manche Daten einfach in meinen guten Freund, die Datenbank, geladen. Selbsterhobene Wetter- und Klimadaten (ungefaked) von drinnen und draussen, Messdaten aller Art bei der Entwicklung, Stromverbrauch zum Analysieren von nutzlosen Kostenbomben, Hausautomation, Logs, Buchhaltung etc. p.p. Er hört mir immer zu und vergisst auch nicht wirklich. So hoffe ich.

Wirklich? Und wenn doch einmal? Dann ist es aus…!

Entgegen dem m.E. kranken Trend übersexualisierte Selbstdarstellungen mit Knutschmund und cooler Pose zu sammeln, die dann das Smartphone und das Servernetz der Big Four und damit die Welt mit digitalem Abfall belasten, sammel ich lieber Daten, die mir etwas bringen. Und nein, ich definiere mich nicht über meine Daten, wie eben genannte Menschengruppe mit stark adiktivem Verhalten, und sie machen mich auch nicht aus. Aber wenn jetzt alles weg wäre? Das wäre, gerade was die Buchhaltung angeht, schon etwas mehr als nur ärgerlich.

Aber wie war das doch gleich?

No Backup, no Mitleid!

Genug der Prosa. Gehen wir es also an! Ich will in diesem Artikel auf PostgreSQL eingehen.

Backup erstellen mit pg_dump #

Lokale Datenbank sichern #

pg_dump -U USER -d DATENBANK > backup.sql

Remote-Backup (z. B. Server im Netzwerk) #

pg_dump -h HOST -U USER -d DATENBANK > backup.sql

Beispiel:

pg_dump -h dataserver.itc-embedded.de -U tkaufmann -d projekt_db > projekt_db_backup.sql

Mit Komprimierung (lokal gespeichert) #

pg_dump -h HOST -U USER -d DATENBANK | gzip > backup.sql.gz

Alle Datenbanken sichern (nur mit postgres-Superuser möglich) #

pg_dumpall -U postgres > all_databases_backup.sql

Backup wiederherstellen mit psql #

Standard-Wiederherstellung #

psql -h HOST -U USER -d DATENBANK < backup.sql

Wiederherstellung aus .gz-Archiv #

gunzip -c backup.sql.gz | psql -h HOST -U USER -d DATENBANK

Oder:

zcat backup.sql.gz | psql -h HOST -U USER -d DATENBANK

Neue Datenbank vorher anlegen (optional) #

createdb -h HOST -U USER -E UTF8 -T template0 projekt_db

Bash-Skript zur automatischen Wiederherstellung #

#!/bin/bash

HOST="dataserver.itc-embedded.de"
USER="tkaufmann"
DB="projekt_db"
BACKUP="$1"

if [ -z "$BACKUP" ]; then
  echo "Bitte Backup-Datei angeben!"
  exit 1
fi

read -p "Datenbank '$DB' neu anlegen? (j/N): " ans
if [[ "$ans" =~ ^[Jj]$ ]]; then
  dropdb -h "$HOST" -U "$USER" "$DB"
  createdb -h "$HOST" -U "$USER" -E UTF8 -T template0 "$DB"
fi

echo "Wiederherstellung läuft..."
if [[ "$BACKUP" == *.gz ]]; then
  gunzip -c "$BACKUP" | psql -h "$HOST" -U "$USER" -d "$DB"
else
  psql -h "$HOST" -U "$USER" -d "$DB" < "$BACKUP"
fi

echo "Fertig."

Hinweise #

• Standardport ist 5432
• Authentifizierung kann über .pgpass automatisiert werden
• Für vollständige Sicherungen (inkl. User etc.) pg_dumpall verwenden
• pg_restore ist nur nötig bei binären Dumps (-Fc)

Tipp: Nutze cron oder systemd timer, um regelmäßig automatisierte Backups auszuführen.

PostgreSQL-Datenbankschemen sichern - OHNE Daten! #

Komplettes Schema (ohne Daten) exportieren #

Das Standard-Werkzeug ist auch hier pg_dump.

pg_dump -U deinuser -h localhost -d deine_db \
  --schema-only \
  --no-owner \
  --no-privileges \
  > schema.sql

Das exportiert:

• Tabellen
• Views
• Materialized Views
• Indizes
• Constraints
• Trigger
• Funktionen
• Sequences
• Extensions

Also alles, was strukturell relevant ist.

Das ist genau das, was man braucht, wenn man z.B. ein Datenbanksystem aufbaut oder vorliegen hat, es vielleicht aber an anderer Stelle wieder nutzen möchte oder Mirgrationsfähigkeit herstellen möcht. In meinem Fall ist es das Datenbanksystem, was ich für meine geschäftlichen Prozesse aufgebaut habe (Faktura, Buchhaltung etc.).

Nur bestimmtes Schema exportieren (z. B. public) #

Falls du mehrere Schemas nutzt:

pg_dump -U deinuser -d deine_db \
  --schema=public \
  --schema-only \
  > schema_public.sql

Nur Funktionen + Trigger separat #

Wenn du gezielt nur Logik sichern willst:

pg_dump -U deinuser -d deine_db \
  --schema-only \
  --section=pre-data \
  --section=post-data \
  > structure.sql

Trigger stehen im post-data Abschnitt.

Schöne, diff-fähige Dumps erzeugen #

Wenn du das versionieren willst (wozu ich dringend rate):

pg_dump -U deinuser -d deine_db \
  --schema-only \
  --no-owner \
  --no-privileges \
  --quote-all-identifiers \
  --no-comments \
  > schema.sql

Dann bekommst du:

• stabile Reihenfolge
• weniger Noise bei Diffs
• saubere Git-Vergleiche

Trigger prüfen (nur zur Sicherheit) #

Falls du misstrauisch bist, ob alles geklappt hat:

SELECT event_object_table, trigger_name
FROM information_schema.triggers
ORDER BY event_object_table;

Oder:

\dS+ tabellenname

Bonus: Komplettbackup (inkl. Daten) #

Wenn du ganz ruhig schlafen willst (Kurzform, siehe oben in ausführlich):

pg_dump -U deinuser -d deine_db -Fc > full.dump

Wiederherstellung:

pg_restore -U deinuser -d neue_db full.dump

Das ist die sichere Variante für produktive Systeme.