CAG Backup Inventory

Home server (10.0.0.3 / 192.168.86.69) — component-by-component

Every component running on this server, and what actually backs it up. Read these cards first — the scripts behind them are explained afterward.

DigitalOcean prod MySQL

True source — 8 DBs: appsecure, cayyappadb, defaultdb, gurukulamdb, schoolmsysdb, schoolmsysdbnew, utilitydb, walkwithmedb

Backed up

Runs at: Remote — DigitalOcean managed DB, reached via SSH tunnel through the VPS
Script: do-backup.sh
Location: /home/storageserver/scripts/do-backup.sh
Destination: /home/storageserver/backups/do_dump_*.sql.gz + Google Drive at cag-backups/ (the root of the folder — not inside server/ or dev/)
Schedule: Hourly

prodmysql

Docker, port 3309 — local mirror, refreshed from the dump above

Partial refreshed hourly, not "backed up" itself; also swept as a raw volume tar

Runs at: Docker container prodmysql
Script: do-backup.sh (restore step) + server-backup.sh (volume tar)
Location: see below
Destination: Local restore only, not a SQL dump of its own
Schedule: Hourly (refresh) / Daily 03:00 (volume tar)

System MySQL

Host-level mysql.service, port 3306

Backed up

Runs at: Host process, not Docker
Script: server-backup.sh (Step 1)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: databases/mysql-prod-all.sql.gz in the combined archive + Drive
Schedule: Daily 03:00

devmysql

Docker, port 3308 — dev databases

Backed up (twice over)

Runs at: Docker container devmysql
Script: devmysql-backup.sh + also server-backup.sh (Steps 1 & 2)
Location: /home/troubleshoot/scripts/devmysql-backup.sh
Destination: /home/backups/storagebackup/dev/ + Drive; also in the combined server archive
Schedule: Daily 02:00 (dedicated) / Daily 03:00 (combined)

servermanager-db

Docker Postgres, port 5433

Backed up

Runs at: Docker container servermanager-db
Script: server-backup.sh (Step 1, explicit pg_dumpall)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: databases/postgres-all.sql.gz in the combined archive + Drive
Schedule: Daily 03:00

postgres-dev-public

Docker Postgres, port 5434

Partial no explicit pg_dump — script only names servermanager-db; caught only by the generic all-volumes tar

Runs at: Docker container postgres-dev-public
Script: server-backup.sh (Step 2 only)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: Raw volume tar in the combined archive + Drive
Schedule: Daily 03:00

postgres-prod-public

Docker Postgres, port 5435

Partial same as above — volume-tar only, not a portable SQL dump

Runs at: Docker container postgres-prod-public
Script: server-backup.sh (Step 2 only)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: Raw volume tar in the combined archive + Drive
Schedule: Daily 03:00

MS SQL Server

mssql-server.service

Not backed up

Runs at: Host process, not Docker, not under /home
Script: — none —

All Docker named volumes

App containers: cagappnative, cagayyappa, servermanageui, yathraapi, manageserverapi, cagattendanceapi, etc.

Backed up

Runs at: Docker
Script: server-backup.sh (Step 2)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: docker-volumes/<name>.tar.gz in the combined archive + Drive
Schedule: Daily 03:00

Nginx configs

Host /etc/nginx + container bind-mounted nginx.conf files

Backed up

Runs at: Host + Docker bind mounts
Script: server-backup.sh (Step 3)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: nginx-configs/ in the combined archive + Drive
Schedule: Daily 03:00

System configs

wireguard, ufw, netplan, cron.d, sudoers.d, resolv.conf, hosts, both crontabs

Backed up

Runs at: Host /etc/
Script: server-backup.sh (Step 4)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: system-configs/ in the combined archive + Drive
Schedule: Daily 03:00

/home project folders

appserver, sysadmin, devserver, storageserver [scripts/logs only], troubleshoot, document

Backed up

