PostgreSQL Datenbankwartung

Einführung

In diesem Lab lernen Sie wesentliche Aufgaben zur Wartung von PostgreSQL-Datenbanken. Ziel ist es, zu verstehen, wie die Größen von Datenbankobjekten überwacht, routinemäßige Wartungsarbeiten zur Leistungsoptimierung durchgeführt und potenzielle Probleme durch Überprüfung von Verbindungen und Protokollen behoben werden.

Sie beginnen damit, eine Verbindung zu einer PostgreSQL-Datenbank herzustellen und die Größen von Tabellen und Indizes abzufragen. Anschließend lernen Sie, wie Sie ANALYZE ausführen, um Datenbankstatistiken zu aktualisieren, und VACUUM, um Speicherplatz zurückzugewinnen. Abschließend erfahren Sie, wie Sie aktive Client-Verbindungen auflisten und Serverprotokolle auf Fehler überprüfen, um Datenbankprobleme zu diagnostizieren und zu beheben.

Abfragen von Datenbankobjektgrößen

In diesem Schritt verbinden Sie sich mit der PostgreSQL-Datenbank und fragen die Größen einer Tabelle und ihres Indexes ab. Das Verständnis der Größe Ihrer Datenbankobjekte ist entscheidend für die Leistungsoptimierung und Kapazitätsplanung.

Öffnen Sie zunächst ein Terminal. Um sich als Benutzer postgres mit dem PostgreSQL-Server zu verbinden und auf die Datenbank mydatabase zuzugreifen, führen Sie den folgenden Befehl aus:

sudo -u postgres psql mydatabase

