PostgreSQL备份不是复制文件？物理vs逻辑咋选？误删还能精准恢复到1分钟前？

1. 备份与恢复的核心目标

备份不是“复制文件”这么简单——它是数据安全的最后一道防线。当遇到以下场景时，备份能帮你“起死回生”：

• 误删数据（比如DROP TABLE）；
• 硬件故障（硬盘损坏）；
• 软件bug（数据库崩溃）；
• 自然灾害（机房火灾）。

备份的终极目标是可恢复性：确保你能从备份中还原出“完整、一致”的数据，且还原过程可重复、可验证。

2. 备份的两种核心类型：物理 vs 逻辑

PostgreSQL的备份分为物理备份和逻辑备份，二者的本质区别在于“备份的是‘数据文件’还是‘数据的逻辑结构’”。我们用“搬家”类比：

• 物理备份=“搬整个房子”：直接复制数据库的数据文件（如base目录、pg_wal日志），速度快，但只能还原到相同版本的PostgreSQL。
• 逻辑备份=“搬房子里的东西”：导出数据的逻辑结构（如CREATE TABLE语句、INSERT数据），灵活但速度慢，支持跨版本迁移。

2.1 物理备份：直接复制数据文件

物理备份的核心工具是pg_basebackup，它通过流复制协议从数据库服务器复制数据文件和WAL（Write-Ahead Log，预写日志）。

适用场景：

• 全量备份整个数据库集群；
• 需要快速恢复（比如TB级数据）；
• 时间点恢复（PITR，Point-in-Time Recovery）。

示例：用pg_basebackup做基础备份

# -D：指定备份目录（必须为空）
# -U replication：使用有“replication”权限的用户（需提前创建）
# -h：数据库服务器地址（本地用localhost）
# -p：端口号（默认5432）
# -X stream：同步复制WAL日志（确保备份一致性）
# -P：显示进度条
pg_basebackup -D /var/lib/postgresql/17/backups/base_20240520 
              -U replication 
              -h localhost 
              -p 5432 
              -X stream 
              -P

参数解释：

• -X stream：关键参数！确保备份过程中产生的WAL日志被同步复制，避免备份“脏数据”。
• -U replication：需要在pg_hba.conf中允许该用户的复制连接（参考下文“常见报错”）。

2.2 逻辑备份：导出数据的“逻辑结构”

逻辑备份的核心工具是pg_dump（单数据库）和pg_dumpall（全集群），它们导出的是可执行的SQL语句或自定义格式文件。

适用场景：

• 备份单个数据库或部分表；
• 跨版本迁移（比如从PostgreSQL 15迁移到17）；
• 导出数据到其他数据库（如MySQL，需转换格式）。

2.2.1 pg_dump：单数据库逻辑备份

pg_dump是最常用的逻辑备份工具，支持多种输出格式：

• Plain Text（默认）：导出SQL文件，可直接用psql恢复；
• Custom（-F c）：自定义格式，支持压缩、并行恢复；
• Tar（-F t）：Tar归档格式，适合跨平台。

示例1：备份单个数据库到SQL文件

# -U postgres：使用超级用户（需有数据库备份权限）
# -d mydb：备份“mydb”数据库
# -f mydb.sql：输出到“mydb.sql”文件
pg_dump -U postgres -d mydb -f mydb.sql

示例2：备份到自定义格式（支持压缩）

# -F c：自定义格式（推荐，支持压缩和并行）
# -Z 5：压缩级别（0=无压缩，9=最高压缩，5是平衡）
# -v：显示详细日志（verbose）
pg_dump -U postgres -d mydb -F c -Z 5 -v -f mydb.dump

2.2.2 pg_dumpall：全集群逻辑备份

pg_dump只能备份单个数据库，而pg_dumpall可以备份全局对象（如角色、表空间）和所有数据库。

示例：备份全集群

# -f full_cluster.sql：输出到“full_cluster.sql”文件
pg_dumpall -U postgres -f full_cluster.sql

3. 恢复的实践：从备份到数据还原

恢复的核心原则是“先恢复全局对象，再恢复数据库”——因为数据库的表、视图可能依赖角色（用户）或表空间。

3.1 逻辑恢复：用psql或pg_restore

场景1：恢复SQL格式备份（pg_dump导出）

# 1. 先连接到“postgres”系统数据库（所有数据库的父数据库）
# 2. -f mydb.sql：恢复“mydb.sql”文件
psql -U postgres -d postgres -f mydb.sql

场景2：恢复自定义格式备份（pg_dump -F c导出）

# -d mydb：恢复到“mydb”数据库（需提前创建：CREATE DATABASE mydb;）
# -F c：输入格式是自定义格式
# -j 4：并行恢复（用4个线程，加快速度）
pg_restore -U postgres -d mydb -F c -j 4 -v mydb.dump

3.2 物理恢复：用pg_basebackup备份还原

物理恢复的本质是“替换数据目录+应用WAL日志”，步骤如下：

步骤1：停止PostgreSQL服务

# 以Ubuntu为例，PostgreSQL 17的服务名是“postgresql@17-main”
sudo systemctl stop postgresql@17-main

