2026年CentOS上PostgreSQL备份与恢复完全指南：pg_dump到PITR全流程(2026)

一、PostgreSQL备份的重要性与类型

数据是企业最核心的资产。无论是硬件故障、人为误操作、软件Bug还是勒索软件攻击，缺少有效的备份策略都可能导致灾难性后果。PostgreSQL作为世界最先进的开源关系型数据库，提供了完整的备份与恢复工具链。

备份的两大类型：

备份类型	工具	优点	缺点	适用场景
逻辑备份	pg_dump, pg_dumpall	灵活、可跨版本恢复	恢复速度慢、数据量大时耗时长	小型数据库、跨版本迁移
物理备份	pg_basebackup	恢复速度快、数据一致性好	需要停止写入或开启归档	大型数据库、PITR需求
增量备份	WAL归档 + pg_basebackup	节省空间、支持PITR	配置复杂	关键业务、需要时间点恢复

二、CentOS上PostgreSQL环境准备

2.1 确认PostgreSQL版本与数据目录

# 查看PostgreSQL版本
psql --version
# 或
sudo -u postgres psql -c "SELECT version();"

# 查看数据目录
sudo -u postgres psql -c "SHOW data_directory;"

# 查看WAL归档设置
sudo -u postgres psql -c "SHOW archive_mode;"
sudo -u postgres psql -c "SHOW archive_command;"

2.2 创建备份专用目录

# 创建备份目录
sudo mkdir -p /var/backups/postgres
sudo chown postgres:postgres /var/backups/postgres
sudo chmod 700 /var/backups/postgres

# 创建归档目录（用于WAL归档）
sudo mkdir -p /var/backups/postgres/wal_archive
sudo chown postgres:postgres /var/backups/postgres/wal_archive
sudo chmod 700 /var/backups/postgres/wal_archive

2.3 配置WAL归档（为物理备份和PITR做准备）

编辑 postgresql.conf：

sudo nano /var/lib/pgsql/15/data/postgresql.conf

# 修改以下参数：
archive_mode = on
archive_command = 'test ! -f /var/backups/postgres/wal_archive/%f && cp %p /var/backups/postgres/wal_archive/%f'
wal_level = replica  # 或 logical

# 重启PostgreSQL使配置生效
sudo systemctl restart postgresql-15

三、使用pg_dump进行逻辑备份

3.1 基础备份命令

# 备份单个数据库（自定义格式，可压缩）
sudo -u postgres pg_dump -Fc myapp_db > /var/backups/postgres/myapp_db_$(date +%Y%m%d_%H%M%S).dump

# 备份单个数据库（SQL格式，可读）
sudo -u postgres pg_dump -Fp myapp_db > /var/backups/postgres/myapp_db_$(date +%Y%m%d_%H%M%S).sql

# 备份所有数据库
sudo -u postgres pg_dumpall > /var/backups/postgres/all_databases_$(date +%Y%m%d_%H%M%S).sql

# 只备份表结构（不含数据）
sudo -u postgres pg_dump -s myapp_db > /var/backups/postgres/myapp_db_schema_$(date +%Y%m%d_%H%M%S).sql

# 只备份数据（不含表结构）
sudo -u postgres pg_dump -a myapp_db > /var/backups/postgres/myapp_db_data_$(date +%Y%m%d_%H%M%S).sql

3.2 并行备份（加速大型数据库）

# 使用pg_dump并行备份（需PostgreSQL 9.3+）
sudo -u postgres pg_dump -Fc -j 4 myapp_db -f /var/backups/postgres/myapp_db_parallel.dump

# -j 4 表示使用4个并行任务，可显著加速大型数据库备份

3.3 压缩与分割备份文件

# 直接压缩备份
sudo -u postgres pg_dump -Fc myapp_db | gzip > /var/backups/postgres/myapp_db_$(date +%Y%m%d_%H%M%S).dump.gz

# 分割大文件（每个文件1GB）
sudo -u postgres pg_dump -Fc myapp_db | split -b 1G - /var/backups/postgres/myapp_db_$(date +%Y%m%d_%H%M%S).dump.

# 使用pigz多线程压缩（比gzip快）
sudo yum install -y pigz
sudo -u postgres pg_dump -Fc myapp_db | pigz > /var/backups/postgres/myapp_db_$(date +%Y%m%d_%H%M%S).dump.pigz