Runs at: /home/<user>/
Script: server-backup.sh (Step 5)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: home/<user>.tar.gz in the combined archive + Drive
Schedule: Daily 03:00

mediaserver

/home/mediaserver/appmedia/ — actual uploaded media files, incl. cagmedia uploads

Backed up — but only via the generic "any other /home dir" catch-all, no size/type exclusions like the dev folders get

Runs at: /home/mediaserver/
Script: server-backup.sh (Step 5, catch-all loop)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: home/mediaserver.tar.gz in the combined archive + Drive
Schedule: Daily 03:00

/home/iapp

Not backed up — explicitly excluded from the catch-all loop, no dedicated step either

Runs at: /home/iapp/
Script: — none —

CAG Monitor

This dashboard itself — cag-monitor.py, watchdog, wiki docs

Backed up — covered as part of the storageserver folder tar (only storageserver/backups/ is excluded)

Runs at: /home/storageserver/monitoring/
Script: server-backup.sh (Step 5)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: home/storageserver.tar.gz in the combined archive + Drive
Schedule: Daily 03:00

VPS network/firewall configs

Pulled from the VPS, not local to this server

Backed up

Runs at: Remote — 77.37.62.240, via SSH
Script: server-backup.sh (Step 6)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: proxy/ in the combined archive + Drive
Schedule: Daily 03:00

System state snapshot

docker ps/images/networks, packages, enabled services, network state — reference only, not restorable data

Captured

Runs at: Host
Script: server-backup.sh (Step 7)
Location: /home/troubleshoot/scripts/server-backup.sh
Destination: state/ in the combined archive + Drive
Schedule: Daily 03:00

Three things worth fixing on the home server:

MS SQL Server has no backup at all — it isn't Docker and isn't under /home, so none of the three scripts touch it.
postgres-dev-public and postgres-prod-public only get a raw Docker-volume tar, not a portable pg_dump — restorable, but only by attaching the exact same Postgres version/container, not by importing a SQL file. server-backup.sh's PG_CONTAINER variable only names servermanager-db.
/home/iapp is silently skipped — worth checking what's in there and whether it matters.

Home server — the three scripts behind the table above

do-backup.sh — hourly, 7-day local retention

Opens an SSH tunnel through the VPS to reach the DigitalOcean-managed MySQL, dumps all 8 prod databases, verifies the dump, wipes the local prodmysql Docker container, restores the fresh dump into it, then uploads the dump to gdrive:cag-backups/ and prunes Drive copies older than 30 days.

/home/storageserver/scripts/do-backup.sh
Log: /home/storageserver/logs/backup.log
Cron: 0 * * * *  (sysadmin crontab)

devmysql-backup.sh — daily 02:00, 7-day local retention

mysqldump --all-databases against the devmysql container, gzipped, uploaded to gdrive:cag-backups/dev/, 30-day Drive prune.

/home/troubleshoot/scripts/devmysql-backup.sh
Log: /home/backups/storagebackup/dev/backup.log
Cron: 0 2 * * *  (sysadmin crontab)

server-backup.sh — daily 03:00, 7-day local retention

The broadest job, in 8 explicit steps: (1) database dumps — system MySQL + devmysql + servermanager-db Postgres only, (2) every Docker named volume as a raw tar, (3) nginx configs, (4) system configs, (5) /home project folders (with dev-artifact exclusions for the named folders, but a size-unaware catch-all for anything else, including mediaserver), (6) VPS network/firewall configs pulled over SSH, (7) a system state snapshot, (8) manifest + combine + upload to gdrive:cag-backups/server/.

/home/troubleshoot/scripts/server-backup.sh
Output: /home/backups/storagebackup/server/server_backup_YYYYMMDD_HHMMSS.tar.gz
Also uploaded to: gdrive:cag-backups/server/
Cron: 0 3 * * *  (sysadmin crontab)
Usage: ./server-backup.sh [--list|--clean|--size]

restore-mysqlprod240.sh — manual only, not scheduled

