Database Backup Strategies: pg_dump, mysqldump, Automation, and Disaster Recovery

"No backup, no sympathy." Every DBA has heard this at least once — usually right after a production incident. It sounds harsh, but the numbers back it up: studies consistently show that 60% of companies that suffer significant data loss close within six months. Not because the data was irreplaceable in theory, but because restoring it took longer than the business could survive.

This guide covers everything you need to build a real database backup strategy — not checkbox compliance, but actual protection. We'll cover PostgreSQL and MySQL, from simple pg_dump commands to point-in-time recovery, automated scripts, remote offsite copies, and what to do when things go wrong.

1. Backup Types: Know What You're Dealing With

Before running a single command, understand what kind of backup you're creating and what it can recover from.

Full Backup

A complete copy of the entire database at a point in time. Easy to restore, no dependencies. Downside: takes the most time and storage. Best used as a weekly or daily baseline.

Incremental Backup

Only captures changes since the last backup (full or incremental). Smallest size, fastest to create. Slowest to restore — you need to apply every incremental in sequence. A chain of incrementals that breaks in the middle means data loss.

Differential Backup

Changes since the last full backup only. Larger than incremental, faster to restore — you need only the last full + latest differential. A reasonable middle ground for most setups.

Continuous (WAL Archiving / Binary Log)

Every transaction is logged to a continuous stream. Enables point-in-time recovery (PITR) — restore to the exact second before an accidental DROP. Requires the most setup and storage, but delivers the lowest possible RPO.

RPO and RTO — The Two Numbers That Matter

RPO (Recovery Point Objective) — How much data can you afford to lose? "Last 24 hours" means daily backups are acceptable. "Last 5 minutes" means you need continuous replication.

RTO (Recovery Time Objective) — How long can the system be down? "4 hours is fine" means logical backups work. "15 minutes" means physical backups and pre-tested restore procedures.

2. PostgreSQL Backup with pg_dump

pg_dump is the standard logical backup tool for PostgreSQL. It produces a consistent snapshot even while other clients are connected — no locking required.

Basic Logical Backups

Plain SQL dump (human-readable, portable):

pg_dump -U postgres mydb > backup.sql

Custom format (compressed, supports parallel restore):

pg_dump -Fc -U postgres mydb > backup.dump

Custom format is the recommended default for most production setups. It's compressed, faster to restore, and supports selective table restore.

Directory format with parallel dump (4 workers):

pg_dump -Fd -j 4 -U postgres mydb -f /backup/mydb_dir/

Single table only:

pg_dump -t tablename -U postgres mydb > table_backup.sql

Schema without data:

pg_dump --schema-only -U postgres mydb > schema.sql

Data without schema:

pg_dump --data-only -U postgres mydb > data.sql

Compressed with gzip (plain format):

pg_dump -U postgres mydb | gzip > backup.sql.gz

All databases including roles and tablespaces:

pg_dumpall -U postgres > all_databases.sql

Use pg_dumpall when you need to restore an entire PostgreSQL cluster to a new server, including user accounts and permissions.

Restoring PostgreSQL Backups

From plain SQL:

psql -U postgres -d mydb < backup.sql

From custom format:

pg_restore -U postgres -d mydb backup.dump

Parallel restore (4 jobs — use with directory or custom format):

pg_restore -j 4 -U postgres -d mydb backup.dump

List contents before restoring (selective restore):

pg_restore -l backup.dump

The output of -l can be edited to comment out tables or indexes you don't want to restore, then fed back with -L list.txt.

Restore specific table only:

pg_restore -t orders -d mydb backup.dump

3. PostgreSQL WAL Archiving and Point-in-Time Recovery

WAL (Write-Ahead Log) is PostgreSQL's transaction journal. Every change is written to WAL before it's applied to data files. By archiving WAL segments continuously, you can restore to any point in time — not just the moment of the last backup.

Enable WAL Archiving

In postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
max_wal_senders = 3

The archive_command runs whenever a WAL segment is ready. In production, use rsync or rclone to ship WAL to a remote location:

archive_command = 'rsync -a %p backup-server:/backup/wal/%f'

Take a Base Backup

pg_basebackup -D /backup/base -Ft -z -P -U postgres

