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

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

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

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

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

目录

1. 了解PostgreSQL备份选项
2. 先决条件和权限要求
3. 方法1——使用pg_dump进行SQL转储
4. 方法2——使用pg_dumpall备份所有数据库
5. 方法3——为大型数据库进行自定义格式备份
6. 从SQL转储恢复
7. 从自定义格式转储恢复
8. 方法4——连续归档和时间点恢复(PITR)
9. 使用Cron自动化备份
10. 保护和离线存储备份
11. 最佳实践总结

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运行备份：

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备份需要纪律和分层方法。遵循这些最佳实践以确保您的数据始终受到保护：