PostgreSQL Wartung automatisieren

PostgreSQL ist eine sehr stabile und leistungsfähige Datenbank. Trotzdem tauchen im Betrieb früher oder später Warnungen auf wie „Table not vacuumed“ oder „Table not analyzed“. Monitoring Systeme wie Checkmk weisen dann darauf hin, dass Tabellen seit Tagen oder Wochen nicht mehr gewartet wurden.

Das ist selten ein akutes Problem, aber ein klares Signal dafür, dass die Wartung nicht regelmässig oder nicht ausreichend durchgeführt wird. In diesem Artikel schauen wir uns an, warum PostgreSQL diese Wartung benötigt und wie man sie mit einem einfachen Script automatisieren kann.

Das Maintenance Script im Überblick

Für die automatisierte Wartung haben wir ein eigenes PostgreSQL Maintenance Script entwickelt. Dieses ist öffentlich verfügbar und kann direkt installiert werden:

curl -fsSL https://git.onesystems.ch/system-tools/postgresql-maintenance-script/-/raw/main/pg-maintenance.sh | sudo bash -s -- install

Das Script wird dabei automatisch unter /opt/postgresql-maintenance installiert und als täglicher Job über /etc/cron.daily eingebunden.

Nach der Installation läuft die Wartung vollständig automatisiert, ohne dass weitere manuelle Eingriffe notwendig sind.

Warum PostgreSQL regelmässige Wartung braucht

PostgreSQL arbeitet intern mit einem Konzept namens MVCC (Multi Version Concurrency Control). Das bedeutet, dass bestehende Daten nicht direkt überschrieben werden. Stattdessen entstehen bei Änderungen neue Versionen eines Datensatzes, während die alten Versionen bestehen bleiben.

Das hat Vorteile für gleichzeitige Zugriffe, führt aber dazu, dass alte Daten nicht automatisch verschwinden. Diese sogenannten „dead tuples“ müssen aktiv entfernt werden.

Ohne Wartung führt das zu:

• wachsenden Tabellen trotz gleichbleibender Datenmenge
• aufgeblähten Indizes
• langsamer werdenden Abfragen

Hier kommt VACUUM ins Spiel. Dieser Prozess entfernt nicht mehr benötigte Daten und sorgt dafür, dass Speicher effizient genutzt wird.

Warum ANALYZE genauso wichtig ist

Neben der Bereinigung braucht PostgreSQL aktuelle Informationen über die Datenverteilung innerhalb von Tabellen. Diese Informationen werden durch ANALYZE gesammelt.

Die Statistiken werden vom Query Planner verwendet, um optimale Ausführungspläne für Abfragen zu erstellen. Sind diese Statistiken veraltet oder fehlen sie, kann PostgreSQL ineffiziente Entscheidungen treffen.

Kurz gesagt:

• VACUUM hält die Datenbank sauber
• ANALYZE sorgt für gute Performance

Typische Symptome in der Praxis

In produktiven Umgebungen zeigt sich das Problem oft durch Monitoring Warnungen:

• Tabellen wurden lange nicht vacuumed
• Tabellen wurden nie analyzed
• einzelne Tabellen fallen immer wieder auf

Gerade bei Anwendungen wie Zammad entstehen viele Tabellen wie zum Beispiel:

• recent_views
• history_objects
• groups_macros
• object_manager_attributes

Diese Tabellen wachsen kontinuierlich und müssen regelmässig gepflegt werden. Das Problem liegt dabei selten an einer einzelnen Tabelle, sondern daran, dass die Wartung insgesamt nicht konsistent durchgeführt wird.

Reicht autovacuum nicht aus

PostgreSQL bringt mit autovacuum bereits eine automatische Wartung mit. In vielen Fällen funktioniert diese gut. In der Praxis gibt es aber Szenarien, in denen autovacuum nicht optimal greift:

• Tabellen ändern sich nur selten und erreichen die Schwellwerte nicht
• Standardwerte sind zu konservativ eingestellt
• Lastsituationen verhindern rechtzeitige Wartung

Das führt dazu, dass Tabellen entweder gar nicht oder erst sehr spät gepflegt werden.

Unser Ansatz: einfache Automatisierung

Statt sich ausschliesslich auf autovacuum zu verlassen, setzen wir auf eine zusätzliche, regelmässige Wartung. Dafür wurde ein einfaches PostgreSQL Maintenance Script entwickelt.

Die Idee ist bewusst simpel: Ein täglicher Lauf stellt sicher, dass alle relevanten Datenbanken gepflegt werden, unabhängig davon, ob autovacuum bereits aktiv war oder nicht.

Wie das Script arbeitet

Das Script wird einmal installiert und anschliessend automatisch über /etc/cron.daily ausgeführt. Der Ablauf ist klar strukturiert:

1. Ermittlung aller Datenbanken über pg_database
2. Ausschluss von Template Datenbanken
3. Optionale Filterung über Include und Exclude Regeln
4. Ausführung des Wartungsbefehls pro Datenbank

VACUUM (VERBOSE, ANALYZE);

Dadurch werden alle Tabellen innerhalb der jeweiligen Datenbank gepflegt, auch solche, die zuvor im Monitoring aufgefallen sind.

Konfiguration über .env

Beim ersten Lauf erstellt das Script automatisch eine .env Datei. Darüber lässt sich das Verhalten flexibel steuern:

• Einzelne Datenbanken ein oder ausschliessen
• VACUUM Optionen anpassen
• Log Aufbewahrung definieren
• Auto Update aktivieren

Das Script bleibt damit einfach, aber ausreichend flexibel für produktive Umgebungen.

Automatische Updates und Betrieb

Ein praktisches Detail ist die integrierte Update Funktion. Wenn aktiviert, prüft das Script bei jedem Lauf, ob eine neue Version verfügbar ist, lädt diese herunter und startet sich neu.

Zusätzlich sorgt das Script für einen stabilen Betrieb durch:

• Lockfile zur Vermeidung paralleler Ausführung
• Logging jedes Durchlaufs
• automatische Bereinigung alter Logdateien
• klare Ausgabe von Erfolg und Fehlern

Warum dieser Ansatz sinnvoll ist

In vielen Umgebungen ist es nicht notwendig, PostgreSQL bis ins Detail zu optimieren. Eine regelmässige, einfache Wartung bringt oft den grössten Effekt.

Das Script ergänzt autovacuum und stellt sicher, dass auch Tabellen gepflegt werden, die sonst nicht rechtzeitig bearbeitet werden.