Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 1: psql-Handwerk

Ziel: Du bewegst dich in psql schnell durch Datenbanken und Objekte, steuerst Ausgaben, schreibst kleine Skripte mit Variablen und verbindest dich ohne Passwortabfrage sauber von pg2 aus. · Dauer: ca. 75 min · Voraussetzung: Tag 0

Briefing

  • psql kennt zwei Sprachen: SQL (endet mit ;) und Meta-Befehle (beginnen mit \, enden am Zeilenende). Meta-Befehle wertet nur der Client aus.
  • Fast jeder Meta-Befehl ist eine Abfrage auf den Systemkatalog. \? listet alle, \h BEFEHL zeigt die SQL-Syntax.
  • Verbindungsdaten kommen aus vier Quellen: Kommandozeile/Verbindungsstring, Umgebungsvariablen (PGHOST …), ~/.pg_service.conf, ~/.pgpass (nur Passwörter).
  • Drill 1 bis 5 laufen auf pg1, Drill 6 bis 8 auf pg2 (Drill 8 teils auf pg1).

Drill 1: Orientierung mit Meta-Befehlen

Erst eine Übungsdatenbank mit Schema, Tabelle, View und Funktion:

createdb tag01
psql -d tag01 <<'EOF'
CREATE SCHEMA lager;
CREATE TABLE lager.artikel (
  id int PRIMARY KEY, name text NOT NULL, preis numeric(10,2));
INSERT INTO lager.artikel
  SELECT i, 'Artikel ' || i, i * 1.5 FROM generate_series(1, 1000) i;
CREATE VIEW lager.teuer AS SELECT * FROM lager.artikel WHERE preis > 1000;
CREATE FUNCTION lager.brutto(n numeric) RETURNS numeric
  LANGUAGE sql AS 'SELECT n * 1.19';
EOF
psql -d tag01 -c '\l tag01' -c '\dn' -c '\dt+ lager.*'

Erwartet (gekürzt):

 Name  | Owner  | Encoding | Locale Provider | Collate |  Ctype  | ...
-------+--------+----------+-----------------+---------+---------+ ...
 tag01 | ubuntu | UTF8     | libc            | C.UTF-8 | C.UTF-8 | ...

  Name  |       Owner
--------+-------------------
 lager  | ubuntu
 public | pg_database_owner

 Schema |  Name   | Type  | Owner  | Persistence | Access method | Size  | ...
--------+---------+-------+--------+-------------+---------------+-------+ ...
 lager  | artikel | table | ubuntu | permanent   | heap          | 88 kB | ...

Das + hängt Details an (Größe, Beschreibung). Jetzt eine Tabelle im Detail, die Rollen und die Funktionen:

psql -d tag01 -c '\d+ lager.artikel' -c '\du' -c '\df lager.*'

Erwartet (gekürzt):

                         Table "lager.artikel"
 Column |     Type      | Collation | Nullable | Default | Storage  | ...
--------+---------------+-----------+----------+---------+----------+ ...
 id     | integer       |           | not null |         | plain    | ...
 name   | text          |           | not null |         | extended | ...
 preis  | numeric(10,2) |           |          |         | main     | ...
Indexes:
    "artikel_pkey" PRIMARY KEY, btree (id)
Not-null constraints:
    "artikel_id_not_null" NOT NULL "id"
    "artikel_name_not_null" NOT NULL "name"
Access method: heap

 Role name |                         Attributes
-----------+------------------------------------------------------------
 notes_app |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 ubuntu    | Superuser, Create role, Create DB

 Schema |  Name  | Result data type | Argument data types | Type
--------+--------+------------------+---------------------+------
 lager  | brutto | numeric          | n numeric           | func

Neu in PostgreSQL 18: NOT NULL ist ein echter, benannter Constraint und erscheint unter „Not-null constraints“. Muster wie lager.* funktionieren bei allen \d-Befehlen; ohne Schema sucht psql nur im search_path (Tag 2).

Tipp: psql -E zeigt die Katalogabfrage hinter jedem Meta-Befehl. So lernst du nebenbei pg_class, pg_namespace und Co. kennen.

Fortschritt speichern? Kostenlos registrieren

Drill 2: Ausgabe steuern – \x, \timing, \watch

