Every team has backups. Fewer have a backup strategy, which is the set of decisions about how often, how long, where, and how you know it works. The platform's Database Backup/Restore add-on does the mechanical part for every database on an account; the decisions are still yours. This post follows a DevOps lead making them for a dozen databases on MassiveGRID PaaS, which is built on Virtuozzo Application Platform, formerly Jelastic.
The company is a logistics software vendor we will call Pemberton Systems, with twelve databases across MariaDB, PostgreSQL and Redis, from a customer-facing order database to internal tools. The Database Backup/Restore add-on attaches to a MySQL, MariaDB, Percona, PostgreSQL or Redis node or cluster, takes scheduled consistent dumps to a separate Backup Storage node, and restores from the dashboard.
Two numbers per database
The lead starts with the two numbers an auditor asks for. Recovery point objective (RPO): how much data, measured in time, the business can lose. Recovery time objective (RTO): how long the database can be unavailable. She writes them down per database with the owning team, and the answers vary more than anyone expected: the order database tolerates a minute of loss and an hour of downtime; the internal wiki tolerates a day of either.
| Database | Engine | RPO | RTO | Schedule | Retention |
|---|---|---|---|---|---|
| Orders (production) | MariaDB cluster | 1 minute | 1 hour | Hourly dumps plus binary logs retained | 7 days hourly, 90 days daily |
| Customer portal | PostgreSQL cluster | 15 minutes | 2 hours | Hourly dumps plus WAL archiving | 7 days hourly, 35 days daily |
| Analytics warehouse | PostgreSQL | 24 hours | 1 day | Nightly | 14 days |
| Session and cache store | Redis | Rebuildable | Minutes | Nightly, for the persistent keys | 3 days |
| Internal tools (8 databases) | MariaDB and PostgreSQL | 24 hours | 1 day | Nightly | 30 days |
What the add-on does per run
On each scheduled run the add-on takes a consistent dump with the engine's native tool, mysqldump or mariadb-dump with the single-transaction option, or pg_dump, so InnoDB and PostgreSQL backups do not lock writes; compresses and timestamps it; copies it to the Backup Storage node; and prunes archives beyond the retention you set. On clusters it prefers a replica so the primary is never burdened. Runs are cron-driven at the frequency and timezone you configure, from hourly to weekly, and each run's log is in the dashboard. The lead configures the add-on per database with the schedule from the table, which takes about five minutes each.
Why the storage node is separate, and where it is
A backup on the same disk as the database is not a backup. The Backup Storage node is a distinct container on a distinct physical host, and the lead gives it a large disk with a disk load alert at 80%. She places it in the same region as the databases, because the company's data residency commitment is per region, and then adds what the add-on does not do by itself: a weekly copy of the archives to a second Backup Storage node in another region, via rsync over the nodes' public IPs with the container firewall opened between the two, for the case where the region itself is the disaster. That second copy is the answer to the auditor's follow-up question.
Point-in-time recovery for the one-minute RPO
Hourly dumps give an RPO of up to an hour. The order database needs a minute, and the add-on's own guidance is the standard answer: restore the nearest dump, then replay the engine's transaction log up to the moment before the failure. For MariaDB that is the binary log, which the lead enables on the cluster with a retention longer than the dump interval and archives to the storage node alongside the dumps; for PostgreSQL it is WAL archiving, configured on the primary with the archive directory on the storage node's mount. Point-in-time recovery is more work than a plain restore, which is why it is reserved for the two databases whose RPO demands it, and why it is the one procedure the lead insists on drilling with a stopwatch.
The restore drill
A backup that has never been restored is a hope. Every quarter the lead runs a drill for each tier, on a cloned environment so production is untouched:
- Clone the environment that owns the database. The clone has the add-on installed and can see the same Backup Storage node.
- From the dashboard, restore a chosen archive into the clone's database. Time it.
- For the order database, additionally replay binary logs to a chosen minute. Time it.
- Run the owning team's verification queries: row counts, the latest order timestamp, a checksum over a critical table.
- Record the times against the RTO. Delete the clone.
The first drill found that the analytics warehouse's restore took four hours against a one-day RTO, fine, but that the order database's point-in-time replay took 70 minutes against a one-hour RTO. The fix was to raise the order database's dump frequency so fewer binary logs need replaying, which brought the drill to 35 minutes. Nobody would have known without the stopwatch.
Cost, and a note on the WordPress add-on
The Backup Storage node is billed on its disk, about 800 GB for the company's retention policy, plus a few cloudlets while transfers run; the second-region copy doubles the disk. The dumps themselves run on replicas and cost the cloudlets they use for a few minutes an hour. Across the estate the backup tier is a low two-figure monthly sum before discounts at MassiveGRID's published $0.003372 per cloudlet-hour, plus the disk, which is the cost of being able to answer the auditor's two numbers with evidence. For the marketing team's WordPress sites the lead uses the dedicated WordPress Backup/Restore add-on, which backs up files and database together, on the same storage node.
Frequently Asked Questions
Does the add-on back up the database's files or a logical dump?
A logical dump with the engine's native tool, which restores across versions and to other environments and is consistent without locking. For very large databases where a dump is slow, combine it with the platform's disk snapshots for fast rollback and keep the dumps for portability and retention.
Can I restore a backup into a different environment?
Yes. The restore dialog lets you target the original database or another environment's database that has the add-on, which is how the quarterly drill restores into a clone and how a staging environment gets a fresh copy of production data.
Which databases and clusters are supported?
MySQL, MariaDB and Percona, PostgreSQL, and Redis, as standalone nodes or as the marketplace clusters including the multi-region packages. The add-on prefers a replica in clusters. PostgreSQL WAL archiving and MariaDB binary logs for point-in-time recovery are configured on the database itself, as described above.
Answer the two numbers with evidence
Attach the Database Backup/Restore add-on to every MySQL, MariaDB, PostgreSQL or Redis node on MassiveGRID PaaS, dump to a separate Backup Storage node on your schedule, and restore into a clone to prove it. Free 14-day trial, no credit card.
Database Backup/Restore on MassiveGRID PaaS