The Project | About Us | Contribute | Donations | License

HOME

PostgreSQL 16 has been running on srv-tramontana since Module 5. It is installed, it has its apt-mark hold and its pinning so that it does not jump a major version without a decision being taken, it listens on 10.0.2.15:5432 and ufw only allows it from 10.0.2.0/24. All of that is well done.

And yet the configuration is the one that came with the package, which is calculated so as to start on any machine, including one with 512 MB of RAM. On srv-tramontana that means PostgreSQL uses 128 MB of shared memory out of the 3.8 GB available, that it sorts on disk what would fit in memory, and that it believes the system has a far smaller disk cache than it does — which changes the execution plans it chooses.

In 08-01 you left a specific clue: the p99 of $upstream_response_time is 1.18 s, and the two slowest paths are reports. When the application's time goes up and the network's does not, the culprit is almost never the application: it is a query. This lesson goes after that, and after what lies beneath it — because the database is where Tramontana's real business lives, and losing it is a problem of an entirely different category from losing the web server.

Objective, prerequisites and starting state
PostgreSQL's process and file architecture
Memory tuning with reasoned formulas
Huge pages: the connection with 07-03
Connections: why a pool and not a bigger number
Authentication: pg_hba.conf field by field
TLS on the connections
Roles and permissions with least privilege
Backups: logical, physical, WAL and point-in-time recovery
Maintenance: vacuum, wraparound and reindexing
Diagnostics: where the time goes
Streaming replication
Automation with Ansible
Daily operation

Objective, prerequisites and starting state

Objective. To leave PostgreSQL tuned to the machine, with restricted and encrypted access, an application role with no unnecessary privileges, backups that allow recovery to a specific instant within the 4-hour RPO, verified automatic maintenance and instrumented diagnostics.

Prerequisites: PostgreSQL 16 installed (05-03), LVM and /srv/tramontana/backups encrypted with LUKS (05-04), restic with GFS retention (05-08), pass and systemd-creds (06-05), the firewall from 06-03, and Ansible in working order (07-06).

Starting state, measured before touching anything:

$ psql --version
psql (PostgreSQL) 16.3 (Ubuntu 16.3-0ubuntu0.24.04.1)

$ sudo -u postgres psql -c "SELECT name, setting, unit, source FROM pg_settings
   WHERE name IN ('shared_buffers','effective_cache_size','work_mem',
                  'maintenance_work_mem','max_connections','wal_buffers');"
         name         | setting | unit |  source
----------------------+---------+------+----------
 effective_cache_size | 524288  | 8kB  | default
 maintenance_work_mem | 65536   | kB   | default
 max_connections      | 100     |      | default
 shared_buffers       | 16384   | 8kB  | default
 wal_buffers          | 512     | 8kB  | default
 work_mem             | 4096    | kB   | default

In plain terms: 128 MB of shared_buffers, 4 GB of effective_cache_size (which as it happens is not bad, but by accident), 4 MB of work_mem and source = default on everything, that is, nobody has ever tuned anything.

# Current size of the database and of its largest tables
$ sudo -u postgres psql -d tramontana -c "
  SELECT relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
         n_live_tup AS live_rows
  FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid
  ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 5;"
   relname    |  total  | live_rows
--------------+---------+-----------
 bookings     | 412 MB  |    186420
 availability | 168 MB  |    891200
 guests       |  54 MB  |     92310
 houses       |  96 kB  |         5
 audit        | 1204 MB |    512880

Two relevant facts: the complete database is around 1.8 GB, that is, it fits entirely in memory if it is configured properly; and the audit table is the largest of the lot, which already suggests a retention policy is pending.

PostgreSQL's process and file architecture

PostgreSQL uses a process-per-connection model, not threads. That explains almost all of its memory behaviour and it is what makes the section on pooling important.

$ ps -eo pid,ppid,user,comm --forest | grep -A9 'postgres$' | head -12
   1121       1 postgres postgres
   1210    1121 postgres  \_ postgres: checkpointer
   1211    1121 postgres  \_ postgres: background writer
   1213    1121 postgres  \_ postgres: walwriter
   1214    1121 postgres  \_ postgres: autovacuum launcher
   1215    1121 postgres  \_ postgres: logical replication launcher
   3402    1121 postgres  \_ postgres: svc_tramontana tramontana 127.0.0.1(51422) idle
   3403    1121 postgres  \_ postgres: svc_tramontana tramontana 127.0.0.1(51424) SELECT

Process	What it does	Why it matters to you
postmaster	The parent process; it accepts connections and spawns backends	If it dies, everything goes down
backend	One per connection; it runs the queries	Each one consumes its own memory: that is the reason for the pool
checkpointer	Flushes dirty pages to disk periodically	An aggressive checkpoint produces I/O spikes
background writer	Writes dirty pages out gradually	It smooths out the checkpointer's spikes
walwriter	Writes the write-ahead log	It is what guarantees durability
autovacuum launcher	Launches cleanup workers	Without it, the database degrades on its own

And where everything lives on Ubuntu, which follows the Debian design with several possible clusters:

$ sudo -u postgres psql -c "SHOW data_directory; SHOW config_file; SHOW hba_file;"
      data_directory
---------------------------
 /var/lib/postgresql/16/main
        config_file
-------------------------------------------
 /etc/postgresql/16/main/postgresql.conf
         hba_file
-------------------------------------------
 /etc/postgresql/16/main/pg_hba.conf

$ sudo ls -1 /var/lib/postgresql/16/main/ | head -8
base            # the data: one subdirectory per database
global          # catalogues shared between databases
pg_wal          # the write-ahead log
pg_stat         # planner statistics
pg_tblspc       # links to external tablespaces
postgresql.auto.conf   # what ALTER SYSTEM writes
PG_VERSION
postmaster.pid

Path	What it is	Careful
`PGDATA` = `/var/lib/postgresql/16/main`	All the data	Never touched by hand with the server running
`pg_wal/`	The write-ahead log	If it fills up, PostgreSQL stops. Never delete files by hand
`/etc/postgresql/16/main/postgresql.conf`	Configuration	On Debian/Ubuntu it lives outside `PGDATA`
`postgresql.auto.conf`	What `ALTER SYSTEM` writes	It takes precedence over `postgresql.conf`: a source of confusion
`pg_hba.conf`	Who can connect and how	Applied with `reload`, in top-to-bottom order

That postgresql.auto.conf row deserves a warning: if somebody once ran ALTER SYSTEM SET work_mem = '64MB', that value wins even if you edit postgresql.conf. When a parameter does not take the value you expect, pg_settings.source tells you where it comes from.

Following the course's drop-in convention, your own configuration is not written by editing the main file, but in a separate file included at the end:

$ sudo cp /etc/postgresql/16/main/postgresql.conf \
          /etc/postgresql/16/main/postgresql.conf.bak-$(date +%F)
$ echo "include_dir = 'conf.d'" | sudo tee -a /etc/postgresql/16/main/postgresql.conf
$ sudo -u postgres mkdir -p /etc/postgresql/16/main/conf.d

Memory tuning with reasoned formulas

The machine has 3.8 GB of RAM and 2 vCPUs. That memory has to be shared between PostgreSQL, the Tramontana application, Nginx and the system. These are the starting formulas — starting points, not dogma — and the reasoning behind them.

Parameter	Usual formula	Value for 3.8 GB	What it controls
`shared_buffers`	25% of RAM	960 MB	PostgreSQL's own page cache
`effective_cache_size`	50-75% of RAM	2560 MB	What the planner believes is cached
`work_mem`	RAM ÷ (connections × 3)	8 MB	Memory per sort or hash operation
`maintenance_work_mem`	5-10% of RAM	256 MB	For `VACUUM`, `CREATE INDEX`, `ALTER TABLE`
`wal_buffers`	1/32 of `shared_buffers`, max. 16 MB	16 MB	WAL buffer before it is written

And now the reasoning for each one, which is what distinguishes tuning from copying numbers:

shared_buffers = 960 MB (25%). PostgreSQL keeps its own page cache in addition to the operating system's cache. That is why it is not set to 80% as in other engines: it would produce double storage, with the same pages in two places and less total usable memory. 25% is the empirically established balance point. With 1.8 GB of data, almost half the database will live permanently here.

effective_cache_size = 2560 MB (67%). This parameter reserves nothing: it is a hint to the planner about how much memory is available between shared_buffers and the system cache. If you leave it low, the planner believes reading from disk is expensive and avoids indexes, preferring sequential scans. It is one of the settings with the greatest impact per unit of effort, and it does not cost a single byte of memory.

work_mem = 8 MB, and why it is the dangerous parameter. It is memory per operation, not per connection. A query with two sorts and a hash join can use three times work_mem. With 100 connections and three-operation queries:

Theoretical worst case = work_mem x connections x operations per query
                       = 8 MB x 100 x 3 = 2400 MB

That is, more than half the server's RAM, on top of the 960 MB of shared_buffers. That arithmetic is the reason why raising work_mem blithely gets PostgreSQL killed by the OOM killer — the same mechanism you saw in 07-02. With PgBouncer limiting things to 25 real connections, the worst case drops to 600 MB, which is manageable. The pool first, work_mem afterwards.

And the elegant part: work_mem can be raised only for the query that needs it, without touching the global setting.

-- In the monthly report's session, not across the whole server
BEGIN;
SET LOCAL work_mem = '128MB';
SELECT house, sum(amount) FROM bookings WHERE date >= '2026-01-01' GROUP BY house;
COMMIT;

maintenance_work_mem = 256 MB. Only maintenance operations use it, and at most autovacuum_max_workers of them at a time. Raising it speeds up VACUUM and index creation enormously, at far less risk than work_mem.

The complete file:

# /etc/postgresql/16/main/conf.d/10-tramontana.conf
# Tuning for srv-tramontana: 3.8 GB RAM, 2 vCPU, SSD, PostgreSQL 16
# Baseline taken on 2026-08-18. Measure before and after.

# ---------- Memory ----------
shared_buffers = 960MB
effective_cache_size = 2560MB
work_mem = 8MB
maintenance_work_mem = 256MB
wal_buffers = 16MB

# ---------- Connections ----------
# LOWERED from 100 to 60. With PgBouncer in front, 60 is plenty, and
# every reserved connection costs memory even when it is idle.
max_connections = 60
superuser_reserved_connections = 3

# ---------- Write-ahead log ----------
wal_level = replica              # needed for the replica and for PITR
max_wal_size = 2GB               # fewer checkpoints, gentler spikes
min_wal_size = 256MB
checkpoint_completion_target = 0.9   # spreads the writing over time
archive_mode = on
archive_command = '/usr/local/bin/archive_wal.sh %p %f'
archive_timeout = 900s           # forces a segment every 15 min

