数据库备份策略

备份策略参考

PostgreSQL 备份与恢复

# Full dump (logical backup)
pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump
# -F c = custom format (recommended, supports parallel restore)

# Restore
pg_restore -h localhost -U postgres -d mydb_new -j 4 mydb.dump
# -j 4 = 4 parallel workers

# Point-in-Time Recovery setup
# postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-bucket/wal/%f'

# Continuous backup with pgBackRest
pgbackrest --stanza=main backup --type=full
pgbackrest --stanza=main backup --type=incr   # incremental

MySQL 备份与恢复

# Logical dump
mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb.sql
# --single-transaction: consistent InnoDB backup without locking

# Restore
mysql -u root -p mydb < mydb.sql

# Physical backup with Percona XtraBackup (no lock)
xtrabackup --backup --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

# Automated daily backup script
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump -u backup_user -p$PASS --all-databases --single-transaction \
  | gzip | aws s3 cp - s3://backups/mysql_$DATE.sql.gz

3-2-1备份原则