Flags: -Ft tar format, -z gzip compressed, -P progress output.

PITR Restore Procedure

1. Stop PostgreSQL: systemctl stop postgresql
2. Move corrupted data directory: mv /var/lib/postgresql/data /var/lib/postgresql/data_broken
3. Extract base backup: tar -xzf /backup/base/base.tar.gz -C /var/lib/postgresql/data
4. Create recovery signal: touch /var/lib/postgresql/data/recovery.signal
5. Configure recovery in postgresql.conf:

   restore_command = 'cp /backup/wal/%f %p'
   recovery_target_time = '2026-03-28 09:45:00'
   recovery_target_action = 'promote'

6. Start PostgreSQL — it will replay WAL up to the target time and promote to normal operation.

PITR is your best tool against accidental data deletion. "Restore to 30 seconds before the DROP TABLE" is entirely possible when WAL archiving is running.

4. MySQL Backup with mysqldump

mysqldump is MySQL's built-in logical backup utility. Simple, portable, and widely supported. For databases under 10GB it's the go-to choice. For a MySQL-only deep dive including automation and recovery scenarios, see our MySQL backup and restore guide.

Basic Logical Backups

Single database:

mysqldump -u root -p mydb > backup.sql

All databases:

mysqldump -u root -p --all-databases > all.sql

InnoDB consistent backup (no table locks — use this for production):

mysqldump -u root -p --single-transaction mydb > backup.sql

The --single-transaction flag starts a transaction before dumping, giving you a consistent snapshot without locking tables. Works only for InnoDB tables.

Include stored procedures, triggers, and events:

mysqldump -u root -p --routines --triggers --events mydb > full.sql

Data only (no CREATE TABLE statements):

mysqldump -t -u root -p mydb > data_only.sql

Schema only (no INSERT statements):

mysqldump -d -u root -p mydb > schema_only.sql

Compressed backup:

mysqldump -u root -p mydb | gzip > backup.sql.gz

Specific tables only:

mysqldump -u root -p mydb orders customers > selected_tables.sql

Restoring MySQL Backups

Standard restore:

mysql -u root -p mydb < backup.sql

From compressed backup:

gunzip < backup.sql.gz | mysql -u root -p mydb

Create database and restore in one step:

mysql -u root -p -e "CREATE DATABASE mydb;"
mysql -u root -p mydb < backup.sql

mysqlpump — Faster Parallel Alternative

mysqlpump --default-parallelism=4 mydb > backup.sql

mysqlpump is bundled with MySQL 5.7+ and supports parallel table dumping, progress reporting, and better exclusion filters. For large databases with many tables, it's significantly faster than mysqldump.

5. MySQL Binary Log and Point-in-Time Recovery

MySQL binary logs record every data-modifying statement. With binary logs enabled, you can replay transactions up to any specific point in time.

Enable Binary Logging

In my.cnf:

[mysqld]
log_bin = /var/lib/mysql/binlog
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

PITR with mysqlbinlog

Replay from a specific binary log file:

mysqlbinlog /var/lib/mysql/binlog.000001 | mysql -u root -p

Stop replay at a specific timestamp:

mysqlbinlog --stop-datetime="2026-03-28 09:45:00" /var/lib/mysql/binlog.000001 | mysql -u root -p

Start from a specific position:

mysqlbinlog --start-position=4 --stop-position=1000 binlog.000001 | mysql -u root -p

MySQL PITR Workflow

1. Restore last full mysqldump
2. Identify which binary log file covers the time of the incident
3. Use mysqlbinlog with --stop-datetime set to just before the incident
4. Verify data integrity

6. Physical Backups for Large Databases

Logical backups (pg_dump, mysqldump) re-construct SQL statements. Physical backups copy raw data files — much faster for large databases (100GB+).

PostgreSQL: pg_basebackup

# Streaming backup with progress
pg_basebackup -D /backup/base -Xs -P -R -U replicator

-R automatically creates standby.signal and recovery configuration — useful when setting up a replica.

MySQL: Percona XtraBackup

XtraBackup is the industry standard for hot MySQL backups — no table locks, works while MySQL is running under full production load.

Full backup:

xtrabackup --backup --target-dir=/backup/full/ --user=root --password=your_pass

