Fehlerbehebung bei hoher WAL-Aktivität und Verwaltung des Speicherplatzes für Archivprotokolle

Hohe Write-Ahead-Log (WAL)-Aktivität in PostgreSQL ist nicht automatisch ein Problem. Eine ausgelastete Datenbank sollte WAL generieren. Das Problem tritt auf, wenn die WAL-Rate überrascht, wenn archivierte WAL nie bereinigt wird oder wenn pg_wal wächst, weil etwas PostgreSQL daran hindert, alte Segmente zu recyceln.

Der schnellste Weg, einen WAL-Vorfall zu verschlimmern, ist das manuelle Löschen von Dateien aus pg_wal. Tun Sie das nicht. Behandeln Sie eine volle WAL-Festplatte als Wiederherstellungssituation: Identifizieren Sie, was WAL zurückhält, schaffen Sie Spielraum, wenn dies sicher möglich ist, und beheben Sie dann das fehlgeschlagene Archiv, den nachhängenden Standby oder den aufgegebenen Slot, der das Wachstum verursacht hat.

Grundlegendes zur Write-Ahead-Protokollierung (WAL)

PostgreSQL schreibt Änderungsdatensätze in das WAL, bevor die zugehörigen Datenseiten sicher geschrieben werden. Nach einem Absturz spielt PostgreSQL das WAL erneut ab, sodass festgeschriebene Änderungen nicht verloren gehen. Derselbe Stream wird auch für die Streaming-Replikation und die Point-in-Time-Wiederherstellung verwendet.

WAL-Dateien werden in Segmentdateien fester Größe gespeichert. Viele Installationen verwenden 16-MB-Segmente, da dies der übliche Standard ist, aber die Größe wird bei der Initialisierung des Clusters gewählt. Eine schreibintensive Arbeitslast kann schnell eine große Anzahl von Segmenten erzeugen. Alte Segmente werden nur dann recycelt oder entfernt, wenn PostgreSQL sie nicht mehr für die Crash-Wiederherstellung, Checkpoints, Archivierung, Replikation oder Slots benötigt.

Wichtige WAL-Konzepte:

Dauerhaftigkeit: Festgeschriebene Transaktionen können nach einem Absturz wiederhergestellt werden.
Replikation: Standbys empfangen WAL-Datensätze vom Primärserver.
Point-in-Time-Wiederherstellung (PITR): Basis-Backups plus archivierte WAL ermöglichen die Wiederherstellung zu einem bestimmten Zeitpunkt innerhalb Ihres Aufbewahrungszeitraums.
WAL-Segmente: WAL wird in Segmentdateien unter pg_wal gespeichert.

Häufige Ursachen für hohe WAL-Aktivität

Mehrere Faktoren können zu einem ungewöhnlich hohen Volumen der WAL-Generierung beitragen. Die Identifizierung der Grundursache ist der erste Schritt zur effektiven Fehlerbehebung.

1. Massenladen und -änderungen von Daten

Operationen wie INSERT, UPDATE, DELETE, TRUNCATE und COPY können erhebliche Mengen an WAL erzeugen. Massenvorgänge, insbesondere bei großen Tabellen, erzeugen naturgemäß mehr WAL-Datensätze als kleine, einzelne Transaktionen.

Beispiel: Ein einzelner COPY FROM-Befehl zum Einfügen von Millionen von Zeilen kann Gigabyte an WAL-Daten erzeugen.
Beispiel: Ausführen einer groß angelegten Datenmigration oder eines Batch-Update-Skripts.

2. Replikationsverzögerung und Standby-Probleme

Wenn Ihre Standby-Server nicht mit dem Primärserver mithalten (Replikationsverzögerung), sammeln sich WAL-Dateien auf dem Primärserver an. Der Primärserver kann abgeschlossene WAL-Segmente erst entfernen, wenn bestätigt wurde, dass sie an alle verbundenen Standbys gesendet und von diesen verarbeitet wurden (wenn wal_keep_size oder max_slot_wal_keep_size nicht konfiguriert ist oder Slots nicht korrekt verwendet werden).

Szenario: Ein Standby-Server ist ausgefallen, getrennt oder hat Leistungsprobleme, sodass er keine WAL-Datensätze vom Primärserver verbrauchen kann.

3. Vollseitenschreibvorgänge nach Checkpoints

