Database Backup Setup: PostgreSQL, MySQL, MongoDB

Why PostgreSQL backups break in production

Copies are created every night, the job reports success — and at the moment of failure, database recovery after a crash hits an error halfway through.

A typical scenario: PostgreSQL backup is configured formally, tested on an empty database, and no one looked into the archive until the first real failure.

The first class of problems is a break in the backup chain. A full copy and daily incrementals work as a single whole: if rotation deleted the base link or a failed session did not finish writing a write-ahead log segment, the chain stops assembling.

The files are still there, the sizes look reasonable, and recovery stops exactly where the missing link is.

The second is taking a copy over an open transaction. When a snapshot is taken without a consistent point, the archive contains a state the database never had: some changes are recorded, some are not.

Such corrupted backups unpack without a single error and diverge from the source already during integrity verification.

The third is the absence of regular checks. Until a copy has been deployed at least once on a separate test environment, a successful script return code proves nothing.

Next, we break down how this is closed: chain continuity control, snapshots from a consistent point, and automatic recovery verification.

What changes after implementation: recovery time and downtime

Recovery from a nightly dump used to take hours: the server was deployed, logs were replayed, and the service was unavailable the entire time.

After continuous write-ahead log archiving in PostgreSQL and regular base copies, the database comes up on a separate node in tens of minutes. The reduction in downtime here is counted not in words but in the incident log — from the moment of failure to service restoration.

Rollback depth stops depending on the dump schedule. The retention policy keeps base copies for several weeks and logs for the last few days, so the recovery point is chosen with minute precision: up to the state before an erroneous query or a failed migration.

A separate metric is the share of failures that turn into an incident rather than a routine rollback. Integrity verification after every copy, checksum comparison, and an alert for an unfinished job turn silent failures into ordinary tasks. A smaller share of data incidents turns into an outage.

Readiness for failure is confirmed by drills, not by a report on the presence of copies: once a quarter, a copy is deployed on an isolated test environment and compared by record count.

A consistent recovery point for related storages removes discrepancies between services after recovery.

Comparison of backup approaches: what to choose

The choice of backup method is determined by two values: the acceptable data loss window and the acceptable recovery time.

For one server, this is rarely a single strategy — the production database is covered by a physical copy with a log, individual schemas are exported with a logical dump for point edits, and a volume is additionally snapshotted before migration.

A false sense of security appears where backup is configured but recovery testing is absent: a dump not tested on a test environment often turns out incomplete, and a volume snapshot inconsistent.

A backup schedule works only together with rotation, retention control, and regular recovery drills; otherwise the archive exists, but there is nothing to recover from it.

Implementation stages: from data schema to handover of runbooks

The order of stages is not accidental: if you start with schedules, the storage scheme comes out either excessive or failing to meet data loss requirements.

Therefore, implementation goes from inventory to runbook, and recovery verification is built into the process rather than postponed.

1. Database audit. Inventory all PostgreSQL, MySQL, and MongoDB instances: volumes, growth rate, load profile, criticality of the environment. This also reveals what is backed up out of inertia and what is not backed up at all.
2. Target metrics. For each environment, record acceptable data loss and target recovery time — they determine the frequency of full and incremental copies and the depth of log archiving.
3. Backup storage scheme. Where copies are stored, retention period, rotation rules, encryption, and access separation: the backup account must not have rights to delete production.
4. Configuration and automation. Schedules, full and incremental copies, continuous log archiving, job monitoring with alerts on error or missed run.
5. Test recovery. Deploy copies on a separate test environment, measure actual recovery time, and verify data integrity at a selected point, not the presence of a file in storage.
6. Recovery runbook. Step-by-step procedure, responsible persons, decision points; drills based on it, so the document is tested by practice rather than sitting in documentation.
7. Scheme review. As volumes grow or architecture changes, schedules and retention depth are reviewed; otherwise data loss requirements quietly stop being met.

Configurations, storage schemes, and recovery runbooks

A backup that cannot be reproduced without the person who configured it is not a backup but a personal agreement.

Therefore, at the end of the work we hand over a set of materials by which your engineer will bring up a copy, verify its integrity, and restore the database without us.

We update the set with every change to the environment — otherwise the documentation diverges from reality within a couple of months.

• Working configurations — schedules of full and incremental copies, log archiving, compression and encryption parameters, with comments on every non-standard value.
• Environment diagram — where databases reside, where copies are written, which site receives the backup, where the failure boundary lies, and how the replica behaves when a node is lost.
• Backup retention policy — how many versions we keep, the window of daily and weekly slices, archive lifetime, and automatic rotation rules.
• Backup access matrix — read, recovery, and delete roles, locations of encryption keys, and the procedure for rotating them.
• Recovery instructions — step-by-step scenarios: node loss, table corruption, erroneous mass deletion, point-in-time rollback, with an estimate of the time for each step.
• Verification runbook — how often a copy is deployed to an isolated environment, what we verify (checksums, index integrity, row counts), and who receives the report.
• Complete backup documentation — change log, responsible persons, escalation procedure, and an honest list of what the environment does not cover.