psql -d tag01 <<'EOF'
\x
SELECT * FROM lager.artikel WHERE id = 7;
\x auto
\timing on
SELECT count(*) FROM lager.artikel;
\timing off
EOF

Erwartet:

Expanded display is on.
-[ RECORD 1 ]----
id    | 7
name  | Artikel 7
preis | 10.50

Expanded display is used automatically.
Timing is on.
 count
-------
  1000
(1 row)

Time: 1.186 ms
Timing is off.

\x dreht die Ausgabe: eine Zeile pro Spalte, ideal für breite Tabellen wie pg_stat_activity. \x auto schaltet nur um, wenn die Zeile nicht ins Terminal passt. \timing misst die Zeit inklusive Netzwerk – für Planzeiten nimmst du an Tag 5 EXPLAIN ANALYZE.

\watch wiederholt die letzte Abfrage. Seit PostgreSQL 16 mit Intervall und Anzahl:

psql -d tag01 -c '\? ' | grep -A2 'watch'
echo "SELECT now()::time(0) AS zeit, count(*) FROM pg_stat_activity \watch i=1 c=3" \
  | psql -d tag01

Erwartet (gekürzt):

  \watch [[i=]SEC] [c=N] [m=MIN]
                         execute query every SEC seconds, up to N times,
                         stop if less than MIN rows are returned
Thu Nov 19 04:49:37 2026 (every 1s)

   zeit   | count
----------+-------
 04:49:37 |     9
(1 row)
...
Thu Nov 19 04:49:39 2026 (every 1s)
...

Interaktiv tippst du die Abfrage ohne ; und schließt mit \watch 2 ab; Strg+C beendet. m=MIN stoppt, sobald weniger als MIN Zeilen kommen – praktisch, um auf das Ende einer Sperre oder einer Replikation zu warten.

Fortschritt speichern? Kostenlos registrieren

Drill 3: Dateien und Editor – \i, \o, \e

mkdir -p ~/tag01 && cd ~/tag01
echo "SELECT current_database(), current_user;" > frage.sql
psql -d tag01 -c '\i frage.sql'
psql -d tag01 <<'EOF'
\o artikel.txt
SELECT id, name FROM lager.artikel ORDER BY id LIMIT 3;
\o
\! cat artikel.txt
EOF

Erwartet:

 current_database | current_user
------------------+--------------
 tag01            | ubuntu
(1 row)

 id |   name
----+-----------
  1 | Artikel 1
  2 | Artikel 2
  3 | Artikel 3
(3 rows)

\i führt eine Datei aus (wie psql -f), \o DATEI leitet alle folgenden Ausgaben in die Datei um, \o ohne Argument schaltet zurück. \! startet einen Shell-Befehl, ohne psql zu verlassen.

\e öffnet den Abfragepuffer im Editor aus PSQL_EDITOR, EDITOR oder VISUAL (sonst vi). Nach dem Speichern führt psql den Puffer aus, wenn er mit ; endet. Interaktiv probieren:

psql -d tag01
SELECT id FROM lager.artikel ORDER BY id LIMIT 1;
\e

Im Editor LIMIT 1 in LIMIT 2 ändern, speichern, schließen.

Erwartet: Zuerst eine Zeile mit 1, nach dem Editor zwei Zeilen 1 und 2.

Verwandt: \ef lager.brutto öffnet die Funktion, \ev lager.teuer die View als CREATE OR REPLACE … im Editor. Nur lesen geht mit \sf und \sv.

Fortschritt speichern? Kostenlos registrieren

Drill 4: Variablen und Logik – \gset, \if

\gset statt ; speichert das Ergebnis einer Abfrage in psql-Variablen, benannt nach den Spalten. :name setzt sie wieder ein, \if verzweigt:

psql -d tag01 <<'EOF'
SELECT count(*) AS anzahl, max(preis) AS maxpreis FROM lager.artikel \gset
\echo :anzahl Artikel, teuerster :maxpreis
SELECT :anzahl > 500 AS viele \gset
\if :viele
  \echo 'mehr als 500 Artikel'
\else
  \echo 'wenige Artikel'
\endif
EOF

Erwartet:

1000 Artikel, teuerster 1500.00
mehr als 500 Artikel

\if versteht nur Wahrheitswerte (t/f, true/false, on/off, 1/0) – deshalb rechnet SQL den Vergleich und \gset legt das Ergebnis ab. Variablen von außen kommen mit psql -v name=wert. Mit :'name' setzt psql den Wert als SQL-Literal in Anführungszeichen ein, mit :"name" als Bezeichner – das schützt vor SQL-Injection in Skripten.

Fortschritt speichern? Kostenlos registrieren

Drill 5: Die eigene .psqlrc

psql liest beim Start ~/.psqlrc (und vorher eine systemweite Datei). Eine bewährte Grundausstattung:

cat > ~/.psqlrc <<'EOF'
\set QUIET 1
\pset null '(null)'
\x auto
\set ON_ERROR_ROLLBACK interactive
\set HISTFILE ~/.psql_history- :DBNAME
\set PROMPT1 '%n@%M/%/%R%x%# '
\set wer 'SELECT pid, usename, state FROM pg_stat_activity WHERE usename IS NOT NULL;'
\unset QUIET
EOF
psql -d tag01 -c 'SELECT NULL AS leer'
psql -X -d tag01 -c 'SELECT NULL AS leer'
echo ':wer' | psql -d tag01

Erwartet (gekürzt):

  leer
--------
 (null)
(1 row)

 leer
------

(1 row)

 pid  | usename  | state
------+----------+--------
 4599 | ubuntu   | active
 4162 | postgres |
(2 rows)

:wer ist ein Makro: psql setzt den Text der Variablen ein. Die Zeile darf nicht umbrochen werden, Meta-Befehle enden am Zeilenende. Die zweite Zeile ist ein Hintergrundprozess ohne Abfrage. (null) unterscheidet NULL von einem leeren String, -X startet ohne .psqlrc – das gehört in jedes Skript und jeden Cronjob, damit persönliche Einstellungen keine Ausgaben verändern. Eine eigene History pro Datenbank verhindert, dass ein DELETE aus der Testdatenbank in der Produktion im Pfeil-hoch landet.

Prompt und ON_ERROR_ROLLBACK zeigen sich interaktiv:

psql -d tag01
BEGIN;
SELECT 1/0;
SELECT 2 AS weiter;
COMMIT;

Erwartet (gekürzt):

ubuntu@[local]/tag01=# BEGIN;
ubuntu@[local]/tag01=*# SELECT 1/0;
ERROR:  division by zero
ubuntu@[local]/tag01=*# SELECT 2 AS weiter;
 weiter
--------
      2
ubuntu@[local]/tag01=*# COMMIT;
COMMIT

%x im Prompt zeigt * in einer offenen Transaktion und ! in einer abgebrochenen. Ohne ON_ERROR_ROLLBACK (mit psql -X) wird der Prompt nach dem Fehler zu tag01=!#, jedes weitere Statement scheitert mit current transaction is aborted, und COMMIT meldet ROLLBACK. Mit interactive setzt psql vor jedes Statement heimlich einen Savepoint (Tag 4) – aber nur interaktiv, nicht in Skripten.

Fortschritt speichern? Kostenlos registrieren

Drill 6: Verbindungsstrings und Umgebungsvariablen

Ab hier auf pg2. Dieselbe Verbindung in drei Schreibweisen – Schlüssel/Wert, URI und Umgebungsvariablen:

export PGPASSWORD=lab-notes-123
psql "host=172.16.0.182 dbname=notes user=notes_app" -Atc 'SELECT current_user'
psql "postgresql://[email protected]:5432/notes?application_name=tag01" \
  -c '\conninfo'
export PGHOST=172.16.0.182 PGUSER=notes_app PGDATABASE=notes PGAPPNAME=tag01-env
psql -Atc "SELECT current_user, current_database(), application_name
  FROM pg_stat_activity WHERE pid = pg_backend_pid()"
unset PGHOST PGUSER PGDATABASE PGAPPNAME PGPASSWORD

Erwartet (gekürzt):

notes_app
      Parameter       |         Value