Nach einem Checkpoint kann die erste Änderung an einer Datenseite ein vollständiges Seitenabbild protokollieren, wenn full_page_writes aktiviert ist. Diese Einstellung schützt die Wiederherstellung vor zerrissenen Seiten und ist normalerweise aktiviert. Wenn Checkpoints zu häufig auftreten, können vollständige Seitenabbilder das WAL-Volumen merklich erhöhen. Die Lösung besteht normalerweise darin, das Checkpoint-Verhalten zu optimieren, nicht die Dauerhaftigkeitsschutzmechanismen zu deaktivieren.

4. Unkontrolliertes Wachstum des `pg_wal`-Verzeichnisses

Wenn die WAL-Archivierung aktiviert ist und fehlschlägt, behält PostgreSQL WAL-Segmente, die noch archiviert werden müssen. Wenn die Archivierung nicht aktiviert ist, sollte pg_wal alte Segmente dennoch recyceln, wenn sie nicht mehr benötigt werden, es sei denn, Replikation, Slots oder Checkpoint-Druck halten sie zurück.

5. Nicht zurückgeforderte Replikationsslots

Replikationsslots garantieren, dass WAL-Segmente nicht entfernt werden, bevor sie von einem bestimmten Standby oder logischen Decodierungsclient verbraucht wurden. Wenn ein Slot erstellt wird, der Consumer jedoch stoppt oder die Verbindung trennt, ohne dass der Slot gelöscht wird, werden die von diesem Slot benötigten WAL-Segmente aufbewahrt, selbst wenn der Standby nicht mehr aktiv ist.

Verwalten des WAL-Festplattenspeichers: Konfiguration und Lösungen

Die Behebung hoher WAL-Aktivität erfordert einen mehrgleisigen Ansatz, der Überwachung, Konfigurationsoptimierung und ordnungsgemäße Wartungsverfahren umfasst.

1. Aktivieren und Überwachen der WAL-Archivierung

Die WAL-Archivierung ist der wichtigste Mechanismus zur Verwaltung des Speicherplatzes und zur Ermöglichung von PITR. Wenn die Archivierung aktiviert ist, werden abgeschlossene WAL-Dateien an einen separaten Speicherort kopiert (z. B. eine Netzwerkfreigabe, einen S3-Bucket oder eine andere Festplatte).

Konfiguration:

Ändern Sie Ihre postgresql.conf-Datei:

wal_level = replica         # Oder logical für logische Replikation
archive_mode = on           # Archivierung aktivieren
archive_command = 'cp %p /path/to/archive/%f'

# Beispiel für S3 mit wal-g oder einem ähnlichen Tool:
# archive_command = 'wal-g wal-push %p'

%p: Platzhalter für den vollständigen Pfad zur zu archivierenden WAL-Datei.
%f: Platzhalter für den Dateinamen der WAL-Datei.

Wichtig: Der archive_command muss erfolgreich ausgeführt werden können. Wenn ein Exit-Code ungleich Null zurückgegeben wird, betrachtet PostgreSQL die Archivierung als fehlgeschlagen, was dazu führen kann, dass WAL-Dateien nicht entfernt werden. Stellen Sie sicher, dass das Zielverzeichnis über ausreichend Speicherplatz verfügt und der Benutzer, unter dem PostgreSQL ausgeführt wird, Schreibberechtigungen hat.

Überwachung der Archivierung

Verwenden Sie SQL-Abfragen, um den Status der Archivierung zu überprüfen:

SELECT archived_count,
       failed_count,
       last_archived_wal,
       last_archived_time,
       last_failed_wal,
       last_failed_time
FROM pg_stat_archiver;

Wenn failed_count ständig steigt oder last_archived_time alt ist, während die Datenbank noch schreibt, beheben Sie das Archivziel, bevor Sie die WAL-Größenparameter optimieren.

2. Verwalten der Größe des `pg_wal`-Verzeichnisses

Selbst bei aktivierter Archivierung kann das pg_wal-Verzeichnis auf dem Primärserver wachsen, wenn WAL-Segmente nach der Archivierung nicht entfernt werden. Dies geschieht, wenn:

Standbys nicht mithalten und der Primärserver zusätzliche WAL für die Replikation aufbewahrt.
Replikationsslots WAL-Dateien festhalten.

`wal_keep_size`

Dieser Parameter behält zusätzliche WAL auf dem Primärserver für die Streaming-Replikation. Er ersetzt die ältere Einstellung wal_keep_segments in PostgreSQL 13. Er ist nützlich für Standbys, die keine Slots verwenden, aber er ist keine Garantie dafür, dass ein stark nachhängender Standby immer aufholen kann.

