Types of database backups: logical vs physical vs incremental

• Logical backup – exports the database as SQL statements or a portable dump (pg_dump, mysqldump); engine-portable and selective but slower on large data.
• Physical backup – copies the actual data files or storage blocks (pg_basebackup); fast and exact but tied to the same engine, version, and platform.
• Incremental backup – captures only blocks or WAL changed since the prior backup; minimizes backup window and storage but restores as a chain.
• Production practice – combine periodic physical base backups with continuous WAL archiving and occasional logical dumps for portability.
• Testing – an untested backup is not a backup; schedule automated restore drills.

What is a logical backup?

A logical backup runs the engine's dump tool to emit a reconstructable representation: schema plus data as SQL or a custom archive.

# PostgreSQL logical backup
pg_dump -h localhost -U bank_admin -d national_bank \
    -Fc -f /backup/national_bank.dump

# MySQL logical backup
mysqldump -u bank_admin -p national_bank > /backup/national_bank.sql

The output is portable across versions and useful for migrating selectively.

# Restore a logical backup
pg_restore -d national_bank /backup/national_bank.dump

# Selective: dump only the accounts table
pg_dump -t accounts -Fc national_bank -f /backup/accounts.dump

What are the trade-offs of logical backups?

| Advantage | Disadvantage |
|---|---|
| Portable across engine versions | Slower to produce and restore |
| Selective (one table, one schema) | Larger file size on big data |
| Verifies logical integrity | Restores by replaying statements |
| Human-readable (SQL format) | Rebuilds indexes from scratch |

What is a physical backup?

A physical backup copies the data directory or storage blocks, often while the server runs.

# PostgreSQL physical backup
pg_basebackup -D /backup/physical -Ft -z -P --checkpoint=fast

# The output is the exact on-disk state
ls -lh /backup/physical/
# base.tar.gz    2.4G
# pg_wal.tar.gz  128M

Physical backups are the basis for point-in-time recovery when combined with archived WAL.

What are the trade-offs of physical backups?

| Advantage | Disadvantage |
|---|---|
| Fast to take and restore | Tied to same engine and major version |
| Exact byte-level copy | Not selective (whole cluster) |
| Basis for PITR | Platform-dependent |
| Small overhead on server | Cannot inspect contents as SQL |

What is an incremental backup?

An incremental backup captures only what changed since a prior backup.

# PostgreSQL incremental with pgBackRest
pgbackrest --stanza=national_bank --type=incr backup

# Differential: changes since last full
pgbackrest --stanza=national_bank --type=diff backup

Backup chain for restore:
┌──────────┐   ┌──────────┐   ┌──────────┐
│ Full     │──▶│ Incr 1   │──▶│ Incr 2   │
│ (2.4 GB) │   │ (120 MB) │   │ (85 MB)  │
└──────────┘   └──────────┘   └──────────┘
                                    │
                    Restore applies all three in order

The chain's integrity is critical. A missing or corrupt increment breaks the restore.

How does WAL archiving relate to incremental backups?

Continuous WAL archiving is effectively a continuous incremental backup. Each WAL segment contains the changes since the previous segment.

-- PostgreSQL WAL archiving setup
-- wal_level = replica
-- archive_mode = on
-- archive_command = 'cp %p /archive/%f'

Combined with a base backup, WAL archiving enables PITR to any point between backups.

How do you choose between backup types?

| Scenario | Recommended Type |
|---|---|
| Cross-version migration | Logical (pg_dump) |
| Fast daily backup for PITR | Physical (pg_basebackup) + WAL |
| Large busy database | Incremental (pgBackRest) + WAL |
| Selective table export | Logical (pg_dump -t table) |
| Regulatory long-term archive | Logical (portable, auditable) |
| Disaster recovery | Physical + continuous WAL |

How does National Bank of India combine all three?

Pankaj uses all three types together for defense in depth.

# Daily physical base backup + continuous WAL = PITR
pg_basebackup -D /backup/daily/$(date +%Y%m%d) -Ft -z -P

# Weekly logical dump for portability and audit
pg_dump -Fc national_bank -f /backup/weekly/national_bank_$(date +%Y%m%d).dump

# Incremental backups between dailies for large tables
pgbackrest --stanza=national_bank --type=incr backup

What should a senior engineer verify?

A senior engineer never trusts a backup without testing.

package com.bank.ops;

public class BackupVerification {
    // Checklist:
    // 1. Can you restore the physical backup to a scratch instance?
    // 2. Can you replay WAL to a specific point in time?
    // 3. Can you restore the logical dump and query the data?
    // 4. Is the incremental chain complete with no gaps?
    // 5. Are backups encrypted and stored off-site?
    // 6. Is the restore time within the RTO target?
}