# ---------- Planner (SSD) ----------
# random_page_cost defaults to 4.0, calibrated for spinning disks
# where a random seek cost far more than a sequential read.
# On an SSD the difference is minimal: 1.1 reflects reality and makes
# the planner use indexes where it should.
random_page_cost = 1.1
effective_io_concurrency = 200   # the SSD serves many requests at once

# ---------- Parallelism (2 vCPU: contention) ----------
max_worker_processes = 2
max_parallel_workers = 2
max_parallel_workers_per_gather = 1

# ---------- Logging ----------
log_destination = 'stderr'
logging_collector = off          # let systemd/journald collect it
log_line_prefix = '%m [%p] %q%u@%d '
log_min_duration_statement = 250ms   # logs the slow queries
log_checkpoints = on
log_connections = off            # noisy with a pool in front
log_lock_waits = on              # lock waits: an important symptom
log_temp_files = 0               # any temporary file = work_mem too small
log_autovacuum_min_duration = 1s

# ---------- Statistics ----------
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 5000
pg_stat_statements.track = top

$ sudo -u postgres psql -c "SELECT pg_reload_conf();"
# shared_buffers and shared_preload_libraries require a RESTART
$ sudo systemctl restart postgresql@16-main

$ sudo -u postgres psql -c "SELECT name, setting, unit, source, pending_restart
   FROM pg_settings WHERE name IN ('shared_buffers','work_mem','random_page_cost');"
      name       | setting | unit |               source                | pending_restart
-----------------+---------+------+-------------------------------------+-----------------
 random_page_cost| 1.1     |      | configuration file                  | f
 shared_buffers  | 122880  | 8kB  | configuration file                  | f
 work_mem        | 8192    | kB   | configuration file                  | f

The pending_restart column is the one that tells you whether a change is applied or merely written. source = configuration file confirms that the drop-in is being read.

Measuring the effect, which is half the job:

# The shared cache hit ratio. Measured BEFORE and AFTER, after letting a
# few hours of real traffic go by.
$ sudo -u postgres psql -d tramontana -c "
  SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit + blks_read), 0), 2)
         AS hit_pct
  FROM pg_stat_database WHERE datname = 'tramontana';"
 hit_pct
---------
   99.42

Before the tuning that number was 91.80%. It looks like a small improvement and it is not: going from 91.8% to 99.4% means that out of every 100 page accesses, those that go to disk fall from 8.2 to 0.6, that is, a reduction of almost 93% in physical reads. Below 95% you should be suspicious; below 90% there is a clear problem either with memory or with queries that scan whole tables.

Huge pages: the connection with 07-03

In 07-03 you disabled transparent huge pages (THP) or left them on madvise, and the lesson noted that PostgreSQL was one of the reasons. Here is the detail.

The problem is not huge pages themselves, which are useful: with 2 MB pages instead of 4 KB, the processor's TLB covers 512 times more memory and translation faults collapse. The problem is the "transparent" part: the kernel tries to merge pages and defragment memory synchronously, inside the context of the process asking for memory. For PostgreSQL, with its nearly one-gigabyte shared_buffers and its short-lived processes, that produces unpredictable pauses of tens or hundreds of milliseconds in queries that ought to take two.

$ cat /sys/kernel/mm/transparent_hugepage/enabled
always [madvise] never

$ cat /sys/kernel/mm/transparent_hugepage/defrag
always defer defer+madvise [madvise] never

madvise is exactly the right setting: huge pages are only used where the program explicitly asks for them with madvise(MADV_HUGEPAGE), and they are not imposed on everybody.

The best of both worlds is to use explicit huge pages for shared_buffers:

# 1. How many PostgreSQL needs (it starts, answers and exits)
$ sudo -u postgres /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main \
      -C shared_memory_size_in_huge_pages
495

# 2. Reserve them with some margin, persistently
$ echo 'vm.nr_hugepages = 520' | \
      sudo tee /etc/sysctl.d/71-postgresql-hugepages.conf
$ sudo sysctl --system

# 3. Ask PostgreSQL to use them
$ echo "huge_pages = try" | \
      sudo tee -a /etc/postgresql/16/main/conf.d/10-tramontana.conf
$ sudo systemctl restart postgresql@16-main

# 4. Verify
$ grep -E 'HugePages_Total|HugePages_Free' /proc/meminfo
HugePages_Total:     520
HugePages_Free:       25

huge_pages = try and not on: with on, if the reserved pages are not enough, PostgreSQL will not start. With try it starts anyway using normal pages, which is the behaviour you want on a server that has to come back after an unsupervised reboot.

And the important warning: the memory in vm.nr_hugepages is reserved and unavailable for anything else. Reserving 520 pages of 2 MB is 1040 MB that the rest of the system will never see. With 3.8 GB you have to be precise, and that is why step 1 is not estimated: it is asked.

Connections: why a pool and not a bigger number

In 07-02 there was an incident worth recalling precisely: the application has max_connections=80 in /etc/tramontana/app.conf and PostgreSQL had max_connections=100. Under load, the application opened its 80, plus bookings_report.sh's connections, plus the maintenance ones, plus the three reserved for the superuser... and FATAL: sorry, too many clients already appeared.

The instinctive reaction — raising max_connections to 300 — is exactly the wrong one, for three measurable reasons:

Cost of each connection	Detail
Memory	A backend process is around 5-10 MB of its own memory, idle or not
Potential `work_mem`	Each connection can claim several `work_mem` at once
Contention	More processes competing for 2 vCPUs: more context switches, more lightweight-lock contention

With 2 vCPUs, the number of queries that can actually run at the same time is 2. Three hundred connections do not get more work done: they give you more processes waiting, more memory consumed and performance that falls as concurrency rises. It is the same saturation phenomenon from the USE method in 05-07.

The starting rule is connections ≈ (2 × cores) + effective disk spindles, which here gives around 5-10 active connections. We need many more open because the application keeps them idle between requests. That is where the pool comes in.

PgBouncer in transaction mode

$ sudo apt install pgbouncer
$ pgbouncer --version
PgBouncer 1.21.0

Mode	When it returns the connection to the pool	Multiplexing	Restrictions
`session`	When the client disconnects	None	None
`transaction`	At the end of each transaction	High	No session `SET`, no session-level prepared statements, no `LISTEN`
`statement`	After each statement	Maximum	Forbids multi-statement transactions

Transaction mode is the correct one for a web application: between requests, the connection goes back to the pool and another request makes use of it. A hundred application clients can share twenty-five real connections because hardly any of them is executing anything at a given instant.

The restrictions have to be checked with Luis before enabling it, because they are real: in transaction mode, a SET search_path outside a transaction is lost, and session-level prepared statements fail unless max_prepared_statements is enabled.

; /etc/pgbouncer/pgbouncer.ini
[databases]
; The application connects to PgBouncer believing it is PostgreSQL
tramontana = host=127.0.0.1 port=5432 dbname=tramontana

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; Delegated authentication query: avoids duplicating passwords
auth_user = pgbouncer_auth

pool_mode = transaction

; --- The sizing, which is the central decision ---
; max_client_conn: how many application clients we accept (they are cheap)
max_client_conn = 200
; default_pool_size: REAL connections to PostgreSQL per user/db pair.
; With 2 vCPU, 25 is generous. This is the number that protects the server.
default_pool_size = 25
; Temporary headroom for spikes, with a warning in the log
reserve_pool_size = 5
reserve_pool_timeout = 3

; Close server connections that have been idle a long time
server_idle_timeout = 600
; Recycle connections every hour: avoids accumulated memory leaks
server_lifetime = 3600
; If a client waits longer than this for a connection, a clear error
query_wait_timeout = 20

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
admin_users = operator
stats_users = operator, monitor

The change in the application is one line of /etc/tramontana/app.conf — remembering that the file has chattr +i and it has to be removed first:

$ sudo chattr -i /etc/tramontana/app.conf
$ sudo cp /etc/tramontana/app.conf /etc/tramontana/app.conf.bak-$(date +%F)
$ sudo sed -i 's/^db_port=5432/db_port=6432/' /etc/tramontana/app.conf
$ sudo diff -u /etc/tramontana/app.conf.bak-$(date +%F) /etc/tramontana/app.conf
--- /etc/tramontana/app.conf.bak-2026-08-18
+++ /etc/tramontana/app.conf
@@ -2,7 +2,7 @@
 db_host=127.0.0.1
-db_port=5432
+db_port=6432
 db_name=tramontana
$ sudo chattr +i /etc/tramontana/app.conf
$ sudo systemctl restart tramontana

And the check that the 07-02 problem is resolved:

$ psql -h 127.0.0.1 -p 6432 -U operator -d pgbouncer -c "SHOW POOLS;"
  database   |     user      | cl_active | cl_waiting | sv_active | sv_idle | maxwait
-------------+---------------+-----------+------------+-----------+---------+---------
 tramontana  | svc_tramontana|        63 |          0 |         4 |      21 |       0

Sixty-three application clients connected, four real connections working and none waiting. maxwait = 0 is the key metric: as soon as it is greater than zero in a sustained way, the pool is running short.

Before	After
80 direct connections, the memory of 80 processes	25 at most
`too many clients already` under load	`maxwait` visible and under control
`max_connections = 100`	`max_connections = 60`, with real headroom

Authentication: pg_hba.conf field by field

pg_hba.conf (host-based authentication) decides who can connect, to what, from where and how. It is evaluated from top to bottom and the first matching line wins, which means that a permissive line at the top nullifies every restrictive one below it.

TYPE      DATABASE        USER             ADDRESS            METHOD

Field	Values	Notes
`TYPE`	`local`, `host`, `hostssl`, `hostnossl`	`local` = Unix socket; `hostssl` requires TLS
`DATABASE`	a name, `all`, `replication`	`replication` is a pseudo-database for replicas
`USER`	a name, `all`, `+group`	`+` means "a member of the role"
`ADDRESS`	CIDR, `samenet`, empty for `local`	The narrower the better
`METHOD`	`scram-sha-256`, `peer`, `cert`, `trust`, `reject`	See the next table

Method	How it authenticates	Verdict
`scram-sha-256`	Challenge-response; the password does not travel	The correct one for network connections
`md5`	Obsolete and weak	Migrate to SCRAM
`peer`	Checks the Unix user on the local socket	Ideal for local maintenance tasks
`ident`	Queries a remote ident server	Do not use: it trusts the remote machine
`cert`	TLS client certificate	Excellent for service-to-service
`trust`	Accepts anybody without checking anything	Never, see below
`reject`	Explicitly denies	Useful for cutting off before a general rule

Why trust never, not even "temporarily" and not even on 127.0.0.1: it means that any process that can open a socket to the port gets in as whatever user it declares itself to be, including postgres. On a server running a web application, an SSRF-type vulnerability is enough — getting the application to make a request to 127.0.0.1:5432 — to have full control of the database. And what gets put in "temporarily" on a Tuesday is still there two years later.

# /etc/postgresql/16/main/pg_hba.conf
# The order MATTERS: the first match wins.

