Day 0: Training Ground
Goal: PostgreSQL 18 from the PGDG repository runs on
pg1andpg2,pg1has the databasenoteswith 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 $KExpected (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-commonand thensudo /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 --versionExpected (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-contribshows16+257build1.1). The contrib extensions such aspg_stat_statementshave been part ofpostgresql-18since 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;'
psqlExpected (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 PGPASSWORDExpected (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_ed25519Expected (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_keysTest 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
- Show the version of the running server via SQL. Target time: 15 s
- Show all clusters on this VM with port and status. Target time: 5 s
- Show where the current value of
listen_addressescomes from. Target time: 30 s - 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_lsclustersExpected: 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 5432Expected: 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] hostnameExpected: 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_lsclustersExpected (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 withpg_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?” –
peerchecks 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_addresseson specific interfaces,pg_hba.confper application and network, plus a firewall –'*'with open rules is a lab value.- TLS with certificates from an internal CA and
hostsslinstead ofhost; clients withsslmode=verify-full. - Data on its own volume, separate from WAL and logs, with monitoring of disk usage.