Pushes the latest complete home-server prod dump to the VPS's standalone mysqlprod240 MySQL instance. Run by hand when the VPS copy needs refreshing.

/home/storageserver/scripts/restore-mysqlprod240.sh [dump_file]
Log: /home/storageserver/logs/restore-mysqlprod240.log

Cleanup jobs — daily 04:00–04:50

Time	Script	Scope
04:00	`prod-backup-cleanup.sh`	Local prod dumps
04:10	`devsql-backup-cleanup.sh`	Local dev dumps
04:20	`server-backup-cleanup.sh`	Local server backups
04:30	`prod-backup-cleanup-drive.sh`	Google Drive prod copies
04:40	`devsql-backup-cleanup-drive.sh`	Google Drive dev copies
04:50	`server-backup-cleanup-drive.sh`	Google Drive server copies

Possible redundancy: the crontab labels these "3-day retention," but each backup script's own header says 7-day, and the inline Drive-prune step already built into each backup script deletes Drive copies older than 30 days by itself. Three different retention numbers for the same data — worth reconciling which is authoritative.

log-cleanup.sh — weekly, Sunday 05:00

Trims log files to the last 30 days of entries. Not a backup job, shares the same crontab block.

VPS (77.37.62.240)

mysqlprod240

Standalone MySQL, port 3306

Manual only

By what: Populated by restore-mysqlprod240.sh, run from the home server
Schedule: Not scheduled

postgres-dev-public / postgres-prod-public

Public-facing Postgres copies

Not backed up

By what: — none —

Everything else on this box

nginx configs, SSL certs

Not backed up

By what: — none —

The VPS runs no backup jobs of its own — every mention of it above is either things pulled from it by the home server's server-backup.sh (network/firewall config only), or things pushed to it manually.

prod-gurukulam (85.209.95.95)

Local MySQL — schoolmsysdb

Confirmed live — active connections from apiuser / api1

Not backed up

By what: — none —

Local MySQL — mydatabase

Appears unused, no active connections

Not backed up

By what: — none —

App jars, deploy scripts, nginx configs

N/A

By what: Not versioned or backed up server-side; recoverable by redeploying from source

App logs

N/A

By what: Rotated (not backed up) by rotate_logs.sh
Schedule: Daily 23:59

This server has zero backup coverage. The live schoolmsysdb database is the most important gap — if this server is lost, that data is gone with no recovery path.

Google Drive — current status

Account used for backups: caginfoapp@gmail.com — this project's dedicated service account (also used as the SMTP sender for monitoring alerts in watchdog.py and for mail in cagdevattendanceapi's config). It owns the cag-backups folder that every Drive upload above targets. Not a personal account — no password is recorded here; whoever manages this account's credentials should keep them in a password manager, not in this repo.

Fixed 2026-08-11. The shared OAuth token in /home/sysadmin/.config/rclone/rclone.conf (remote name gdrive) had expired (invalid_grant), silently breaking the offsite copy for every home-server job — every run failed in the surviving logs going back to 2026-07-10 (the oldest entry still on disk; log-cleanup.sh trims logs to ~30 days weekly, so the true start date is unknown and could be earlier). This lines up with what Google Drive itself shows: the server folder's newest file was from April 23 — the Drive-side cleanup scripts share the same broken token, so old files never got pruned either, which is why that stale file was still sitting there instead of being deleted by the 30-day retention rule. Local backups were unaffected throughout — this only broke the offsite copy. Reconnected via rclone config reconnect gdrive:, signed in as caginfoapp@gmail.com. Verified end-to-end by running devmysql-backup.sh manually — it uploaded successfully and the file was confirmed present in gdrive:cag-backups/dev/ via rclone lsl.

