PostgreSQL Datenbankwartung

Einführung

In diesem Lab werden wir uns mit wesentlichen Wartungsaufgaben für die PostgreSQL-Datenbank befassen. Ziel ist es, zu verstehen, wie man die Größen von Datenbankobjekten überwacht, routinemäßige Wartungsarbeiten durchführt und potenzielle Probleme behebt.

Wir beginnen mit dem Abfragen der Größen von Tabellen und Indizes mithilfe der Funktionen pg_size_pretty und pg_relation_size. Anschließend lernen wir, wie man ANALYZE und VACUUM ausführt, um die Abfrageleistung zu optimieren und Speicherplatz freizugeben. Abschließend werden wir behandeln, wie man aktive Client-Verbindungen auflistet und Serverprotokolle auf Fehler überprüft, um Datenbankprobleme zu diagnostizieren und zu beheben.

Skills Graph

Verbindung zu PostgreSQL herstellen und Tabellengröße abfragen

In diesem Schritt stellen wir eine Verbindung zur PostgreSQL-Datenbank her und fragen die Größe einer Tabelle ab. Das Verständnis der Größe Ihrer Datenbankobjekte ist entscheidend für die Leistungsoptimierung (Performance Tuning) und Kapazitätsplanung (Capacity Planning).

Öffnen Sie zunächst ein Terminal in Ihrem Verzeichnis ~/project.

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 eine Eingabeaufforderung wie mydatabase=# sehen. Dies zeigt an, dass Sie mit der Datenbank mydatabase verbunden sind.

Wenn Sie die Datenbank mydatabase und die Tabelle mytable nicht haben, erstellen Sie diese mit den folgenden SQL-Befehlen:

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    data TEXT
);

INSERT INTO mytable (name, data) SELECT 'Name ' || i, 'Data ' || i FROM generate_series(1, 1000) AS i;

Bestimmen wir nun die Größe von mytable. Wir verwenden die Funktionen pg_size_pretty und pg_relation_size. Die Funktion pg_relation_size gibt die Größe einer Tabelle in Byte zurück, und pg_size_pretty formatiert die Größe in ein für Menschen lesbares Format (z. B. KB, MB, GB).

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:

 pg_size_pretty
------------------
 128 kB
(1 row)

Dies zeigt an, dass die Tabelle mytable derzeit 128 KB Speicherplatz belegt.

Indexgröße überprüfen

In diesem Schritt erstellen wir einen Index und fragen dann seine Größe ab. Indizes werden verwendet, um die Abfrageleistung zu beschleunigen, verbrauchen aber auch Speicherplatz. Wir werden auch die Gesamtgröße der Tabelle einschließlich der Indizes überprüfen.

Erstellen wir zunächst einen Index für die Spalte name von mytable:

CREATE INDEX idx_mytable_name ON mytable (name);

Überprüfen wir nun die Größe des Index. Wir können die gleichen Funktionen verwenden, müssen aber den Indexnamen angeben.

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

Die Ausgabe könnte wie folgt aussehen:

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

Dies zeigt, dass der Index idx_mytable_name 48 KB Speicherplatz belegt.

Ermitteln wir abschließend die Gesamtgröße der Tabelle einschließlich der Indizes. Hierfür können wir die Funktion pg_total_relation_size verwenden:

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

Die Ausgabe könnte wie folgt aussehen:

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

Dies zeigt, dass die Gesamtgröße der Tabelle mytable einschließlich aller Indizes 176 KB beträgt.

ANALYZE und VACUUM zur Wartung ausführen

In diesem Schritt lernen wir die Befehle ANALYZE und VACUUM in PostgreSQL kennen, die für die Aufrechterhaltung der Datenbankleistung unerlässlich sind.

ANALYZE aktualisiert die Datenbankstatistiken, die vom Abfrageplaner (Query Planner) verwendet werden, um die effizientesten Ausführungspläne auszuwählen. Diese Statistiken beschreiben den Inhalt der Tabellen in der Datenbank. Ohne genaue Statistiken kann der Abfrageplaner schlechte Entscheidungen treffen, was zu einer langsamen Abfrageleistung führt.