Prepare backup (apply transaction logs):

xtrabackup --prepare --target-dir=/backup/full/

Restore:

systemctl stop mysql
xtrabackup --copy-back --target-dir=/backup/full/
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysql

Incremental backup with XtraBackup:

# Create full backup
xtrabackup --backup --target-dir=/backup/full/

# First incremental
xtrabackup --backup --target-dir=/backup/inc1/ --incremental-basedir=/backup/full/

# Second incremental
xtrabackup --backup --target-dir=/backup/inc2/ --incremental-basedir=/backup/inc1/

# Prepare full + apply incrementals
xtrabackup --prepare --apply-log-only --target-dir=/backup/full/
xtrabackup --prepare --apply-log-only --target-dir=/backup/full/ --incremental-dir=/backup/inc1/
xtrabackup --prepare --target-dir=/backup/full/ --incremental-dir=/backup/inc2/

Physical vs Logical Backup Comparison

7. Automated Backup Scripts

Manual backups are backups that don't happen. Automation is non-negotiable.

PostgreSQL Daily Backup Script

#!/bin/bash
# /opt/scripts/backup_postgres.sh

BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
DATABASES=("mydb" "analytics" "accounts")
LOG_FILE="/var/log/backup_postgres.log"
ALERT_EMAIL="[email protected]"

mkdir -p "$BACKUP_DIR"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

backup_failed() {
    log "ERROR: Backup failed for $1"
    echo "PostgreSQL backup FAILED for database: $1 on $(hostname) at $(date)" | \
        mail -s "BACKUP FAILURE: $1" "$ALERT_EMAIL"
}

for DB in "${DATABASES[@]}"; do
    log "Starting backup: $DB"
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.dump"

    if pg_dump -Fc -U postgres "$DB" > "$BACKUP_FILE"; then
        SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
        log "Success: $DB -> $(basename $BACKUP_FILE) ($SIZE)"
    else
        backup_failed "$DB"
        rm -f "$BACKUP_FILE"
    fi
done

# Cleanup old backups
log "Removing backups older than ${RETENTION_DAYS} days..."
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete

log "Backup run complete."

MySQL Daily Backup Script

#!/bin/bash
# /opt/scripts/backup_mysql.sh

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
MYSQL_USER="backup_user"
MYSQL_PASS="your_backup_password"
LOG_FILE="/var/log/backup_mysql.log"

mkdir -p "$BACKUP_DIR"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Get all databases (excluding system databases)
DATABASES=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW DATABASES;" 2>/dev/null | \
    grep -Ev "^(Database|information_schema|performance_schema|mysql|sys)$")

for DB in $DATABASES; do
    log "Starting backup: $DB"
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

    if mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASS" \
        --single-transaction \
        --routines --triggers --events \
        "$DB" | gzip > "$BACKUP_FILE"; then
        SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
        log "Success: $DB -> $(basename $BACKUP_FILE) ($SIZE)"
    else
        log "ERROR: Backup failed for $DB"
    fi
done

# Create a dedicated backup user with minimal privileges
# GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup_user'@'localhost';

# Cleanup
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

log "MySQL backup run complete."

Cron Schedule

# Edit with: crontab -e

# PostgreSQL: 2am daily
0 2 * * * /opt/scripts/backup_postgres.sh >> /var/log/backup.log 2>&1

# MySQL: 3am daily
0 3 * * * /opt/scripts/backup_mysql.sh >> /var/log/backup.log 2>&1

# Weekly full backup to separate directory (Sunday 1am)
0 1 * * 0 /opt/scripts/backup_postgres_weekly.sh >> /var/log/backup.log 2>&1

Pro tip: Create a dedicated backup_user in MySQL with only the minimum required privileges. Never run automated backups as root.

8. Remote Backups: The 3-2-1 Rule

A backup stored on the same server as the database it protects is not a backup. It's a slightly delayed copy of the disaster. For how different hosting panels approach automated offsite backup, see JetBackup vs Plesk vs Panelica.

The 3-2-1 Rule:

• 3 copies of the data
• 2 different storage media (local disk + remote server, or NAS + cloud)
• 1 offsite copy (different physical location, or cloud storage)

rsync to Remote Server

