PostgreSQL 使用pg_dump备份和恢复数据库

PostgreSQL 使用 pg_dump 备份和恢复数据库

pg_dump 是 PostgreSQL 提供的用于备份数据库的工具，它可以将数据库内容导出为不同格式的文件，以便之后进行还原或迁移。下面是关于 pg_dump 和 pg_restore 的详细使用说明。

---

一、pg_dump 基本语法

pg_dump [选项] -d <数据库名> -f <备份文件路径>

常用参数说明：

• -U <用户名>：指定数据库用户名。
• -h <主机名>：指定数据库主机地址，默认为 localhost。
• -p <端口号>：指定数据库端口，默认是 5432。
• -d <数据库名>：需要备份的数据库名称。
• -F <格式>：指定备份文件的格式，常见格式如下：
  • c：自定义格式（推荐使用）。
  • t：TAR 格式。
  • p：纯文本格式。
• -f <备份文件路径>：指定备份文件的保存路径。
• -t <表名>：备份特定表。
• -s：仅导出数据库的结构（schema）。

---

二、备份数据库的常见场景

1. 备份整个数据库

   pg_dump -U <用户名> -h <主机名> -p <端口号> -d <数据库名> -F c -f <备份文件路径>
   

   • 该命令会将指定数据库备份为一个自定义格式的文件。
   • 示例：

     pg_dump -U postgres -h localhost -p 5432 -d mydb -F c -f /backup/mydb.dump
     

2. 备份单个表

   pg_dump -U <用户名> -h <主机名> -p <端口号> -d <数据库名> -t <表名> -F c -f <备份文件路径>
   

   • 只备份特定的表。
   • 示例：

     pg_dump -U postgres -h localhost -p 5432 -d mydb -t mytable -F c -f /backup/mytable.dump
     

3. 仅备份数据库结构（不包含数据）

   pg_dump -U <用户名> -h <主机名> -p <端口号> -d <数据库名> -s -F c -f <备份文件路径>
   

   • 只备份数据库的 schema，不包含实际数据。
   • 示例：

     pg_dump -U postgres -h localhost -p 5432 -d mydb -s -F c -f /backup/mydb_schema.dump
     

4. 备份到纯文本格式

   pg_dump -U <用户名> -h <主机名> -p <端口号> -d <数据库名> -F p -f <备份文件路径>
   

   • 将数据库备份为纯文本格式，可以直接查看和编辑。
   • 示例：

     pg_dump -U postgres -h localhost -p 5432 -d mydb -F p -f /backup/mydb.sql
     

---

三、还原数据库

1. 还原自定义格式或 TAR 格式的备份
   使用 pg_restore 恢复自定义格式或 TAR 格式的备份文件：

   pg_restore -U <用户名> -h <主机名> -p <端口号> -d <目标数据库名> -F c <备份文件路径>
   

   • 该命令会将备份文件中的数据恢复到指定数据库。
   • 示例：

     pg_restore -U postgres -h localhost -p 5432 -d mydb -F c /backup/mydb.dump
     

2. 先删除数据库对象后恢复
   如果你需要在恢复前清空数据库中的对象，可以使用 -c 参数：

   pg_restore -U <用户名> -h <主机名> -p <端口号> -d <目标数据库名> -c -F c <备份文件路径>
   

   • -c：表示在恢复之前会先删除数据库中的已有对象。
   • 示例：

     pg_restore -U postgres -h localhost -p 5432 -d mydb -c -F c /backup/mydb.dump
     

3. 从纯文本备份恢复
   对于纯文本格式的备份文件，直接使用 psql 命令恢复：

   psql -U <用户名> -h <主机名> -p <端口号> -d <目标数据库名> -f <备份文件路径>
   

   • 示例：

     psql -U postgres -h localhost -p 5432 -d mydb -f /backup/mydb.sql
     

---

四、常见问题

1. 权限问题
   如果出现权限不足的问题，确保执行命令的用户对备份文件路径和 PostgreSQL 数据库拥有足够的权限。可以使用 sudo 提高权限：

   sudo -u postgres pg_dump ...
   

2. 指定数据库角色
   在恢复数据库时，可以使用 -O 选项忽略创建对象时的所有者信息，并用当前登录用户作为对象所有者：

   pg_restore -U <用户名> -h <主机名> -p <端口号> -d <目标数据库名> -O -F c <备份文件路径>
   

---

五、总结

• 使用 pg_dump 可以灵活地备份整个数据库、部分表或仅备份结构。
• 使用 pg_restore 可轻松还原备份数据，特别是自定义格式和 TAR 格式的备份文件。
• 备份格式推荐使用自定义格式（-F c），支持部分还原以及高效的数据恢复。

注意事项

有时候我们进行数据库迁移时，使用pgAdmin进行备份，当再次还原的时候，有可能会报错。其中一个原因就是我们使用的备份工具针对的是pg16版本，不支持当前版本等等。所以我们尽量使用同版本的工具进行备份，以新数据库版本为主