Database Backup Strategies for Production Apps

Why Backups Are Non-Negotiable

Data loss can come from hardware failure, accidental DELETE statements, ransomware, or botched migrations. Without a tested backup strategy, any of these events can be catastrophic. A backup that has never been restored is not a backup — it's wishful thinking.

This guide covers the practical backup strategies every production application should implement.

Types of Backups

Logical Backups with pg_dump

pg_dump exports a PostgreSQL database as SQL or a compressed archive:

# Plain SQL — human-readable, portable
pg_dump -h localhost -U myuser -d myapp > backup_$(date +%F).sql

# Compressed custom format — faster, supports parallel restore
pg_dump -h localhost -U myuser -Fc -d myapp -f backup_$(date +%F).dump

# Table-only backup
pg_dump -h localhost -U myuser -d myapp -t orders -Fc -f orders_$(date +%F).dump

Automate daily backups with a cron expression (or a PandaStack cronjob):

# Run at 2 AM every day
0 2 * * * pg_dump -h $DB_HOST -U $DB_USER -Fc -d $DB_NAME   -f /backups/myapp_$(date +%F).dump

Restoring from a pg_dump

# From plain SQL
psql -h localhost -U myuser -d myapp_restore < backup_2026-05-01.sql

# From custom format (supports parallel workers)
pg_restore -h localhost -U myuser -d myapp_restore   -j 4 backup_2026-05-01.dump

Always restore to a separate database first to verify integrity before touching production.

MySQL Backups with mysqldump

# Full logical backup
mysqldump -h localhost -u myuser -p myapp   --single-transaction --routines --triggers   > backup_$(date +%F).sql

# Restore
mysql -h localhost -u myuser -p myapp < backup_2026-05-01.sql

The --single-transaction flag takes a consistent snapshot without locking tables — critical for production.

Point-in-Time Recovery (PITR)

PITR lets you roll back to any exact moment — invaluable for recovering from DROP TABLE or accidental bulk deletes.

PostgreSQL implements PITR via Write-Ahead Logging (WAL):

# In postgresql.conf — enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /wal-archive/%f'

# Restore to a specific time (in recovery.conf / postgresql.conf)
restore_command = 'cp /wal-archive/%f %p'
recovery_target_time = '2026-05-01 14:30:00'

Managed databases on [PandaStack](https://dashboard.pandastack.io) handle WAL archiving and PITR automatically — no manual configuration needed.

The 3-2-1 Backup Rule

Every production database should follow the 3-2-1 rule:

• 3 copies of the data
• 2 different storage media
• 1 copy offsite (e.g., a different cloud region or provider)

In practice:

# Local backup
pg_dump -Fc -d myapp -f /backups/myapp_$(date +%F).dump

# Upload to S3 (offsite)
aws s3 cp /backups/myapp_$(date +%F).dump   s3://my-backup-bucket/postgres/myapp_$(date +%F).dump

# Verify upload
aws s3 ls s3://my-backup-bucket/postgres/ | grep $(date +%F)

Testing Your Backups

A backup you've never restored is untested. Schedule monthly restore drills:

# Spin up a test database
createdb myapp_test_restore

# Restore the latest backup
pg_restore -d myapp_test_restore -j 4 /backups/myapp_latest.dump

# Run smoke tests
psql -d myapp_test_restore -c "SELECT COUNT(*) FROM users;"
psql -d myapp_test_restore -c "SELECT COUNT(*) FROM orders WHERE status='completed';"

# Drop test database
dropdb myapp_test_restore

Automate these checks and alert on failure.

Backup Retention Policy

Keep backups long enough to detect problems, but not so long that storage costs become excessive:

Prune old backups automatically:

# Delete local backups older than 14 days
find /backups -name "*.dump" -mtime +14 -delete

Managed Backups on PandaStack

[PandaStack](https://dashboard.pandastack.io) automates backups for all managed databases — PostgreSQL, MySQL, Redis, and MongoDB. Daily snapshots, configurable retention, and one-click restore are built in. No cron scripts, no S3 wiring, no manual verification.

Explore the docs at [docs.pandastack.io](https://docs.pandastack.io).

Summary

• Use pg_dump/mysqldump for portable logical backups
• Enable WAL archiving for point-in-time recovery
• Follow the 3-2-1 rule: 3 copies, 2 media, 1 offsite
• Test restores regularly — untested backups are unreliable
• Automate everything and alert on failures