----------------------+------------------------
 Database             | notes
 Client User          | notes_app
 Host                 | 172.16.0.182
 Server Port          | 5432
 Protocol Version     | 3.0
 Password Used        | true
 SSL Connection       | true
 SSL Protocol         | TLSv1.3
 ALPN                 | postgresql
 Superuser            | off
 Hot Standby          | off
notes_app|notes|tag01-env

Seit PostgreSQL 18 zeigt \conninfo eine Tabelle statt eines Satzes. Der URI kennt alle Parameter als Query-String. Zwei nützliche Spielarten:

export PGPASSWORD=lab-notes-123
psql "postgresql://[email protected],172.16.0.182/notes?connect_timeout=2\
&target_session_attrs=read-write" -Atc 'SELECT inet_server_addr()'
psql 'postgresql://[email protected]/notes?options=-c%20search_path%3Dpublic' \
  -Atc 'SHOW search_path'
unset PGPASSWORD

Erwartet:

172.16.0.182
public

Mehrere Hosts probiert libpq der Reihe nach; 172.16.0.99 antwortet nicht, nach 2 s geht es zum nächsten. target_session_attrs=read-write nimmt nur einen Primary – ab Tag 10 der Trick, damit Clients nach einem Failover den neuen Primary finden. options setzt Serverparameter für die Sitzung; Sonderzeichen müssen URL-kodiert sein.

Achtung: PGPASSWORD sieht jeder, der die Umgebung des Prozesses lesen darf (/proc/<pid>/environ, Debug-Ausgaben). Nur für schnelle Tests.

Fortschritt speichern? Kostenlos registrieren

Drill 7: .pgpass und pg_service.conf

Weiter auf pg2. ~/.pgpass hält Passwörter im Format host:port:datenbank:rolle:passwort (* als Platzhalter). libpq prüft die Rechte:

echo '172.16.0.182:5432:notes:notes_app:lab-notes-123' > ~/.pgpass
ls -l ~/.pgpass
psql "postgresql://[email protected]/notes" -Atc 'SELECT current_user' </dev/null
chmod 600 ~/.pgpass
psql "postgresql://[email protected]/notes" -Atc 'SELECT current_user'

Erwartet:

-rw-rw-r-- 1 ubuntu ubuntu 48 Nov 19 04:50 /home/ubuntu/.pgpass
WARNING: password file "/home/ubuntu/.pgpass" has group or world access; permissions
should be u=rw (0600) or less
Password for user notes_app:
psql: error: connection to server at "172.16.0.182", port 5432 failed: fe_sendauth:
no password supplied
notes_app

Mit Gruppen- oder Weltrechten ignoriert libpq die Datei komplett und fragt nach dem Passwort (</dev/null beantwortet die Frage leer). Nach chmod 600 klappt es ohne Rückfrage.

Dazu eine Service-Datei: benannte Verbindungsprofile, damit niemand mehr IPs tippt. Passwörter gehören nicht hinein, die kommen weiter aus .pgpass:

cat > ~/.pg_service.conf <<'EOF'
[notes]
host=172.16.0.182
port=5432
dbname=notes
user=notes_app
sslmode=require
application_name=notes-cli
EOF
psql service=notes -Atc "SELECT current_user, application_name
  FROM pg_stat_activity WHERE pid = pg_backend_pid()"
PGSERVICE=notes psql -Atc 'SELECT inet_server_addr(), inet_client_addr()'
psql "service=notes dbname=postgres" -c 'SELECT 1'
psql service=nix -c 'SELECT 1'

Erwartet:

notes_app|notes-cli
172.16.0.182|172.16.0.123
psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:  no
pg_hba.conf entry for host "172.16.0.123", user "notes_app", database "postgres",
SSL encryption
psql: error: definition of service "nix" not found

Parameter direkt im String schlagen den Service (dbname=postgres). Wegen sslmode=require steht die Ablehnung nur einmal da – kein zweiter Versuch ohne TLS. Systemweit liegt die Datei in /etc/postgresql-common/pg_service.conf (Debian, pg_config --sysconfdir), eine andere wählst du mit PGSERVICEFILE.

Fortschritt speichern? Kostenlos registrieren

Drill 8: Fehlersuche bei Verbindungen

Jede Fehlermeldung sagt, an welcher Stelle es klemmt. Auf pg2:

pg_isready -h 172.16.0.182; echo "rc=$?"
pg_isready -h 172.16.0.182 -p 5433; echo "rc=$?"
psql -h 172.16.0.182 -p 5433 -U notes_app -d notes -c 'SELECT 1'
psql "host=172.16.0.99 dbname=notes user=notes_app connect_timeout=3" -c 'SELECT 1'
PGPASSWORD=falsch psql "service=notes" -c 'SELECT 1'

Erwartet:

172.16.0.182:5432 - accepting connections
rc=0
172.16.0.182:5433 - no response
rc=2
psql: error: connection to server at "172.16.0.182", port 5433 failed: Connection
refused
        Is the server running on that host and accepting TCP/IP connections?
psql: error: connection to server at "172.16.0.99", port 5432 failed: timeout expired
psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:
password authentication failed for user "notes_app"

PGPASSWORD schlägt .pgpass, daher das falsche Passwort. Die genaue Ursache einer Ablehnung verrät der Client nie – das Serverlog schon. Auf pg1:

sudo grep -A1 'password authentication failed' \
  /var/log/postgresql/postgresql-18-main.log | tail -2
psql -d gibtsnicht -c 'SELECT 1'
psql -U niemand -d notes -c 'SELECT 1'
psql -h 127.0.0.1 -d notes -c 'SELECT 1' </dev/null

Erwartet (gekürzt):

... notes_app@notes FATAL:  password authentication failed for user "notes_app"
... notes_app@notes DETAIL:  Connection matched file
"/etc/postgresql/18/main/pg_hba.conf" line 133: "host  notes  notes_app
172.16.0.0/24  scram-sha-256"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: FATAL:  database "gibtsnicht" does not exist
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: FATAL:  Peer authentication failed for user "niemand"
Password for user ubuntu:
psql: error: connection to server at "127.0.0.1", port 5432 failed: fe_sendauth:
no password supplied
Meldung Schicht Prüfen
Connection refused Netz/Port ss -tlnp, listen_addresses, Port
timeout expired Netz Route, Firewall, falsche IP
no pg_hba.conf entry Zugangsregel pg_hba_file_rules, Reload
password/Peer authentication failed Anmeldung Passwort, Methode, Log-DETAIL
database … does not exist Ziel \l, Tippfehler, -d vergessen

Das letzte Beispiel zeigt einen Klassiker: Über 127.0.0.1 gilt nicht peer, sondern scram-sha-256 – und ubuntu hat gar kein Passwort.

Fortschritt speichern? Kostenlos registrieren

Übung: Speed-Drills

  1. Zeige die Definition der View lager.teuer als SQL. Zeitziel: 15 s
  2. Zeige alle Tabellen in lager mit Größe. Zeitziel: 10 s
  3. Lass SELECT count(*) FROM pg_stat_activity fünfmal im Abstand von 2 s laufen. Zeitziel: 30 s
  4. Starte psql auf pg1 ohne .psqlrc und gib nur die Zahl der Artikel aus, ohne Kopfzeile. Zeitziel: 20 s

Lösung

psql -d tag01 -c '\sv lager.teuer'
psql -d tag01 -c '\dt+ lager.*'
echo 'SELECT count(*) FROM pg_stat_activity \watch i=2 c=5' | psql -d tag01
psql -X -d tag01 -Atc 'SELECT count(*) FROM lager.artikel'

Erwartet (gekürzt):

CREATE OR REPLACE VIEW lager.teuer AS
 SELECT id,
    name,
    preis
   FROM lager.artikel
  WHERE preis > 1000::numeric
...
1000

-A schaltet die Ausrichtung ab, -t Kopf- und Fußzeilen – zusammen die Grundlage für jede Weiterverarbeitung in der Shell.

Fortschritt speichern? Kostenlos registrieren

Kontrollpunkt

Auf pg1:

psql -X -d tag01 -Atc 'SELECT count(*) FROM lager.artikel'

Erwartet: 1000

psql -d tag01 -c 'SELECT NULL AS leer'

Erwartet: (null) in der Ergebniszeile – die .psqlrc greift.

Auf pg2:

stat -c '%a %n' ~/.pgpass

Erwartet: 600 /home/ubuntu/.pgpass