One thing found along the way: the custom Google OAuth client originally configured in rclone.conf (client_id/client_secret) had a redirect_uri_mismatch — it was never set up with http://127.0.0.1:53682/ as an authorized redirect URI in Google Cloud Console, so any future reconnect using that custom app would fail the same way. Worked around by removing client_id/client_secret from rclone.conf entirely, so it now uses rclone's own built-in OAuth app instead (functionally identical — same Drive account, just a different app name on the consent screen). Old config backed up at rclone.conf.bak-20260810 on the home server.

How to redo this in future (requires an interactive browser — cannot be done via a plain SSH command):

ssh -L 53682:localhost:53682 home-proxy-server
rclone config reconnect gdrive: --config /home/sysadmin/.config/rclone/rclone.conf
# open the printed 127.0.0.1:53682 URL in a PRIVATE/incognito browser window
# on your Mac, and sign in specifically as caginfoapp@gmail.com (not your
# personal account) — otherwise Google will default to whichever account
# is already logged in.

# verify:
rclone lsd gdrive: --config /home/sysadmin/.config/rclone/rclone.conf

Every job above shares the same gdrive: remote, so a single reconnect fixes all of them at once — no per-script changes needed.

External USB drive — third leg of 3-2-1

Added 2026-08-11: a 128GB Lexar USB drive (116GB formatted), reformatted from its factory FAT32 (which caps individual files at 4GB — too small for the 7.6GB server_backup_*.tar.gz archives) to ext4, mounted permanently at /mnt/cagbackup.

Commands to check the mount

# is it mounted, and how much space is used/free?
df -h /mnt/cagbackup

# filesystem + mount details
lsblk -f
mount | grep sdb

# fstab entry (persists across reboots)
grep cagbackup /etc/fstab

Commands to browse the synced files

# top-level folders
ls -la /mnt/cagbackup/backup/

# size of each folder
du -sh /mnt/cagbackup/backup/*/

# a specific folder
ls -lh /mnt/cagbackup/backup/dodb/

# everything, recursively
find /mnt/cagbackup/backup/ -maxdepth 2 -type f

Folder mapping

dodb

Wired

Synced from: /home/storageserver/backups/ — DigitalOcean prod dumps

homeserverdb

Wired

Synced from: /home/backups/storagebackup/dev/ — devmysql dumps (the home server's own DB)

server

Wired

Synced from: /home/backups/storagebackup/server/ — full disaster-recovery archives

scripts

Wired

Synced from: /home/storageserver/scripts/ + /home/troubleshoot/scripts/, kept in separate subfolders

media

Wired

Synced from: /home/mediaserver/appmedia/

component-backup

Reserved, unmapped

Note: Nothing currently produces standalone per-component output to sync here — see README.txt in that folder

sync-external-drive.sh — daily 05:00

Rsyncs the folders above from their existing local backup locations onto the USB drive. Doesn't create any backups itself — just mirrors what do-backup.sh, devmysql-backup.sh, and server-backup.sh already produced. Skips entirely (exit 1) if the drive isn't mounted, so an unplugged drive won't break anything else.

/home/troubleshoot/scripts/sync-external-drive.sh
Log: /home/storageserver/logs/sync-external-drive.log
Cron: 0 5 * * *  (sysadmin crontab)

Verified working via a real scheduled run on 2026-08-11 04:00 (before the reschedule below) — completed successfully end-to-end, confirming the whole pipeline works unattended, not just under manual testing.

Two issues found and fixed on 2026-08-11:

I/O contention: running this at the same time as a large Google Drive upload (or server-backup.sh itself) causes real disk I/O contention and noticeably slows the whole server — observed directly when a manual server-backup.sh test run and this sync overlapped (load average hit 4.2, multiple processes stuck in uninterruptible I/O wait).
Schedule collision: originally scheduled for 04:00, which collided exactly with prod-backup-cleanup.sh (also 04:00) — the cleanup job deleted an old dump file at the same instant this script's dodb rsync was reading the directory, causing a harmless but avoidable file has vanished warning. Moved to 05:00, after all six cleanup jobs (04:00–04:50) finish.