步骤2：清空数据目录（注意备份原有数据！）

# 数据目录默认是/var/lib/postgresql/17/main（根据安装路径调整）
sudo rm -rf /var/lib/postgresql/17/main/*

步骤3：复制基础备份到数据目录

# 将备份目录的内容复制到数据目录
sudo cp -R /var/lib/postgresql/17/backups/base_20240520/* /var/lib/postgresql/17/main/

步骤4：配置恢复信号文件（PostgreSQL 12+）

PostgreSQL 12以后，恢复不再需要recovery.conf，而是通过recovery.signal文件触发：

# 在数据目录创建recovery.signal（告诉PostgreSQL启动恢复模式）
sudo touch /var/lib/postgresql/17/main/recovery.signal

步骤5：启动PostgreSQL服务

sudo systemctl start postgresql@17-main

验证恢复：

# 连接到数据库，检查数据是否存在
psql -U postgres -d mydb -c "SELECT count(*) FROM my_table;"

3.3 时间点恢复（PITR）：精准还原到任意时刻

如果数据被误删，你可能不需要恢复到“最后一次备份”，而是“误删前的1分钟”——这就是时间点恢复（PITR）。

前提条件：

1. 已做过物理基础备份（pg_basebackup）；
2. 已开启WAL日志归档（在postgresql.conf中设置wal_level = replica，archive_mode = on，archive_command = 'cp %p /path/to/archive/%f'）。

步骤：

1. 按照“物理恢复”步骤1-4操作（复制基础备份、创建recovery.signal）；
2. 在数据目录创建recovery.conf（或在postgresql.conf中设置恢复目标）：

   # 恢复到2024-05-20 14:30:00（误删前的时间）
   recovery_target_time = '2024-05-20 14:30:00'
   # 恢复后自动提升为正常服务器（不再接受WAL日志）
   recovery_target_action = 'promote'
   

3. 启动PostgreSQL服务，等待恢复完成；
4. 验证数据：检查误删的表是否恢复。

4. 课后Quiz：巩固你的理解

问题1：如何备份PostgreSQL集群的所有角色（用户）和表空间？

答案：用pg_dumpall——它能备份全局对象（角色、表空间）和所有数据库。命令如下：

pg_dumpall -U postgres -f globals.sql

参考链接：www.postgresql.org/docs/17/bac…

问题2：物理备份和逻辑备份的主要区别是什么？分别适用于哪些场景？

答案：

• 物理备份：复制数据文件，速度快，适合全量备份和快速恢复（如TB级数据）；
• 逻辑备份：导出逻辑结构（SQL/自定义格式），灵活，适合跨版本迁移、部分数据恢复（如导出单个表）。
  参考链接：www.postgresql.org/docs/17/bac…

问题3：用pg_restore恢复自定义格式备份时，为什么需要提前创建数据库？

答案：pg_restore只能恢复数据库的内容（表、数据），不能创建数据库本身。因此需要提前用CREATE DATABASE mydb;创建目标数据库。

5. 常见报错与解决办法

报错1：pg_dump: error: connection to database "mydb" failed: FATAL: role "postgres" does not exist

原因：指定的用户（postgres）不存在于数据库集群中。
解决：

1. 检查现有用户：psql -U your_user -d postgres -c "SELECT rolname FROM pg_roles;"；
2. 使用存在的超级用户（如admin）：pg_dump -U admin -d mydb -f mydb.sql；
3. 若需要postgres用户，创建它：CREATE ROLE postgres SUPERUSER LOGIN;。

报错2：pg_basebackup: error: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "127.0.0.1"

原因：pg_hba.conf未允许replication用户从该主机连接。
解决：

1. 编辑pg_hba.conf（默认路径：/var/lib/postgresql/17/main/pg_hba.conf）；
2. 添加一行：host replication replication 127.0.0.1/32 trust（允许replication用户从本地连接）；
3. 重新加载配置：pg_ctl reload -D /var/lib/postgresql/17/main/。

报错3：pg_restore: error: input file does not appear to be a valid archive

原因：输入文件格式错误（如用pg_restore恢复SQL文件）或文件损坏。
解决：

1. 检查备份格式：若备份时用-F p（SQL文件），需用psql恢复；若用-F c（自定义格式），需用pg_restore；
2. 验证文件完整性：pg_restore -l mydb.dump（列出文件内容，若报错则文件损坏）。

参考链接

1. PostgreSQL备份与恢复总览：www.postgresql.org/docs/17/bac…
2. pg_dump文档：www.postgresql.org/docs/17/app…
3. pg_dumpall文档：www.postgresql.org/docs/17/bac…
4. pg_basebackup文档：www.postgresql.org/docs/17/app…
5. 时间点恢复文档：www.postgresql.org/docs/17/con…

余下文章内容请点击跳转至 个人博客页面 或者 扫码关注或者微信搜一搜：编程智域 前端至全栈交流与成长，阅读完整的文章：PostgreSQL备份不是复制文件？物理vs逻辑咋选？误删还能精准恢复到1分钟前？