KAHIBARO
Discord Login Register ☰
☰

28.2. Database Backups

Table of Contents

Why Database Backups Matter

Backups are your safety net. Disks fail, developers run the wrong DELETE query, cloud providers have outages, and attackers can destroy data.

A database backup strategy answers three questions:

We will focus on practical concepts you can apply to any relational database, such as PostgreSQL.

A backup that you have never restored is not a backup.
It is just a file.

Core Backup Concepts

RPO and RTO

Two key metrics drive your backup design:

ScenarioExample RPOExample RTOTypical Approach
Small hobby app24 h24 hDaily full backup
Small business, important data1 h2 hHourly incremental + daily full backup
Payments, financial transactions5 min15 minContinuous log backup + hot standby replica

Always define RPO and RTO before choosing your backup tools.
Tools serve your objectives, not the other way around.

Types of Database Backups

Logical vs Physical Backups

Logical backups

A logical backup saves your data as SQL statements or a logical format.

Characteristics:

Physical backups

A physical backup copies the actual database files on disk:

Characteristics:

Backup TypeLevelProsCons
LogicalDatabasePortable, readable, flexibleSlow restore, heavier load on running server
PhysicalFileFast restore, good for very large DBsLess portable, more complex to manage

Full, Incremental, and Differential Backups

These terms are often used in storage-level or physical backups.

Example schedule:

To recover Wednesday:

With differential:

To recover Wednesday:

Incremental saves more space, differential simplifies restores.

Hot, Warm, and Cold Backups

These terms describe whether the database is running during the backup.

ModeDowntimeData up to moment of backup?Typical for
HotNoneYesProduction systems
WarmLowYes, but limited activityMaintenance windows
ColdYesYesSimple setups, very small applications

In modern production systems you usually aim for hot backups.

Point-in-Time Recovery (PITR)

Sometimes a regular backup is not enough. Imagine this timeline:

If you restore the 12:00 backup, you lose all data from 12:00 to 16:00.

If you have Point-in-Time Recovery, you can restore to 14:59:59 and lose almost nothing.

PITR is usually implemented by:

To achieve PITR, you must:

  1. Enable continuous log archiving.
  2. Store logs on separate, durable storage.
  3. Test restoring to a specific timestamp.

Backup Storage and Retention

Offsite and Off-system Storage

If you store backups on the same server as the database, one incident can destroy both.

Follow these rules:

Retention Policies

You cannot keep every backup forever, it is too expensive. Define:

Example retention policy:

Write this as a clear rule, for example:

Retention example:

  • Keep daily full backups for 7 days.
  • Keep log archives for 14 days.
  • Keep monthly full backups for 12 months.
  • Automatically delete older backups.

Then automate cleanup. Never rely on manual deletion.

Backup Encryption

Backups contain the same sensitive data as your production database, sometimes more.

Basic rules:

A common pattern:

Designing a Backup Strategy

Start from Requirements

Before choosing commands or tools, define:

  1. Business impact of data loss
    • Can you reconstruct some data from logs or other systems?
    • Are there legal rules about data retention?
  2. Traffic patterns
    • When is traffic low? That is a good time for full backups.
    • Is traffic constant? You need hot backups that avoid locks.
  3. Database size and growth
    • A 1 GB DB can be backed up with a simple logical dump.
    • A 2 TB DB probably needs physical backups and careful planning.

Combine Techniques

Typical production patterns:

Pattern 1: Simple app, small DB

Pattern 2: Growing app, medium DB

Pattern 3: Critical system, large DB

Automate Everything

Manual backups are forgotten. Use:

Each run should:

  1. Create the backup.
  2. Verify it at least at a basic level (file exists, non-zero size, checksum).
  3. Upload it to remote storage.
  4. Optionally, remove very old backups.

Backup Procedures in Practice

We will stay database-agnostic, but imagine a PostgreSQL-like system.

