pg_dump 和 pg_dumpall

在 PostgreSQL 中，pg_dump 和 pg_dumpall 是两个常用的备份工具，分别用于逻辑备份单个数据库和整个数据库集群。

检查pg_dump 和 pg_dumpall命令是否可用

su - postgres
pg_dump --version
pg_dumpall --version

使用 pg_dump 备份单个数据库

pg_dump -U <用户名> -h <主机名> -p <端口号> -F <格式> -f <输出文件路径> <数据库名>

参数：

• -U：指定数据库用户名。
• -h：指定数据库主机地址（默认 localhost）。
• -p：指定数据库端口（默认 5432）。
• -F：指定备份格式：
  • plain（默认）：生成 SQL 脚本文件。
  • c：自定义格式（支持压缩，需用 pg_restore 恢复）。
  • d：目录格式（支持并行备份）。
  • t：tar 格式。
• -f：指定输出文件路径。
• --schema-only：仅备份表结构（不包含数据）。
• --data-only：仅备份数据（不包含表结构）。
• -t <表名>：备份特定表。
• -j <并行任务数>：并行备份（适用于大数据库）。

示例

1. 备份整个数据库为 SQL 文件：

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

   • -F p 表示输出为普通 SQL 文件。
   • mydb 是要备份的数据库名。

2. 备份整个数据库为自定义格式（支持压缩）：

   pg_dump -U postgres -h localhost -p 5432 -F c -f /path/to/backup.custom mydb
   

   • -F c 表示输出为自定义格式（需用 pg_restore 恢复）。

3. 备份特定表：

   pg_dump -U postgres -h localhost -p 5432 -F p -t users -f /path/to/users_backup.sql mydb
   

   • -t users 表示仅备份 users 表。

4. 仅备份表结构：

   pg_dump -U postgres -h localhost -p 5432 --schema-only -f /path/to/schema.sql mydb
   

5. 仅备份数据（不包含表结构）：

   pg_dump -U postgres -h localhost -p 5432 --data-only -f /path/to/data.sql mydb
   

使用 pg_dumpall 备份整个数据库集群

基本用法

pg_dumpall 用于备份整个 PostgreSQL 集群，包括所有数据库、角色（用户）、表空间等全局对象。

命令格式：

pg_dumpall -U <用户名> -h <主机名> -p <端口号> -f <输出文件路径> [选项]

参数：

• -g：仅备份全局对象（角色、表空间等）。
• -c：在备份中包含删除数据库的命令（用于恢复时清理旧数据）。
• -v：启用详细模式（显示备份过程）。

示例

1. 备份整个集群：

   pg_dumpall -U postgres -h localhost -p 5432 -f /path/to/cluster_backup.sql
   

   • 生成的 SQL 文件包含所有数据库、角色和表空间。

2. 仅备份全局对象（角色、表空间）：

   pg_dumpall -U postgres -h localhost -p 5432 -g -f /path/to/globals.sql
   

3. 备份整个集群并包含清理命令：

   pg_dumpall -U postgres -h localhost -p 5432 -c -f /path/to/cluster_backup.sql
   

恢复备份

恢复 pg_dump 备份

• SQL 文件恢复：

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

  示例：

  psql -U postgres -h localhost -d mydb -f /path/to/backup.sql
  

• 自定义格式恢复：

  pg_restore -U <用户名> -h <主机名> -d <目标数据库> <备份文件路径>
  

  示例：

  pg_restore -U postgres -h localhost -d mydb /path/to/backup.custom
  

恢复 pg_dumpall 备份

• 恢复整个集群备份：

  psql -U postgres -h localhost -d postgres -f /path/to/cluster_backup.sql
  

  • 需以 postgres 用户连接到默认数据库（如 postgres），因为恢复过程中会创建其他数据库。

• 恢复全局对象备份：

  psql -U postgres -h localhost -d postgres -f /path/to/globals.sql
  

Tips

1. 权限要求：
   • pg_dump 需要对目标数据库有读取权限。
   • pg_dumpall 需要超级用户权限（以备份角色和表空间）。
2. 备份格式选择：
   • 如果需要灵活的恢复选项（如选择性恢复表），建议使用自定义格式（-F c）。
   • 如果需要快速恢复，SQL 文件可能更直接。
3. 并行备份：
   • 对大型数据库，使用 -j <并行任务数> 可加速备份（需 PostgreSQL 12+）。

删除数据库报错 ERROR: database “xxx_prod” is being accessed by other usersDETAlL: There are 33 other sessions using the database.

踢掉所有连接的服务，然后在删除数据库

-- 步骤 1: 阻止新连接到目标数据库
ALTER DATABASE "xxx_prod" ALLOW_CONNECTIONS false;

-- 步骤 2: 终止所有现有连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'xxx_prod';

-- 步骤 3: 删除数据库
DROP DATABASE "xxx_prod";