# --- 1. Local maintenance over the Unix socket ---
# 'peer' compares the system user with the role requested: 'sudo -u postgres
# psql' works with no password, and nobody else can impersonate it.
local   all             postgres                                peer
local   all             all                                     peer

# --- 2. PgBouncer and the application, from the loopback ---
# Only the application role, only its database, with SCRAM.
host    tramontana      svc_tramontana  127.0.0.1/32            scram-sha-256
host    tramontana      pgbouncer_auth  127.0.0.1/32            scram-sha-256

# --- 3. Monitoring (08-06), read-only on statistics ---
host    postgres        monitor         127.0.0.1/32            scram-sha-256

# --- 4. Replication (section 12), with TLS MANDATORY ---
# 'hostssl' rejects the connection if it is not encrypted; the WAL
# carries the complete data and cannot travel in the clear.
hostssl replication     replicator      10.0.2.16/32            scram-sha-256

# --- 5. Administrative access from the internal network, encrypted ---
hostssl tramontana      operator        10.0.2.0/24             scram-sha-256

# --- 6. Explicit denial of everything else ---
# Redundant (the default behaviour is already to deny) but it records
# the intent and produces a clear message in the log.
host    all             all             0.0.0.0/0               reject
host    all             all             ::/0                    reject

$ sudo -u postgres psql -c "SELECT pg_reload_conf();"
$ sudo -u postgres psql -c "SELECT line_number, type, database, user_name,
    address, auth_method, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"
(0 rows)

pg_hba_file_rules is a view that validates the file without reloading it: it tells you whether there are lines with errors before they break access. It is the nginx -t equivalent for pg_hba.conf, and it must always be used — "never close the door you are coming in through" applies here as well.

And the verification that the rules do what you think:

# Must work
$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -p 5432 \
      -U svc_tramontana -d tramontana -c 'SELECT 1;' >/dev/null && echo OK
OK

# Must NOT work: the application role against another database
$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -U svc_tramontana \
      -d postgres -c 'SELECT 1;'
psql: error: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user
"svc_tramontana", database "postgres", no encryption

TLS on the connections

With the application and PgBouncer on the same machine, the traffic goes over the loopback and encryption adds little. But the replica on 10.0.2.16 and the administrative access from the internal network do need it: the WAL contains all the data in the clear.

# Ubuntu generates a self-signed certificate and links it at install time
$ sudo ls -l /var/lib/postgresql/16/main/server.crt
lrwxrwxrwx 1 postgres postgres 36 -> /etc/ssl/certs/ssl-cert-snakeoil.pem

For internal traffic between your own servers, the right answer is not a Let's Encrypt certificate — which requires a public name and external validation — but an internal CA using the openssl tools from 06-05:

# Internal CA (generated once, on a secure machine, NOT on the server)
$ openssl req -new -x509 -days 3650 -nodes -out ca-tramontana.crt \
      -keyout ca-tramontana.key -subj "/CN=Tramontana internal CA"

# Server certificate for the database
$ openssl req -new -nodes -out db.csr -keyout db.key \
      -subj "/CN=srv-tramontana.internal"
$ openssl x509 -req -in db.csr -days 825 -CA ca-tramontana.crt \
      -CAkey ca-tramontana.key -CAcreateserial -out db.crt

$ sudo install -o postgres -g postgres -m 0600 db.key /etc/postgresql/16/main/
$ sudo install -o postgres -g postgres -m 0644 db.crt /etc/postgresql/16/main/

# conf.d/20-tls.conf
ssl = on
ssl_cert_file = '/etc/postgresql/16/main/db.crt'
ssl_key_file  = '/etc/postgresql/16/main/db.key'
ssl_ca_file   = '/etc/postgresql/16/main/ca-tramontana.crt'
ssl_min_protocol_version = 'TLSv1.2'
ssl_prefer_server_ciphers = on

$ sudo -u postgres psql -c "SELECT pid, ssl, version, cipher, client_addr
    FROM pg_stat_ssl JOIN pg_stat_activity USING (pid) WHERE ssl;"
 pid  | ssl | version |         cipher         | client_addr
------+-----+---------+------------------------+-------------
 8812 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 10.0.2.16

The detail most people miss: on the client, sslmode=require encrypts but does not verify the certificate, so it does not protect against a man in the middle. Only verify-full checks the chain and the name.

`sslmode`	Encrypts	Verifies the CA	Verifies the name
`disable`	No	—	—
`require`	Yes	No	No
`verify-ca`	Yes	Yes	No
`verify-full`	Yes	Yes	Yes

Roles and permissions with least privilege

It is the same principle as 05-01 and 05-02, applied inside the database. An application role must not be able to create tables, alter the schema, read other databases or, of course, be SUPERUSER.

-- Run as postgres: sudo -u postgres psql -d tramontana

-- 1. Application role: it can only connect and work with data
CREATE ROLE svc_tramontana WITH LOGIN
    PASSWORD 'injected-from-pass'
    NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT
    CONNECTION LIMIT 30;

-- 2. Lock down the public schema, which in PostgreSQL 15+ already comes
--    restricted, but it is worth being explicit. Before 15,
--    ANY user could create objects in 'public'.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE tramontana FROM PUBLIC;

-- 3. The application's own schema
CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION postgres;

-- 4. Bounded permissions: connect, use the schema, and DML on the
--    existing tables. NO CREATE: it cannot alter the schema.
GRANT CONNECT ON DATABASE tramontana TO svc_tramontana;
GRANT USAGE ON SCHEMA app TO svc_tramontana;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app
    TO svc_tramontana;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO svc_tramontana;

-- 5. And for FUTURE tables, which is what almost everybody forgets:
--    without this, the table the next migration creates will be
--    inaccessible to the application and the deployment will fail in
--    production.
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO svc_tramontana;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT USAGE ON SEQUENCES TO svc_tramontana;

-- 6. Read-only role for reports (bookings_report.sh) and for the
--    monitoring in 08-06
CREATE ROLE reports_reader WITH LOGIN PASSWORD 'a-different-one'
    NOSUPERUSER NOCREATEDB NOCREATEROLE CONNECTION LIMIT 5;
GRANT CONNECT ON DATABASE tramontana TO reports_reader;
GRANT USAGE ON SCHEMA app TO reports_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO reports_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO reports_reader;

-- 7. Monitoring role: a predefined role, without being a superuser
CREATE ROLE monitor WITH LOGIN PASSWORD 'yet-another';
GRANT pg_monitor TO monitor;

-- 8. Replication role (section 12)
CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'and-another';

Verifying is as important as granting — checking that what is forbidden is forbidden:

$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -U svc_tramontana \
      -d tramontana -c "CREATE TABLE test(id int);"
ERROR:  permission denied for schema app

$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -U svc_tramontana \
      -d tramontana -c "SELECT * FROM pg_shadow;"
ERROR:  permission denied for table pg_shadow

$ sudo -u postgres psql -c "\du" | grep -E 'svc_tramontana|reports'
 reports_reader | 5 connections                  | {}
 svc_tramontana | No inheritance, 30 connections | {}

The password, of course, is not written into the SQL: it is injected from pass as in 06-05.

$ sudo -u postgres psql -d tramontana <<SQL
ALTER ROLE svc_tramontana PASSWORD '$(pass tramontana/db)';
SQL
# And it is wiped from the psql history, which stores EVERYTHING typed
$ shred -u ~/.psql_history 2>/dev/null || true

Backups: logical, physical, WAL and point-in-time recovery

backup_tramontana.sh currently does a pg_dump. That is correct and it is not enough, and here is why.

	`pg_dump` (logical)	`pg_basebackup` (physical)
What it copies	SQL statements to rebuild	The `PGDATA` files byte by byte
Portable across major versions	Yes	No
Selective restore of one table	Yes	No, it is all or nothing
Restore time for 1.8 GB	~10 min (it rebuilds indexes)	~2 min (it copies files)
Allows PITR	No	Yes, with WAL archiving
Granularity of the recovery point	The instant of the dump	Any instant
Cost on the server	High: it reads and serialises everything	Moderate: sequential I/O

Both are necessary, and they are not alternatives: the logical one saves you in a major-version migration and lets you recover one specific table; the physical one with WAL is the only one that meets a 4-hour RPO without taking a dump every four hours.

WAL archiving

The write-ahead log contains every change, in order. If you keep a physical backup and all the WAL segments since then, you can replay the history up to any instant.

#!/usr/bin/env bash
#
# /usr/local/bin/archive_wal.sh - Archives one WAL segment
#
# PostgreSQL invokes it as: archive_wal.sh %p %f
#   %p = relative path to the segment; %f = just the name
#
# CRITICAL CONTRACT:
#   - It must return 0 ONLY if the segment is safe and verified.
#   - If it returns != 0, PostgreSQL RETRIES indefinitely and does NOT
#     delete the segment. That is correct: better to fill pg_wal than to
#     lose data.
#   - It must NEVER overwrite an existing file with different content.
#
set -euo pipefail

readonly SOURCE="$1"
readonly NAME="$2"
readonly DEST="/srv/tramontana/backups/wal"

umask 077

# Already archived and identical: idempotent success (07-06)
if [[ -f "${DEST}/${NAME}" ]]; then
    if cmp -s "$SOURCE" "${DEST}/${NAME}"; then
        exit 0
    fi
    logger -t archive_wal "ERROR: ${NAME} already exists with DIFFERENT content"
    exit 1
fi

# Atomic copy: write to a temporary file and rename. If the process dies
# halfway, no truncated segment is left looking valid.
tmp="${DEST}/.${NAME}.$$"
trap 'rm -f "$tmp"' EXIT

cp "$SOURCE" "$tmp"
sync -f "$tmp"                  # force it to disk BEFORE renaming
mv "$tmp" "${DEST}/${NAME}"
sync "$DEST"

exit 0

$ sudo install -o root -g root -m 0755 archive_wal.sh /usr/local/bin/
$ sudo -u postgres mkdir -p /srv/tramontana/backups/wal

$ sudo -u postgres psql -c "SELECT pg_switch_wal();"   # force a segment
$ sudo -u postgres psql -c "SELECT archived_count, last_archived_wal,
    last_archived_time, failed_count, last_failed_wal FROM pg_stat_archiver;"
 archived_count |    last_archived_wal     |      last_archived_time       | failed_count
----------------+--------------------------+-------------------------------+--------------
             47 | 000000010000000000000031 | 2026-08-18 12:41:08.221+02    |            0

failed_count is the metric to watch relentlessly: if archiving fails, pg_wal grows until it fills the disk and PostgreSQL stops. It goes straight into the monitoring in 08-06.

The physical backup

$ sudo -u postgres pg_basebackup \
      -h /var/run/postgresql -U postgres \
      -D /srv/tramontana/backups/base/$(date +%F) \
      -Ft -z -Xs -P -c fast --manifest-checksums=SHA256
 1843712/1843712 kB (100%), 1/1 tablespace

