Skip to content
Hands-on IT IT Drills

Day 0: Training Ground

Goal: PostgreSQL 18 from the PGDG repository runs on pg1 and pg2, pg1 has the database notes with test data and accepts connections from the lab network. · Duration: approx. 60 min · Prerequisite: two VMs with Ubuntu 24.04

Briefing

Ubuntu 24.04 ships PostgreSQL 16. We use the project’s official repository (PGDG, apt.postgresql.org): it delivers every supported major version with all minor updates. pg1 becomes the primary, pg2 becomes the backup target from Day 9 and the standby from Day 10. Today pg2 is mainly a client.

  pg1 .182 (argo)                         pg2 .123 (prod)
  PostgreSQL 18, cluster 18/main          PostgreSQL 18, cluster 18/main
  database notes, 10,000 rows             psql as client  ---- 5432/tcp ---->  pg1
  4 vCPU / 8 GB                           2 vCPU / 4 GB
        |_______________________________________|
              172.16.0.0/24 · SSH postgres@pg1 <-> postgres@pg2

Replace the IPs with your own. There is no name resolution between the VMs, which is why IPs appear everywhere. Drills 1 and 2 run on pg1 and pg2, Drills 3 to 6 on pg1.

Drill 1: Add the PGDG repository

Package sources in this book query the architecture (dpkg --print-architecture). On a Mac with Apple Silicon (for example VMs via multipass) that is arm64 – PGDG also builds arm64 packages for Ubuntu’s LTS releases, the commands stay the same, only the outputs in the book show amd64.

First fetch the signing key and check its fingerprint:

sudo apt-get update
sudo apt-get install -y curl ca-certificates gpg
sudo install -d /usr/share/postgresql-common/pgdg
K=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
sudo curl -fsSL -o $K https://www.postgresql.org/media/keys/ACCC4CF8.asc
gpg --show-key $K

Expected (abridged):

pub   rsa4096 2011-10-13 [SC]
      B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8
uid                      PostgreSQL Debian Repository

The fingerprint must match the one on postgresql.org/download/linux/ubuntu. Then add the source in deb822 format. The shell fills in codename and architecture:

cat <<EOF | sudo tee /etc/apt/sources.list.d/pgdg.sources
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: $(lsb_release -cs)-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: $K
EOF
sudo apt-get update
apt-cache policy postgresql-18 postgresql-19 | grep -E '^post|Candidate'

Expected:

Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: noble-pgdg
Architectures: amd64
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
postgresql-18:
  Candidate: 18.7-1.pgdg24.04+2
postgresql-19:
  Candidate: (none)

main contains only released versions. Pre-releases (beta, release candidate) live in separate components such as 19 – nothing for a server you want to run.

Tip: The documentation also mentions a ready-made script: sudo apt install -y postgresql-common and then sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh. It does the same as the commands above – here you see what happens.

Save your progress? Sign up for free

Drill 2: Install PostgreSQL 18

Continue on both VMs:

sudo apt-get install -y postgresql-18 postgresql-client-18
pg_lsclusters
systemctl list-units 'postgresql*' --no-legend
psql --version

Expected (abridged):

Creating new PostgreSQL cluster 18/main ...
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main --auth-local peer \
  --auth-host scram-sha-256 --no-instructions
...
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...
  postgresql.service         loaded active exited  PostgreSQL RDBMS
  [email protected] loaded active running PostgreSQL Cluster 18-main
psql (PostgreSQL) 18.7 (Ubuntu 18.7-1.pgdg24.04+2)

The package creates a cluster right away: version 18, name main, port 5432. Debian/Ubuntu separate configuration (/etc/postgresql/18/main) and data (/var/lib/postgresql/18/main). Each cluster is its own systemd unit postgresql@<version>-<name>; postgresql.service is just the master switch.

Warning: Do not install postgresql-contrib. The Ubuntu metapackage pulls in PostgreSQL 16 (apt-cache policy postgresql-contrib shows 16+257build1.1). The contrib extensions such as pg_stat_statements have been part of postgresql-18 since PostgreSQL 10.

sudo -u postgres psql -c "SELECT name, default_version FROM pg_available_extensions
  WHERE name IN ('pg_stat_statements', 'amcheck', 'pgcrypto') ORDER BY name;"

Expected:

        name        | default_version
--------------------+-----------------
 amcheck            | 1.5
 pg_stat_statements | 1.12
 pgcrypto           | 1.4
(3 rows)

Save your progress? Sign up for free

Drill 3: First login as postgres

From here on, on pg1. After installation there is exactly one role: postgres. Locally, peer applies: PostgreSQL trusts the operating system about who is knocking on the Unix socket. Whoever is Linux user postgres is also database role postgres – without a password.

