One Dump per Tag: Database Backups That Can Answer

# One Dump per Tag: Database Backups That Can Answer the Question

Something happened in a KotaPortal project I maintain. I needed production-like data to test a new feature in staging. All I found was a massive file named all_databases_20261001.sql from the daily cron. Complete contents. But upon restore, the users table lacked the column the application was looking for. The schema in the dump and the schema required by the code were different versions, and there was no clue indicating which release the dump matched.

I first blamed the cron. I thought the schedule wasn't tight enough. Turns out the problem wasn't frequency, but the trigger: time. A backup triggered by the clock has no relationship whatsoever to the version of the application currently running.

So I changed the contract: one dump paired with one release tag. Tags are designed to mark release points [4], and annotated tags store the date, the tagger, plus an explanatory message [3]. From that day on, the backup db following the release tag became the official rule in the .docs/BACKUPDB folder. Cron still runs for daily needs, but it is no longer asked to be the primary recovery point.

The Tag is a Conscious Decision

Cron runs on the clock, regardless of whether the repo is quiet or in the middle of a hot deploy. Tags are different. They only exist if I create them. Making an annotated tag is an explicit statement that the code at this point is worthy of being called a version.

The side effect is neat: the audit trail sorts itself out. When production hits a snag, I don't guess. I look at the last stable release tag, grab the dump with the matching name. Daily backups can't give this kind of certainty because cron files are never asked for their version.

Those who like managing automation get a piece of this too. GitHub Actions can be set up so the workflow only runs when a specific tag is pushed [5], and restricting triggers to tags is indeed official syntax in the workflow syntax page [6]. I still choose the manual route for now: the restore ritual is exactly the part I want to handle by hand, not hide behind an automated runner.

Logical Dump is Enough, Why Physical

The tool is mariadb-dump, the new name for mysqldump whose symlinks have been deprecated since MariaDB 11.0 [1]. One command, out comes an SQL file containing the commands to recreate the database from scratch, complete with its data [1].

Physical backups like mariadb-backup are indeed faster for massive databases. But logical backups are more flexible. As documented, physical backups cannot be imported on significantly different hardware, a different DBMS, or potentially even a different MariaDB version [2]. The downside of logical backups is clear: they are slower during backup and restore [2]. For a database sized in the tens of megabytes, that difference in seconds is far cheaper than losing flexibility.

The practical reason: I am the one doing the restore, a few times a year, with a medium level of panic. That scenario is best suited for a plain SQL file that can be opened with any text editor.

The `.docs/BACKUPDB` Structure

All backup rules in that project now live in a single folder. There is a rule file, an index.md that serves as a table of contents, and a place to drop local dumps. The folder is committed, its contents a mini-documentation teaching whoever takes over the project next.

Dump naming follows the tag. portal-warga-v1.2.0.sql for tag v1.2.0. No -final or -revision suffixes, because the tag gives it the name, not my mood.

One thing that surprises people: local dumps do not go to Git. The .gitignore in this folder blocks all .sql files. Official dumps are generated at release time and then archived to external backup storage. The repo only stores the rules, the index, and the audit trail, not a copy of the data for every release.

Backup Proof: Restore to a Clean Container

Rules, no matter how sophisticated, mean nothing if the dump fails to restore when needed. So every release goes through a brief ritual: dump, restore to an empty MariaDB container, count the contents.

# tandai titik rilis yang valid
git tag -a v1.2.0 -m "rilis fitur notifikasi dan perubahan skema users"
git push origin v1.2.0

# dump logis: satu file per tag, ganti <DB_PASSWORD>
mariadb-dump -u root -p"<DB_PASSWORD>" --databases portal_warga > .docs/BACKUPDB/portal-warga-v1.2.0.sql

# restore uji ke container MariaDB kosong
docker run --name uji-restore -e MARIADB_ROOT_PASSWORD="<DB_PASSWORD>" -d mariadb:11
docker exec -i uji-restore mariadb -u root -p"<DB_PASSWORD>" portal_warga < .docs/BACKUPDB/portal-warga-v1.2.0.sql

# bukti dump hidup: daftar tabel harus muncul
docker exec uji-restore mariadb -u root -p"<DB_PASSWORD>" portal_warga -e "SHOW TABLES;"

If SHOW TABLES outputs and the list makes sense, that dump earns the right to be called a backup. The same command is used to fill index.md: when the tag was, what file, how many seconds the restore took. The next release just reads the index, no digital archaeology needed.

What I realized after switching to this pattern: a good backup isn't about expensive tools, but about being able to answer the most embarrassing question quickly. Which dump matches the version currently running? Now the answer is a single filename, not an excavation session.

Sources

[1] official mariadb-dump documentation

[2] MariaDB backup and restore overview

[3] official git-tag documentation

[4] the official Git book, tagging chapter

[5] GitHub Actions events that trigger workflows

[6] GitHub Actions workflow syntax