Option	What it does
`-Ft -z`	Compressed tar format: one file per tablespace
`-Xs`	Includes the WAL generated during the backup (streaming)
`-c fast`	Immediate checkpoint: it starts right away, with an I/O spike
`--manifest-checksums`	A manifest verifiable with `pg_verifybackup`

$ sudo -u postgres pg_verifybackup /srv/tramontana/backups/base/2026-08-18
backup successfully verified

And the integration with restic from 05-08, which is what takes it off the server:

# Fragment added to backup_tramontana.sh
backup_postgresql() {
    local dest="${TRAMONTANA_BACKUP_DIR}/base/$(date +%F)"
    log "starting PostgreSQL base backup"
    sudo -u postgres pg_basebackup -h /var/run/postgresql -U postgres \
        -D "$dest" -Ft -z -Xs -c fast --manifest-checksums=SHA256 \
        || die 74 "pg_basebackup failed"
    sudo -u postgres pg_verifybackup "$dest" \
        || die 65 "the base backup does NOT verify: it is not uploaded"
    log "base backup verified: $(format_bytes "$(du -sb "$dest" | cut -f1)")"

    restic backup "$dest" "${TRAMONTANA_BACKUP_DIR}/wal" \
        --tag postgresql --tag base \
        || die 74 "restic failed to upload the backup"
}

The pg_verifybackup before uploading is deliberate: uploading a corrupt backup consumes space and, worse still, gives a false sense of security. The 05-08 rule holds here: an unverified backup is not a backup.

Point-in-time recovery, with the complete procedure

This is the procedure you have to have written down before you need it, and tested. The scenario: at 14:32 somebody runs DELETE FROM app.bookings WHERE date < '2026-08-01' without the right WHERE and deletes 40,000 rows. It is detected at 14:51.

# ===== DONE ON srv-tramontana-test, NEVER on production =====
# Restoring over the live server destroys the only copy of the data
# from after the incident. You restore separately and extract what was lost.

# 1. Stop PostgreSQL on the recovery machine and empty PGDATA
$ sudo systemctl stop postgresql@16-main
$ sudo -u postgres mv /var/lib/postgresql/16/main \
      /var/lib/postgresql/16/main.broken-$(date +%F)
$ sudo -u postgres mkdir -m 0700 /var/lib/postgresql/16/main

# 2. Unpack the last base backup BEFORE the incident
$ sudo -u postgres tar -xzf /srv/tramontana/backups/base/2026-08-18/base.tar.gz \
      -C /var/lib/postgresql/16/main

# 3. Tell PostgreSQL where it gets the WAL from and how far to replay
$ sudo -u postgres tee /var/lib/postgresql/16/main/postgresql.auto.conf <<'EOF'
restore_command = 'cp /srv/tramontana/backups/wal/%f %p'
# Replay up to JUST BEFORE the DELETE. One second of margin.
recovery_target_time = '2026-08-18 14:31:55+02'
recovery_target_action = 'pause'
EOF

# 'pause' and not 'promote': the server stops at the target instant
# and waits. That way you can LOOK at the data before confirming. If you
# went too far, you restart with another target having destroyed nothing.

# 4. The signal that activates recovery mode (PostgreSQL >= 12)
$ sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

# 5. Start and watch
$ sudo systemctl start postgresql@16-main
$ sudo journalctl -u postgresql@16-main -f
LOG:  starting point-in-time recovery to 2026-08-18 14:31:55+02
LOG:  restored log file "000000010000000000000031" from archive
LOG:  restored log file "000000010000000000000032" from archive
LOG:  recovery stopping before commit of transaction 84412, time 2026-08-18 14:32:04+02
LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.

# 6. VERIFY before confirming anything
$ sudo -u postgres psql -d tramontana -c \
    "SELECT count(*) FROM app.bookings WHERE date < '2026-08-01';"
 count
-------
 40218

# 7. Confirm: promote the recovered server
$ sudo -u postgres psql -c "SELECT pg_wal_replay_resume();"
$ sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f

# 8. Extract ONLY what was lost and take it to production
$ sudo -u postgres pg_dump -d tramontana -t app.bookings \
      --data-only --where="date < '2026-08-01'" > /tmp/recovered.sql
$ scp /tmp/recovered.sql [email protected]:/tmp/
$ ssh [email protected] \
    "sudo -u postgres psql -d tramontana -1 -f /tmp/recovered.sql"

Five decisions in that procedure that have to be understood:

Recovery happens on another machine. Restoring over production destroys the transactions from after the incident, which are legitimate and exist nowhere else.
recovery_target_action = 'pause'. It lets you inspect before committing. With promote, if you overshot the instant, you have to start from scratch.
The target is set one second earlier, not at the exact instant: the incident's timestamp is rarely known to millisecond precision.
Only what was lost is extracted. Replacing the whole database would lose 19 minutes of real bookings.
psql -1 wraps the import in a transaction: either it all goes in or none of it does.

And the result that goes into the runbook, with numbers:

Metric	Measured value
Real RPO achieved	Seconds (with `archive_timeout=900s`, worst case 15 min)
Restore time for 1.8 GB	4 min 20 s
Total time of the procedure with verification	~25 min
Agreed RTO	2 h

The RPO goes from 4 hours to minutes at no additional cost, and that is news that deserves a paragraph in the report to Marta.

Maintenance: vacuum, wraparound and reindexing

PostgreSQL uses multiversion concurrency control (MVCC): an UPDATE does not modify the row, it writes a new version and marks the old one as dead. A DELETE only marks. Dead versions keep taking up space until somebody cleans them up.

$ sudo -u postgres psql -d tramontana -c "
  SELECT relname, n_live_tup AS live, n_dead_tup AS dead,
         round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),1) AS pct_dead,
         last_autovacuum
  FROM pg_stat_user_tables WHERE n_dead_tup > 1000
  ORDER BY n_dead_tup DESC;"
   relname    |  live  |  dead   | pct_dead |        last_autovacuum
--------------+--------+---------+----------+-------------------------------
 availability | 891200 |  412880 |     31.7 | 2026-08-16 03:12:41+02
 bookings     | 186420 |   18122 |      8.9 | 2026-08-18 04:02:11+02

31.7% dead tuples in availability is bloat: a third of that table is rubbish that gets read on every sequential scan, occupies shared_buffers and makes the indexes point at nearly empty pages. And the last_autovacuum from two days ago indicates that autovacuum is not keeping up with that table.

Operation	What it does	Does it block?
`VACUUM`	Marks the dead space as reusable	No: it coexists with the load
`VACUUM FULL`	Rewrites the table and returns space to the OS	Yes, ACCESS EXCLUSIVE: nobody reads or writes
`ANALYZE`	Recomputes the planner's statistics	No
`REINDEX`	Rebuilds bloated indexes	Yes, unless `CONCURRENTLY`

VACUUM FULL in production is a classic mistake: on a 400 MB table it takes minutes, during which the application cannot touch it. If you need to reclaim space while live, the tool is pg_repack.

Tuning autovacuum where it is needed

The defaults trigger the cleanup when dead tuples exceed 20% of the table. On a 900,000-row table that is 180,000 dead tuples before anybody lifts a finger, and an expensive cleanup each time.

-- PER-TABLE tuning, which is how it is done: you do not change the
-- global setting because of one problematic table.
ALTER TABLE app.availability SET (
    autovacuum_vacuum_scale_factor = 0.02,   -- clean at 2%, not at 20%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 2         -- more aggressive
);

-- The audit table only grows: it does not need the same treatment,
-- it needs a retention policy.
ALTER TABLE app.audit SET (autovacuum_vacuum_scale_factor = 0.1);

# conf.d/30-maintenance.conf — global settings
autovacuum_max_workers = 2           # with 2 vCPU, no more
autovacuum_naptime = 30s
autovacuum_vacuum_cost_limit = 1000  # the default 200 is too slow

Wraparound, and why it is an emergency

This is the most serious failure a badly maintained PostgreSQL database can suffer, and the one fewest people know about until they suffer it.

Every transaction gets a 32-bit identifier. That is around 4 billion, and they run out. PostgreSQL resolves the circularity by treating the identifiers as a circle where 2 billion are "in the past" and 2 billion "in the future". For that to work, very old rows have to be marked as frozen: visible to everybody, regardless of the counter. VACUUM takes care of that.

If VACUUM does not get round to it — because autovacuum is disabled, or because a transaction that has been open for days is blocking it — PostgreSQL warns, then warns more loudly, and finally refuses to accept writes:

ERROR:  database is not accepting commands to avoid wraparound data loss
in database "tramontana"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

The database goes read-only and the only way out is to stop the service and clean up in single-user mode, which on a large database can take hours. It is a total outage, and that is why it is monitored:

$ sudo -u postgres psql -c "
  SELECT datname, age(datfrozenxid) AS xid_age,
         round(100.0*age(datfrozenxid)/2000000000, 1) AS pct_to_limit
  FROM pg_database ORDER BY age(datfrozenxid) DESC LIMIT 3;"
  datname   |  xid_age  | pct_to_limit
------------+-----------+--------------
 tramontana |  48212104 |          2.4
 postgres   |  12088311 |          0.6

2.4% is a perfectly healthy situation. The operational rule:

`pct_to_limit`	Situation	Action
< 25%	Normal	None
25-50%	Watch	Review autovacuum and long transactions
50-75%	Warning	A planned manual `VACUUM FREEZE`
> 75%	Critical	Intervene now

The two causes are almost always the same, and both are detected in one query:

$ sudo -u postgres psql -c "
  SELECT pid, state, age(backend_xid) AS xid_age,
         now()-xact_start AS duration, left(query,50) AS query_text
  FROM pg_stat_activity
  WHERE backend_xid IS NOT NULL ORDER BY age(backend_xid) DESC LIMIT 3;"
 pid  |        state        | xid_age |    duration     |        query_text
------+---------------------+---------+-----------------+---------------------------
 4471 | idle in transaction | 8812044 | 3 days 04:12:09 | BEGIN; SELECT * FROM app...

idle in transaction for three days is the enemy: an open transaction stops everything after it being frozen and blocks the cleanup of the whole database. It is usually an application that opened a transaction and did not close it. The defence is preventive:

# conf.d/30-maintenance.conf
idle_in_transaction_session_timeout = 300s   # kill after 5 min idle
statement_timeout = 60s                      # no query longer than 1 min
lock_timeout = 10s                           # do not wait for locks forever

statement_timeout = 60s is global; the reports that legitimately take longer raise it in their own session with SET LOCAL statement_timeout.

Diagnostics: where the time goes

We pick up the thread from 08-01: the p99 of $upstream_response_time was 1.18 s and the slow paths were reports.

`pg_stat_statements`: the query that consumes the most time