# postgresql.conf auf dem Primärserver
wal_keep_size = 1024 # 1 GB WAL auf der Festplatte behalten

Replikationsslots werden oft bevorzugt, wenn der Primärserver WAL für einen bestimmten Consumer aufbewahren muss, aber Slots müssen überwacht werden, da sie WAL auf unbestimmte Zeit aufbewahren können.

`max_slot_wal_keep_size` (PostgreSQL 13+)

Diese Einstellung begrenzt, wie viel WAL ein Replikationsslot aufbewahren kann. Sie ist eine Schutzmaßnahme gegen unbegrenztes Wachstum durch einen defekten Slot, kann aber auch dazu führen, dass ein nachhängender Consumer die benötigte WAL verliert und eine Neuinitialisierung erforderlich ist.

# postgresql.conf auf dem Primärserver
max_slot_wal_keep_size = 2048 # Slots begrenzen, um 2 GB WAL aufzubewahren

# Auch zu beachten: wal_keep_size -- immer noch relevant für nicht-slotbasiertes Streaming
# wal_keep_size = 1024 # 1 GB für Nicht-Slot-Streaming behalten

Wenn ein Slot zu weit zurückfällt und dieses Limit überschreitet, kann PostgreSQL zum Checkpoint-Zeitpunkt benötigte WAL entfernen. Dies schützt den Speicherplatz, aber der betroffene Standby oder logische Replikationsclient kann möglicherweise nicht mehr von seiner alten Position aus fortfahren.

Replikationsslots

Replikationsslots sind entscheidend, um WAL-Verlust zu verhindern und eine zuverlässige Replikation zu gewährleisten. Sie können jedoch dazu führen, dass sich WAL-Dateien ansammeln, wenn sie nicht korrekt verwaltet werden.

Problem: Ein Replikationsslot wird erstellt, aber der Consumer (Standby oder logischer Client) trennt die Verbindung oder fällt aus, und der Slot wird nie gelöscht. Der Primärserver behält alle WAL-Dateien, auf die der Slot wartet.
Lösung: Überwachen Sie regelmäßig die Replikationsslots und löschen Sie alle, die nicht mehr verwendet werden.

-- Replikationsslots auflisten
SELECT slot_name,
       plugin,
       slot_type,
       active,
       restart_lsn,
       wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

-- Einen ungenutzten Slot löschen
SELECT pg_drop_replication_slot('slot_name_to_drop');

Warnung: Das Löschen eines Replikationsslots führt dazu, dass jeder verbundene Consumer seine Position verliert. Stellen Sie sicher, dass der Consumer nicht mehr benötigt wird oder ordnungsgemäß neu initialisiert wurde, bevor Sie ihn löschen.

3. Optimieren von `min_wal_size` und `max_wal_size`

Diese Parameter beeinflussen die Checkpoint-Häufigkeit und wie viel WAL PostgreSQL für die Wiederverwendung aufbewahren möchte. Sie begrenzen nicht die für Archivierung oder Replikation aufbewahrte WAL.

min_wal_size: Ermutigt PostgreSQL, mindestens so viel WAL für die Wiederverwendung aufzubewahren, anstatt es sofort zu entfernen.
max_wal_size: Das WAL-Volumen, das tendenziell einen Checkpoint auslöst. Es ist kein hartes Maximum, wenn WAL aus anderen Gründen aufbewahrt wird.

# postgresql.conf
min_wal_size = 1GB
max_wal_size = 4GB

Eine Erhöhung von max_wal_size kann dem System bei Spitzenlasten mehr Spielraum geben, bedeutet aber auch, dass mehr Speicherplatz durch vorab zugewiesene WAL-Dateien belegt wird.

4. Regelmäßige Bereinigung archivierter WAL-Dateien

Die WAL-Archivierung, die für die Wiederherstellung unerlässlich ist, kann auch zu Speicherplatzproblemen führen, wenn die archivierten Dateien nie bereinigt werden. Sie müssen eine Strategie für die Verwaltung der Aufbewahrung Ihrer archivierten WAL-Dateien haben.