3.4 备份特定表

# 只备份特定表
sudo -u postgres pg_dump -t users -t orders -t products myapp_db > /var/backups/postgres/specific_tables_$(date +%Y%m%d_%H%M%S).sql

# 排除特定表
sudo -u postgres pg_dump -T logs -T temp_data myapp_db > /var/backups/postgres/exclude_tables_$(date +%Y%m%d_%H%M%S).sql

四、使用pgAdmin进行图形化备份

4.1 通过pgAdmin备份数据库

打开pgAdmin，连接到目标服务器
在左侧树形菜单中右键点击要备份的数据库
选择「Backup…」
在弹出窗口中配置：
Filename：备份文件路径（如 /var/backups/postgres/myapp_db.backup）
Format：选择 Custom 或 Plain（推荐Custom格式）
Compression：选择压缩级别（1-9）
Encoding：选择 UTF8
Only data/Only schema：根据需要选择
点击「Backup」开始备份

4.2 备份作业调度（pgAgent）

可以使用pgAgent创建定时备份作业：

-- 安装pgAgent扩展
CREATE EXTENSION pgagent;

-- 创建每日凌晨2点备份的作业（通过pgAdmin界面配置更直观）

五、使用pg_basebackup进行物理备份

5.1 基础物理备份

# 创建物理备份（基础备份）
sudo -u postgres pg_basebackup -D /var/backups/postgres/basebackup_$(date +%Y%m%d_%H%M%S) -Ft -z -Xs -P

# 参数说明：
# -D : 备份目录
# -Ft : 输出格式为tar
# -z : 压缩
# -Xs : 包含WAL文件（流复制模式）
# -P : 显示进度

5.2 远程物理备份

# 从远程服务器备份到本地
pg_basebackup -h 192.168.1.100 -p 5432 -U replicator \
  -D /var/backups/postgres/remote_backup_$(date +%Y%m%d_%H%M%S) \
  -Ft -z -Xs -P

# 需要配置pg_hba.conf允许replicator用户连接

六、数据恢复方法

6.1 恢复逻辑备份（pg_restore）

# 恢复整个数据库（先创建空数据库）
sudo -u postgres psql -c "CREATE DATABASE myapp_db_restored;"
sudo -u postgres pg_restore -d myapp_db_restored /var/backups/postgres/myapp_db_20260511_230000.dump

# 恢复时指定并行度（加速大型数据库恢复）
sudo -u postgres pg_restore -d myapp_db_restored -j 4 /var/backups/postgres/myapp_db_parallel.dump

# 恢复SQL格式的备份
sudo -u postgres psql myapp_db_restored < /var/backups/postgres/myapp_db_20260511_230000.sql

# 只恢复表结构
sudo -u postgres pg_restore -d myapp_db_restored -s /var/backups/postgres/myapp_db_20260511_230000.dump

# 恢复特定表
sudo -u postgres pg_restore -d myapp_db_restored -t users -t orders /var/backups/postgres/myapp_db_20260511_230000.dump

6.2 恢复物理备份

“`bash# 1. 停止PostgreSQL服务
sudo systemctl stop postgresql-15

sudo mv /var/lib/pgsql/15/data /var/lib/pgsql/15/data.bak

sudo mkdir /var/lib/pgsql/15/data
sudo tar -xzf /var/backups/postgres/basebackup_20260511_230000.tar.gz -C /var/lib/pgsql/15/data

sudo chown -R postgres:postgres /var/lib/pgsql/15/data
sudo chmod 700 /var/lib/pgsql/15/data

sudo systemctl start postgresql-15


### 6.3 恢复pg_dumpall的备份

```bash
# 恢复所有数据库（会覆盖现有数据！）
sudo -u postgres psql -f /var/backups/postgres/all_databases_20260511_230000.sql

# 或者先删除现有数据库再恢复
sudo -u postgres psql -c "DROP DATABASE IF EXISTS myapp_db;"
sudo -u postgres psql -c "CREATE DATABASE myapp_db;"
sudo -u postgres psql myapp_db < /var/backups/postgres/myapp_db_20260511_230000.sql

七、时间点恢复（PITR – Point-In-Time Recovery）

7.1 PITR原理

PITR允许你将数据库恢复到任意时间点，而不仅仅是备份时刻。它通过结合基础备份和WAL归档来实现。