$ sudo -u postgres psql -d tramontana -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
$ sudo -u postgres psql -d tramontana -c "
  SELECT round(total_exec_time::numeric,0) AS total_ms, calls,
         round(mean_exec_time::numeric,1) AS mean_ms,
         round(100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0),1) AS hit_pct,
         left(query, 60) AS query_text
  FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
 total_ms  | calls | mean_ms | hit_pct |                  query_text
-----------+-------+---------+---------+----------------------------------------------
   4128200 |  1088 |  3794.3 |    41.2 | SELECT house, sum(amount) FROM app.bookings
    881400 | 92104 |     9.6 |    99.8 | SELECT * FROM app.availability WHERE house =
    412900 |  1204 |   342.9 |    98.1 | SELECT * FROM app.bookings WHERE guest_id

The first row is the culprit, and the hit_pct column at 41.2% confirms it: that query reads more than half of what it needs from disk. Order by total time, not by mean time: a 10 ms query run 92,000 times consumes more server than a 4-second one run 1,000 times, and it is a common mistake to optimise the slow, spectacular one instead of the frequent one.

`EXPLAIN (ANALYZE, BUFFERS)`

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT house, sum(amount) FROM app.bookings
WHERE date >= '2026-01-01' GROUP BY house;

 HashAggregate  (cost=48122.11..48122.16 rows=5 width=40)
                (actual time=3781.442..3781.449 rows=5 loops=1)
   Group Key: house
   Buffers: shared hit=812 read=41208
   ->  Seq Scan on bookings  (cost=0.00..47188.20 rows=186782 width=18)
                             (actual time=0.031..3402.118 rows=186420 loops=1)
         Filter: (date >= '2026-01-01'::date)
         Rows Removed by Filter: 0
         Buffers: shared hit=812 read=41208
 Planning Time: 0.184 ms
 Execution Time: 3781.512 ms

How to read this, which is a skill you train:

Element	What it says	Here
`cost=`	The planner's estimate	48122 arbitrary units
`actual time=`	The real time: first row..last	3.78 s
Estimated `rows=` vs actual `rows=`	If they differ a lot, statistics are missing	186782 vs 186420: fine
`Buffers: shared hit / read`	Cache pages / disk pages	41208 from disk: the problem
`Seq Scan`	A full sequential scan	The whole table to filter nothing
`Rows Removed by Filter: 0`	The filter discards nothing	The `WHERE` is useless here

Diagnosis: the WHERE date >= '2026-01-01' discards no rows because every booking is later than that. The query reads 41,208 disk pages — 322 MB — to aggregate five groups. And sum(amount) forces the complete rows to be read, so an index on date would not help: the planner would still prefer the sequential scan.

The right solution here is not an index, but an index that covers the entire query:

-- Covering index: it contains everything the query needs, so it is
-- answered WITHOUT touching the table (Index Only Scan).
CREATE INDEX CONCURRENTLY idx_bookings_date_house_amount
    ON app.bookings (date) INCLUDE (house, amount);
ANALYZE app.bookings;

 HashAggregate  (actual time=182.401..182.409 rows=5 loops=1)
   Group Key: house
   Buffers: shared hit=1204 read=3811
   ->  Index Only Scan using idx_bookings_date_house_amount on bookings
         (actual time=0.048..96.221 rows=186420 loops=1)
         Index Cond: (date >= '2026-01-01'::date)
         Heap Fetches: 0
         Buffers: shared hit=1204 read=3811
 Execution Time: 182.478 ms

From 3,781 ms to 182 ms: twenty times faster, and the pages read from disk fall from 41,208 to 3,811. Heap Fetches: 0 confirms that the table is not touched at all.

CREATE INDEX CONCURRENTLY is mandatory in production: the normal version blocks writes to the table while it builds. CONCURRENTLY takes longer and does not block, in exchange for leaving an invalid index that has to be dropped if it fails.

Slow queries in the log

With log_min_duration_statement = 250ms, the slow ones end up in the journal:

$ sudo journalctl -u postgresql@16-main --since today | grep -oP 'duration: \K[0-9.]+' | \
      sort -rn | head -3
3812.402
1204.118
890.331

$ sudo journalctl -u postgresql@16-main --since today | grep 'temporary file'
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp4471.0", size 18874368
STATEMENT:  SELECT ... ORDER BY date DESC

That temporary-file message is gold: it means a sort did not fit in work_mem and was done on disk. 18 MB with work_mem = 8 MB. The answer is to raise work_mem only in that session, not globally.

Indexes that are missing and indexes that are surplus

# Tables with many sequential scans over a large volume
$ sudo -u postgres psql -d tramontana -c "
  SELECT relname, seq_scan, seq_tup_read, idx_scan,
         seq_tup_read/nullif(seq_scan,0) AS rows_per_scan
  FROM pg_stat_user_tables
  WHERE seq_scan > 100 AND seq_tup_read/nullif(seq_scan,0) > 10000
  ORDER BY seq_tup_read DESC;"
 relname  | seq_scan | seq_tup_read | idx_scan | rows_per_scan
----------+----------+--------------+----------+---------------
 bookings |     1088 |    202744960 |    91204 |        186346

# Indexes nobody uses: they take up space and slow down every INSERT
$ sudo -u postgres psql -d tramontana -c "
  SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan
  FROM pg_stat_user_indexes WHERE idx_scan < 50
    AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
  ORDER BY pg_relation_size(indexrelid) DESC;"
    indexrelname    | size  | idx_scan
--------------------+-------+----------
 idx_bookings_phone | 22 MB |        0

An unused index is not neutral: it takes up 22 MB of disk and of cache, and every INSERT, UPDATE and DELETE has to update it. Before dropping it you have to confirm that the statistics cover a representative period — an index used only at the monthly close will look useless on the 12th.

Streaming replication

We pick up 07-07, where it was decided: asynchronous replication (the 4-hour RPO makes it more than sufficient) and manual promotion (with two nodes there is no quorum, and automatic promotion produces divergent data).

# ===== On the PRIMARY (10.0.2.15) =====
# wal_level = replica and archive_mode = on are already set.
$ sudo -u postgres psql -c "
  SELECT slot_name FROM pg_create_physical_replication_slot('replica_16');"

A replication slot makes the primary retain the WAL segments that the replica has not yet consumed, even if the replica has been down for hours. It is what guarantees that a replica which goes down overnight can recover in the morning without being recreated from scratch.

And it brings the symmetric risk, which has to be understood: a slot belonging to a replica that never comes back fills pg_wal until it stops the primary. That is why it is bounded:

# conf.d/40-replication.conf on the PRIMARY
max_wal_senders = 3
max_replication_slots = 3
wal_keep_size = 1GB
# Safety limit: if the slot accumulates more than 8 GB, it is invalidated.
# Better to lose the replica than to stop the primary.
max_slot_wal_keep_size = 8GB

