psql Tricks

psql ist wohl bei weitem das beliebteste Tool für die Administration von PostgreSQL. Sein großer Vorteil ist, dass es bei der Installation von PostgreSQL bereits enthalten ist. Es ist für Linux-Kenner intuitiv handhabbar und enthält viele aus der Shell bekannte und nützliche Funktionen. 10 hilfreiche Funktionen, mit denen man sich die Administration vereinfachen kann, zeige ich nun hier auf.

Wer sich über Postgresql- und Datenbankthemen austauschen oder beraten möchte, kann gerne direkt
Kontakt mit mir aufnehmen.

>>Zum Kontaktformular

1. Query Tagging

psql unterstützt die Suche mit CTRL+R nach vorher ausgeführten Queries. Dies kann man generell für das schnellere Finden von beliebten Queries nutzen. Einer Query kann man auch einen Kommentar mitgeben. Dieser dient dann als Tag für die erleichterte Suche.

Copy to Clipboard

In der Regel reichen 2 – 3 Buchstaben, um die getaggte Query zu finden.

2. Meta-Befehl Queries anzeigen

Eine ganze Reihe von Meta-Befehlen ermöglicht das komfortable Abfragen von Datenbank- und Objektinformationen. Dabei kann man sich auch anzeigen lassen, welches SQL Statement ein Meta-Befehl für die Abfrage aus den Katalogen nutzt. Diese Funktion wird über den Befehl \set ECHO_HIDDEN on aktiviert.

Copy to Clipboard

postgres=# \set ECHO_HIDDEN ON
postgres=# \l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

3. Query Ausführungszeit

Die Dauer der Ausführungszeit einer Query kann bequem in psql angezeigt werden. Mit dem Befehl \timing lässt sich diese Funktion an- bzw. ausschalten.

Copy to Clipboard

4. Sichtbarkeit von NULL

Standardmäßig ist in psql beim Anzeigen von Feldinhalten kein Unterschied zwischen einem leeren String und NULL. Dies lässt sich mit `\pset null NULLSTRING` ändern.

Copy to Clipboard

5. Automatische Query Wiederholung

Manchmal möchte man eine Query öfters ausführen, um Zustandsänderungen zu verfolgen. psql bietet hierfür den watch-Befehl gefolgt von dem Intervallwert in Sekunden.

Copy to Clipboard

postgres=# SELECT pid, usename, application_name, backend_start, query_start FROM pg_stat_activity;
 pid  | usename  | application_name |         backend_start         |          query_start
------+----------+------------------+-------------------------------+-------------------------------
 1160 |          |                  | 2021-05-03 06:28:53.291358+00 |
 1163 | postgres |                  | 2021-05-03 06:28:53.363345+00 |
 1739 | efm      | efm-4.1          | 2021-05-03 06:40:00.075319+00 | 2021-05-03 06:40:00.087519+00
 1737 | postgres | psql             | 2021-05-03 06:39:59.159283+00 | 2021-05-03 06:40:01.50697+00
 1158 |          |                  | 2021-05-03 06:28:53.293136+00 |
 1157 |          |                  | 2021-05-03 06:28:53.292727+00 |
 1159 |          |                  | 2021-05-03 06:28:53.293496+00 |
(7 rows)

postgres=# \watch 2
                             Mon 03 May 2021 06:40:09 AM UTC (every 2s)

pid  | usename  | application_name |         backend_start         |          query_start
------+----------+------------------+-------------------------------+-------------------------------
 1160 |          |                  | 2021-05-03 06:28:53.291358+00 |
 1163 | postgres |                  | 2021-05-03 06:28:53.363345+00 |
 1739 | efm      | efm-4.1          | 2021-05-03 06:40:00.075319+00 | 2021-05-03 06:40:00.087519+00
 1737 | postgres | psql             | 2021-05-03 06:39:59.159283+00 | 2021-05-03 06:40:09.577924+00
 1158 |          |                  | 2021-05-03 06:28:53.293136+00 |
 1157 |          |                  | 2021-05-03 06:28:53.292727+00 |
 1159 |          |                  | 2021-05-03 06:28:53.293496+00 |
(7 rows)

Mon 03 May 2021 06:40:11 AM UTC (every 2s)