工作流程：
1. 创建一个基础备份（pg_basebackup）
2. 持续归档WAL文件
3. 恢复时：恢复基础备份 + 重放WAL文件到指定时间点

7.2 配置PITR环境

编辑 postgresql.conf：

sudo nano /var/lib/pgsql/15/data/postgresql.conf

# 设置以下参数：
archive_mode = on
archive_command = 'test ! -f /var/backups/postgres/wal_archive/%f && cp %p /var/backups/postgres/wal_archive/%f'
wal_level = replica
max_wal_senders = 10  # 允许足够的WAL发送进程

7.3 执行PITR恢复

# 1. 停止PostgreSQL
sudo systemctl stop postgresql-15

# 2. 备份当前数据目录
sudo mv /var/lib/pgsql/15/data /var/lib/pgsql/15/data.bak

# 3. 恢复基础备份
sudo tar -xzf /var/backups/postgres/basebackup_20260510_020000.tar.gz -C /var/lib/pgsql/15/data

# 4. 创建recovery.signal文件（PostgreSQL 12+）
sudo touch /var/lib/pgsql/15/data/recovery.signal

# 5. 配置恢复参数（postgresql.conf或恢复命令文件）
sudo nano /var/lib/pgsql/15/data/postgresql.conf

# 添加以下参数：
restore_command = 'cp /var/backups/postgres/wal_archive/%f %p'
recovery_target_time = '2026-05-11 14:30:00 UTC'  # 恢复到这个时间
# 或者使用 recovery_target_name（需要之前创建过还原点）
# recovery_target_name = 'before_disaster'

# 6. 启动PostgreSQL（会自动进入恢复模式）
sudo systemctl start postgresql-15

# 7. 恢复完成后，Promote数据库
sudo -u postgres pg_ctl promote -D /var/lib/pgsql/15/data

7.4 创建还原点

-- 在执行重要操作前创建还原点
SELECT pg_create_restore_point('before_disaster');

-- 查看所有还原点
SELECT * FROM pg_create_restore_point('before_disaster');

八、备份策略设计

8.1 推荐备份策略

备份类型	频率	保留时间	存储位置
pg_dump（逻辑备份）	每日	7天	本地 + 远程
pg_basebackup（物理备份）	每周	4周	本地 + 远程
WAL归档	实时	4周	远程
全系统快照	每月	3个月	远程（对象存储）

8.2 自动化备份脚本

#!/bin/bash
# /usr/local/bin/postgres_backup.sh

BACKUP_DIR="/var/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# 创建当日备份目录
mkdir -p $BACKUP_DIR/$DATE

# 逻辑备份
sudo -u postgres pg_dump -Fc myapp_db -f $BACKUP_DIR/$DATE/myapp_db_$DATE.dump

# 压缩备份
gzip $BACKUP_DIR/$DATE/myapp_db_$DATE.dump

# 清理旧备份
find $BACKUP_DIR -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

# 远程同步（使用rsync）
rsync -avz $BACKUP_DIR/ remote_backup_server:/backups/postgres/

echo "Backup completed at $DATE"

添加到crontab：

# 每日凌晨2点执行备份
0 2 * * * /usr/local/bin/postgres_backup.sh >> /var/log/postgres_backup.log 2>&1

九、备份验证与监控

9.1 验证备份完整性

# 验证pg_dump备份文件
pg_restore --list /var/backups/postgres/myapp_db_20260511_230000.dump

# 验证物理备份
sudo -u postgres pg_checksums -D /var/lib/pgsql/15/data

# 定期恢复测试（建议在测试环境执行）
sudo -u postgres pg_restore -d test_db /var/backups/postgres/myapp_db_20260511_230000.dump

9.2 监控备份状态

-- 查看最近备份时间
SELECT datname, 
       pg_size_pretty(pg_database_size(datname)) as db_size,
       (SELECT MAX(backup_start_time) FROM pg_stat_bgwriter) as last_bgwriter_update
FROM pg_database;

-- 查看WAL归档状态
SELECT * FROM pg_stat_archiver;

-- 查看归档延迟
SELECT 
    pending_location AS pending_lsn,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), pending_location)) AS pending_size
FROM pg_stat_archiver;

十、常见问题与解决方案

10.1 备份时提示”permission denied”

