Tag 1: psql-Handwerk
Ziel: Du bewegst dich in
psqlschnell durch Datenbanken und Objekte, steuerst Ausgaben, schreibst kleine Skripte mit Variablen und verbindest dich ohne Passwortabfrage sauber vonpg2aus. · Dauer: ca. 75 min · Voraussetzung: Tag 0
Briefing
psqlkennt 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 BEFEHLzeigt 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 aufpg2(Drill 8 teils aufpg1).
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 -Ezeigt die Katalogabfrage hinter jedem Meta-Befehl. So lernst du nebenbeipg_class,pg_namespaceund 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
EOFErwartet:
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 tag01Erwartet (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
EOFErwartet:
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 tag01SELECT id FROM lager.artikel ORDER BY id LIMIT 1;
\eIm 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
EOFErwartet:
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 tag01Erwartet (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 tag01BEGIN;
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 PGPASSWORDErwartet (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 PGPASSWORDErwartet:
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:
PGPASSWORDsieht 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/nullErwartet (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
- Zeige die Definition der View
lager.teuerals SQL. Zeitziel: 15 s - Zeige alle Tabellen in
lagermit Größe. Zeitziel: 10 s - Lass
SELECT count(*) FROM pg_stat_activityfünfmal im Abstand von 2 s laufen. Zeitziel: 30 s - Starte
psqlaufpg1ohne.psqlrcund 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' ~/.pgpassErwartet: 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.txtErwartet:
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 ~/tag01Wer sie wieder nutzen will, benennt sie nach dem Buch zurück.
Interview-Notiz
- „Wie verbindest du dich ohne Passwort im Skript?“ –
~/.pgpassmit Modus 600 (oderPGPASSFILE), Verbindungsprofile inpg_service.conf;PGPASSWORDnur 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 passendepg_hba.conf-Zeile. - „Was macht
psql -X -v ON_ERROR_STOP=1?“ – Ohne persönliche.psqlrcstarten 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-prodstatt von IPs. - Statt
.pgpassmit statischen Passwörtern: kurzlebige Zugangsdaten aus Vault oder dem Cloud-IAM, Zertifikate oder Kerberos. application_namein jedem Verbindungsstring – inpg_stat_activityund im Log siehst du sofort, welche Anwendung die Last macht.- Produktions-Prompts färben (
%[%033[1;31m%]inPROMPT1), 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.