# ===== On the REPLICA (10.0.2.16) =====
$ sudo systemctl stop postgresql@16-main
$ sudo -u postgres rm -rf /var/lib/postgresql/16/main/*

$ sudo -u postgres PGPASSWORD=$(pass tramontana/replicator) pg_basebackup \
      -h 10.0.2.15 -U replicator -D /var/lib/postgresql/16/main \
      -R -P -Xs -C -S replica_16 \
      -d "sslmode=verify-full sslrootcert=/etc/postgresql/ca-tramontana.crt"
 1843712/1843712 kB (100%), 1/1 tablespace

Option	What it does
`-R`	Writes `postgresql.auto.conf` and `standby.signal`: the replica is ready
`-S replica_16`	Uses the slot that was created
`-Xs`	Receives the WAL while it copies: nothing is lost
`sslmode=verify-full`	The WAL travels encrypted and verified

$ sudo -u postgres cat /var/lib/postgresql/16/main/postgresql.auto.conf
primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass''
  host=10.0.2.15 port=5432 sslmode=verify-full ...'
primary_slot_name = 'replica_16'

$ sudo systemctl start postgresql@16-main
$ sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 t

And the monitoring, from the primary:

$ sudo -u postgres psql -x -c "
  SELECT client_addr, state, sync_state,
         pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_bytes,
         write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
-[ RECORD 1 ]-+----------------
client_addr   | 10.0.2.16
state         | streaming
sync_state    | async
lag_bytes     | 0 bytes
write_lag     | 00:00:00.002
replay_lag    | 00:00:00.004

lag_bytes is how much data would be lost if the primary went down now: the most important metric in the whole section, and it goes into the monitoring in 08-06 alongside the age of the last backup.

The replica also serves an immediate and valuable purpose: hot_standby = on allows read-only queries against it, so the heavy reports — the 3.7-second ones — can run there without touching the primary.

# On the replica, so that long queries are not cut off by a conflict
# with WAL replay
$ echo "max_standby_streaming_delay = 300s" | \
      sudo tee -a /etc/postgresql/16/main/conf.d/40-replication.conf

And the reminder that is never superfluous, already stated in 07-07: the replica is not a backup. The DELETE from section 9 reaches the replica in four milliseconds. PITR is what undoes it; the replica protects against hardware failure, not against human error.

Automation with Ansible

# ~/tramontana-infra/roles/db/defaults/main.yml
---
db_version: 16
db_ram_mb: 3800
# The formulas live in the variables: change the RAM and everything recalculates
db_shared_buffers_mb: "{{ (db_ram_mb * 0.25) | int }}"
db_effective_cache_mb: "{{ (db_ram_mb * 0.67) | int }}"
db_work_mem_mb: 8
db_maintenance_work_mem_mb: "{{ (db_ram_mb * 0.07) | int }}"
db_max_connections: 60
db_random_page_cost: 1.1          # SSD
db_log_min_duration_ms: 250
db_archive_dir: /srv/tramontana/backups/wal
db_pgbouncer_pool_size: 25
db_pgbouncer_max_clients: 200
db_allowed_networks:
  - { network: '127.0.0.1/32', database: tramontana,  role: svc_tramontana, type: host }
  - { network: '10.0.2.0/24',  database: tramontana,  role: operator,       type: hostssl }
  - { network: '10.0.2.16/32', database: replication, role: replicator,     type: hostssl }

# ~/tramontana-infra/roles/db/tasks/main.yml
---
- name: Check that the declared RAM matches the real RAM
  ansible.builtin.assert:
    that: (ansible_memtotal_mb - db_ram_mb) | abs < 400
    fail_msg: >-
      db_ram_mb ({{ db_ram_mb }}) does not match the real RAM
      ({{ ansible_memtotal_mb }} MB). The memory calculations would be
      wrong and could leave the server out of memory.

- name: Install PostgreSQL and PgBouncer
  ansible.builtin.apt:
    name:
      - "postgresql-{{ db_version }}"
      - "postgresql-contrib-{{ db_version }}"
      - pgbouncer
      - python3-psycopg2        # needed by the postgresql_* modules
    state: present
  tags: [packages]

- name: Hold the major version (the 05-03 policy)
  ansible.builtin.dpkg_selections:
    name: "postgresql-{{ db_version }}"
    selection: hold

- name: WAL archiving directory
  ansible.builtin.file:
    path: "{{ db_archive_dir }}"
    state: directory
    owner: postgres
    group: postgres
    mode: '0700'

- name: Install the WAL archiving script
  ansible.builtin.copy:
    src: archive_wal.sh
    dest: /usr/local/bin/archive_wal.sh
    owner: root
    group: root
    mode: '0755'

- name: Enable include_dir in postgresql.conf
  ansible.builtin.lineinfile:
    path: "/etc/postgresql/{{ db_version }}/main/postgresql.conf"
    line: "include_dir = 'conf.d'"
    regexp: '^#?\s*include_dir\s*='
    backup: true
  notify: Restart postgresql

- name: Deploy the calculated tuning
  ansible.builtin.template:
    src: 10-tramontana.conf.j2
    dest: "/etc/postgresql/{{ db_version }}/main/conf.d/10-tramontana.conf"
    owner: postgres
    group: postgres
    mode: '0640'
    backup: true
  notify: Restart postgresql

- name: Deploy pg_hba.conf
  ansible.builtin.template:
    src: pg_hba.conf.j2
    dest: "/etc/postgresql/{{ db_version }}/main/pg_hba.conf"
    owner: postgres
    group: postgres
    mode: '0640'
    backup: true
  notify: Reload postgresql

- name: Force the handlers before verifying
  ansible.builtin.meta: flush_handlers

# --- Verification: an invalid pg_hba leaves the server unreachable ---
- name: Verify that pg_hba.conf has no errors
  community.postgresql.postgresql_query:
    db: postgres
    login_unix_socket: /var/run/postgresql
    query: "SELECT count(*) AS errors FROM pg_hba_file_rules WHERE error IS NOT NULL"
  become: true
  become_user: postgres
  register: hba
  failed_when: hba.query_result[0].errors | int > 0

- name: Create the roles with passwords from Vault
  community.postgresql.postgresql_user:
    name: "{{ item.name }}"
    password: "{{ item.password }}"
    role_attr_flags: "{{ item.flags }}"
    conn_limit: "{{ item.limit | default(omit) }}"
    state: present
  become: true
  become_user: postgres
  loop:
    - { name: svc_tramontana, password: "{{ vault_db_app }}",
        flags: 'LOGIN,NOSUPERUSER,NOCREATEDB,NOCREATEROLE', limit: 30 }
    - { name: replicator, password: "{{ vault_db_replicator }}",
        flags: 'LOGIN,REPLICATION' }
    - { name: monitor, password: "{{ vault_db_monitor }}", flags: 'LOGIN' }
  no_log: true                     # the passwords must not reach the screen
  tags: [roles]

- name: Grant pg_monitor to the monitoring role
  community.postgresql.postgresql_membership:
    group: pg_monitor
    target_roles: monitor
    state: present
  become: true
  become_user: postgres

- name: Configure PgBouncer
  ansible.builtin.template:
    src: pgbouncer.ini.j2
    dest: /etc/pgbouncer/pgbouncer.ini
    owner: postgres
    group: postgres
    mode: '0640'
    backup: true
  notify: Restart pgbouncer

- name: Verify that WAL archiving works
  community.postgresql.postgresql_query:
    db: postgres
    login_unix_socket: /var/run/postgresql
    query: "SELECT failed_count, last_archived_time FROM pg_stat_archiver"
  become: true
  become_user: postgres
  register: archiver
  failed_when: archiver.query_result[0].failed_count | int > 0
  tags: [verify]

# roles/db/handlers/main.yml
---
- name: Reload postgresql
  community.postgresql.postgresql_query:
    db: postgres
    login_unix_socket: /var/run/postgresql
    query: "SELECT pg_reload_conf()"
  become: true
  become_user: postgres

- name: Restart postgresql
  # A restart cuts the connections. It only fires when a parameter that
  # requires it changes, and in production it is run inside a window.
  ansible.builtin.systemd:
    name: "postgresql@{{ db_version }}-main"
    state: restarted

- name: Restart pgbouncer
  ansible.builtin.systemd:
    name: pgbouncer
    state: restarted

The initial assert deserves attention: without it, applying the role to a machine with less RAM than declared would produce a shared_buffers larger than physical memory and PostgreSQL would not start. It is the kind of failure Ansible turns into a global one if it is not checked.

Daily operation

#!/usr/bin/env bash
# Fragment to integrate into health_check.sh: the database block
check_database() {
    local status=0

    # 1. Connections waiting in the pool
    local waiting
    waiting="$(psql -h 127.0.0.1 -p 6432 -U operator -d pgbouncer -tAc \
        "SHOW POOLS" | awk -F'|' '$1=="tramontana"{print $4}')"
    if (( waiting > 0 )); then
        error "there are $waiting clients waiting for a connection"; status=1
    fi

    # 2. Replica lag (bytes that would be lost right now)
    local lag
    lag="$(sudo -u postgres psql -tAc \
        "SELECT coalesce(max(pg_wal_lsn_diff(sent_lsn,replay_lsn)),0)::bigint
         FROM pg_stat_replication")"
    if (( lag > 104857600 )); then          # 100 MB
        error "the replica is $(format_bytes "$lag") behind"; status=1
    fi

    # 3. WAL archiving failures: if it fails, pg_wal grows without limit
    local failures
    failures="$(sudo -u postgres psql -tAc "SELECT failed_count FROM pg_stat_archiver")"
    if (( failures > 0 )); then
        error "WAL archiving has failed $failures times"; status=2
    fi

    # 4. Wraparound
    local pct
    pct="$(sudo -u postgres psql -tAc \
        "SELECT round(100.0*max(age(datfrozenxid))/2000000000) FROM pg_database")"
    if (( pct > 75 )); then
        error "wraparound at ${pct}%: EMERGENCY"; status=2
    elif (( pct > 50 )); then
        error "wraparound at ${pct}%"; status=1
    fi

    # 5. Transactions left open forever
    local zombies
    zombies="$(sudo -u postgres psql -tAc \
        "SELECT count(*) FROM pg_stat_activity
         WHERE state='idle in transaction' AND now()-xact_start > interval '10 min'")"
    if (( zombies > 0 )); then
        error "$zombies idle transactions older than 10 min"; status=1
    fi

    (( status == 0 )) && log "database correct"
    return "$status"
}

What gets looked at and when:

Frequency	Check	Alert threshold
Continuous (08-06)	Replica lag	> 100 MB
Continuous	`pg_stat_archiver.failed_count`	> 0
Continuous	Clients waiting in PgBouncer	> 0 sustained
Continuous	Free space in `pg_wal`	< 20%
Daily	Cache hit ratio	< 95%
Daily	Long `idle in transaction` transactions	> 10 min
Weekly	Dead tuples per table	> 20%
Weekly	Top 5 of `pg_stat_statements`	Changes in the ranking
Monthly	Age of the transaction counter	> 50%
Monthly	Unused indexes and database size	Unexpected growth
Quarterly	A complete PITR drill	Must complete in < 30 min

That last row is the most important in the table and the one most often ignored. A backup that has never been restored is not a backup: it is a file. The quarterly drill goes into the six-monthly rehearsal you proposed in 07-07.

Common Mistakes and Tips

Raising max_connections instead of putting in a pool. Every connection costs memory and contention. With 2 vCPUs, 300 connections get less work done than 25.
Raising work_mem globally. It is memory per operation and per connection: multiply it out before touching it, or the OOM killer will remind you.
Setting shared_buffers to 80%. You duplicate the storage with the system cache and end up with less usable memory than at 25%.
Leaving random_page_cost = 4 on an SSD. The planner avoids indexes it should be using, and the queries degrade for no apparent reason.
Editing postgresql.conf when postgresql.auto.conf has the same parameter. The second one wins. Check pg_settings.source.
Using trust in pg_hba.conf, even "temporarily" on localhost. It is total control of the database for any local process, and the temporary lasts years.
Reloading pg_hba.conf without looking at pg_hba_file_rules. You can lock yourself out of your own database.
Believing that sslmode=require verifies the certificate. It does not. Only verify-full does.
Giving SUPERUSER to the application role "so it does not cause problems". An SQL injection goes from reading data to running commands on the server.
Forgetting ALTER DEFAULT PRIVILEGES. The application works until a migration creates a new table, and then it fails in production.
Confusing a replica with a backup. A DELETE replicates in milliseconds. Undoing it needs PITR.
VACUUM FULL in production. An exclusive lock: nobody reads or writes while it lasts. Use pg_repack.
Ignoring wraparound until the database stops accepting writes. Watch age(datfrozenxid) and kill the idle transactions.
Not bounding max_slot_wal_keep_size. A downed replica fills pg_wal and stops the primary. Better to lose the replica.
CREATE INDEX without CONCURRENTLY in production. It blocks writes to the table while it builds.
Optimising by mean_exec_time. Order by total_exec_time: the 10 ms query run 92,000 times costs more than the 4 s one run 1,000 times.
A tip on method. Before changing a parameter, note down the current value and the metric you expect to move. A tuning change with no prior measurement is indistinguishable from superstition.

Exercises

Exercise 1

health_check.sh warns at 03:14 that the space on /var/lib/postgresql is at 91% and rising. Diagnose the cause, explain the mechanism, resolve it without losing data and propose the definitive prevention.

Exercise 2

Design and document the complete procedure for the quarterly point-in-time recovery drill, in the form of a runbook that somebody other than you can act on, with its success criteria.

Exercise 3

Marta asks whether the PostgreSQL replica discussed in 07-07 is worth it, now that there are backups with recovery to the minute. Write the answer.

Solutions

Solution 1

Diagnosis. The first thing is to find out what is growing, not how much:

$ sudo du -sh /var/lib/postgresql/16/main/* | sort -rh | head -4
9.8G	/var/lib/postgresql/16/main/pg_wal
1.6G	/var/lib/postgresql/16/main/base
2.1M	/var/lib/postgresql/16/main/global

$ ls /var/lib/postgresql/16/main/pg_wal/*.ready 2>/dev/null | wc -l
612

pg_wal at 9.8 GB and 612 .ready files in archive_status/. A .ready is a segment that PostgreSQL wants to archive and has not managed to archive. The cause is immediate:

$ sudo -u postgres psql -c "SELECT archived_count, failed_count,
    last_failed_wal, last_failed_time FROM pg_stat_archiver;"
 archived_count | failed_count |     last_failed_wal      |       last_failed_time
----------------+--------------+--------------------------+-------------------------------
           4128 |         2044 | 000000010000000000000A31 | 2026-08-18 03:12:55.118+02

$ sudo journalctl -t archive_wal --since "6 hours ago" | tail -2
archive_wal[8812]: cp: cannot create regular file
  '/srv/tramontana/backups/wal/.000000010000000000000A31.8812': No space left on device

The mechanism, which is what has to be understood. The 15 GiB lv-backups LV, encrypted with LUKS, has filled up. archive_wal.sh returns a non-zero code, and here the archiving contract comes into play: PostgreSQL does not delete a segment until the archiving command confirms success. It is correct, deliberate behaviour — losing a segment would break the PITR chain and with it every later backup — but it produces a cascade effect:

lv-backups full -> archive_wal.sh fails -> segments are not deleted
  -> pg_wal grows -> /var/lib fills up -> PostgreSQL STOPS

If /var/lib/postgresql fills up completely, PostgreSQL panics and stops. At 91% and rising, there are hours left, not days.

And a second suspect that must always be ruled out:

$ sudo -u postgres psql -c "SELECT slot_name, active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
  FROM pg_replication_slots;"
 slot_name  | active | retained
------------+--------+----------
 replica_16 | t      | 12 MB

The slot is active and only retains 12 MB: it is not the cause. If active = f with several GB retained, the cause would be a downed replica.

Resolution, in order and without losing data:

# --- STEP 1: immediate space at the archiving destination ---
# Upload to restic what is already archived and free up the oldest
$ sudo restic backup /srv/tramontana/backups/wal --tag wal-urgent
$ sudo restic check --read-data-subset=5%      # verify BEFORE deleting

# Only then, delete the WAL older than the oldest base backup we want
# to keep. pg_archivecleanup works out which ones are surplus: they are
# NEVER deleted by hand by date.
$ sudo -u postgres pg_archivecleanup -d /srv/tramontana/backups/wal \
      000000010000000000000900
pg_archivecleanup: removing file "000000010000000000000412"
...
pg_archivecleanup: 1288 files removed

$ df -h /srv/tramontana/backups
Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/vg--data-lv--backups  15G  4.2G  9.9G  30% /srv/tramontana/backups

# --- STEP 2: drain the .ready queue ---
$ sudo -u postgres psql -c "SELECT pg_switch_wal();"
$ sleep 60
$ ls /var/lib/postgresql/16/main/archive_status/*.ready | wc -l
0
$ sudo du -sh /var/lib/postgresql/16/main/pg_wal
412M	/var/lib/postgresql/16/main/pg_wal

$ sudo -u postgres psql -c "SELECT pg_stat_reset_shared('archiver');"

What you do NOT do, and it is the temptation of the moment:

Tempting action	Consequence
`rm` on files in `pg_wal`	Breaks the PITR chain and may stop the server starting. Never
`archive_mode = off`	Relieves things today and eliminates the ability to do PITR. It is giving up
`archive_command = '/bin/true'`	Silently discards segments: PITR broken with no warning
Just extending the LV	Legitimate, but without fixing the cause it recurs in three months

That third row is especially treacherous: everything appears to work, failed_count stays at zero, and the problem only shows up on the day you have to restore.

Definitive prevention, in four measures:

# 1. Automatic WAL retention, weekly, after verifying restic
$ cat /home/operator/scripts/purge_wal.sh
#!/usr/bin/env bash
set -euo pipefail
source "$(dirname "${BASH_SOURCE[0]}")/lib/common.sh"

readonly WAL_DIR=/srv/tramontana/backups/wal
readonly BASE_DIR=/srv/tramontana/backups/base

main() {
    require_command pg_archivecleanup
    require_command restic

    # Never purge below the oldest base backup being kept
    local oldest_base
    oldest_base="$(find "$BASE_DIR" -maxdepth 1 -type d -name '20*' | sort | head -1)" \
        || die 75 "there is no base backup: NOTHING is purged"
    [[ -n "$oldest_base" ]] || die 75 "no base backup; aborting"

    # And only if restic has the content safely stored
    restic check --read-data-subset=2% >/dev/null \
        || die 65 "restic does not verify: nothing is purged"

    local start_wal
    start_wal="$(awk '/^START WAL LOCATION/{print $6}' \
        "${oldest_base}/backup_label" | tr -d ')')"
    log "purging WAL older than $start_wal"
    pg_archivecleanup -d "$WAL_DIR" "$start_wal"
}
main "$@"

# 2. Alert BEFORE the problem, not when there is no way out
# /etc/systemd/system/watch-wal.service (run every 15 min)
[Service]
Type=oneshot
ExecStart=/home/operator/scripts/watch_wal.sh

# watch_wal.sh: two thresholds, two levels
#   - .ready > 20              -> warning: archiving is falling behind
#   - lv-backups usage > 75%   -> warning; > 90% -> critical
#   - failed_count > 0         -> immediately critical

# 3. A safety net in PostgreSQL itself
# Limits how much WAL a slot can accumulate before being invalidated.
max_slot_wal_keep_size = 8GB

# 4. Extend lv-backups with margin (05-04), now with data behind it
$ sudo lvextend -L +10G /dev/vg-data/lv-backups
$ sudo cryptsetup resize backups-encrypted
$ sudo resize2fs /dev/mapper/backups-encrypted

And the three lessons on method, which go into the on-call notebook:

An archiving failure is an availability incident, even though the symptom is about space: the chain ends with PostgreSQL stopped.
failed_count > 0 must be a critical alert from the very first failure, not when the disk is at 91%. It is a perfect example of a cause alert (08-06) that does deserve to exist, because its symptom takes hours to appear and by then it is too late.
The monitoring should have warned at 4,128 archived and 1 failure, not at 2,044 failures. This incident is direct justification for lesson 08-06.

Solution 2

Runbook: Quarterly point-in-time recovery drill

Document: RB-DB-02 · Version: 1.0 · Date: 2026-08-18 Owner: Operations · Frequency: quarterly (March, June, September, December) Estimated duration: 60 minutes · Risk to production: none if the steps are followed Location: a printed copy in the operations file and in ~/tramontana-infra/docs/. It is not kept solely on srv-tramontana.

0. Why this document exists

A backup that has never been restored is not a backup: it is a file about which we assume things. This drill verifies that the complete chain — base backup, WAL archiving, restic, and the procedure — works before we need it. It also measures the real time, which is the figure that underpins the agreed 2-hour RTO.

1. Prerequisites (5 min)

# Check Command Criterion

1.1 Test machine powered on virsh list --all srv-tramontana-test active

1.2 Same PostgreSQL major version ssh ... psql --version 16.x on both

1.3 Space on the test machine df -h /var/lib/postgresql ≥ 3 × the size of the DB

1.4 restic repository reachable restic snapshots --tag base | tail -3 At least 2 base backups

1.5 restic passphrase available pass restic/tramontana It is retrieved

1.6 Window announced — Marta informed by email

If 1.4 or 1.5 fail, the drill stops and an incident is declared. Not being able to reach the backups is exactly the scenario this drill is meant to uncover.

2. Choose the target (5 min)

The drill must recover to an arbitrary instant within the last 24 hours, not to the base backup's instant — recovering to the base backup does not exercise the WAL, which is half the mechanism.
# Target instant: yesterday at 15:00
$ TARGET="$(date -d 'yesterday 15:00' '+%Y-%m-%d %H:%M:%S%:z')"

# Control datum: a row that exists at that moment and that can be
# verified afterwards. It is noted down HERE, before starting.
$ sudo -u postgres psql -d tramontana -tAc \\
    "SELECT id, amount FROM app.bookings
     WHERE created_at < '$TARGET' ORDER BY created_at DESC LIMIT 1;"
1023|412.50
Note down: target 2026-08-17 15:00:00+02, control: booking 1023, amount €412.50.

3. Restore (25 min)
# 3.1 Mark the start: the stopwatch starts HERE
$ START=$(date +%s)

# 3.2 On srv-tramontana-test
$ ssh [email protected]
$ sudo systemctl stop postgresql@16-main
$ sudo -u postgres rm -rf /var/lib/postgresql/16/main
$ sudo -u postgres mkdir -m 0700 /var/lib/postgresql/16/main

# 3.3 Pull the base backup from BEFORE the target out of restic
$ restic restore latest --tag base --target /tmp/rest --host srv-tramontana
$ sudo -u postgres tar -xzf /tmp/rest/srv/tramontana/backups/base/*/base.tar.gz \\
      -C /var/lib/postgresql/16/main

