Maintenance Scheduling and Runbooks

Write a bloat-alert query, a maintenance script, and schedule both with real OS-level cron — no pg_cron required

pg_cron is the tool this curriculum would normally reach for here — and it is not installed on this image at all, with no network access to add it. That does not mean scheduled maintenance is out of reach: every Unix-like system already ships a real, working scheduler, and this training image is no exception. This lab uses actual crontab and crond — the real thing production servers have relied on for decades, long before pg_cron existed — to schedule a real maintenance script against a real database.\n\nThere is one genuine wrinkle specific to this environment worth understanding rather than working around silently: crond has to run as root to read a crontab file that the setuid crontab binary creates owned by root. This lab shows that failure mode briefly, explains exactly why it happens, and then runs crond the way that actually works — via sudo, the same root-access mechanism as-postgres has been quietly using all along.

A Bloat-Alert Query

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 0
  AND n_dead_tup::float / nullif(n_live_tup + n_dead_tup, 0) > 0.20
ORDER BY dead_pct DESC;

Every column this query reads has already been used elsewhere in this course — n_live_tup and n_dead_tup from Lab 2.4.1's statistics collector work. A row appearing here at all is the alert; a monitoring system just needs to run this on a schedule and page someone when it returns anything.

A Script, Verified Directly Before It Is Ever Scheduled

echo '#!/bin/sh' > /tmp/scripts/weekly_maintenance.sh
echo "/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c 'VACUUM ANALYZE runbook_demo;'" >> /tmp/scripts/weekly_maintenance.sh
chmod +x /tmp/scripts/weekly_maintenance.sh
/tmp/scripts/weekly_maintenance.sh   # run it directly first — never trust an unscheduled script to a schedule

Real Cron, Since pg_cron Is Not Here

mkdir -p /var/spool/cron/crontabs
echo "* * * * * /tmp/scripts/weekly_maintenance.sh" | crontab -
crontab -l

sudo -c 'crond -L /tmp/cronlog.txt -l 8'

crond needs root to read the crontab file crontab itself just created — on a real server this is invisible, because a root-owned crond service is already running from boot. On this training VM, no such service starts automatically, so sudo -c starts it explicitly, the same way as-postgres has been quietly running commands as the postgres OS user all along.

Bulk-Load ANALYZE: Triggered by the Load, Not the Clock

#!/bin/sh
/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c "COPY big_table FROM '/tmp/import.csv' CSV;"
/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c "ANALYZE big_table;"

A weekly VACUUM ANALYZE and a post-load ANALYZE solve different problems: the weekly job catches steady, ongoing bloat; the load hook exists because a single enormous bulk import can invalidate a table's statistics all at once, right when a burst of new queries against that fresh data is most likely — waiting for the next scheduled run is often too late.

Why crond Needs Root Here Specifically

the crontab command is installed setuid-root, so the crontab file it writes for any user is owned by root, mode 600 — readable only by root, regardless of which user ran crontab to create it. A crond process running as an ordinary user has no permission to open that file at all. Running cron as root (the only account with read access) is not a workaround specific to this training image; it is exactly how a real system's cron service is normally already running, from boot, as root.

Scheduled Maintenance vs Event-Triggered Maintenance

A weekly VACUUM ANALYZE via cron is scheduled: it runs on a clock, regardless of what happened since the last run. A post-bulk-load ANALYZE hook is event-triggered: it runs because a specific event (the load finishing) just happened, independent of any clock. Both matter, and neither substitutes for the other — a schedule cannot know a bulk load just happened, and an event hook cannot catch slow, steady bloat that accumulates between distinct events.

🚨 Write the Bloat-Alert Query

Write a query that flags any table with more than 20% dead tuples, and confirm it correctly flags the bloated demo table.

/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c "SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct FROM pg_stat_user_tables WHERE n_live_tup + n_dead_tup > 0 AND n_dead_tup::float / nullif(n_live_tup + n_dead_tup, 0) > 0.20 ORDER BY dead_pct DESC;"

student@lab:~$ /usr/local/bin/psql -h /tmp -U postgres -d beer_db -c "SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct FROM pg_stat_user_tables WHERE n_live_tup + n_dead_tup > 0 AND n_dead_tup::float / nullif(n_live_tup + n_dead_tup, 0) > 0.20 ORDER BY dead_pct DESC;" SET schemaname | relname | n_live_tup | n_dead_tup | dead_pct ------------+--------------+------------+------------+---------- public | runbook_demo | 6667 | 3333 | 33.3 (1 row)

