Archiv der Kategorie: Postgres

Postgresql: Grafische Ausgabe von Explain Analyze

Wenn eine Datenbankabfrage zu lange dauert, kann man sich in Postgres die Informationen vom Query-Planer anzeigen lassen. Dies geschieht, indem der Abfrage ein EXPLAIN oder EXPLAIN ANALYZE vorangestellt wird. Leider ist diese Ausgabe sehr unübersichtlich.

EXPLAIN zeigt an, wie der Query-Planer die Abfrage auszuführen gedenkt.
EXPLAIN ANALYZE führt zusätzlich die Abfrage aus und verändert gegebenenfalls auch Daten (DELETE, INSERT, UPDATE). Um das zu verhindern kann die EXPLAIN ANALYZE-Abfrage als Transaktion ausgeführt und zurückgedreht werden:

BEGIN; –Transaktionsbeginn
EXPLAIN ANALYZE DELETE FROM payments WHERE id > 100;
ROLLBACK; — zurückdrehen der Änderungen

Dankbarerweise gibt es ein paar Seiten im Netz, die die unübersichtliche Ausgabe grafisch aufbereiten können.

http://tatiyants.com/pev/ benötigt die Ausgabe im JSON-Format, was wie folgt erreicht werden kann:
a) EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
b) psql mydatabse -qAt -f explain.sql > analyze.json

mydatabase ist der Name der DB und explain.sql ist ein Textfile mit dem EXPLAIN-Ausdruck aus a) und dem SQL, was ausgewertet werden soll.

Eine Vorgängerseite, die aber auch mit der RAW-Ausgabe von Postgres umgehen kann:
https://explain.depesz.com/

Postgres: Alter table SET WITHOUT OIDS für alle Datenbanken und Tabellen

Spätestens bei Upgrade auf die Version 12 gibt es Probleme mit den OIDS in Postgresql.

Dieses seit 2005 als „deprecated“ gekennzeichnete Feature wird ab Version 12 nicht mehr unterstützt und führt zu Fehlern (vorher: Warnungen).

Wenn man sich sicher ist, dass OIDs in Datentabellen nicht mehr gebraucht werden, dann hilft ein kleines Script, daß die Arbeit ohne großen Aufwand übernimmt.

https://pastebin.com/3JZLM5YT

Hier werden erst alle Datenbanken aus der pg_database-Tabelle (DB: postgres) ausgelesen und dann über jede Datenbank die Tabellen iteriert, um die OIDS abzustellen.

Aufruf des Scriptes als User „postgres“:

sudo su postgres

Postgresql: Prüfen, ob Replikation läuft

Als postgres-User in der psql-Console folgende Befehle eingeben:

SELECT pg_is_in_recovery();
-> muss „true“ sein

SELECT pg_last_xlog_receive_location();
-> zeigt letztes erhaltenes xlog file an

SELECT pg_last_xlog_replay_location();
-> zeigt letztes eingespielte xlog file an

SELECT pg_last_xact_replay_timestamp();
-> zeigt Timestamp der letzten Einspielung an, sollte etwa die aktuelle Uhrzeit sein

Postgresql: Datenbank mit aktiven Connections löschen

Ein „DROP DATABASE mydb“ wird nicht funktionieren, solange es aktive Verbindungen gibt (pgbouncer, phpmyadmin, etc.). Manchmal können nicht alle Verbindungen identifiziert und geschlossen werden.

Dann hilft folgende SQL-Zeile (mydb durch eigenen Datenbanknamen ersetzen):

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'mydb' AND pid <> pg_backend_pid();

Stackoverflow

Postgres: Zeilen einer Tabelle als Eingabe (INSERT) für ähnliche Tabelle verwenden (mit CTE)

Tabelle 1 (table1):
Hat die Spalten id, name, language, type

Tabelle 2 (table2):
Hat die Spalten id, name, language, type_id (identisch mit Tabelle 1 „type“)

Alle Zeilen mit dem Wert „fr“ als language sollen von der ersten Tabelle in die zweite Tabelle übernommen werden – mit einem SQL.

SET search_path TO 'public';

WITH 
    addressor_types as (SELECT type as type_id, name, language FROM table1 WHERE language='fr')

INSERT INTO table2 (type_id, name, language) (SELECT type_id, name, language FROM addressor_types);

SET search_path TO … = Setzen des Namensraumes, in dem Postgres sucht (kann weggelassen werden, wenn search_path=public)

WITH … = Common Table Expression (CTE), um die zutreffenden Zeilen der ersten Tabelle zwischenzuspeichern für den INSERT. Die Reihenfolge/Namen der Spalten beim INSERT muss/müssen übereinstimmen.

INSERT … = Der Befehl zum Einfügen der zuvor zwischengespeicherten Daten.