Automate Database Backups Across a Server Fleet with AI: 7 Recipes for 2026

If you are still SSH-ing into four boxes to confirm last night's dump actually

ran, automated database backups across servers are exactly the kind of chore

that should be running on autopilot — not eating your evenings. In this guide we

wire up postgres backup automation for a small fleet (one primary, two

streaming replicas, an offsite backup box) and drive the whole thing from a

single AI interface using the MCP server

Quick summary of the seven recipes

Why and when to use this

Database fleets are where "I'll just SSH in real quick" goes to die. A single

forgotten retention prune fills a disk; a silent pg_dump failure isn't noticed

until the day you need the dump; a schema migration applied to the primary but

not the replicas causes mysterious read errors hours later. Here's where driving

the fleet through one AI interface pays off:

• 
  Cron that survives the network. schedule_add installs the cron on the
  host itself, so your nightly 0 3 * * * * dump runs even if the relay link
  is down, your laptop is closed, or the AI session has long ended. The schedule
  is not tied to your connection.


• 
  Per-host truth in one call. fleet_exec and file_stat give you the dump
  size and mtime on all four boxes in a single round trip instead of four SSH
  sessions. One failing host does not sink the batch — it just shows up red in
  the aggregated result.


• 
  Replica maintenance without drift. fleet_git + fleet_exec against the
  db,replica tag apply the same migration to both replicas and report each one
  separately, so a half-applied change is impossible to miss.


• 
  Safe-by-default reads. plan mode makes a host read-only, so you can pull
  a prod .env or run pg_stat_replication during an incident with no chance of
  fat-fingering a write.


• 
  Cross-platform reach. target="os:linux" hits the Postgres boxes; a
  Windows SQL Server host joins the same room and answers os:windows targeting
  with a sqlcmd backup instead of pg_dump.

This is not a replacement for a declarative backup orchestrator or a managed

RDS-style service. It shines for self-hosted databases, small-to-mid fleets,

dev/staging tiers, and the operational glue around backups that nobody wants to

maintain in YAML.

Recipe 1 — Tag db hosts and look around in plan mode

Before automating anything, look first and touch nothing. Set every database

host to plan mode — read-only read_file, git_status, and safe exec

only — and get oriented.

1. Flip the database hosts to read-only.


2. Confirm Postgres is up and check current data directory size.


3. Eyeball where existing backups (if any) land.
   

set_mode    target="db,replica"   mode=plan

set_mode    agent_id=db-primary   mode=plan



fleet_exec  target="db,primary"   command="systemctl is-active postgresql && psql -tAc 'SELECT version();'"

fleet_exec  target="db,primary"   command="du -sh /var/lib/postgresql/16/main; df -h /var"

list_dir    agent_id=backup-box   path=/srv/backups

Tip: plan mode still allows a read-only exec, so psql -tAc 'SELECT ...'

and du -sh work fine. The moment a command would write, the agent rejects it.

Start every incident here — you literally cannot break prod from plan.

The aggregated reply comes back per host: you'll see db-primary reporting

active and a 240 GB data directory, while a replica that's catching up might

report differently. That per-host shape is the whole point — no guessing which

box you're looking at.

Recipe 2 — Schedule a nightly pg_dump with retention

This is the core of scheduled pg_dump automation. We install a host-local

cron on db-primary that dumps the database, gzips it, and prunes anything

older than 14 days. Because schedule_add writes the cron to the host, it

keeps firing at 03:00 every night whether or not anyone is connected.

Remember the cron is a 6-field spec — sec min hour day month dow — so

"3 AM nightly" is 0 0 3 * * *.

1. Switch the primary to edit so the agent may create the dump file and the
   wrapper script.


2. Add the nightly dump-and-prune schedule.


3. List schedules to confirm it registered.
   

# the command the cron runs on db-primary (one line, gzip + 14-day prune)

pg_dump -Fc -U postgres app_prod \

  | gzip > /srv/backups/app_prod-$(date +\%F).sql.gz \

  && find /srv/backups -name 'app_prod-*.sql.gz' -mtime +14 -delete

set_mode      agent_id=db-primary  mode=edit



schedule_add  agent_id=db-primary  name=nightly-pgdump \

  cron="0 0 3 * * *" \

  command="pg_dump -Fc -U postgres app_prod | gzip > /srv/backups/app_prod-$(date +%F).sql.gz && find /srv/backups -name 'app_prod-*.sql.gz' -mtime +14 -delete"



schedule_list agent_id=db-primary

The -Fc custom format gives you a compressed, pg_restore-friendly archive; a

240 GB cluster typically lands around a 30–45 GB gzipped dump depending on how

much of it is indexes and TOAST. The -mtime +14 -delete clause keeps roughly

two weeks of dumps and nothing more, so the disk doesn't quietly fill.

Note: for a mysqldump cron the only thing that changes is the command —

mysqldump --single-transaction app_prod | gzip > ... — wrapped in the exact

same schedule_add. And for the Windows SQL Server host you'd schedule a

sqlcmd -Q "BACKUP DATABASE ..." instead. The scheduler, retention, and

verification flow are identical across all of them.

To remove the schedule later (say you've migrated to WAL archiving):

schedule_remove  agent_id=db-primary  name=nightly-pgdump

Recipe 3 — Verify backups are fresh across the fleet

A backup you never check is a backup you don't have. This recipe answers the only

question that matters at 9 AM: did last night's dump actually run, and is it the

right size, on every host? We combine fleet_exec to find the newest dump with

file_stat to read its exact size and mtime.

1. Find the newest dump file on each db host.


2. 
   file_stat that file for an authoritative size and modification time.


3. Flag anything older than ~26 hours as stale.
   

fleet_exec  target="db,primary"  \

  command="ls -t /srv/backups/app_prod-*.sql.gz 2>/dev/null | head -n1"



file_stat   agent_id=db-primary  path=/srv/backups/app_prod-2026-06-18.sql.gz



fleet_exec  target="db,primary"  \

  command="find /srv/backups -name 'app_prod-*.sql.gz' -mmin -1560 -printf '%p %s bytes\n' || echo NO_FRESH_BACKUP"

file_stat returns the size and mtime directly, which is more trustworthy than

parsing ls output. The -mmin -1560 window (26 hours) gives the 03:00 job some

slack — anything that didn't write a fresh file in that window prints

NO_FRESH_BACKUP, and because results are aggregated per host, a replica

that skipped its dump stands out instantly while the healthy boxes report a clean

~42 GB file.

Important: size is your early-warning signal. A dump that suddenly drops

from 42 GB to 200 KB almost always means pg_dump errored out mid-run (bad

credentials, a dropped connection, a full disk) and gzipped only the error.

Watching size over time catches silent failures that a green exit code hides.

You can fold this into a once-a-day self-check by wrapping the find in another

schedule_add that appends to a log — same host-local cron pattern as Recipe 2.

Also read: ·

source and docs