PostgreSQL Cheat Sheet

27 nützliche Befehle für die Nutzung von PostgreSQL

Nachdem ich einige Zeit nicht mit PostgreSQL gearbeitet habe, sind mir einige Befehle nicht direkt wieder eingefallen. Um nicht immer wieder suchen zu müssen, sondern direkt was mit im Kopf schwebt umsetzen zu können gibt es nun diese Liste mit nützlichen Befehlen.

Der konkrete Auslöser war eine Tabelle mit mehreren Millionen Einträgen. Aufgrund der Einstellungen lief mein SQL nicht zuende und ich brauchte ein paar Ideen, wie ich es optimieren kann, ohne eine erste Ausgabe zu erhalten. Dazu benötigte ich Informationen zur Struktur und dem Aufbau der Datenbank.

Zu PostgreSQL verbinden

In eine PostgreSQL Konsole Einloggen

sudo -u postgres psql

Einloggen und direkt zu einer Datenbank verbinden

sudo -u postgres psql -d <DATENBANK>

---

Arbeiten mit Konsolenbefehlen

Alle Datenbanken anzeigen

postgres=# \l
                                           List of databases
   Name    |  Owner   | Encoding |   Collate   |   Ctype    |            Access privileges
-----------+----------+----------+-------------+------------+------------------------------------------
 newark    | postgres | UTF8     | en_US.UTF-8 | de_DE.utf8 | postgres=CTc/postgres                   +
           |          |          |             |            | =T/postgres                             +
postgres  | postgres | UTF8     | en_US.UTF-8 | de_DE.utf8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | de_DE.utf8 | =c/postgres                             +
           |          |          |             |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | de_DE.utf8 | postgres=CTc/postgres                   +
           |          |          |             |            | =c/postgres
(4 rows)

oder ohne vorherigen Login mit „sudo -u postgres psql -l„

Benutzer auflisten

postgres-# \du
                             List of roles
 Role name |              Attributes                   | Member of
-----------+-------------------------------------------+-----------
postgres   | Superuser                                 | {}
xymon      |                                           | {db_readers}

Zu einer Datenbank verbinden

postgres=#\c Datenbankname

You are now connected to database "<DATENBANK>" as user "<USER>".

Alle Schemas anzeigen

postgres=#\dn

                        List of schemas
             Name              |             Owner
-------------------------------+-------------------------------
       my_service              |       my_service
....
(40 rows)

Alle Tabellen anzeigen

postgres=#\dt 

Did not find any relations.

Mehr Informationen zu den Tabellen anzeigen

\dt+

Funktionen anzeigen

postgres=#\df

                                                                                                                                                                                                                                                                                                                                                           List of functions
Schema | Name | Result data type | Argument data types | Type

Mehr Informationen zu den Funktionen anzeigen

postgres=#\df+

                                                                                                                                                                                                                                                                                                                                                                                                                                            List of functions
 Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges        | Language | Source code | Description

Sequenzen anzeigen

postgres=#\ds

Views anzeigen

postgres=#\dv

               List of relations
 Schema |        Name        | Type |  Owner
--------+--------------------+------+----------
 public | pg_stat_statements | view | postgres
(1 row)

Detaillierte Informationen zu einer Tabelle

\d+ <TABELLEN_NAME>

Ausgabe von Suchanfragen im Pretty-Format

postgres=#\x

Expanded display is on.

Alle verfügbaren Konsolenbefehle anzeigen

postgres=#\?

General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
...

Eine bestimmte Datenbankgröße auslesen

select pg_database_size('<DATENBANK>');

 pg_database_size
------------------
       4434474471
(1 row)

Eine bestimmte Datenbankgröße auslesen formatiert

SELECT pg_size_pretty(pg_database_size('<DATENBANK>'));

 pg_size_pretty
----------------
 4229 MB
(1 row)

Alle vorhanden Datenbankgrößen anzeigen

newark=> select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database;


  datname  |  size
-----------+---------
 postgres  | 7829 kB
 db1       | 4229 MB
 db2       | 7617 kB
 db3.      | 7521 kB
(4 rows)

Offene Datenbankverbindungen anzeigen

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

 datname | usename  | client_addr | client_port
---------+----------+-------------+-------------
 db1     | USER1    |             |          -1
         |          |             |
         |          |             |
         |          |             |
         |          |             |
(5 rows)

Die Konfiguration neu laden (ohne Neustart)

select pg_reload_conf();

Logfile Rotation anwerfen

select pg_rotate_logfile()

PostgreSQL verlassen

postgres=#\q

Tabellen bearbeiten

Datenbank und Benutzer mit Rechten erstellen

CREATE <DATENBANK>;
CREATE USER <BENUTZER> WITH PASSWORD '<PASSWORT>';  
GRANT ALL PRIVILEGES ON DATABASE <DATENBANK> to <BENUTZER>; 

Neue Spalte zu einer Tabelle hinzufügen

ALTER TABLE <TABELLE> ADD COLUMN <SPALTEN_NAME> TYPE;

Spalte aus einer Tabelle entfernen

ALTER TABLE <TABELLE> DROP COLUMN <SPALTEN_NAME>;

Backup und Wiederherstellung

Ein Datenbank Backup erstellen

pg_dump --user <BENUTZER> --host <HOST> -b -F c <DATEBANK> > /tmp/datenbank.dump 

Alle verfügbaren Datenbanken sichern

pg_dumpall

Datenbank wiederherstellen

pg_restore --user <BENUTZER> --host <HOST> -c -d <DATENBANK> datenbank.dump