Database Backups and Point-in-Time Recovery

Everything Follows From Two Numbers

Before choosing tools, settle two figures with whoever owns the business risk. They determine every technical decision that follows, and arguing about backup software before agreeing them is wasted effort.

The recovery point objective is how much data you can afford to lose, measured in time. A nightly dump gives you a recovery point of up to 24 hours: whatever happened since last night is gone.

The recovery time objective is how long the restore may take before the loss becomes unacceptable. A compressed dump of a large database can take hours to load, and that time counts.

Point-in-time recovery exists to shrink the first number to seconds without making the second one worse. It does this by separating the expensive part, a full copy of the data, from the cheap part, a continuous record of every change since.

The wider estate around the database, the servers and files and images, is a separate exercise covered in cloud backup strategies. This article is only about the database.

A Base Copy Plus a Stream of Changes

Every serious relational engine writes changes to a sequential log before applying them to data files. That log exists for crash recovery: on restart, the engine replays it to bring the data files back to a consistent state.

Point-in-time recovery reuses the same machinery for a longer journey. Take a full physical copy of the database at some moment, keep every log segment generated afterwards, and you can restore the copy and replay the log up to any instant you name.

So a working setup has exactly three parts:

1. Periodic base backups, taken while the database keeps running.
2. Continuous archiving of the log, shipped somewhere safe as each segment completes.
3. A retention policy saying how far back the window extends and when old material is discarded.

The recovery window is the span between the oldest base backup you still keep and the newest archived log. Within it you can land on any second. Outside it you cannot land at all, which is why retention deserves a deliberate decision rather than a default.

The names differ per engine. PostgreSQL calls it the write-ahead log, MySQL and MariaDB call it the binary log, SQL Server calls it the transaction log. The concept is identical.

What Each Engine Needs Switched On

The trap in that table is the last column. Every one of these engines ships in a default configuration where point-in-time recovery is impossible, and the setting that enables it must be turned on before the incident, not after. A database running for a year without an archive command has no recovery window at all, regardless of how many dumps exist.

On PostgreSQL the mechanism is described in the continuous archiving chapter, which is the authoritative account of how base backups and log archiving fit together. The configuration that accompanies it is covered in running PostgreSQL on a cloud server.

Use a purpose-built tool rather than assembling this from shell scripts. pgBackRest, Barman and XtraBackup all handle parallel compression, retention expiry, encryption and, crucially, verification of what they wrote.

Where Logical Dumps Still Belong

A dump produced by pg_dump or mysqldump is a logical backup: a description of the data in a portable form rather than a copy of the files. It cannot participate in point-in-time recovery, and it is still worth taking.

It answers different questions. A dump restores into a different major version, or a different platform, which physical backups generally cannot. It restores a single table without touching anything else, which is the actual need after a mistaken delete on one table. And it is readable, so you can confirm what is in it.

The sensible arrangement is both. Physical backups with log archiving for the disaster case, and a periodic logical dump for portability, selective restores and migrations. The dump is also what you would use to seed a copy of production data into a staging environment, as discussed in building staging environments.

What a Recovery Actually Looks Like

The moment you need this, someone will say a time. Perhaps a migration ran at 14:32 and destroyed a table. You want the database as it was at 14:31.

The sequence is the same across engines:

1. Stop the damage. Take the application offline or into read-only mode so no further writes complicate the picture.
2. Restore to a new instance, never over the original. The original is evidence and it is your fallback if the recovery goes wrong.
3. Restore the most recent base backup taken before the target time.
4. Configure the recovery target as a timestamp, or on PostgreSQL optionally a named restore point or transaction id, and start the engine in recovery mode.
5. Let it replay the log and stop at the target, then inspect before promoting. Check the table is back and that the most recent legitimate data is present.
6. Promote and repoint the application, then take a fresh base backup immediately, because the timeline has changed.

Two practical notes. Recovery replays the log at roughly the speed the database originally wrote it, so a target far from the last base backup takes real time; frequent base backups shorten recovery even though they cost storage. And restoring a single table is often better done by recovering the whole database to a scratch instance and exporting that one table across, which avoids taking production back in time at all.

What Managed Services Do and Do Not Give You

Managed databases build all of this in. Amazon RDS, Cloud SQL, Azure Database and the managed offerings from DigitalOcean, Linode and Scaleway take automated backups and continuously archive the log, then expose a restore-to-timestamp button over a retention window you configure. The behaviour is documented clearly, for example in the RDS point-in-time recovery documentation.

That removes almost all of the work, and it leaves three gaps worth knowing about.

The restore creates a new instance rather than rewinding the existing one, so the endpoint changes and the application must be repointed. Plan for that in your runbook rather than discovering it mid-incident.

The retention window has a maximum, and it is finite. Corruption discovered after the window closed cannot be recovered from automated backups, which is why periodic manual snapshots or exported dumps still matter for anything you might need to look at months later.

And the backups live inside the same account. An account-level problem takes them with it, which is the argument for keeping an independent exported copy somewhere else entirely. The wider trade between running your own and buying the managed version is set out in managed databases versus self-hosted. Both an AWS account and an Azure account offer this on their managed engines, and the AWS and Azure comparison covers how the two platforms differ more broadly.

Key Takeaways

• Agree the acceptable data loss and the acceptable downtime first. Everything else is implementation.
• Point-in-time recovery is a base backup plus every log segment since. Both parts are mandatory.
• Every engine ships with the enabling setting off. Turn it on now, because it cannot be applied retroactively.
• Keep logical dumps as well, for cross-version restores, single-table recovery and seeding test environments.
• Recover to a new instance and inspect before promoting. Take a fresh base backup straight afterwards.
• Managed services do this well, but the window is finite and the backups share the account. Keep one independent copy.