Case study: recovery in minutes instead of hours

Before the work, the backup scheme looked like this: a nightly full dump, seven files on the same disk where the data resides, recovery manually according to a wiki instruction.

Integrity checks were never performed, so they learned about two corrupted archives exactly when they were needed. Recovery took 4-6 hours, the recovery point was one day, and the service was down the entire time.

We moved the databases to continuous write-ahead log archiving in PostgreSQL plus a base copy once a day and incremental copies every 15 minutes.

Retention is built in tiers: minute slices are kept for a day, daily for a month, monthly for a year, and all in object storage in another data center, not next to the data.

We separately described what to do if the archive volume is unavailable: recovery proceeds from the latest base copy and replaying logs.

Every copy is automatically deployed to a separate test environment: checksums, row counts for key tables, and the time of the last transaction are verified. If a copy does not come up or diverges by data, a notification arrives, and the archive is quarantined until review. This way a corrupted backup stops being a surprise during an incident.

Metrics after: recovery point — 5 minutes instead of a day, bring-up — 12-18 minutes versus 4-6 hours, share of verified copies — 100%. Rollback is possible to any point within a month, not only to the last night.

A monthly practice alarm was added to the runbook: recovery to a test environment without warning the team.

What to do if the database already runs in a cluster with replication?

The replica is responsible for availability, but not for data safety. It continuously repeats everything the primary node does, including an error: deleting a table or a mass update without a condition propagates to all replicas within seconds.

A fault-tolerant cluster protects against hardware and network failure, but not against a logical error, a failed migration, or application actions — only a separate backup protects against that.

The second limitation is the recovery point. The replica stores the current state, so you cannot roll back "an hour before the incident" with it: if you notice the problem later, the data has already been overwritten.

A snapshot together with a continuous log archive provides point-in-time recovery, not only recovery to the moment of the last copy.

Replication and backup are integrated into a running cluster without rebuilding it. Copies are taken from a dedicated replica removed from failover: the primary node does not receive extra load, and the schedule does not overlap with maintenance windows. Such an environment is added with one node and a set of rules.

We check not the presence of copies but recovery: we deploy the snapshot on a separate test environment, verify integrity, and measure the time to a working state.

We keep backup storage in a separate environment with a write-only account, so an incident at the site does not also take away the reserves. Rebuilding the backup environment does not touch the cluster itself and does not require stopping the service.

Technical concerns: backup size, encryption, and access

The volume of backups grows faster than the data itself: a full copy, incrementals, and the write-ahead log together amount to 2–3 times the original database size.

Rotation is more important here than disk size — the last N full copies on a daily cycle, weekly and monthly slices, compression. Without it, the backup registry grows linearly and hits the disk.

The load is determined by the backup source, not the start time. Taking from a secondary replica or a file system snapshot does not take resources from the primary database: reading occurs on a separate node.

We enable encryption on the source side and store keys separately from the copies — access to storage then does not give access to the data.

We separate access by roles: one environment runs jobs and views status, another can only restore, a third sees only metadata. We confirm suitability by recovery on a separate test environment — a copy that has never been deployed does not exist for us.

Will the copies take up the entire disk?

We calculate the volume from daily growth and retention depth, not from database size. In PostgreSQL, an incremental scheme with rotation usually takes less than ten full daily copies.

Who will get access to the copies?

Only roles that need it for their task: recovery, verification, audit. Encryption keys are kept in a separate storage, so exporting a copy outside does not expose the data.

Let's discuss the task: send the database schema and current runbook

To make the backup assessment based on facts rather than general wishes, a few inputs are enough: data placement scheme, current runbook, and recovery requirements.

Based on them, engineers calculate the backup volume, window duration, and number of recovery points — and return with an implementation plan.

What to send for assessment:

• topology: instances, replicas, total data volume, and daily growth;
• current runbook: frequency of full and incremental copies, retention depth, where they are stored;
• recovery requirements: how much data may be lost and in what time the database must come up;
• bottlenecks: window duration, disk contention, impact of backup on the workload;
• incident history: when recovery from a copy last happened and what did not work;
• constraints: access, network segments, encryption, and a site for backup storage;
• list of databases and services with priority — what is critical and what can be copied less often.

At the first meeting, we review the audit of the current backup scheme and its failure points, discuss an engineer consultation on choosing a backup mode and storing copies outside the primary site.

Then — an implementation plan for backup with recovery testing on a test environment, not just the fact of a successful export.

Describe the task — we will return with an estimate of the backup work and a plan.