sudo -u postgres psql -Atc 'SELECT version()'
sudo -u postgres psql -Atc 'SHOW data_directory' -c 'SHOW config_file' \
  -c 'SHOW hba_file' -c 'SHOW data_checksums' -c 'SHOW server_encoding'
sudo -u postgres psql -c 'SELECT datname FROM pg_database;'
psql

Expected (abridged):

PostgreSQL 18.7 (Ubuntu 18.7-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by ...
/var/lib/postgresql/18/main
/etc/postgresql/18/main/postgresql.conf
/etc/postgresql/18/main/pg_hba.conf
on
UTF8
  datname
-----------
 postgres
 template1
 template0
(3 rows)
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: FATAL:  role "ubuntu" does not exist

data_checksums is on by default starting with PostgreSQL 18: every data page carries a checksum, so broken blocks are detected on read instead of silently returning wrong data. The last psql fails because it logs you in as role ubuntu – which does not exist yet.

Save your progress? Sign up for free

Drill 4: Your own role and databases

In the lab, ubuntu gets superuser rights so you don’t have to type sudo -u postgres every time. psql without -d connects to the database named like the role – that is why the bare createdb creates the database ubuntu:

sudo -u postgres createuser --superuser ubuntu
createdb
createdb notes
psql -c "SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolname !~ '^pg_';"
psql -d notes -c '\conninfo' | grep -E 'Database|Client User|Socket|Password|SSL Conn'

Expected:

 rolname  | rolsuper | rolcanlogin
----------+----------+-------------
 postgres | t        | t
 ubuntu   | t        | t
(2 rows)

 Database             | notes
 Client User          | ubuntu
 Socket Directory     | /var/run/postgresql
 Password Used        | false
 SSL Connection       | false

Over the socket there is no password and no TLS – that is peer. Day 2 builds roles with exactly the privileges they need.

Save your progress? Sign up for free

Drill 5: Table notes and test data

The table matches the schema that notes-api creates with notes-api migrate (Part 2 connects the application). gen_random_uuid() has been built in since PostgreSQL 13:

psql -d notes <<'EOF'
CREATE TABLE notes (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  text       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO notes (text) VALUES
  ('Tag 0: PostgreSQL 18 läuft'),
  ('Backup ist erst ein Backup, wenn der Restore geklappt hat'),
  ('Der Standby kommt an Tag 10');
INSERT INTO notes (text, created_at)
SELECT 'Testnotiz ' || i, now() - i * interval '1 minute'
FROM generate_series(1, 10000) AS i;
EOF
psql -d notes -c '\d notes'
psql -d notes -c "SELECT count(*), pg_size_pretty(pg_total_relation_size('notes'))
  FROM notes;"

Expected:

CREATE TABLE
INSERT 0 3
INSERT 0 10000
                               Table "public.notes"
   Column   |           Type           | Collation | Nullable |      Default
------------+--------------------------+-----------+----------+-------------------
 id         | uuid                     |           | not null | gen_random_uuid()
 text       | text                     |           | not null |
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)

 count | pg_size_pretty
-------+----------------
 10003 | 1120 kB
(1 row)

generate_series creates the 10,000 rows in one go. Day 5 (generate_series) and Day 7 (pgbench) build more load.

Save your progress? Sign up for free

Drill 6: Open network access

First a role for connections over the network, with a password and only the privileges it needs. The password is a lab value:

psql -d notes <<'EOF'
CREATE ROLE notes_app LOGIN PASSWORD 'lab-notes-123';
GRANT SELECT, INSERT ON notes TO notes_app;
EOF
psql -Atc "SELECT rolname, left(rolpassword, 14) FROM pg_authid
  WHERE rolname = 'notes_app';"

Expected:

CREATE ROLE
GRANT
notes_app|SCRAM-SHA-256$

PostgreSQL stores only a SCRAM hash, never the password. Interactively, \password notes_app is better: then the password ends up neither in the shell history nor in the log.

Right now the server listens only on localhost. Two knobs open it up: listen_addresses (which addresses it listens on) and pg_hba.conf (who may log in from where and how):

sudo ss -tlnp | grep 5432
psql -c "ALTER SYSTEM SET listen_addresses = '*';"
echo 'host  notes  notes_app  172.16.0.0/24  scram-sha-256' \
  | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
sudo systemctl restart postgresql@18-main
sudo ss -tlnp | grep 5432
psql -c "SELECT line_number AS nr, database, user_name, address, auth_method
  FROM pg_hba_file_rules WHERE type = 'host';"

Expected (abridged):

LISTEN 0  200   127.0.0.1:5432   0.0.0.0:*  users:(("postgres",pid=4095,fd=7))
LISTEN 0  200       [::1]:5432      [::]:*  users:(("postgres",pid=4095,fd=6))
ALTER SYSTEM
host  notes  notes_app  172.16.0.0/24  scram-sha-256
LISTEN 0  200     0.0.0.0:5432   0.0.0.0:*  users:(("postgres",pid=4704,fd=6))
LISTEN 0  200        [::]:5432      [::]:*  users:(("postgres",pid=4704,fd=7))
 nr  |   database    |  user_name  |  address   |  auth_method
-----+---------------+-------------+------------+---------------
 125 | {all}         | {all}       | 127.0.0.1  | scram-sha-256
 127 | {all}         | {all}       | ::1        | scram-sha-256
 131 | {replication} | {all}       | 127.0.0.1  | scram-sha-256
 132 | {replication} | {all}       | ::1        | scram-sha-256
 133 | {notes}       | {notes_app} | 172.16.0.0 | scram-sha-256
(5 rows)

ALTER SYSTEM writes to postgresql.auto.conf in the data directory, not to /etc/postgresql. listen_addresses needs a restart, a change to pg_hba.conf only a reload. The rule lets exactly one role into exactly one database, and only from the lab network. Day 3 goes deeper into both.

Save your progress? Sign up for free

Drill 7: Prove the connection from pg2

On pg2:

psql -h 172.16.0.182 -U notes_app -d notes -c 'SELECT count(*) FROM notes;'

Expected:

Password for user notes_app:
 count
-------
 10003
(1 row)

The password is lab-notes-123. So it doesn’t ask on every command, it goes into the environment for this drill (Day 1 shows the clean way with .pgpass):

export PGPASSWORD=lab-notes-123
C="host=172.16.0.182 dbname=notes user=notes_app"
psql "$C" -c '\conninfo' | grep -E 'Host|Client User|Password|SSL (Conn|Proto)'
psql "$C" -c "INSERT INTO notes (text) VALUES ('Gruß von pg2') RETURNING id;"
psql "$C" -c 'DELETE FROM notes;'
psql -h 172.16.0.182 -U notes_app -d postgres -c 'SELECT 1;'
psql -h 172.16.0.182 -U postgres -d notes -c 'SELECT 1;'
unset PGPASSWORD

Expected (abridged):

 Client User          | notes_app
 Host                 | 172.16.0.182
 Password Used        | true
 SSL Connection       | true
 SSL Protocol         | TLSv1.3
                  id
--------------------------------------
 ed030bbc-f4d5-43ae-be17-06cdd6801dd7
(1 row)

INSERT 0 1
ERROR:  permission denied for table notes
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: connection to server at "172.16.0.182", port 5432 failed: FATAL:  no
pg_hba.conf entry for host "172.16.0.123", user "postgres", database "notes", ...

Three proofs: the connection is encrypted (Ubuntu sets up ssl = on with the self-signed “snakeoil” certificate; Day 3 replaces it with your own CA). notes_app may read and write, but not delete. And pg_hba.conf rejects every other combination. Each rejection appears twice because psql retries unencrypted after the TLS attempt (sslmode=prefer).

Save your progress? Sign up for free

Drill 8: SSH between pg1 and pg2 for postgres

pgBackRest (Day 9) talks to the other server via SSH, and an archive_command with rsync or scp pushes WAL files to pg2. Both run as Linux user postgres. (pg_basebackup and pg_rewind don’t need SSH, they use a normal database connection.) On both VMs, create a lab key without a passphrase:

sudo -u postgres ssh-keygen -t ed25519 -N '' -C postgres@$(hostname) \
  -f /var/lib/postgresql/.ssh/id_ed25519

Expected (abridged):

Created directory '/var/lib/postgresql/.ssh'.
Your identification has been saved in /var/lib/postgresql/.ssh/id_ed25519

ssh-copy-id doesn’t help: postgres has no password (!* in /etc/shadow). Your own machine, which reaches both VMs as ubuntu with a key, distributes the public keys:

ssh [email protected] sudo cat /var/lib/postgresql/.ssh/id_ed25519.pub \
  | ssh [email protected] \
  'sudo -u postgres tee -a /var/lib/postgresql/.ssh/authorized_keys'
ssh [email protected] sudo cat /var/lib/postgresql/.ssh/id_ed25519.pub \
  | ssh [email protected] \
  'sudo -u postgres tee -a /var/lib/postgresql/.ssh/authorized_keys'

Expected (abridged):

ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIITG4bCuO5YItznoqwPrfs4KIVTL1dgP5CVcBl8Y4R3u ...
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIGJC8fo7nzZ7fT3xixy5xXz7xJIiACatIjyrWJafHwf0 ...

tee creates the file with the umask of sudo (rw-rw-r--). Set it to 600 on both VMs:

sudo -u postgres chmod 600 /var/lib/postgresql/.ssh/authorized_keys

Test from pg1 to pg2 and back; accept-new stores the host key on first contact:

sudo -u postgres ssh -o StrictHostKeyChecking=accept-new [email protected] \
  'hostname; whoami; pg_lsclusters'

Expected (abridged):

Warning: Permanently added '172.16.0.123' (ED25519) to the list of known hosts.
prod
postgres
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...

On pg2:

sudo -u postgres ssh -o StrictHostKeyChecking=accept-new [email protected] \
  'hostname; whoami'

Expected:

Warning: Permanently added '172.16.0.182' (ED25519) to the list of known hosts.
argo
postgres

In the lab, the VM for pg1 is called argo and the one for pg2 is called prod – the book always uses the roles. From now on, all commands run on pg1 unless stated otherwise.

Save your progress? Sign up for free

Exercise: Speed Drills

  1. Show the version of the running server via SQL. Target time: 15 s
  2. Show all clusters on this VM with port and status. Target time: 5 s
  3. Show where the current value of listen_addresses comes from. Target time: 30 s
  4. Reload the configuration without dropping connections. Target time: 10 s

Solution

psql -Atc 'SHOW server_version'
pg_lsclusters
psql -Atc "SELECT setting, sourcefile FROM pg_settings
  WHERE name = 'listen_addresses';"
psql -Atc 'SELECT pg_reload_conf()'

Expected (abridged):

18.7 (Ubuntu 18.7-1.pgdg24.04+2)
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...
*|/var/lib/postgresql/18/main/postgresql.auto.conf
t

sourcefile points to postgresql.auto.conf: ALTER SYSTEM beats every line in postgresql.conf. Equivalent to the last command: sudo systemctl reload postgresql@18-main.

Save your progress? Sign up for free

Checkpoint

pg_lsclusters

Expected: cluster 18 main on port 5432, online – on pg1 and on pg2.

psql -Atc 'SHOW server_version'

Expected: 18.7 (Ubuntu 18.7-1.pgdg24.04+2) or a newer 18.x.

psql -d notes -Atc 'SELECT count(*) FROM notes'

Expected: 10004 (10,003 from Drill 5 plus the note from pg2).

sudo ss -tlnp | grep 5432

Expected: 0.0.0.0:5432 and [::]:5432.

psql -Atc 'SHOW data_checksums'

Expected: on

On pg2:

psql -h 172.16.0.182 -U notes_app -d notes -Atc 'SELECT count(*) FROM notes'

Expected: password prompt, then 10004.

sudo -u postgres ssh [email protected] hostname

Expected: argo – without a prompt for a password or host key.

Challenge

On pg2, create a second cluster 18/test next to 18/main, start it, log in and show port and data directory. Then remove it again completely.

Hint: The Debian tools are called pg_createcluster and pg_dropcluster; pg_createcluster picks the port by itself.

Solution

sudo pg_createcluster 18 test --start
pg_lsclusters
sudo -u postgres psql -p 5433 -Atc 'SHOW port' -c 'SHOW data_directory'
systemctl is-active postgresql@18-test
sudo pg_dropcluster --stop 18 test
pg_lsclusters

Expected (abridged):

Ver Cluster Port Status Owner    Data directory              Log file
18  test    5433 online postgres /var/lib/postgresql/18/test ...
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...
18  test    5433 online postgres /var/lib/postgresql/18/test ...
5433
/var/lib/postgresql/18/test
active
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main ...

A cluster is one server instance with its own data directory and port – not several machines. pg_dropcluster deletes configuration, data and log. This is exactly how the new cluster for the upgrade is created on Day 13.

Save your progress? Sign up for free

Interview Note

  • “What is a cluster in PostgreSQL?” – One server instance: one data directory, one port, several databases. On Debian/Ubuntu <version>/<name>, managed with pg_lsclusters, pg_ctlcluster, pg_createcluster.
  • “Why PGDG instead of the distribution package?” – Free choice of major version, all minor releases promptly, several versions installable side by side (the basis for pg_upgrade).
  • “peer vs. scram-sha-256?” – peer checks the Linux user on the local socket; over the network you log in with a password, and SCRAM never transmits it in plain text.

Enterprise Note

  • Packages come from an internal mirror (Artifactory, Nexus, aptly) with a verified key; the major version is in the package name, minor updates are scheduled.
  • No superuser for humans in daily work: personal roles with group privileges, login via LDAP/Kerberos or certificates, superuser only in emergencies.
  • listen_addresses on specific interfaces, pg_hba.conf per application and network, plus a firewall – '*' with open rules is a lab value.
  • TLS with certificates from an internal CA and hostssl instead of host; clients with sslmode=verify-full.
  • Data on its own volume, separate from WAL and logs, with monitoring of disk usage.