Day 1: psql Craft
Goal: You move quickly through databases and objects in
psql, control output, write small scripts with variables and connect cleanly frompg2without a password prompt. · Duration: approx. 75 min · Prerequisite: Day 0
Briefing
psqlspeaks two languages: SQL (ends with;) and meta-commands (start with\, end at the end of the line). Only the client evaluates meta-commands.- Almost every meta-command is a query against the system catalog.
\?lists them all,\h COMMANDshows the SQL syntax. - Connection data comes from four sources: command line/connection
string, environment variables (
PGHOST…),~/.pg_service.conf,~/.pgpass(passwords only). - Drills 1 to 5 run on
pg1, Drills 6 to 8 onpg2(Drill 8 partly onpg1).
Drill 1: Find your way with meta-commands
First a practice database with a schema, table, view and function:
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.*'Expected (abridged):
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 | ...
The + adds details (size, description). Now a table in
detail, the roles and the functions:
psql -d tag01 -c '\d+ lager.artikel' -c '\du' -c '\df lager.*'Expected (abridged):
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
New in PostgreSQL 18: NOT NULL is a real, named
constraint and appears under “Not-null constraints”. Patterns like
lager.* work with all \d commands; without a
schema, psql searches only the search_path
(Day 2).
Tip:
psql -Eshows the catalog query behind every meta-command. That way you get to knowpg_class,pg_namespaceand friends along the way.
Save your progress? Sign up for free
Drill 2: Control output – \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
EOFExpected:
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 flips the output: one line per column, ideal for wide
tables such as pg_stat_activity. \x auto
switches only when the row doesn’t fit the terminal.
\timing measures time including the network – for plan
times you use EXPLAIN ANALYZE on Day 5.
\watch repeats the last query. Since PostgreSQL 16 with
interval and count:
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 tag01Expected (abridged):
\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)
...
Interactively, you type the query without ; and finish
with \watch 2; Ctrl+C stops it. m=MIN stops as
soon as fewer than MIN rows come back – handy for waiting for a lock or
a replication to finish.
Save your progress? Sign up for free
Drill 3: Files and 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
EOFExpected:
current_database | current_user
------------------+--------------
tag01 | ubuntu
(1 row)
id | name
----+-----------
1 | Artikel 1
2 | Artikel 2
3 | Artikel 3
(3 rows)
\i runs a file (like psql -f),
\o FILE redirects all following output to the file,
\o without an argument switches back. \! runs
a shell command without leaving psql.
\e opens the query buffer in the editor from
PSQL_EDITOR, EDITOR or VISUAL
(otherwise vi). After saving, psql runs the
buffer if it ends with ;. Try it interactively:
psql -d tag01SELECT id FROM lager.artikel ORDER BY id LIMIT 1;
\eIn the editor, change LIMIT 1 to LIMIT 2,
save, close.
Expected: First one row with 1, after
the editor two rows 1 and 2.
Related: \ef lager.brutto opens the function,
\ev lager.teuer the view as
CREATE OR REPLACE … in the editor. Read-only works with
\sf and \sv.
Save your progress? Sign up for free
Drill 4: Variables and logic – \gset,
\if
\gset instead of ; stores the result of a
query in psql variables, named after the columns. :name
inserts them again, \if branches:
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
EOFExpected:
1000 Artikel, teuerster 1500.00
mehr als 500 Artikel
\if understands only boolean values
(t/f, true/false,
on/off, 1/0) –
that’s why SQL computes the comparison and \gset stores the
result. Variables from outside come in with
psql -v name=value. With :'name',
psql inserts the value as a quoted SQL literal, with
:"name" as an identifier – this protects scripts against
SQL injection.
Save your progress? Sign up for free
Drill 5: Your own .psqlrc
On startup, psql reads ~/.psqlrc (and a
system-wide file before that). A proven basic setup:
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 tag01Expected (abridged):
leer
--------
(null)
(1 row)
leer
------
(1 row)
pid | usename | state
------+----------+--------
4599 | ubuntu | active
4162 | postgres |
(2 rows)
:wer is a macro: psql inserts the text of
the variable. The line must not be wrapped, meta-commands end at the end
of the line. The second row is a background process without a query.
(null) distinguishes NULL from an empty string,
-X starts without .psqlrc – that belongs in
every script and every cron job so personal settings don’t change any
output. A separate history per database prevents a DELETE
from the test database from showing up under arrow-up in production.
Prompt and ON_ERROR_ROLLBACK show up interactively:
psql -d tag01BEGIN;
SELECT 1/0;
SELECT 2 AS weiter;
COMMIT;Expected (abridged):
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 in the prompt shows * in an open
transaction and ! in an aborted one. Without
ON_ERROR_ROLLBACK (with psql -X), the prompt
turns into tag01=!# after the error, every further
statement fails with current transaction is aborted, and
COMMIT reports ROLLBACK. With
interactive, psql quietly sets a savepoint
before every statement (Day 4) – but only interactively, not in
scripts.
Save your progress? Sign up for free
Drill 6: Connection strings and environment variables
From here on, on pg2. The same connection in three notations – key/value, URI and environment variables:
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 PGPASSWORDExpected (abridged):
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
Since PostgreSQL 18, \conninfo shows a table instead of
a sentence. The URI accepts all parameters as a query string. Two useful
variants:
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 PGPASSWORDExpected:
172.16.0.182
public
libpq tries multiple hosts in order; 172.16.0.99 doesn’t
answer, after 2 s it moves on to the next.
target_session_attrs=read-write accepts only a primary –
from Day 10 on, that’s the trick that lets clients find the new primary
after a failover. options sets server parameters for the
session; special characters must be URL-encoded.
Warning: Anyone allowed to read the process environment can see
PGPASSWORD(/proc/<pid>/environ, debug output). Only for quick tests.
Save your progress? Sign up for free
Drill 7: .pgpass and pg_service.conf
Continue on pg2. ~/.pgpass holds
passwords in the format host:port:database:role:password
(* as a wildcard). libpq checks the permissions:
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'Expected:
-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
With group or world permissions, libpq ignores the file completely
and asks for the password (</dev/null answers the prompt
with nothing). After chmod 600 it works without a
prompt.
Next, a service file: named connection profiles so nobody types IPs
anymore. Passwords don’t belong in it, they still come from
.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'Expected:
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
Parameters given directly in the string beat the service
(dbname=postgres). Because of sslmode=require,
the rejection appears only once – no second attempt without TLS. The
system-wide file lives in
/etc/postgresql-common/pg_service.conf (Debian,
pg_config --sysconfdir); you pick a different one with
PGSERVICEFILE.
Save your progress? Sign up for free
Drill 8: Troubleshooting connections
Every error message tells you where it’s stuck. On 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'Expected:
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 beats .pgpass, hence the wrong
password. The client never reveals the exact reason for a rejection –
the server log does. On 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/nullExpected (abridged):
... 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
| Message | Layer | Check |
|---|---|---|
Connection refused |
Network/port | ss -tlnp, listen_addresses, port |
timeout expired |
Network | Route, firewall, wrong IP |
no pg_hba.conf entry |
Access rule | pg_hba_file_rules, reload |
password/Peer authentication failed |
Login | Password, method, log DETAIL |
database … does not exist |
Target | \l, typo, forgot -d |
The last example shows a classic: over 127.0.0.1, it’s
not peer that applies but scram-sha-256 – and
ubuntu has no password at all.
Save your progress? Sign up for free
Exercise: Speed Drills
- Show the definition of the view
lager.teueras SQL. Target time: 15 s - Show all tables in
lagerwith their size. Target time: 10 s - Run
SELECT count(*) FROM pg_stat_activityfive times at 2 s intervals. Target time: 30 s - Start
psqlonpg1without.psqlrcand print only the number of articles, without a header. Target time: 20 s
Solution
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'Expected (abridged):
CREATE OR REPLACE VIEW lager.teuer AS
SELECT id,
name,
preis
FROM lager.artikel
WHERE preis > 1000::numeric
...
1000
-A turns off alignment, -t header and
footer lines – together the basis for any further processing in the
shell.
Save your progress? Sign up for free
Checkpoint
On pg1:
psql -X -d tag01 -Atc 'SELECT count(*) FROM lager.artikel'Expected: 1000
psql -d tag01 -c 'SELECT NULL AS leer'Expected: (null) in the result row –
the .psqlrc takes effect.
On pg2:
stat -c '%a %n' ~/.pgpassExpected: 600 /home/ubuntu/.pgpass
psql service=notes -Atc 'SELECT current_user, current_database()'Expected: notes_app|notes without a
password prompt.
psql service=notes -c '\conninfo' | grep -E 'SSL Connection|Password Used'Expected: Password Used | true and
SSL Connection | true.
Challenge
On pg1, write a check script pruefung.sql
that prints OK if all articles have a price, otherwise a
warning with the count, and writes the affected rows to
fehlende.txt. Then set the price of two articles to NULL
and run it again.
Hint: \gset for the count, a second
query for the boolean value, then \if and
\o.
Solution
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.txtExpected:
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 aborts at the first SQL error with
exit code 3 – without it, a script simply keeps running after an
error.
Save your progress? Sign up for free
Cleanup
.pgpass and .pg_service.conf on
pg2 stay – Day 2 uses service=notes. Park the
.psqlrc on pg1: the outputs in the book are
produced without it, and ON_ERROR_ROLLBACK would obscure
the error states on Day 4. The practice database is no longer
needed:
mv ~/.psqlrc ~/psqlrc.tag01
dropdb tag01
rm -r ~/tag01If you want to use your .psqlrc again, rename it back
after finishing the book.
Interview Note
- “How do you connect without a password in a script?” –
~/.pgpasswith mode 600 (orPGPASSFILE), connection profiles inpg_service.conf;PGPASSWORDonly in tests. - “How do you find out why a login fails?” – Map the message to its
layer (network,
pg_hba.conf, authentication), then check the server log: it names the matchingpg_hba.confline. - “What does
psql -X -v ON_ERROR_STOP=1do?” – Start without the personal.psqlrcand abort at the first error with exit code 3; mandatory for scripts and migrations.
Enterprise Note
- Service files are distributed centrally (Ansible, configuration
management); teams talk about
service=billing-prodinstead of IPs. - Instead of
.pgpasswith static passwords: short-lived credentials from Vault or cloud IAM, certificates or Kerberos. application_namein every connection string – inpg_stat_activityand in the log you see immediately which application is causing the load.- Color production prompts (
%[%033[1;31m%]inPROMPT1) so nobody mixes up the environments; history per database. - Human access goes through a bastion host or an access portal with audit logging, not directly from the office network.