VPS Backup Design: Database Dumps + rsync

Synchronizing container directories alone is not a complete backup. Databases need consistent logical exports, while configuration, certificates, and attachments are good candidates for incremental rsync synchronization.

Layers

Layer	Method	Contents
Database	mysqldump, pg_dumpall	Data, users, privileges
Files	rsync -az	Config, certificates, attachments
Verification	Lists, sizes, restore sample	Prove usability

Do not copy a live database data directory like ordinary files. Prefer dumps for MySQL/PostgreSQL, and stop SQLite writers or use the application's backup function.

Manual backup

BACKUP="$HOME/backup/server-1"
mkdir -p "$BACKUP"/{app,authentik,proxy,db-dump}

ssh root@<server> \
  "docker exec <mysql-container> mysqldump -uroot -p'<password>' \
   --all-databases --single-transaction --routines --events" \
  > "$BACKUP/db-dump/mysql-all.sql"

ssh root@<server> \
  "docker exec <postgres-container> pg_dumpall -U <db-user>" \
  > "$BACKUP/db-dump/postgres-all.sql"

rsync -az --delete root@<server>:/opt/1panel/ "$BACKUP/app/"
rsync -az --delete root@<server>:/etc/<service>/ "$BACKUP/proxy/"

Keep passwords out of public scripts; use restricted environment files, SSH configuration, or Docker secrets.

Scheduling and retention

0 3 * * * /Users/<user>/bin/backup-server.sh

Keep several daily and weekly versions on storage separate from the server. rsync --delete creates a mirror, not historical versions, so keep dated dumps as well.

Restore drill

scp "$BACKUP/db-dump/mysql-all.sql" root@<server>:/tmp/
ssh root@<server> \
  "docker exec -i <mysql-container> mysql -uroot -p'<password>' < /tmp/mysql-all.sql"
rsync -az "$BACKUP/app/" root@<server>:/opt/1panel/

After restoring, check login, database connections, certificates, attachments, and reverse proxy routes. A backup is not proven until it has been restored successfully.

Common pitfalls

• Named volumes may live outside the project directory and need separate handling.
• Reversing the source and destination with --delete can remove the wrong data.
• Database-only backups miss keys, certificates, and application configuration.
• A successful rsync exit code does not prove that a dump is non-empty or restorable.