📝 Write and Test the Maintenance Script

Write a real shell script that runs VACUUM ANALYZE, and run it directly first — never trust an unverified script to a schedule.

mkdir -p /tmp/scripts

echo '#!/bin/sh' > /tmp/scripts/weekly_maintenance.sh

echo "/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c 'VACUUM ANALYZE runbook_demo;' >> /tmp/maintenance.log 2>&1" >> /tmp/scripts/weekly_maintenance.sh

chmod +x /tmp/scripts/weekly_maintenance.sh

/tmp/scripts/weekly_maintenance.sh

student@lab:~$ mkdir -p /tmp/scripts student@lab:~$ echo '#!/bin/sh' > /tmp/scripts/weekly_maintenance.sh student@lab:~$ echo "/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c 'VACUUM ANALYZE runbook_demo;' >> /tmp/maintenance.log 2>&1" >> /tmp/scripts/weekly_maintenance.sh student@lab:~$ chmod +x /tmp/scripts/weekly_maintenance.sh student@lab:~$ /tmp/scripts/weekly_maintenance.sh student@lab:~$ cat /tmp/maintenance.log

⏰ Schedule It With Real Cron

Since pg_cron is not installed, install this script into the real OS crontab.

echo "* * * * * /tmp/scripts/weekly_maintenance.sh" | crontab -

crontab -l

student@lab:~$ mkdir -p /var/spool/cron/crontabs student@lab:~$ echo "* * * * * /tmp/scripts/weekly_maintenance.sh" | crontab - student@lab:~$ crontab -l * * * * * /tmp/scripts/weekly_maintenance.sh

🚀 Start crond — as Root

Clear the log produced by the manual run, then start the cron daemon as root with sudo. The scheduled run must create fresh evidence.

rm -f /tmp/maintenance.log && sudo cron

student@lab:~$ sudo -c 'crond -L /tmp/cronlog.txt -l 8'

✅ Confirm It Genuinely Fired

Wait for the schedule to trigger, then confirm the job actually ran — not just that the crontab entry exists.

sleep 65

cat /tmp/maintenance.log

student@lab:~$ sleep 65 student@lab:~$ cat /tmp/maintenance.log SET VACUUM SET VACUUM

📦 Write a Bulk-Load ANALYZE Hook

Write a script that runs ANALYZE immediately after a bulk load — triggered by the load finishing, not by any clock.

echo '#!/bin/sh' > /tmp/scripts/after_bulk_load.sh

echo "/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c 'INSERT INTO runbook_demo (payload) SELECT repeat(chr(65+((g%26))),20) FROM generate_series(1,5000) g;'" >> /tmp/scripts/after_bulk_load.sh

echo "/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c 'ANALYZE runbook_demo;'" >> /tmp/scripts/after_bulk_load.sh

chmod +x /tmp/scripts/after_bulk_load.sh

/tmp/scripts/after_bulk_load.sh

student@lab:~$ echo '#!/bin/sh' > /tmp/scripts/after_bulk_load.sh student@lab:~$ echo "/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c 'INSERT INTO runbook_demo (payload) SELECT repeat(chr(65+((g%26))),20) FROM generate_series(1,5000) g;'" >> /tmp/scripts/after_bulk_load.sh student@lab:~$ echo "/usr/local/bin/psql -h /tmp -U postgres -d beer_db -c 'ANALYZE runbook_demo;'" >> /tmp/scripts/after_bulk_load.sh student@lab:~$ chmod +x /tmp/scripts/after_bulk_load.sh student@lab:~$ /tmp/scripts/after_bulk_load.sh SET INSERT 0 5000 SET ANALYZE

Lab 2.6.5 complete — and Block 2.6 complete. A real, scheduled maintenance process, verified running end to end:\n\n\n Bloat-alert query : ✅ correctly flagged a genuinely bloated table\n Maintenance script : ✅ written, tested directly before scheduling\n Real crontab : ✅ pg_cron absent — OS-level cron used instead\n cron as root : ✅ the one real wrinkle, explained and solved\n Job confirmed firing : ✅ fresh maintenance log confirms execution\n Bulk-load hook : ✅ event-triggered, not clock-triggered\n

Enable JavaScript to run the live terminal and track your progress.