PostgreSQL数据库备份与恢复的四种办法_MsSqlserver

在数据为王的时代，数据库中存储的信息堪称企业的生命线，而postgresql作为一款广泛应用的开源数据库，学会如何妥善进行备份与恢复操作，是每个开发者与运维人员必备的技能。今天，咱们就深入探究一下postgresql相关的备份恢复策略，并附上丰富的代码示例。

一、pg_dump：逻辑备份的利器

pg_dump是postgresql自带的用于生成数据库逻辑备份的工具。它能把数据库的结构以及数据，以sql脚本的形式导出，方便在其他环境里重建。

备份单个数据库 假设我们有个名为test_db的数据库，要将它完整备份下来，操作如下：

pg_dump -u your_username -fc test_db > test_db.dump

这里，-u指定用户名，-fc表示以自定义的压缩二进制格式导出，这种格式备份速度较快，而且生成的文件体积较小。最后将备份内容重定向到test_db.dump文件中。

备份指定表 要是只想备份数据库里的部分表，例如test_db中的users表和orders表：

pg_dump -u your_username -t users -t orders -fc test_db > partial_dump.dump

-t参数用来指定需要备份的表名，可以多次使用这个参数来添加多个目标表。

二、pg_restore：逻辑备份的恢复操作

有了备份文件，借助pg_restore就能让数据起死回生。

恢复完整数据库 先创建一个空的目标数据库，假设叫new_test_db，然后执行恢复：

createdb -u your_username new_test_db
pg_restore -u your_username -d new_test_db test_db.dump

createdb用于创建新数据库，-d参数在pg_restore里表示目标数据库，按照备份文件test_db.dump中的指令，把数据和结构填充进新的new_test_db数据库。

恢复部分表 如果只想恢复之前备份里的部分表，例如只恢复users表：

pg_restore -u your_username -d new_test_db -t users test_db.dump

-t参数的含义和备份时一致，精准定位到需要恢复的表。

三、pg_basebackup：物理备份的实现

物理备份会直接拷贝数据库文件，相较于逻辑备份，速度更快，适合大型数据库。

基础物理备份 以超级用户身份执行以下命令：

pg_basebackup -d /path/to/backup -u your_superuser -p

-d指定备份文件存放的目录，-u是超级用户用户名，-p 会显示备份进度，让我们能实时知晓备份状态。不过物理备份要注意，目标目录必须为空，而且在备份期间数据库读写操作尽量减少，避免数据不一致。

增量物理备份 先完成一次基础物理备份，后续增量备份基于这个基础来做。我们要记录下基础备份的 wal（write-ahead logging，预写式日志）文件位置。增量备份时：

pg_basebackup -d /path/to/incremental_backup -u your_superuser -p -x stream -c -r \
--checkpoint=fast --target-wal-segment-size=16mb \
--write-recovery-conf --recovery-target-time='2025-01-15 12:00:00'

-x stream开启流模式来传输wal日志，-c表示在备份结束后清理不再需要的wal段，-r自动生成恢复配置文件，后面几个参数用于精细调整备份的检查点和目标wal段大小，--recovery-target-time则设定恢复到的时间点。

四、基于wal日志的恢复

wal日志是postgresql物理恢复的关键，就算数据库出现故障，只要wal日志完整，就能恢复到故障前的状态。

假设数据库崩溃了，先把物理备份文件拷贝到合适位置，然后利用wal日志恢复：

cp -r /path/to/backup /var/lib/postgresql/data
# 找到相关wal日志，假设存放在 /var/lib/postgresql/wal_logs
cp /var/lib/postgresql/wal_logs/*.log /var/lib/postgresql/data/pg_wal
# 启动postgresql服务，它会自动根据wal日志恢复数据
sudo systemctl start postgresql