Strategie: Implementieren Sie ein Skript oder verwenden Sie ein spezielles Tool (wie pg_archivecleanup, pgBackRest, wal-g, barman), um alte WAL-Dateien aus dem Archivspeicherort zu entfernen, sobald sie nicht mehr für PITR oder Replikation benötigt werden.
Verwendung von pg_archivecleanup: Dieses Dienstprogramm kann auf dem Primärserver ausgeführt werden, um alte WAL-Dateien aus dem Archivverzeichnis zu entfernen.
```
pg_archivecleanup /path/to/archive/location 0000000100000037000000AF
```
Das zweite Argument ist der Name der ältesten WAL-Datei, die noch aufbewahrt werden muss, nicht ein beliebiges Alter. In der Praxis sind Backup-Tools wie pgBackRest, Barman und WAL-G sicherer, da sie die Backup-Aufbewahrung und die für die Wiederherstellung erforderliche WAL verstehen.

Wichtig: Stellen Sie immer sicher, dass Ihre Bereinigungsstrategie mit Ihren Backup- und Wiederherstellungsanforderungen für die Point-in-Time-Wiederherstellung (PITR) übereinstimmt. Sie müssen WAL-Dateien lange genug aufbewahren, um Ihr gewünschtes Wiederherstellungsfenster abzudecken.

5. Überwachung des Speicherplatzes und der WAL-Generierungsrate

Proaktive Überwachung ist der Schlüssel zur Vermeidung von Speicherplatzerschöpfung.

Speicherplatz überwachen: Verfolgen Sie den freien Speicherplatz im Datenverzeichnis, in pg_wal, in temporären Dateispeicherorten und in Archivzielen.
WAL-Generierung überwachen: Verwenden Sie LSN-Differenzen im Zeitverlauf, um die Generierungsrate zu schätzen.
```
SELECT now() AS sample_time,
       pg_current_wal_lsn() AS current_lsn;
```
Speichern Sie diesen Wert regelmäßig und vergleichen Sie Stichproben mit pg_wal_lsn_diff(new_lsn, old_lsn). Für eine schnelle Ansicht der aktuellen Größe des pg_wal-Verzeichnisses:
```
SELECT pg_size_pretty(sum(size)) AS pg_wal_size
FROM pg_ls_waldir();
```

Schritte zur Fehlerbehebung bei vollen Festplatten

Wenn Ihre Festplatte aufgrund von WAL-Aktivität bereits voll ist, sind sofortige Maßnahmen erforderlich:

Ursache identifizieren: Überprüfen Sie pg_stat_archiver auf Archivierungsfehler. Untersuchen Sie pg_replication_slots auf ungenutzte oder problematische Slots. Überprüfen Sie die Replikationsverzögerung auf Standbys.
Speicherplatz freigeben, ohne die Wiederherstellung zu beeinträchtigen:
- Löschen Sie keine Dateien manuell aus pg_wal.
- Wenn das Archivziel voll ist, entfernen Sie alte archivierte WAL nur, wenn sie außerhalb Ihres Backup-Aufbewahrungszeitraums liegt.
- Fügen Sie nach Möglichkeit Speicherplatz hinzu oder verschieben Sie das Archivziel, und lassen Sie PostgreSQL dann normal archivieren und recyceln.
Grundursache beheben:
- Archivierung reparieren: Stellen Sie sicher, dass archive_command korrekt ist und das Ziel Speicherplatz hat.
- Slots verwalten: Löschen Sie alle ungenutzten Replikationsslots.
- Replikation reparieren: Beheben Sie Probleme, die Standby-Verzögerungen verursachen.
- Speicherplatz erhöhen: Fügen Sie vorübergehend oder dauerhaft mehr Speicher hinzu.
Archivierer anstoßen: Nachdem Sie den Archivierungsbefehl oder das Ziel repariert haben, sollte PostgreSQL es erneut versuchen. Ein Neuladen kann für Konfigurationsänderungen ausreichen; ein vollständiger Neustart sollte während eines Festplattenvorfalls das letzte Mittel sein.

Ein sichereres mentales Modell

Wenn pg_wal wächst, stellen Sie drei Fragen in der richtigen Reihenfolge:

Generiert PostgreSQL mehr WAL als üblich, weil sich die Arbeitslast geändert hat?
Kann PostgreSQL WAL nicht archivieren?
Wird PostgreSQL angewiesen, WAL für die Replikation oder einen Slot aufzubewahren?

Diese Antworten führen zu unterschiedlichen Lösungen. Massenschreibvorgänge erfordern möglicherweise Planung, Batch-Verarbeitung oder Checkpoint-Optimierung. Archivierungsfehler erfordern Speicher- und Befehlskorrekturen. Slot-Aufbewahrung erfordert Consumer-Wiederherstellung, Slot-Bereinigung oder ein Aufbewahrungslimit. Das Raten bei max_wal_size löst das eigentliche Problem selten von allein.