Skip to content
Hands-on IT IT Drills

Day 1: psql Craft

Goal: You move quickly through databases and objects in psql, control output, write small scripts with variables and connect cleanly from pg2 without a password prompt. · Duration: approx. 75 min · Prerequisite: Day 0

Briefing

  • psql speaks 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 COMMAND shows 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 on pg2 (Drill 8 partly on pg1).

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 -E shows the catalog query behind every meta-command. That way you get to know pg_class, pg_namespace and 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
EOF

Expected:

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 tag01

Expected (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
EOF

Expected:

 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 tag01
SELECT id FROM lager.artikel ORDER BY id LIMIT 1;
\e

In 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
EOF

Expected:

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 tag01

Expected (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 tag01
BEGIN;
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 PGPASSWORD

Expected (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 PGPASSWORD

Expected:

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/null

Expected (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

  1. Show the definition of the view lager.teuer as SQL. Target time: 15 s
  2. Show all tables in lager with their size. Target time: 10 s
  3. Run SELECT count(*) FROM pg_stat_activity five times at 2 s intervals. Target time: 30 s
  4. Start psql on pg1 without .psqlrc and 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' ~/.pgpass

Expected: 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.txt

Expected:

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 ~/tag01

If 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?” – ~/.pgpass with mode 600 (or PGPASSFILE), connection profiles in pg_service.conf; PGPASSWORD only 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 matching pg_hba.conf line.
  • “What does psql -X -v ON_ERROR_STOP=1 do?” – Start without the personal .psqlrc and 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-prod instead of IPs.
  • Instead of .pgpass with static passwords: short-lived credentials from Vault or cloud IAM, certificates or Kerberos.
  • application_name in every connection string – in pg_stat_activity and in the log you see immediately which application is causing the load.
  • Color production prompts (%[%033[1;31m%] in PROMPT1) 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.