PostgreSQL 数据库备份和恢复：AlexHost 用户完整指南

为什么 PostgreSQL 备份策略比你想象的更重要

数据丢失不是假设风险 — 它是每个数据库管理员都会在某个时刻面临的操作确定性。硬件故障、意外删除、损坏的事务和勒索软件攻击都可能在几秒内使生产环境瘫痪。对于 PostgreSQL 用户来说，拥有一个强大的、经过测试的、自动化的备份策略是小事件和灾难性业务失败之间的区别。

AlexHost 专用服务器为托管和保护 PostgreSQL 数据库提供了理想的基础。凭借企业级 NVMe SSD 存储提供的卓越 I/O 吞吐量、完整的 root 访问权限以实现完全配置控制，以及内置的 DDoS 防护，AlexHost 为您提供了严肃数据库工作负载所需的基础设施性能和安全态势。

无论您运行的是高流量电子商务平台、SaaS 应用程序、由关系数据库支持的 WordPress 安装，还是自定义企业系统，本指南都会引导您了解每种主要的 PostgreSQL 备份和恢复方法 — 从简单的 SQL 转储到高级时间点恢复 (PITR) — 所有这些都针对生产环境进行了优化。

1. 理解 PostgreSQL 备份选项

PostgreSQL 附带了多个成熟、文档完善的备份机制。选择正确的备份方式取决于您的数据库大小、恢复时间目标 (RTO)、恢复点目标 (RPO) 和运营复杂性容限。

在开始之前理解这些权衡至关重要。大多数生产环境受益于结合至少两种方法 — 例如，每晚的自定义格式转储以及连续的 WAL 归档以获得粒度化的恢复能力。

2. 前置条件和权限要求

在执行任何备份操作之前，请确认以下前置条件已满足：

用户权限：

• 您必须是 PostgreSQL 超级用户或目标数据库的所有者才能执行完整备份。
• 对于 pg_dumpall，超级用户权限是必需的。

验证您的 PostgreSQL 版本：

psql --version

备份前检查可用磁盘空间：

df -h /var/lib/postgresql/

确保您的备份目标有足够的可用空间 — 至少是要备份的数据库大小的 1.5 倍，以便为临时文件和压缩开销留出空间。

通过 SSH 连接到您的服务器：

ssh root@your-server-ip

如果您使用的是 VPS 主机计划，您将拥有完整的 SSH 访问权限，并能够不受限制地安装、配置和管理 PostgreSQL。

3. 方法 1 — 使用 pg_dump 的 SQL 转储

pg_dump 实用程序是最常用的 PostgreSQL 备份工具。它生成单个数据库的一致快照，即使数据库正在被积极使用也是如此。输出是一个纯文本 SQL 脚本，可以在任何兼容的 PostgreSQL 安装上进行审查、编辑和重放。

步骤 1：打开终端并访问您的服务器

ssh root@your-alexhost-server-ip

步骤 2：运行 pg_dump 命令

pg_dump -U username -W -F p database_name > /backups/backup_file.sql

参数说明：

实际示例：

pg_dump -U postgres -W -F p my_production_db > /backups/my_production_db_$(date +%Y%m%d_%H%M%S).sql

> 专业提示：使用 $(date +%Y%m%d_%H%M%S) 在备份文件名中附加时间戳，可确保您永远不会意外覆盖以前的备份，并创建自然的时间顺序存档。

步骤 3：验证备份文件

ls -lh /backups/
head -50 /backups/my_production_db_*.sql

该文件应以 PostgreSQL 标头注释和 SET 语句开头，确认已创建有效的转储。

4. 方法 2 — 使用 pg_dumpall 备份所有数据库

当您需要备份 PostgreSQL 实例中的每个数据库时 — 包括全局对象（如角色和表空间）— pg_dumpall 是正确的工具。

pg_dumpall -U postgres -W > /backups/all_databases_$(date +%Y%m%d).sql

