pg数据库如何备份到另一台服务器？步骤是什么？

酷盾叔 • 2025年12月20日 04:34 • 虚拟主机 • 阅读 27

将PostgreSQL数据库备份到另一台服务器是数据管理和灾备恢复中的常见操作,通常涉及逻辑备份（如pg_dump）或物理备份（如pg_basebackup）两种方式，以下是详细操作步骤及注意事项，确保数据迁移的完整性和安全性。

备份前准备工作

源服务器配置
- 确认PostgreSQL服务运行正常,可通过SELECT pg_is_in_recovery();检查数据库状态（非恢复模式）。
- 创建具有备份权限的用户（如backup_user），赋予CONNECT、SELECT权限：
```
CREATE USER backup_user WITH PASSWORD 'secure_password';  
GRANT CONNECT ON DATABASE your_db TO backup_user;  
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;  
```
- 记录源数据库的版本（pg_version）、表空间路径等信息，确保目标服务器兼容。
目标服务器配置
- 安装与源服务器相同或更高版本的PostgreSQL。
- 创建与源数据库同名的用户和数据库（若使用逻辑备份恢复）：
```
CREATE USER backup_user WITH PASSWORD 'secure_password';  
CREATE DATABASE your_db OWNER backup_user;  
```
- 确保目标服务器磁盘空间充足,可通过df h检查。

适用于中小型数据库或跨版本迁移,备份为SQL脚本或自定义格式文件。

执行备份
在源服务器执行以下命令（以自定义格式为例）：
```
pg_dump U backup_user F c f /path/to/backup.dump your_db
```
- U：指定用户；F c：自定义格式（压缩率高）；f：备份文件路径。
传输备份文件
使用scp或rsync将备份文件传输到目标服务器：
```
scp /path/to/backup.dump user@target_server:/path/to/destination/
```
确保传输过程中文件完整性（可通过md5sum校验）。
恢复数据库
在目标服务器执行：
```
pg_restore U backup_user d your_db /path/to/backup.dump
```
- 若为SQL格式备份,使用psql U backup_user d your_db f backup.sql恢复。

适用于大型数据库或流复制场景,直接复制数据文件，恢复速度快。

执行备份
在源服务器（需开启wal_level=replica）执行：
```
pg_basebackup h localhost U replication_user Ft z P D /path/to/backup
```
- Ft：tar格式；z：压缩；P：显示进度。
传输备份文件
将备份目录（含数据文件和WAL日志）打包传输至目标服务器。
恢复数据库
- 停止目标服务器PostgreSQL服务：systemctl stop postgresql。
- 替换数据目录：mv /var/lib/pgsql/data /var/lib/pgsql/data_old，解压备份文件到/var/lib/pgsql/data。
- 修改postgresql.conf，确保data_dir、port等配置正确。
- 启动服务：systemctl start postgresql。

验证数据一致性
- 检查表数量、行数是否匹配：
```
SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';  
SELECT reltuples::bigint FROM pg_class WHERE relname = 'your_table';  
```
- 对比源库和目标库的pg_dump输出摘要。
定期备份与自动化
结合cron任务实现定时备份，例如每日凌晨2点执行逻辑备份：
```
0 2 * * * /usr/bin/pg_dump U backup_user F c f /backup/$(date +%Y%m%d).dump your_db
```
备份文件管理
采用备份轮转策略（如保留最近7天的备份），避免磁盘空间耗尽。

问题场景	可能原因	解决方案
恢复后表权限丢失	未授予目标用户对象权限	重新执行`GRANT`语句或使用`noowner`备份
物理备份恢复后无法启动	数据文件权限或配置文件路径错误	检查`data_dir`权限，确保`pg_hba.conf`配置正确