Backup Unit Succeeds Manually but Fails in the Patch Window
On Ubuntu 22.04, backup.service (Type=oneshot, User=backup, Wants=network.target, After=network.target) runs /usr/local/bin/pg-backup.sh, and backup.timer triggers it daily at 02:30. The script runs pg_dump -Fc against pg-prod-01 at 10.20.0.15:5432, then rclone sync to object storage. For the last three Tuesday patch windows the job died at 02:30:07 with connection to server at "10.20.0.15", port 5432 failed: Connection refused and status 1. Running systemctl start backup.service by hand at 09:00 the same day completes successfully in 41 minutes. The patch script reboots both hosts at 02:25, and pg-prod-01 needs roughly four minutes of crash recovery before it accepts connections. The window is open until 06:00. Separately, the most recent successful run left a partially uploaded archive in the bucket after a brief network interruption, and the team only noticed because a restore drill failed.
1. Explain the failure mechanism, and why Wants=network.target plus After=network.target does not prevent it.
Read the worked answer
The failure is an ordering-of-events problem, not a permissions or script bug. The backup host boots at 02:25 and the timer fires five minutes later. pg-prod-01 is still replaying WAL and not listening on 5432, so the connection is refused within seven seconds and the unit exits 1. Nothing retries.
network.target is reached as soon as the local network stack is configured; it says nothing about whether a route exists, whether DNS resolves, or whether a remote peer is accepting connections. Wants= only pulls a unit into the transaction; After= only orders activation within that transaction. Neither can express "the database on another machine is ready", because systemd has no visibility into a remote service. Swapping in network-online.target is a genuine improvement - it waits for the local interface to be routable, which network.target does not - but it still does not guarantee the peer is up, especially when the peer is itself rebooting at the same minute.
So the fix must live in two places: the schedule (do not reboot the database and run the backup in the same five-minute slice) and the script (wait for readiness with a deadline instead of failing on the first refused connection).
2. Which commands would you use to reconstruct the timeline and confirm the diagnosis, and what specifically would you look for?
Read the worked answer
On the backup host: journalctl -b -1 -u backup.service -o short-precise to see the exact attempt and exit time, systemctl show backup.service -p ExecMainStatus -p Result, and systemd-analyze critical-chain backup.service to see what it actually waited on. The tell is the elapsed time between the timer firing and the failure: seven seconds means one attempt with no retry, whereas a timeout would show a much longer gap. journalctl -u backup.timer confirms the trigger time was 02:30 and not delayed.
On the database host: journalctl -b -1 -u postgresql plus uptime -s or last reboot to find when recovery finished and the listener started. If the listener came up at 02:33 and the backup failed at 02:30:07, the timeline is proven.
Also check systemd-analyze verify /etc/systemd/system/backup.service for directive typos, and compare the unit file's mtime against the last change review - a silently edited unit is a common reason a job "used to work".
3. Rewrite the approach for both the scheduling and the script, and address the partial-upload problem. Justify each change.
Read the worked answer
Scheduling: move the backup timer to 03:30 (or run the patch script first and trigger the backup via systemctl start at the end of the window). Add Wants=network-online.target and After=network-online.target as a correct-but-insufficient first step; the real change is a 30-minute deadline.
Script: wrap the dump in a readiness loop using pg_isready -h 10.20.0.15 -p 5432 with backoff, aborting after the deadline with a distinct exit code so monitoring can tell "database never came up" from "dump failed". Do not use a bare sleep 300. Keep the script idempotent: write to orders-$(date +%F).dump.partial, rename only on success, so a retry cannot overwrite a good archive. Where the systemd version supports it, Restart=on-failure with RestartSec=300 and a StartLimitBurst gives retries at the unit level; verify with systemd-analyze verify rather than assuming, since restart semantics for Type=oneshot differ across versions.
Uploads: sync to a temp prefix then server-side copy/rename, or run rclone check --size-only after upload so a partial archive is never treated as valid. Exit non-zero on any mismatch so the alert fires the same night.