Debian PostgreSQL备份与恢复实践

一、备份实践

1. 逻辑备份（常用）

   • pg_dump：导出数据库结构和数据为SQL或自定义格式文件，支持压缩和增量备份。
     • 备份整个数据库：
       pg_dump -U username -F c -b -v -f /path/backup.dump dbname
     • 备份特定表：
       pg_dump -U username -t table_name -f /path/table_backup.sql dbname
     • 压缩备份：
       pg_dump -U username -F c dbname | gzip > dbname.dump.gz
   • pg_dumpall：备份整个集群（所有数据库和角色），仅支持SQL格式。
     pg_dumpall -U username -f /path/cluster_backup.sql

2. 物理备份

   • pg_basebackup：复制数据库文件，适用于大型数据库快速恢复。
     pg_basebackup -U username -D /path/backup -F t -z -P

3. 自动化备份

   • 通过cron定时执行备份脚本，例如每日凌晨备份：
     0 2 * * * pg_dump -U username -F c dbname /backup/dbname_$(date +\%Y\%m\%d).dump

二、恢复实践

1. 逻辑备份恢复

   • pg_restore：恢复自定义格式或tar格式备份。
     pg_restore -U username -d dbname /path/backup.dump
   • psql：恢复SQL格式备份。
     psql -U username -d dbname -f /path/backup.sql

2. 物理备份恢复

   • 停止PostgreSQL服务：sudo systemctl stop postgresql
   • 清空数据目录（谨慎操作）：rm -rf /var/lib/postgresql/*
   • 解压并恢复备份文件：tar -xzf /path/backup.tar.gz -C /var/lib/postgresql/
   • 启动服务：sudo systemctl start postgresql

3. 高级恢复（PITR）

   • 配置WAL归档（修改postgresql.conf）：
     wal_level = replica\narchive_mode = on\narchive_command = 'cp %p /backup/wal/%f'
   • 恢复时指定时间点：
     recovery_target_time='YYYY-MM-DD HH:MI:SS'

三、注意事项

• 权限与安全：确保备份文件存储在安全位置，恢复时使用sudo权限。
• 验证备份：定期用pg_restore -l列出备份内容，或尝试恢复到测试环境。
• 大数据库优化：优先使用pg_basebackup物理备份，或结合WAL归档减少恢复时间。

参考来源：[1,2,3,4,5,6,7,8,9,10,11]