Sie sollten die psql-Eingabeaufforderung (mydatabase=#) sehen, die anzeigt, dass Sie mit der Datenbank mydatabase verbunden sind. Alle nachfolgenden SQL-Befehle in diesem Lab sollten in dieser psql-Shell ausgeführt werden, sofern nicht anders angegeben.

Nun ermitteln wir die Größe von mytable. Wir verwenden die Funktionen pg_size_pretty und pg_relation_size. pg_relation_size gibt die Größe einer Tabelle in Bytes zurück, und pg_size_pretty formatiert sie in ein für Menschen lesbares Format (z. B. KB, MB).

Führen Sie die folgende SQL-Abfrage aus, um die Größe von mytable zu erhalten:

SELECT pg_size_pretty(pg_relation_size('mytable'));

Sie sollten eine Ausgabe ähnlich dieser sehen, die den von den Tabellendaten belegten Speicherplatz anzeigt:

 pg_size_pretty
----------------
 56 kB
(1 row)

Überprüfen Sie als Nächstes die Größe des Indexes idx_mytable_name, der auf der Spalte name erstellt wurde:

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

Die Ausgabe zeigt den vom Index belegten Speicherplatz an:

 pg_size_pretty
----------------
 48 kB
(1 row)

Um schließlich die Gesamtgröße der Tabelle einschließlich aller ihrer Indizes zu erhalten, verwenden Sie die Funktion pg_total_relation_size:

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

Diese Ausgabe zeigt die kombinierte Größe der Tabelle und ihres Indexes an:

 pg_size_pretty
----------------
 176 kB
(1 row)

Optimierung mit ANALYZE

In diesem Schritt lernen Sie den Befehl ANALYZE kennen, der für die Aufrechterhaltung einer guten Abfrageleistung unerlässlich ist.

Verständnis von ANALYZE

Der Befehl ANALYZE sammelt Statistiken über den Inhalt von Tabellen in der Datenbank. Der PostgreSQL-Abfrageplaner verwendet diese Statistiken, um die effizientesten Ausführungspläne für Abfragen auszuwählen. Ohne genaue Statistiken kann der Planer schlechte Entscheidungen treffen, was zu einer langsamen Abfrageleistung führt. Es ist eine gute Praxis, ANALYZE regelmäßig auszuführen, insbesondere nach erheblichen Änderungen an Tabellendaten.

Während Sie sich noch in der psql-Shell befinden, führen Sie ANALYZE für die Tabelle mytable aus:

ANALYZE mytable;

Dieser Befehl analysiert mytable und aktualisiert seine Statistiken. Sie sehen die folgende Ausgabe, die bestätigt, dass der Befehl erfolgreich war:

ANALYZE

Obwohl der Befehl einfach ANALYZE zurückgibt, hat er im Hintergrund die internen Statistiken für mytable aktualisiert.

Speicherplatzrückgewinnung mit VACUUM

In diesem Schritt verwenden Sie den Befehl VACUUM, um eine weitere kritische Wartungsaufgabe durchzuführen: die Rückgewinnung von Speicherplatz.

Verständnis von VACUUM

In PostgreSQL wird beim Aktualisieren oder Löschen einer Zeile die alte Version der Zeile (ein "toter Tupel") nicht sofort von der Festplatte entfernt. VACUUM gibt den von diesen toten Tupeln belegten Speicherplatz zurück und macht den Platz für die Wiederverwendung verfügbar. Es aktualisiert auch die Datenvisibilitätsinformationen, was zur Verbesserung der Abfrageleistung beiträgt.

Lassen Sie uns VACUUM für die Tabelle mytable ausführen. Führen Sie in der psql-Shell Folgendes aus:

VACUUM mytable;

Dieser Befehl verarbeitet die Tabelle und gibt eine Bestätigung zurück:

VACUUM

Sie können VACUUM und ANALYZE auch zu einem einzigen, effizienten Befehl kombinieren. Dies ist eine gängige Wartungspraxis.

VACUUM ANALYZE mytable;

Dieser Befehl gibt zuerst Speicherplatz zurück und aktualisiert dann die Statistiken für die Tabelle, wobei nach Abschluss VACUUM zurückgegeben wird.

Hinweis: Es gibt auch den Befehl VACUUM FULL, der aggressiver Speicherplatz zurückgewinnt und die Datei auf der Festplatte verkleinert. Er sperrt jedoch die gesamte Tabelle und verhindert während seines Betriebs jegliche Lese- oder Schreibvorgänge. Daher sollte er mit Vorsicht und nur bei Bedarf verwendet werden.

Überwachung von Verbindungen und Protokollen

In diesem letzten Schritt lernen Sie, wie Sie aktive Datenbankverbindungen überwachen und die Serverprotokolle auf Fehler überprüfen, was Schlüsselkompetenzen für die Fehlerbehebung sind.

Zuerst können Sie, während Sie sich noch in der psql-Shell befinden, die Ansicht pg_stat_activity abfragen, um alle aktiven Verbindungen zum Server anzuzeigen.

Führen Sie die folgende SQL-Abfrage aus:

SELECT datname, usename, client_addr, state FROM pg_stat_activity;

Diese Abfrage zeigt den Datenbanknamen (datname), den Benutzernamen (usename), die IP-Adresse des Clients (client_addr) und den aktuellen Status der Verbindung (state) an. Sie sehen Ihre eigene Verbindung in der Ausgabe, ähnlich wie hier:

  datname  | usename  | client_addr | state
-----------+----------+-------------+--------
 mydatabase| postgres |             | active
 postgres  | postgres |             |
           | labex    |             |
(3 rows)

Als Nächstes überprüfen Sie die Serverprotokolle. Dazu müssen Sie zuerst die psql-Shell verlassen. Geben Sie \q ein und drücken Sie Enter:

\q

Sie befinden sich nun wieder in der Standard-Linux-Terminal-Eingabeaufforderung. PostgreSQL-Protokolle befinden sich im Verzeichnis /var/log/postgresql/. Auf diesem System ist die Protokolldatei postgresql-14-main.log.

Verwenden Sie den Befehl grep, um nach Zeilen zu suchen, die "ERROR" in der Protokolldatei enthalten:

grep ERROR /var/log/postgresql/postgresql-14-main.log

Wenn keine Fehler vorhanden sind, erzeugt dieser Befehl keine Ausgabe. Dies ist das erwartete Ergebnis für dieses Lab, da wir keine Aktionen durchgeführt haben, die einen Fehler verursachen würden. Die Überprüfung von Serverprotokollen ist ein grundlegender Schritt zur Diagnose von Datenbankproblemen.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie wichtige Wartungsaufgaben für PostgreSQL-Datenbanken durchführen. Sie verstehen nun, wie Sie die Größe von Tabellen und Indizes überwachen, ANALYZE ausführen, um die Abfrageleistung zu optimieren, und VACUUM verwenden, um Speicherplatz zurückzugewinnen. Sie haben auch das Auflisten aktiver Client-Verbindungen und das Überprüfen von Serverprotokollen auf Fehler geübt. Diese Fähigkeiten sind entscheidend, um die Gesundheit, Leistung und Zuverlässigkeit Ihrer PostgreSQL-Datenbank sicherzustellen.