rsync -avz --delete /backup/postgresql/ backup-server:/remote/backup/postgresql/
rsync -avz -e "ssh -p 22 -i /root/.ssh/backup_key" /backup/mysql/ user@backup-server:/remote/backup/mysql/

rclone to Cloud Storage

rclone supports S3, Google Cloud Storage, Backblaze B2, Azure Blob, and 40+ other providers.

# Configure once
rclone config

# Sync backups to S3
rclone sync /backup/postgresql/ s3:your-bucket-name/postgresql/ --progress

# Sync to Backblaze B2 (cheaper than S3 for large datasets)
rclone sync /backup/mysql/ b2:your-bucket-name/mysql/ --progress

# Add to cron (after local backup script runs)
30 3 * * * rclone sync /backup/ b2:your-bucket-name/ --log-file=/var/log/rclone.log

restic — Encrypted, Deduplicated Backups

restic is worth learning. It deduplicates chunks across backups, encrypts everything by default, and supports snapshots with retention policies.

# Initialize repository on S3
restic -r s3:s3.amazonaws.com/your-bucket/db-backups init

# Create backup
restic -r s3:s3.amazonaws.com/your-bucket/db-backups backup /backup/postgresql/

# List snapshots
restic -r s3:s3.amazonaws.com/your-bucket/db-backups snapshots

# Restore specific snapshot
restic -r s3:s3.amazonaws.com/your-bucket/db-backups restore latest --target /restore/

# Enforce retention policy (keep 7 daily, 4 weekly, 3 monthly)
restic -r s3:s3.amazonaws.com/your-bucket/db-backups forget --keep-daily 7 --keep-weekly 4 --keep-monthly 3 --prune

Cloud Storage Cost Comparison (2026)

9. Testing Your Backups — The Step Everyone Skips

A backup that has never been tested is not a backup. It is a hope.

This is the most skipped step in any backup strategy. You discover broken backups either during a scheduled restore drill — or during an actual incident. One of those scenarios is significantly less stressful.

Monthly Restore Drill Procedure

1. Take the latest backup file (use yesterday's, not today's).
2. Create a test database: createdb pg_test_restore or CREATE DATABASE mysql_test_restore;
3. Restore the backup to the test database.
4. Run count queries on critical tables and compare against production numbers.
5. Spot-check 5–10 specific records by primary key.
6. Verify foreign key relationships and indexes are intact.
7. Record the restore time — this is your actual RTO.
8. Drop the test database.

PostgreSQL Backup Verification Script

#!/bin/bash
# /opt/scripts/verify_backup.sh
BACKUP_FILE=$1
TEST_DB="pg_verify_$(date +%s)"

echo "Testing backup: $BACKUP_FILE"

# Create test database
createdb -U postgres "$TEST_DB"