此命令导出：

• 所有数据库
• 所有角色（用户和组）
• 所有表空间
• 所有全局配置

重要： 来自 pg_dumpall 的输出文件在繁忙的服务器上可能非常大。确保您的备份分区有足够的空间，并考虑立即压缩输出：

pg_dumpall -U postgres | gzip > /backups/all_databases_$(date +%Y%m%d).sql.gz

5. 方法 3 — 大型数据库的自定义格式备份

对于超过几个 GB 的生产数据库，强烈建议使用自定义格式 (-F c) 而不是纯 SQL 转储。自定义格式备份具有以下特点：

• 默认压缩 — 显著减少存储需求
• 恢复速度更快 — 支持使用 -j 标志进行并行恢复操作
• 可选择性恢复 — 允许您恢复单个表或模式

创建自定义格式备份

pg_dump -U postgres -W -F c my_production_db > /backups/my_production_db_$(date +%Y%m%d).dump

创建压缩目录格式备份（支持并行处理）

pg_dump -U postgres -W -F d -j 4 -f /backups/my_production_db_dir my_production_db

这种方法可以显著减少多核服务器上的备份时间，非常适合 AlexHost 提供的高性能专用服务器环境。

6. 从 SQL 转储恢复

从纯 SQL 转储恢复单个数据库

首先，确保目标数据库存在。如果不存在，请创建它：

psql -U postgres -c "CREATE DATABASE my_restored_db;"

然后恢复：

psql -U postgres -d my_restored_db -f /backups/my_production_db_backup.sql

参数说明：

监控恢复进度

对于大型 SQL 文件，您可以使用 pv 监控进度：

pv /backups/my_production_db_backup.sql | psql -U postgres -d my_restored_db

7. 从自定义格式转储恢复

自定义格式转储需要 pg_restore 实用程序，而不是 psql。

基本恢复

pg_restore -U postgres -d my_restored_db /backups/my_production_db.dump

恢复并自动创建数据库

使用 -C 标志指示 pg_restore 在填充数据库之前创建数据库：

pg_restore -U postgres -C -d postgres /backups/my_production_db.dump

并行恢复以加快恢复速度

pg_restore -U postgres -d my_restored_db -j 4 /backups/my_production_db_dir/

在四核服务器上使用 -j 4 和目录格式备份可将恢复时间减少高达 75% — 这在灾难恢复期间最小化停机时间时是一个显著优势。

仅恢复特定表

pg_restore -U postgres -d my_restored_db -t orders /backups/my_production_db.dump

这种细粒度功能是自定义格式相对于纯 SQL 转储的关键优势之一。

8. 方法 4 — 连续归档和时间点恢复 (PITR)

PITR 是任务关键型 PostgreSQL 部署的黄金标准。它允许您将数据库恢复到任何特定时刻 — 不仅仅是最后一次备份 — 通过在基础备份之上重放预写日志 (WAL) 段。这对于需要从在已知时间戳发生的逻辑错误（例如意外的 DROP TABLE）恢复的场景至关重要。

步骤 1: 在 postgresql.conf 中启用 WAL 归档

找到并编辑您的 PostgreSQL 配置文件：

nano /etc/postgresql/15/main/postgresql.conf

添加或修改以下指令：

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

参数说明：

创建归档目录并设置正确的权限：

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

重启 PostgreSQL 以应用更改：

systemctl restart postgresql

步骤 2: 使用 pg_basebackup 进行基础备份

pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P -Xs

步骤 3: 恢复到特定时间点

从基础备份和 WAL 归档恢复：

1. 停止 PostgreSQL：

systemctl stop postgresql

1. 清空现有数据目录：