pid  | usename  | application_name |         backend_start         |          query_start
------+----------+------------------+-------------------------------+-------------------------------
 1160 |          |                  | 2021-05-03 06:28:53.291358+00 |
 1163 | postgres |                  | 2021-05-03 06:28:53.363345+00 |
 1748 | efm      | efm-4.1          | 2021-05-03 06:40:10.075638+00 | 2021-05-03 06:40:10.086446+00
 1737 | postgres | psql             | 2021-05-03 06:39:59.159283+00 | 2021-05-03 06:40:11.581457+00
 1158 |          |                  | 2021-05-03 06:28:53.293136+00 |
 1157 |          |                  | 2021-05-03 06:28:53.292727+00 |
 1159 |          |                  | 2021-05-03 06:28:53.293496+00 |
(7 rows)

Die automatische Ausführung lässt sich durch CTRL+C wieder beenden.

6. Unicode Table Style

Die tabellarische Standardanzeige von z.B. Tabellen lässt sich individuell anpassen. Dabei lassen sich sowohl der Linienstil als auch der Rahmenstil anpassen.

Copy to Clipboard

postgres=# \pset linestyle unicode
Line style is unicode.
postgres=# \pset border 2
Border style is 2.
postgres=# SELECT pid, usename, application_name, backend_start, query_start FROM pg_stat_activity;
┌──────┬──────────┬──────────────────┬───────────────────────────────┬───────────────────────────────┐
│ pid  │ usename  │ application_name │         backend_start         │          query_start          │
├──────┼──────────┼──────────────────┼───────────────────────────────┼───────────────────────────────┤
│ 1160 │          │                  │ 2021-05-03 06:28:53.291358+00 │                               │
│ 1163 │ postgres │                  │ 2021-05-03 06:28:53.363345+00 │                               │
│ 1779 │ efm      │ efm-4.1          │ 2021-05-03 06:42:20.075908+00 │ 2021-05-03 06:42:20.085952+00 │
│ 1737 │ postgres │ psql             │ 2021-05-03 06:39:59.159283+00 │ 2021-05-03 06:42:23.123465+00 │
│ 1158 │          │                  │ 2021-05-03 06:28:53.293136+00 │                               │
│ 1157 │          │                  │ 2021-05-03 06:28:53.292727+00 │                               │
│ 1159 │          │                  │ 2021-05-03 06:28:53.293496+00 │                               │
└──────┴──────────┴──────────────────┴───────────────────────────────┴───────────────────────────────┘
(7 rows)

7. Shellbefehle aufrufen

Aus psql heraus können Shell Befehle aufgerufen werden. Ein Beenden und hin und her springen zwischen psql und der Shell ist somit überflüssig. Der Befehl beginnt mit \!.

Copy to Clipboard

8. Integrierte Syntax Referenz

Beim Schreiben von SQL Statements kommt man manchmal in die missliche Lage, dass man die richtige Syntax eines Befehls vergessen hat. Das ist mit psql kein Problem, da über den Befehl \h die jeweilige Syntax direkt angezeigt wird. Zusätzlich ist auch die Referenz URL auf die PostgreSQL Dokumentation beinhaltet, wenn man doch mehr lesen möchte.

Copy to Clipboard

9. Tabelle anzeigen

Normalerweise nutzt man hier das klassische SQL Statement SELECT * FROM tablename;. Wenn man jetzt aber oft in Tabellen reinschaut, ohne Joins und Where-Bedingungen, kommen eine ganze Menge Tastenanschläge zusammen. Kürzer geht es mit dem Befehl table.

Copy to Clipboard

10. .psqlrc

Schlussendlich sind ein Teil der hier gezeigten Tricks nur während der aktuellen psql-Session aktiv. Mit Hilfe der Datei ~/.psqlrc im Homeverzeichnis lassen sie sich aber dauerhaft einstellen, so dass der erneute Konfigurationsaufwand entfällt.

Copy to Clipboard

psql Tricks

1. Query Tagging

2. Meta-Befehl Queries anzeigen

3. Query Ausführungszeit

4. Sichtbarkeit von NULL

5. Automatische Query Wiederholung

6. Unicode Table Style

7. Shellbefehle aufrufen

8. Integrierte Syntax Referenz

9. Tabelle anzeigen

10. .psqlrc

Dirk Aumüller