Example: Logical Backup Procedure

A minimal logical backup flow:

  1. Run a dump command.
  2. Compress the output.
  3. Upload to object storage.
  4. Log the result.

Pseudo-steps:

bash 
# 1. Dump database
pg_dump --format=custom --file=/backups/app_$(date +%F_%H-%M).dump app_db
# 2. Compress (if not already compressed)
gzip /backups/app_*.dump
# 3. Upload to object storage (example using AWS CLI)
aws s3 cp /backups/app_*.dump.gz s3://my-backups/app-db/
# 4. Clean up local backups older than 3 days
find /backups -type f -mtime +3 -delete

You would put this script in a cron job:

bash 
0 3 * * * /usr/local/bin/backup_app_db.sh >> /var/log/backup.log 2>&1

Example: Physical Backup with Log Archiving

High-level flow:

  1. Enable transaction log archiving on the database.
  2. Periodically take a physical base backup.
  3. Continuously ship logs to object storage.

For restore:

  1. Download base backup from date X.
  2. Download logs from after X up to your target time.
  3. Restore base backup.
  4. Configure database to replay logs up to a given timestamp.

The specific commands depend on your database and are usually covered in its own documentation.

Testing Restores

Why Restore Tests Are Critical

Many teams discover their backups are unusable during a crisis:

To avoid this, schedule regular restore tests.

Rule: For every backup strategy, you must have a documented, tested restore procedure.
No exceptions.

Types of Restore Tests

You do not need a full-scale drill every day. Mix several levels:

  1. Quick checks (frequent)
    • Verify backup files exist and are not empty.
    • Verify checksums or hashes.
  2. Partial restore (weekly or bi-weekly)
    • Restore a small backup (for example, a smaller database) into a test environment.
    • Run basic queries to ensure data looks correct.
  3. Full restore drill (monthly or quarterly)
    • Restore a production-sized backup into a separate environment.
    • Time how long it takes.
    • Validate that:
      • The right schema is present.
      • Data counts match expectations.
      • Application can connect and operate on restored DB.
    • Test restoring to a specific point in time if you support PITR.

Document the Restore Procedure

Your documentation should be clear enough that another engineer can follow it without prior experience.

Include:

Example outline:

  1. Identify incident time and target restore time.
  2. Choose base backup and log segment range.
  3. Provision a new database server.
  4. Download and extract backup files.
  5. Configure and start the database in recovery mode.
  6. Replay logs up to target time.
  7. Point staging app to this database and run checks.
  8. If promoting to production, update application configuration.

Common Pitfalls and How to Avoid Them

Backups on the Same Server

Pitfall:

Solution:

Incomplete Backups

Pitfall:

Solution:

Ignoring Schema and Migrations

Pitfall:

Solution:

No Monitoring or Alerts

Pitfall:

Solution:

Example Backup Strategy for a Typical Backend

Imagine a production application with:

A reasonable plan:

RequirementImplementation
Daily full backupNightly physical or logical backup to object storage
RPO 15 minutesContinuous WAL / log archiving to object storage
RTO 1 hourPre-documented restore steps, tested monthly
Offsite storageBackups stored in S3 in a different region
EncryptionServer-side encryption in S3, limited IAM access
RetentionDaily backups for 7 days, weekly for 4 weeks, monthly 6 mo
MonitoringAlerts if last backup is older than 26 hours or if failed
TestingWeekly small restore, quarterly full disaster recovery drill

This strategy is not perfect for every case, but it shows how to translate RPO / RTO into a concrete setup.

Checklists

Backup Checklist

Use this whenever you design or review backups:

Restore Checklist

Use this for drills and during incidents:

Summary

In production backend engineering, database backups are as important as the database itself.

Key ideas to remember:

If you cannot confidently restore your database to a specific point in time, your backup strategy is not finished.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3096 / 24h
Impressum / Imprint
Datenschutz / Data Policy