psql service=notes -Atc 'SELECT current_user, current_database()'

Erwartet: notes_app|notes ohne Passwortabfrage.

psql service=notes -c '\conninfo' | grep -E 'SSL Connection|Password Used'

Erwartet: Password Used | true und SSL Connection | true.

Challenge

Schreibe auf pg1 ein Prüfskript pruefung.sql, das OK ausgibt, wenn alle Artikel einen Preis haben, sonst eine Warnung mit der Anzahl, und die betroffenen Zeilen in fehlende.txt schreibt. Setze dann bei zwei Artikeln den Preis auf NULL und lass es erneut laufen.

Hinweis: \gset für die Anzahl, eine zweite Abfrage für den Wahrheitswert, dann \if und \o.

Lösung

cd ~/tag01
cat > pruefung.sql <<'EOF'
SELECT count(*) AS ohne_preis FROM lager.artikel WHERE preis IS NULL \gset
SELECT :ohne_preis > 0 AS problem \gset
\if :problem
  \echo 'WARNUNG:' :ohne_preis 'Artikel ohne Preis'
  \o fehlende.txt
  SELECT id, name FROM lager.artikel WHERE preis IS NULL ORDER BY id;
  \o
\else
  \echo 'OK: alle Artikel haben einen Preis'
\endif
EOF
psql -X -v ON_ERROR_STOP=1 -d tag01 -f pruefung.sql
psql -X -d tag01 -c 'UPDATE lager.artikel SET preis = NULL WHERE id IN (3, 5)'
psql -X -v ON_ERROR_STOP=1 -d tag01 -f pruefung.sql
cat fehlende.txt

Erwartet:

OK: alle Artikel haben einen Preis
UPDATE 2
WARNUNG: 2 Artikel ohne Preis
 id |   name
----+-----------
  3 | Artikel 3
  5 | Artikel 5
(2 rows)

-v ON_ERROR_STOP=1 bricht beim ersten SQL-Fehler mit Exit-Code 3 ab – ohne läuft ein Skript nach einem Fehler einfach weiter.

Fortschritt speichern? Kostenlos registrieren

Aufräumen

.pgpass und .pg_service.conf auf pg2 bleiben – Tag 2 nutzt service=notes. Die .psqlrc auf pg1 parkst du: Die Ausgaben im Buch entstehen ohne sie, und ON_ERROR_ROLLBACK würde die Fehlerzustände in Tag 4 verschleiern. Die Übungsdatenbank wird nicht mehr gebraucht:

mv ~/.psqlrc ~/psqlrc.tag01
dropdb tag01
rm -r ~/tag01

Wer sie wieder nutzen will, benennt sie nach dem Buch zurück.

Interview-Notiz

  • „Wie verbindest du dich ohne Passwort im Skript?“ – ~/.pgpass mit Modus 600 (oder PGPASSFILE), Verbindungsprofile in pg_service.conf; PGPASSWORD nur im Test.
  • „Wie findest du heraus, warum eine Anmeldung scheitert?“ – Meldung der Schicht zuordnen (Netz, pg_hba.conf, Authentifizierung), dann das Serverlog: Es nennt die passende pg_hba.conf-Zeile.
  • „Was macht psql -X -v ON_ERROR_STOP=1?“ – Ohne persönliche .psqlrc starten und beim ersten Fehler mit Exit-Code 3 abbrechen; Pflicht für Skripte und Migrationen.

Enterprise-Notiz

  • Service-Dateien werden zentral verteilt (Ansible, Konfigurationsmanagement); Teams sprechen von service=billing-prod statt von IPs.
  • Statt .pgpass mit statischen Passwörtern: kurzlebige Zugangsdaten aus Vault oder dem Cloud-IAM, Zertifikate oder Kerberos.
  • application_name in jedem Verbindungsstring – in pg_stat_activity und im Log siehst du sofort, welche Anwendung die Last macht.
  • Produktions-Prompts färben (%[%033[1;31m%] in PROMPT1), damit niemand die Umgebung verwechselt; History pro Datenbank.
  • Der Zugriff von Menschen läuft über einen Bastion-Host oder ein Zugriffsportal mit Protokollierung, nicht direkt aus dem Büronetz.