Back to Blog
team@tinypod.app

Database Backup Strategies: Never Lose Data Again

Database backups are your last line of defense. Here's how to implement bulletproof backup strategies for PostgreSQL, MySQL, and SQLite.

databasebackupspostgresqlmysql

Types of Database Backups


Logical Backups (Dumps)

Export data as SQL statements. pg_dump for PostgreSQL, mysqldump for MySQL.

  • Portable across versions
  • Human-readable
  • Slow for large databases
  • Full backup only (no incremental)

    • Physical Backups

    Copy the actual data files. pg_basebackup for PostgreSQL, xtrabackup for MySQL.

  • Fast for large databases
  • Supports incremental backups
  • Must match the exact database version

    • WAL/Binlog Archiving

    Continuously archive transaction logs. Enables point-in-time recovery (PITR).

  • Zero data loss possible
  • Recover to any point in time
  • Requires more storage
  • More complex setup

    • PostgreSQL Backup Strategy


    Daily Dumps

    pg_dump -Fc dbname > backup_$(date +%Y%m%d).dump


    -Fc = custom format (compressed, supports parallel restore)


    Continuous WAL Archiving

    Configure archive_mode = on and archive_command to copy WAL files to backup storage.


    Recover to any point: pg_restore + WAL replay to desired timestamp.


    Verification

    Restore backups to a test database weekly. A backup you haven't tested is not a backup.


    MySQL/MariaDB Backup Strategy


    Daily Dumps

    mysqldump --single-transaction --routines --triggers dbname > backup.sql


    --single-transaction: Consistent backup without locking tables.


    Binary Log Archiving

    Enable log_bin for point-in-time recovery. Archive binlogs to backup storage.


    SQLite Backup


    SQLite is a single file. The .backup command creates a safe copy:

    sqlite3 mydb.sqlite ".backup backup.sqlite"


    Never copy the file while it's being written to — use .backup or sqlite3_backup API.


    Storage


    Where to Store Backups

  • Local disk: Fast recovery but lost if server dies
  • Remote server: Different physical location
  • Object storage (S3, B2, R2): Cheap, durable, offsite
  • Use Restic or Borg for encrypted, deduplicated backups to any storage

    • Retention Policy

  • Daily backups: keep 7 days
  • Weekly backups: keep 4 weeks
  • Monthly backups: keep 12 months

    • Common Mistakes


    1. Not testing restores (most common)

    2. Storing backups on the same disk

    3. No encryption for backup files

    4. No monitoring for backup job failures

    5. Backing up only the database, not the app data


    TinyPod automates database backups with configurable retention and one-click restore.