rm -rf /var/lib/postgresql/15/main/*

1. 提取基础备份：

tar -xzf /backups/base_backup/base.tar.gz -C /var/lib/postgresql/15/main/

1. 创建 recovery.conf（PostgreSQL 11 及更早版本）或配置 postgresql.conf 并创建 recovery.signal 文件（PostgreSQL 12+）：

# For PostgreSQL 12+
touch /var/lib/postgresql/15/main/recovery.signal

添加到 postgresql.conf：

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-01-15 14:30:00'
recovery_target_action = 'promote'

1. 设置正确的所有权并启动 PostgreSQL：

chown -R postgres:postgres /var/lib/postgresql/15/main/
systemctl start postgresql

PostgreSQL 将重放 WAL 段直到指定的时间戳，然后提升为正常的读写状态。

9. 使用 Cron 自动化备份

手动备份不可靠。使用 cron 自动化备份计划可确保一致性并消除人为错误因素。

创建备份脚本

nano /usr/local/bin/pg_backup.sh

#!/bin/bash

# PostgreSQL Automated Backup Script
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_USER="postgres"
DB_NAME="my_production_db"
RETENTION_DAYS=14

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Perform the backup
pg_dump -U "$DB_USER" -F c "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +"$RETENTION_DAYS" -delete

# Log completion
echo "[$TIMESTAMP] Backup of $DB_NAME completed successfully." >> /var/log/pg_backup.log

使脚本可执行：

chmod +x /usr/local/bin/pg_backup.sh

使用 Cron 计划

crontab -e

添加以下行以每晚 2:00 AM 运行备份：

0 2 * * * /usr/local/bin/pg_backup.sh

对于每周完整备份加上每日增量 WAL 归档，将其与前一部分中描述的 PITR 设置相结合。

10. 保护和离线存储备份

存储在与生产数据库相同服务器上的备份不是真正的备份——它是单点故障。实施以下安全和离线存储实践：

在传输前加密备份

gpg --symmetric --cipher-algo AES256 /backups/my_production_db.dump

使用 rsync 将备份传输到远程位置

rsync -avz --progress /backups/postgresql/ user@remote-backup-server:/remote/backups/postgresql/

使用 pg_dump 和 SSH 管道进行直接远程备份

pg_dump -U postgres my_production_db | gzip | ssh user@remote-server "cat > /backups/my_production_db_$(date +%Y%m%d).sql.gz"

PostgreSQL 防火墙规则 (UFW)

将 PostgreSQL 端口访问限制为仅受信任的 IP：

ufw allow from 192.168.1.0/24 to any port 5432
ufw deny 5432
ufw enable

对于在不同托管层级上管理多个项目的团队，AlexHost 共享虚拟主机计划也支持数据库管理工具，可以为较小的项目补充您的备份工作流。

11. 最佳实践总结

正确实施 PostgreSQL 备份需要纪律和分层方法。遵循这些最佳实践以确保您的数据始终受到保护：

> 关键提醒：从未测试过的备份不是备份——它是一个假设。定期安排恢复演练并记录结果。

结论：在 AlexHost 上自信地保护您的 PostgreSQL 数据

PostgreSQL 提供了任何开源数据库系统中最全面的备份和恢复工具集之一。从 pg_dump 的简单性用于快速 SQL 快照，到 PITR 的精确性用于粒度级时间点恢复，您拥有构建防弹数据保护策略所需的一切。

关键是执行：自动化您的备份，定期验证它们，在传输前加密它们，并将它们存储在异地。结合 AlexHost 专用服务器的性能和可靠性——具有 NVMe 存储、完全根访问权限和企业级 DDoS 保护——您的 PostgreSQL 数据库将既快速又具有弹性。

对于需要可扩展基础设施而无需管理裸机开销的团队，AlexHost VPS 主机提供了一个灵活、经济高效的替代方案，具有相同的性能和正常运行时间承诺。如果您需要端到端保护数据库支持的 Web 应用程序，将您的主机与 AlexHost SSL 证书配对可确保应用程序层和用户之间的加密通信。

立即开始实施这些备份策略。您的未来自我——在凌晨 3 点面对损坏的生产数据库事件——会感谢您的。