Technologie-Ratgeber

PostgreSQL im Unternehmen: Datenbankdesign, Effizienz und bewährtes Backup

Eine gute Datenbank setzt Geschäftsregeln durch und lässt sich nach einem Ausfall wiederherstellen. Beginnen Sie bei PostgreSQL mit einem konsistenten Datenmodell, realen Abfragen sowie klaren Anforderungen an den tolerierbaren Datenverlust und die Wiederherstellungsdauer.

Etwa 4 Min. LesezeitZu den Übungen

PostgreSQL planen: Regeln auch in der Datenbank durchsetzen

Trennen Sie Kunden, Bestellungen und Bestellpositionen. Legen Sie Schlüssel, Beziehungen und Pflichtfelder fest. Ein Formular prüft Daten für die Benutzerfreundlichkeit, doch auch Importe und API-Integrationen schreiben Datensätze. Datenbank-Constraints müssen die Regeln schützen, die für alle Zugriffswege gelten.

Ein Fremdschlüssel verhindert Verweise auf nicht vorhandene Datensätze, UNIQUE sichert Eindeutigkeit und CHECK prüft eine Bedingung für die Zeile. CHECK allein schließt NULL nicht aus. Für eine verpflichtend angegebene Menge werden deshalb beide Constraints benötigt. Das folgende kleine Modell einer Bestellposition dient zum Üben in einer Testdatenbank.

sql
CREATE TABLE order_items (
  order_id bigint NOT NULL,
  line_no integer NOT NULL CHECK (line_no > 0),
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, line_no)
);

Wählen Sie PostgreSQL-Indizes basierend auf tatsächlichen Abfragen aus

Listen Sie häufige Vorgänge auf: letzte Bestellungen eines Kunden, offene Serviceanfragen und Produktsuche. Nutzen Sie Daten mit repräsentativem Umfang und repräsentativer Verteilung. Ein schnelles Ergebnis bei zwanzig Datensätzen sagt wenig über das Verhalten nach einem Jahr Betrieb aus.

EXPLAIN zeigt den Ausführungsplan. EXPLAIN ANALYZE führt die Abfrage zusätzlich aus und liefert Messwerte. Deshalb darf ANALYZE nicht unbedacht für datenverändernde Vorgänge eingesetzt werden. Prüfen Sie die Zahl der gelesenen Zeilen, Sortierungen und Abweichungen zwischen Schätzung und tatsächlicher Ausführung. Auch ein zusätzlicher Index benötigt Speicherplatz und muss bei Schreibvorgängen gepflegt werden.

PostgreSQL-Backup: pg_dump und Wiederherstellung testen

pg_dump erstellt ein logisches Backup einer einzelnen Datenbank, das zum Beginn des Exports konsistent ist. Das Custom-Format lässt sich mit pg_restore verarbeiten. Die Dump-Datei allein enthält nicht alle Informationen zu Rollen, benötigten Erweiterungen, Anwendungskonfiguration und außerhalb der Datenbank gespeicherten Dateien. Berücksichtigen Sie diese Bestandteile im Wiederherstellungsplan.

Die folgenden Befehle setzen konfigurierte libpq-Verbindungen mit den Namen firma_prod und firma_test voraus. Prüfen Sie zuerst die zugehörigen Hosts: Die Wiederherstellung darf ausschließlich auf einem separaten Testserver erfolgen. Erforderliche Rollen und Erweiterungen müssen vorbereitet sein. Die Zieldatenbank firma_restore_test darf noch nicht existieren.

bash
pg_dump --dbname='service=firma_prod' --format=custom --file=firma.dump
createdb --maintenance-db='service=firma_test dbname=postgres' --template=template0 firma_restore_test
pg_restore --dbname='service=firma_test dbname=firma_restore_test' --exit-on-error firma.dump

Wann ist eine zeitpunktgenaue Wiederherstellung mit PITR erforderlich?

Legen Sie zwei Werte fest: den tolerierbaren Datenverlust und die maximal zulässige Ausfallzeit. Ein tägliches Backup kann für ein System, das jede Minute Bestellungen annimmt, unzureichend sein. Wird eine Wiederherstellung bis zu einem bestimmten Zeitpunkt benötigt, planen Sie ein Basis-Backup mit kontinuierlicher WAL-Archivierung.

PITR erfordert eine vollständige Folge der benötigten WAL-Dateien. Es entsteht nicht durch die Kombination eines gewöhnlichen pg_dump-Exports mit späteren Protokollen. Überwachen Sie erfolgreiche Archivierung, freien Speicher und Aufbewahrungsfristen, damit das Löschen alter Dateien den zugesicherten Wiederherstellungszeitraum nicht unterbricht.

Wie kann ich überprüfen, ob die PostgreSQL-Sicherung tatsächlich funktioniert?

Stellen Sie das Backup in einer isolierten Umgebung wieder her, starten Sie die Anwendung und führen Sie einen grundlegenden Geschäftsprozess aus. Vergleichen Sie die Anzahl wichtiger Datensätze, Beziehungen und ausgewählte Datenprüfsummen. Messen Sie die tatsächliche Dauer vom Beginn der Wiederherstellung bis zur Betriebsbereitschaft.

Bewahren Sie ein Backup auch außerhalb des Datenbankservers auf, mit Zugriffskontrolle und einem dem Inhalt angemessenen Schutz. Lösen Sie einen Alarm aus, wenn kein neues Backup vorliegt, nicht nur bei gemeldeten Programmfehlern. Der Plan muss eine verantwortliche Person und regelmäßige Wiederherstellungstests vorsehen.

Die Empfehlungen praktisch anwenden

Übungen und Checkliste

Testen Sie Ihre Entscheidungen anhand eines Beispiels und gehen Sie dann die Aufgaben in Ihrer eigenen Testumgebung durch.

Wie groß ist das Zeitfenster für einen möglichen Datenverlust?

Bis zu 1.440 Minuten zwischen Sicherungen

Dieses Intervall überschreitet den tolerierbaren Datenverlust. Erwägen Sie häufigere Backups oder ein Basis-Backup mit WAL-Archivierung für PITR.

Das vereinfachte Beispiel setzt erfolgreiche Backups voraus. Es erfasst weder die Wiederherstellungsdauer noch Verzögerungen bei der Archivierung oder fehlgeschlagene Backups.

Aufgabenliste

Erledigte Schritte: 0 von 4

Testen Sie Ihr Wissen

1. Das Feld quantity muss immer einen Wert enthalten, der größer als null ist. Welche Constraints erfüllen diese Anforderung?
2. Was bildet die Grundlage für die zeitpunktgenaue Wiederherstellung von PostgreSQL, also PITR?

Weiterlernen

Daten und Integrationen · Schritt 1 von 3

Als Nächstes: Organisieren Sie die Integrationslogik

Implementierung und Wartung · Schritt 2 von 3

Als Nächstes: Bereiten Sie den Server vor und aktualisieren Sie ihn