VACUUM gibt Speicherplatz frei, der von toten Tupeln (dead tuples) belegt wird. In PostgreSQL wird die alte Version einer Zeile nicht sofort entfernt, wenn eine Zeile aktualisiert oder gelöscht wird. Stattdessen wird sie als tot markiert. VACUUM gibt den von diesen toten Tupeln belegten Speicherplatz frei und stellt ihn zur Wiederverwendung zur Verfügung. Es aktualisiert auch die Sichtbarkeitskarte (visibility map), die dem Abfrageplaner hilft, zu bestimmen, welche Zeilen für Transaktionen sichtbar sind.

Führen wir ANALYZE für die Tabelle mytable aus:

ANALYZE mytable;

Dieser Befehl analysiert die Tabelle mytable und aktualisiert die Statistiken. Sie sehen keine Ausgabe, aber die Statistiken werden im Hintergrund aktualisiert.

Führen wir als Nächstes VACUUM für die Tabelle mytable aus:

VACUUM mytable;

Dieser Befehl gibt Speicherplatz frei, der von toten Tupeln in der Tabelle mytable belegt wird. Auch hier sehen Sie keine Ausgabe, aber der Vacuum-Prozess wird im Hintergrund ausgeführt.

Für eine aggressivere Bereinigung können Sie VACUUM FULL verwenden. VACUUM FULL sperrt die Tabelle jedoch exklusiv und verhindert, dass während des Vacuum-Prozesses andere Operationen an der Tabelle durchgeführt werden. Es wird im Allgemeinen empfohlen, VACUUM anstelle von VACUUM FULL zu verwenden, es sei denn, Sie haben einen bestimmten Grund, VACUUM FULL zu verwenden.

-- VACUUM FULL mytable; -- Uncomment this line to run VACUUM FULL (use with caution)

Schließlich können Sie ANALYZE und VACUUM zu einem einzigen Befehl kombinieren:

VACUUM ANALYZE mytable;

Dieser Befehl gibt zuerst Speicherplatz frei, der von toten Tupeln belegt wird, und aktualisiert dann die Statistiken. Dies ist oft der effizienteste Weg, um die Datenbankleistung aufrechtzuerhalten.

Aktive Client-Verbindungen auflisten und Serverprotokolle überprüfen

In diesem Schritt werden wir untersuchen, wie man aktive Client-Verbindungen auflistet und Serverprotokolle auf Fehler überprüft.

Um aktive Client-Verbindungen aufzulisten, können Sie die Ansicht pg_stat_activity abfragen. Diese Ansicht bietet Informationen zu jedem Serverprozess, einschließlich des Benutzers, der Datenbank und der ausgeführten Abfrage.

Führen Sie die folgende SQL-Abfrage aus:

SELECT datname, usename, client_addr, state, query FROM pg_stat_activity WHERE state != 'idle';

Diese Abfrage zeigt Ihnen alle aktiven Verbindungen, die sich nicht im Leerlauf (idle) befinden. Die Ausgabe enthält den Datenbanknamen (datname), den Benutzernamen (usename), die Client-Adresse (client_addr), den aktuellen Status (state) und die ausgeführte Abfrage (query).

Als Nächstes überprüfen wir die Serverprotokolle auf Fehler. PostgreSQL-Protokolle befinden sich typischerweise im Verzeichnis /var/log/postgresql. Die Protokolldateinamen folgen normalerweise dem Muster postgresql-VERSION-main.log, wobei VERSION die PostgreSQL-Versionsnummer ist.

Um die Protokolle auf Fehler zu überprüfen, können Sie den Befehl grep verwenden. Um beispielsweise nach Fehlern in der Protokolldatei zu suchen, führen Sie den folgenden Befehl in einem neuen Terminal (außerhalb von psql) aus:

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

Hinweis: Ersetzen Sie postgresql-14-main.log durch den tatsächlichen Namen Ihrer PostgreSQL-Protokolldatei. Sie können die Dateien im Verzeichnis /var/log/postgresql auflisten, um den korrekten Protokolldateinamen zu finden.

Dieser Befehl zeigt alle Zeilen in der Protokolldatei an, die das Wort "ERROR" enthalten. Die Untersuchung der Serverprotokolle ist entscheidend für die Diagnose und Behebung 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 Datenbankobjekten überwachen, ANALYZE und VACUUM ausführen, um die Abfrageleistung zu optimieren und Speicherplatz freizugeben, aktive Client-Verbindungen auflisten und Serverprotokolle auf Fehler überprüfen. Diese Fähigkeiten sind entscheidend, um die Gesundheit und Leistung Ihrer PostgreSQL-Datenbank sicherzustellen.