Archiv der Kategorie: Postgres

Postgresql: Ermitteln Datenbankname aus postgresql-filesystem

Postgres speichert seine Datenbanken in einem „base“-Ordner mit Unterordnern pro Datenbank. Das Problem ist, dass die Unterordner aus einer Zahlenkombination bestehen (welche die oid der Datenbank darstellt). Falls es zu Problemen mit dem Filesystem kommt, kann man mit folgender Abfrage ermitteln, welcher Unterordner zu welcher Datenbank gehört (von postgresql-console psql):

postgres=# SELECT pg_database.datname,pg_database.oid FROM pg_database ORDER BY datname;

Damit erhält man eine Auflistung aller Datenbanknamen und der zugehörigen oid (welche die Zahl des Unterordners ist).

Ist der Unterordner bekannt (Unterordner = oid zB 16429), kann man mit folgender Abfrage die Datenbank ermitteln:

postgres=# SELECT oid,datname from pg_database WHERE oid=16429;

Beispiel: rsync error in /var/lib/postgresql/9.3/main/base/16429/234035538

Dann verbindet man sich mit der betroffenen Datenbank:

postgres=# \c my-database

Um den Tabellennamen bei Postgresql < 9.4 zu ermitteln:

SELECT 
    n.nspname AS tableschema, 
    c.relname AS tablename
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE c.relfilenode = 234035538; -- "subdir name"
 tableschema |          tablename          
-------------+-----------------------------
 public      | customers_bookings_services
(1 row)

Für Postgresql > 9.4:

SELECT pg_filenode_relation(0, 234035538); -- "0" stands for default tablespace

Postgresql: Prüfen, ob Replikation noch läuft 

postgres=# SELECT client_addr, state, sent_location, write_location, replay_location 
           FROM pg_stat_replication; 

Ausgabe:

client_addr |   state   | sent_location | write_location | replay_location ------------+-----------+---------------+----------------+--------------
10.10.11.14 | streaming | 4C91/1700DF30 | 4C91/1700DF30  | 4C91/1700DE60
(1 row)

Siehe auch: https://blog.medianetix.de/2021/02/postgresql-pruefen-ob-replikation-laeuft/

Postgres: Auflisten aller Datenbanken mit deren Größe 

SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Listet alle Datenbanken nach Größe sortiert auf (aus psql).

psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;" -U postgres -W "password" -h localhost -x

Dieser Befehl kann auf der Shell abgesetzt werden.
– c = Query
– U = Username
– W = Password
– h = Host
-x = Ausgabe als „expanded“

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