Database backup and recovery
Database backup and recovery
A self-managed database has three recovery layers, and they fail in different ways. Logical dumps recover from a bad statement. Volume snapshots recover from a corrupted filesystem or a botched upgrade. A copy held outside the instance recovers from losing the volume itself. A deployment that stops at the first layer can still lose everything in a single volume failure.
Layer 1: logical dumps#
A logical dump is a text or archive file the engine produces from its own data. It restores onto a different host, a different volume, and in many cases a different engine version, which makes it the layer that travels.
Both database templates install a dump job on first boot:
| Template | Script and schedule | Output |
|---|---|---|
| Self-Managed PostgreSQL | /usr/local/bin/pg-backup.sh on the backup_schedule cron (0 2 * * * by default) | Compressed pg_dumpall in /var/lib/postgresql/backups/ |
| MySQL/MariaDB Database | mysqldump --all-databases on the backup_schedule cron | Dump files in /var/lib/mysql/backups/ |
Both jobs write to the same Block Storage volume the engine writes to. That covers a dropped table or a bad UPDATE while the volume is healthy, and covers nothing at all once the volume is gone.
Layer 2: volume snapshots#
A Block Storage snapshot captures the volume at a point in time, including the data directory and the write-ahead log. Snapshots are incremental, so a regular schedule stays space-efficient, and you restore by creating a new volume from the snapshot and attaching it to an instance.
A snapshot of a volume the engine is actively writing to is crash-consistent: the restored volume looks to the engine like a host that lost power, and the engine replays its log on startup. Engines handle that case, though a quiesced snapshot is cleaner. For a consistent snapshot, stop writes first (FLUSH TABLES WITH READ LOCK for MySQL and MariaDB, or a maintenance pause for PostgreSQL), take the snapshot, then release. How to schedule volume snapshots and restore data covers the automation, and Volumes covers the consistency boundary.
Layer 3: copies outside the instance#
A dump that never leaves the database volume shares that volume's fate. Copying dumps to Object storage puts recovery data on separate infrastructure with its own lifecycle and retention. Back up to Quake AI object storage covers the rclone upload pattern for PostgreSQL and MySQL dumps, along with age-based retention.
Set retention at both ends: the local directory fills the data volume otherwise, and the bucket accumulates dumps nobody reads.
Point-in-time recovery with PostgreSQL#
The PostgreSQL template turns on WAL archiving, and each completed segment lands in /var/lib/postgresql/wal_archive on the data volume. Replaying those segments against a base copy rolls the database forward to a chosen moment after a logical mistake, as long as the volume holding both the data directory and the archive survives.
Because the base data and the archive share one volume, that configuration recovers from mistakes rather than from volume loss. Offsite point-in-time recovery requires shipping WAL segments elsewhere by changing archive_command in the template's cloud-init. How to restore PostgreSQL from a self-managed-postgres backup documents the current scope of the template's WAL archiving in detail.
Restore drills#
A backup nobody has restored is a hypothesis. Run the restore against a separate scratch instance so the drill follows the path a real recovery would take, then compare databases, roles, and row counts against the source before you discard the scratch instance.
Re-run the drill on a schedule, after any engine major-version change, and after any edit to the backup script or its cron schedule. The PostgreSQL restore guide walks a full drill, including provisioning the scratch target and tearing it down afterwards.
Choosing a combination#
| Failure | Layer that recovers it |
|---|---|
Bad DELETE, dropped table, application bug | Logical dump, or PostgreSQL WAL replay to a moment before the mistake |
| Corrupted filesystem, failed engine upgrade | Volume snapshot restored to a new volume |
| Lost or deleted data volume | Dump copied to Object Storage or another project |
| Lost instance, volume intact | Attach the volume to a replacement instance |
A common combination is a nightly dump uploaded to a bucket, a daily volume snapshot with short retention, and a monthly restore drill.
Further reading#
- How to restore PostgreSQL from a self-managed-postgres backup: a full restore drill against a scratch instance
- How to emulate a branchable Postgres workflow: the same mechanics used to spin up isolated database copies
- How to create a volume snapshot: snapshot procedure for a data volume
- Back up to Quake AI object storage:
rcloneupload and retention patterns for database dumps - How to back up and restore a Quake AI VM: instance-level snapshots, for comparison with database-level recovery
Related content
Pages
How-tos
Overviews