# Restore
if pg_restore -U postgres -d "$TEST_DB" "$BACKUP_FILE"; then
    echo "Restore: SUCCESS"

    # Count tables
    TABLE_COUNT=$(psql -U postgres -d "$TEST_DB" -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public';")
    echo "Tables restored: $TABLE_COUNT"

    # Verify contents of a critical table
    ROW_COUNT=$(psql -U postgres -d "$TEST_DB" -t -c "SELECT COUNT(*) FROM users;" 2>/dev/null || echo "N/A")
    echo "Row count (users): $ROW_COUNT"
else
    echo "Restore: FAILED"
fi

# Cleanup
dropdb -U postgres "$TEST_DB"
echo "Test database dropped."

Checksum Verification

# Generate checksums after creating backups
sha256sum /backup/postgresql/*.dump > /backup/postgresql/checksums.sha256

# Verify before restoring
sha256sum -c /backup/postgresql/checksums.sha256

If the checksum fails, the file was corrupted in transit or on disk. You want to find out now, not when you need it.

10. Monitoring and Alerting

Backups running silently with no monitoring is almost as dangerous as no backups at all. You want to know immediately when something goes wrong.

Backup Age Check Script

#!/bin/bash
# /opt/scripts/check_backup_age.sh
# Run this every 30 minutes via cron

BACKUP_DIR="/backup/postgresql"
MAX_AGE_HOURS=25
ALERT_EMAIL="[email protected]"

LATEST=$(find "$BACKUP_DIR" -name "*.dump" -printf '%T@ %p\n' | sort -n | tail -1 | awk '{print $2}')

if [ -z "$LATEST" ]; then
    echo "CRITICAL: No backup files found in $BACKUP_DIR" | \
        mail -s "BACKUP MISSING" "$ALERT_EMAIL"
    exit 2
fi

AGE_HOURS=$(( ($(date +%s) - $(stat -c %Y "$LATEST")) / 3600 ))
SIZE=$(stat -c %s "$LATEST")

echo "Latest backup: $(basename $LATEST)"
echo "Age: ${AGE_HOURS}h | Size: $(du -h "$LATEST" | cut -f1)"

if [ "$AGE_HOURS" -gt "$MAX_AGE_HOURS" ]; then
    echo "ALERT: Latest backup is ${AGE_HOURS} hours old (threshold: ${MAX_AGE_HOURS}h)" | \
        mail -s "BACKUP STALE: $(hostname)" "$ALERT_EMAIL"
fi

# Alert if backup is suspiciously small (< 100KB might indicate empty dump)
if [ "$SIZE" -lt 102400 ]; then
    echo "ALERT: Latest backup is only $(du -h "$LATEST" | cut -f1) — may be empty or corrupted" | \
        mail -s "BACKUP SIZE WARNING: $(hostname)" "$ALERT_EMAIL"
fi

Prometheus Metrics Integration

If you're running Prometheus, expose backup metrics via a custom exporter or textfile collector:

# Write metrics to file for node_exporter textfile collector
BACKUP_AGE_HOURS=24
BACKUP_SIZE=$(stat -c %s /backup/postgresql/latest.dump)
PROM_FILE="/var/lib/node_exporter/textfile_collector/backup.prom"

cat > "$PROM_FILE" << EOF
# HELP backup_age_hours Hours since last successful backup
# TYPE backup_age_hours gauge
backup_age_hours{database="postgresql",server="$(hostname)"} ${BACKUP_AGE_HOURS}

# HELP backup_size_bytes Size of latest backup in bytes
# TYPE backup_size_bytes gauge
backup_size_bytes{database="postgresql",server="$(hostname)"} ${BACKUP_SIZE}
EOF

In Grafana, set an alert rule: fire if backup_age_hours > 25 or backup_size_bytes < 1024.

11. Disaster Recovery Scenarios

Planning is the difference between a 30-minute outage and a three-day recovery. Document these procedures before you need them.

Scenario 1: Accidental DROP TABLE

Detection: Application throws errors for a specific feature; query returns "relation does not exist".

Recovery (PostgreSQL with WAL archiving):

1. Note the approximate time of the DROP.
2. Create a new PostgreSQL instance (or restore to test server first).
3. Restore latest base backup.
4. Set recovery_target_time to 2 minutes before the incident.
5. Start PostgreSQL — it replays WAL and stops before the DROP.
6. Export the recovered table: pg_dump -t dropped_table recovered_db > table.sql
7. Import into production: psql production_db < table.sql

Estimated RTO: 15–45 minutes depending on database size.

Scenario 2: Server Disk Failure

Detection: I/O errors in logs, filesystem read-only, server unreachable.

Recovery:

1. Provision new server or attach new disk.
2. Install PostgreSQL/MySQL matching the original version.
3. Download latest remote backup (S3/B2/SFTP).
4. Restore using pg_restore or mysql.
5. Apply any WAL/binary logs from the remote archive if available.
6. Update application connection strings.

Estimated RTO: 1–3 hours. Faster if you have a warm standby replica.

Scenario 3: Ransomware Attack

Detection: Files encrypted, ransom note present, backups on same server also encrypted.

Recovery:

1. Isolate the server immediately (kill network access).
2. Do NOT pay the ransom.
3. Retrieve backups from the offsite/cloud location (this is why 3-2-1 exists).
4. Provision a clean server from scratch.
5. Restore from clean backup — verify checksum before restoring.
6. Investigate the attack vector before going live again.

Key lesson: Backups stored on the same server are useless in ransomware scenarios. Offsite is non-negotiable.

Scenario 4: Corruption from Application Bug

Detection: Data validation errors, referential integrity violations, missing or garbled records.

Recovery:

1. Stop writes to the affected tables immediately (maintenance mode).
2. Use PITR to restore to before the bug was deployed.
3. Export only the affected tables from the restored database.
4. Compare against current production data carefully.
5. Import clean data with explicit transaction control.

Key lesson: Application bugs can corrupt data silently over days. Retain at least 7 days of backups.

Scenario 5: Cloud Region Outage

Detection: Provider status page shows region-wide incident; application unreachable.

Recovery:

1. If you have a replica in another region: promote it (pg_ctl promote).
2. Update DNS to point to the replica (low TTL is your friend here).
3. If no replica: restore from cross-region backup to a new instance in an available region.

Key lesson: If availability matters, replicas beat restores. Backups are for data protection; replicas are for uptime.

12. Backup Security

Backups contain everything — user data, credentials, application secrets. They need the same security treatment as production databases, often more.

Encrypt Backups at Rest

Using GPG:

# Encrypt
pg_dump -Fc -U postgres mydb | gpg --cipher-algo AES256 -c > backup.dump.gpg

# Decrypt and restore
gpg -d backup.dump.gpg | pg_restore -d mydb

Using age (simpler, modern alternative to GPG):

# Generate key pair once
age-keygen -o backup.key

# Encrypt
pg_dump -Fc -U postgres mydb | age -r $(grep "public key" backup.key | awk '{print $4}') > backup.dump.age

# Decrypt
age -d -i backup.key backup.dump.age | pg_restore -d mydb

Minimal Privilege Backup Users

PostgreSQL — create a dedicated backup role:

CREATE ROLE backup_user LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup_user;
-- For pg_dumpall, the user also needs pg_read_all_data superuser attribute
GRANT pg_read_all_data TO backup_user;

MySQL — create a dedicated backup user:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Access Control and Compliance

• Store backup encryption keys separately from backup files (different server or secret manager).
• Audit access to backup storage — who can retrieve a backup? Log every access.
• Define retention policies: GDPR requires you to delete personal data on request, including backups. Know where your data lives.
• Never store backups on the same physical or virtual machine as the database they protect.
• Rotate backup encryption keys annually and re-encrypt older backups.

Quick Reference: Command Cheatsheet

PostgreSQL Commands

MySQL Commands

Backup Strategy Matrix

Frequently Asked Questions

How often should I back up a production database?

At minimum, daily full logical backups (pg_dump or mysqldump) retained for 7 days. If your RPO requirement is tighter than 24 hours, add WAL archiving (PostgreSQL) or binary log replication (MySQL) on top of the daily baseline.

Is mysqldump or pg_dump enough, or do I need physical backups too?

Logical backups (mysqldump, pg_dump) are sufficient for most databases under roughly 50GB. Above that, physical backup tools like XtraBackup or pg_basebackup become significantly faster to create and restore, since they copy raw data files instead of reconstructing SQL statements.

Why did my backup restore fail even though the backup file looked fine?

The most common causes are a corrupted file (verify with a checksum before restoring), a version mismatch between the backup tool and the target database, or a permissions issue with the restore user. Testing restores on a schedule, not just when something breaks, catches this before it becomes a crisis.

What is the difference between a backup and a database replica?

A backup protects against data loss and corruption; a replica protects against downtime. Restoring from backup takes time and only recovers to the point of the last backup or WAL/binlog replay. Promoting a replica is near-instant but does not protect against replicated corruption or accidental deletes, since those replicate too.

Conclusion

Backups are insurance. Like all insurance, their value is invisible until the moment you actually need them — and at that point, how well you prepared determines whether a bad day becomes a recoverable incident or a permanent loss.

Start with the basics: daily pg_dump or mysqldump, retained for 7 days, synced to a remote location. Then add WAL archiving or binary log replication when your RPO requirements tighten. Test your restores on a schedule, not just when something breaks.

The difference between companies that survive data incidents and those that don't isn't luck. It's the work done before the incident happened.

If you're managing multiple databases across multiple servers, Panelica handles automated backups — full, incremental, and per-database — with remote destinations (S3, SFTP, Google Drive, OneDrive) and one-click restore. See the complete backup and restore guide for the full feature set, or granular restore if you only need one file back, not the whole database.