原因：postgres用户没有备份目录的写权限
解决：

sudo chown postgres:postgres /var/backups/postgres
sudo chmod 700 /var/backups/postgres

10.2 恢复时提示”database already exists”

解决：先删除现有数据库或使用-C参数让pg_restore自动创建：

sudo -u postgres pg_restore -C -d postgres /path/to/backup.dump

10.3 WAL归档失败

排查：

# 查看归档状态
sudo -u postgres psql -c "SELECT * FROM pg_stat_archiver;"

# 查看PostgreSQL日志
sudo tail -f /var/lib/pgsql/15/data/log/postgresql-*.log | grep archive

常见原因：
– archive_command路径不存在或权限不足
– 磁盘空间不足
– WAL文件已存在（archive_command中的test ! -f条件）

十一、总结

PostgreSQL在CentOS上的备份与恢复是保障数据安全的核心工作。本指南详细介绍了：

逻辑备份：pg_dump适合小型数据库和跨版本迁移
物理备份：pg_basebackup适合大型数据库，恢复速度快
时间点恢复：结合WAL归档，可将数据库恢复到任意时间点，为关键业务提供最高级别的数据保护
自动化策略：通过cron+脚本实现定期备份，结合远程同步提升容灾能力
验证与监控：定期验证备份完整性，监控WAL归档状态

构建备份策略时，建议采用”3-2-1原则”：至少保留3份副本、存储在2种不同介质、其中1份在异地。同时，备份只是手段，能够快速恢复才是目的——定期进行恢复演练，确保在真正需要时能够快速有效地恢复数据。

鲨鱼博客

2026年CentOS上PostgreSQL备份与恢复完全指南：pg_dump到PITR全流程(2026)

一、PostgreSQL备份的重要性与类型

二、CentOS上PostgreSQL环境准备

2.1 确认PostgreSQL版本与数据目录

2.2 创建备份专用目录

2.3 配置WAL归档（为物理备份和PITR做准备）

三、使用pg_dump进行逻辑备份

3.1 基础备份命令

3.2 并行备份（加速大型数据库）

3.3 压缩与分割备份文件

3.4 备份特定表

四、使用pgAdmin进行图形化备份

4.1 通过pgAdmin备份数据库

4.2 备份作业调度（pgAgent）

五、使用pg_basebackup进行物理备份

5.1 基础物理备份

5.2 远程物理备份

六、数据恢复方法

6.1 恢复逻辑备份（pg_restore）

6.2 恢复物理备份

七、时间点恢复（PITR – Point-In-Time Recovery）

7.1 PITR原理

7.2 配置PITR环境

7.3 执行PITR恢复

7.4 创建还原点

八、备份策略设计

8.1 推荐备份策略

8.2 自动化备份脚本

九、备份验证与监控

9.1 验证备份完整性

9.2 监控备份状态

十、常见问题与解决方案

10.1 备份时提示”permission denied”

10.2 恢复时提示”database already exists”

10.3 WAL归档失败

十一、总结

发表回复取消回复

一、PostgreSQL备份的重要性与类型

二、CentOS上PostgreSQL环境准备

2.1 确认PostgreSQL版本与数据目录

2.2 创建备份专用目录

2.3 配置WAL归档（为物理备份和PITR做准备）

三、使用pg_dump进行逻辑备份

3.1 基础备份命令

3.2 并行备份（加速大型数据库）

3.3 压缩与分割备份文件

3.4 备份特定表

四、使用pgAdmin进行图形化备份

4.1 通过pgAdmin备份数据库

4.2 备份作业调度（pgAgent）

五、使用pg_basebackup进行物理备份

5.1 基础物理备份

5.2 远程物理备份

六、数据恢复方法

6.1 恢复逻辑备份（pg_restore）

6.2 恢复物理备份

七、时间点恢复（PITR – Point-In-Time Recovery）

7.1 PITR原理

7.2 配置PITR环境

7.3 执行PITR恢复

7.4 创建还原点

八、备份策略设计

8.1 推荐备份策略

8.2 自动化备份脚本

九、备份验证与监控

9.1 验证备份完整性

9.2 监控备份状态

十、常见问题与解决方案

10.1 备份时提示”permission denied”

10.2 恢复时提示”database already exists”

10.3 WAL归档失败

十一、总结

发表回复 取消回复

发表回复取消回复