# 3.4 And the WAL
$ restic restore latest --tag wal --target /tmp/rest

# 3.5 Configure the recovery
$ sudo -u postgres tee /var/lib/postgresql/16/main/postgresql.auto.conf <<EOF
restore_command = 'cp /tmp/rest/srv/tramontana/backups/wal/%f %p'
recovery_target_time = '$TARGET'
recovery_target_action = 'pause'
EOF
$ sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

# 3.6 Start and follow the process
$ sudo systemctl start postgresql@16-main
$ sudo journalctl -u postgresql@16-main -f
Expected output (if recovery stopping before... does not appear, the drill has failed):
LOG:  starting point-in-time recovery to 2026-08-17 15:00:00+02
LOG:  restored log file "0000000100000000000009F1" from archive
LOG:  recovery stopping before commit of transaction 91204, time 2026-08-17 15:00:03+02
LOG:  pausing at the end of recovery
4. Verification (10 min) — the success criteria

# Criterion Command Threshold

4.1 The server reached the target journalctl | grep 'recovery stopping' Appears, with a time ≈ the target

4.2 The control datum exists and matches SELECT amount FROM app.bookings WHERE id=1023 412.50

4.3 There is no data later than the target SELECT count(*) FROM app.bookings WHERE created_at > '$TARGET' 0

4.4 Structural integrity SELECT count(*) FROM app.bookings Consistent with production

4.5 No checksum errors journalctl | grep -ci 'checksum|corrupt' 0

4.6 Total time echo $(( $(date +%s) - START )) < 1800 s

Criterion 4.3 is the one that really validates the PITR: if there were data later than the target, the recovery did not stop where it should have and the mechanism is no use for undoing a deletion.
$ sudo -u postgres psql -d tramontana -c "
  SELECT (SELECT amount FROM app.bookings WHERE id=1023) AS control,
         (SELECT count(*) FROM app.bookings WHERE created_at > '$TARGET') AS after_target,
         (SELECT count(*) FROM app.bookings) AS total;"
 control | after_target | total
---------+--------------+--------
  412.50 |            0 | 185912
5. Cleanup (5 min)
$ sudo systemctl stop postgresql@16-main
$ sudo -u postgres rm -rf /var/lib/postgresql/16/main /tmp/rest
$ sudo virsh snapshot-revert srv-tramontana-test clean   # from the host
Never leave the test machine holding a copy of production data: it contains real personal data belonging to customers and would be outside the scope of production's security measures. It is a GDPR requirement, not a fussy habit.

6. Recording the result

It is noted in ~/tramontana-infra/docs/pitr-drills.md, even if the drill goes perfectly:
| Date       | Target              | Time   | Criteria  | Issues                          |
|------------|---------------------|--------|-----------|---------------------------------|
| 2026-08-18 | 2026-08-17 15:00+02 | 24m11s | 6/6 OK    | None                            |
| 2026-06-14 | 2026-06-13 11:00+02 | 41m02s | 5/6       | 4.6 failed: restic slow on network |
7. If something fails

Failure Probable cause Action

requested recovery stop point is before consistent recovery point The base backup is later than the target Use an earlier base backup

could not restore file ... from archive A WAL segment is missing: the chain is broken Serious incident: review pg_stat_archiver and the purge

The recovery does not stop and runs to the end recovery_target_time badly formatted, or the time zone Check the format with an explicit +02

Time > 30 min Network, LUKS encryption or decompression Analyse it and review the RTO with Marta

Checksum errors Corruption in the backup Serious incident: try another backup and check the hardware

8. Escalation

If the drill fails on 4.2, 4.3 or 4.5, a high-severity incident is declared the same day: it means that today we could not recover the data. Marta is informed and all other work stops until it is resolved.

#	Check	Command	Criterion
1.1	Test machine powered on	`virsh list --all`	`srv-tramontana-test` active
1.2	Same PostgreSQL major version	`ssh ... psql --version`	16.x on both
1.3	Space on the test machine	`df -h /var/lib/postgresql`	≥ 3 × the size of the DB
1.4	restic repository reachable	`restic snapshots --tag base \| tail -3`	At least 2 base backups
1.5	restic passphrase available	`pass restic/tramontana`	It is retrieved
1.6	Window announced	—	Marta informed by email

#	Criterion	Command	Threshold
4.1	The server reached the target	`journalctl \| grep 'recovery stopping'`	Appears, with a time ≈ the target
4.2	The control datum exists and matches	`SELECT amount FROM app.bookings WHERE id=1023`	412.50
4.3	There is no data later than the target	`SELECT count(*) FROM app.bookings WHERE created_at > '$TARGET'`	0
4.4	Structural integrity	`SELECT count(*) FROM app.bookings`	Consistent with production
4.5	No checksum errors	`journalctl \| grep -ci 'checksum\|corrupt'`	0
4.6	Total time	`echo $(( $(date +%s) - START ))`	< 1800 s

Failure	Probable cause	Action
`requested recovery stop point is before consistent recovery point`	The base backup is later than the target	Use an earlier base backup
`could not restore file ... from archive`	A WAL segment is missing: the chain is broken	Serious incident: review `pg_stat_archiver` and the purge
The recovery does not stop and runs to the end	`recovery_target_time` badly formatted, or the time zone	Check the format with an explicit `+02`
Time > 30 min	Network, LUKS encryption or decompression	Analyse it and review the RTO with Marta
Checksum errors	Corruption in the backup	Serious incident: try another backup and check the hardware

Two design notes on the runbook: it is written so that somebody who did not draft it can run it — every command is copy-pasteable and every criterion has a numeric threshold — and it includes what to do when it fails, which is the part almost every runbook omits and the only one you need on the bad day.

Solution 3

Is the database replica worth it? To: Marta Vidal · From: Systems Operations · 18 August 2026

Short answer: yes, but not for the reason it is usually set up, and the order matters. I recommend it, at a moderate investment, and above all for a benefit that is not the obvious one.

First, some good news. This week's work has improved our recovery capability far more than anticipated:

Before Now

Data we could lose in a disaster Up to 4 hours Less than 15 minutes

Can we undo an accidental deletion? No: only go back to the overnight backup Yes, to the second before

Time for a full restore ~2 hours, estimated 24 minutes, measured

That jump has not cost money: it is a technique that continuously saves the database's change log, so that we can "rewind" to any instant. I have rehearsed it and it works.

So what is the replica for? Because it solves a different problem, and it is important not to confuse them:

Problem Do the backups solve it? Does the replica solve it?

Somebody deletes data by mistake Yes, to the second before No: the deletion is copied in milliseconds

Logical data corruption Yes No

The server's disk breaks Yes, in 24 minutes Yes, in 5-15 minutes

Heavy reports slow down the website No Yes, and from day one

The server has to be updated No Yes: you work on one while the other serves

The provider has a general outage No Only if it is in another location

And here is the main argument, and it is not the one about breakdowns. Today we have report queries that take almost four seconds and compete with customers' bookings for the same machine. With a replica, those reports run on the copy, and the website stops noticing them. It is an immediate, perceptible performance improvement, not insurance against a day that may never come.

What the replica protects against and what it does not, one line each:

It protects against the main server's hardware breaking.

It protects the website's performance against the heavy reports.

It allows updates without a maintenance window.

It does not protect against a mistaken deletion, or against corrupt data: that is copied immediately. That is what the backups are for, and they remain just as necessary.

It does not protect if the problem is in the application or the network.

It does not switch over on its own. I expressly recommend that the server switch be manual, and I explain why in the next point.

Why manual, even though it sounds worse. With only two servers there is a risk the industry calls "split-brain": if communication between the two is cut but both stay alive, each concludes the other has gone down and both start accepting bookings. The result is two databases with different, incompatible information, and reconciling them can be impossible: duplicate bookings on the same cottage for the same night. Avoiding that automatically requires a minimum of five machines. With two, a person makes the decision in five or ten minutes, and those minutes are a small price against that risk.

What it would cost:

Item Cost

One more machine Equivalent to the current server

Setting it up 2-3 days, already automated along with the rest

Additional maintenance ~1 h a month

Change to the application None for the breakdown case; a small one to point the reports at the replica

My recommendation, in order of priority:

Already done, at zero cost: the backups with rewind-to-the-second and the quarterly drill that verifies them. This was the urgent part and it is done.

This quarter: the replica, justified above all by the performance of the reports and, incidentally, by being able to update without cutting the service. With a manual switch.

I do not recommend, today: automatic server switching. It requires five machines to be safe, and with two it would create a greater risk than the one it avoids.

Outstanding, and I will bring it separately: the audit table already takes up more space than all the booking data put together. We need to decide how long we keep that history, and that is your decision rather than mine, because it has legal implications for data protection.

One last thing I want to put in writing. With the replica we would still need exactly the same backups as today. It is the most common confusion in this area and the one that causes the most serious data losses: having a live copy of the data gives a sense of security that does not match reality, because it faithfully copies the mistakes as well. The replica is for breakdowns; the backups are for mistakes. You need both.

	Before	Now
Data we could lose in a disaster	Up to 4 hours	Less than 15 minutes
Can we undo an accidental deletion?	No: only go back to the overnight backup	Yes, to the second before
Time for a full restore	~2 hours, estimated	24 minutes, measured

Problem	Do the backups solve it?	Does the replica solve it?
Somebody deletes data by mistake	Yes, to the second before	No: the deletion is copied in milliseconds
Logical data corruption	Yes	No
The server's disk breaks	Yes, in 24 minutes	Yes, in 5-15 minutes
Heavy reports slow down the website	No	Yes, and from day one
The server has to be updated	No	Yes: you work on one while the other serves
The provider has a general outage	No	Only if it is in another location

Item	Cost
One more machine	Equivalent to the current server
Setting it up	2-3 days, already automated along with the rest
Additional maintenance	~1 h a month
Change to the application	None for the breakdown case; a small one to point the reports at the replica

Conclusion

PostgreSQL no longer runs with the configuration that came with the package. You have divided up the 3.8 GB with formulas you can justify: shared_buffers at 25% because there is a second cache underneath, effective_cache_size at 67% because it reserves nothing and changes the plans the optimiser chooses, work_mem at the value that survives being multiplied by connections and by operations, and random_page_cost at 1.1 because the disk is an SSD and the default describes a world of spinning platters. And you have closed the loop with 07-03: huge pages on madvise, with an explicit reservation calculated by asking the server itself instead of estimating it.

You have resolved the incident left open in 07-02, and in the right way: not by raising max_connections, but by lowering it to 60 and putting PgBouncer in transaction mode in front, where 63 application clients share four real connections. You have locked down pg_hba.conf line by line, knowing that the first match wins and that trust is never used, not on 127.0.0.1 and not "temporarily". You have created an application role that cannot create tables, and you verified it by trying. And you know that sslmode=require encrypts but verifies nothing.

Above all, the database can now be recovered. The physical backup with pg_basebackup, the WAL archiving with a script whose contract you understand — return zero only if the segment is safe — and a point-in-time recovery procedure you have run from start to finish, with recovery_target_action = 'pause' so you can look before committing. The RPO has gone from 4 hours to minutes without spending a euro, and the quarterly drill is written so that somebody other than you can run it. You know about VACUUM, bloat, transaction-counter wraparound and why an idle in transaction transaction three days old is capable of bringing down an entire database. And you can read an EXPLAIN (ANALYZE, BUFFERS), which is what turned a 3,781 ms query into a 182 ms one.

In 08-03 the setting changes completely, and deliberately. You are going to build a media server for your home: Jellyfin on your own hardware, with redundant storage, hardware acceleration for transcoding, shares for the family's devices and access from outside. It is the project where you confirm that nothing you have learned was "a business-server thing": the same UUIDs in fstab, the same hardened systemd unit, the same keys in keyrings, the same verified backups and the same smartctl watching the disks. With two differences that matter at home and not at work: power consumption and noise. And with a warning worth reading before you start, about what content it is legitimate to keep there.

Configuring a Database Server

Contents

Objective, prerequisites and starting state

PostgreSQL's process and file architecture

Memory tuning with reasoned formulas

Huge pages: the connection with 07-03

Connections: why a pool and not a bigger number

PgBouncer in transaction mode

Authentication: pg_hba.conf field by field

TLS on the connections

Roles and permissions with least privilege

Backups: logical, physical, WAL and point-in-time recovery

WAL archiving

The physical backup

Point-in-time recovery, with the complete procedure

Maintenance: vacuum, wraparound and reindexing

Tuning autovacuum where it is needed

Wraparound, and why it is an emergency

Diagnostics: where the time goes

pg_stat_statements: the query that consumes the most time

EXPLAIN (ANALYZE, BUFFERS)

Slow queries in the log

Indexes that are missing and indexes that are surplus

Streaming replication

Automation with Ansible

Daily operation

Common Mistakes and Tips

Exercises

Exercise 1

Exercise 2

Exercise 3

Solutions

Runbook: Quarterly point-in-time recovery drill

0. Why this document exists

1. Prerequisites (5 min)

2. Choose the target (5 min)

3. Restore (25 min)

4. Verification (10 min) — the success criteria

5. Cleanup (5 min)

6. Recording the result

7. If something fails

8. Escalation

Conclusion

Linux Course: From Beginner to System Administrator

Module 1: Introduction to Linux

Module 2: Basic Linux Commands

Module 3: Advanced Command-Line Skills

Module 4: Shell Scripting

Module 5: System Administration

Module 6: Networking and Security

Module 7: Advanced Topics

Module 8: Practical Projects

`pg_stat_statements`: the query that consumes the most time

`EXPLAIN (ANALYZE, BUFFERS)`