如何仅在PostgreSQL无用户表时从pg_dumpall备份恢复（9.6版本）

当然有可行的方法！在PostgreSQL 9.6中，你可以通过检查数据库中是否存在用户自定义表来控制恢复流程，确保只有空数据库才能执行恢复。下面分两种场景给你详细方案：

一、核心检查逻辑：识别用户自定义表 #

首先我们需要明确：用户自定义表指的是你自己创建的普通表，要排除PostgreSQL自带的系统表（比如pg_catalog、information_schema、pg_toast这些schema下的表）。可以用以下SQL查询目标数据库中的用户自定义表数量：

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND table_type = 'BASE TABLE';

如果查询结果为0，说明数据库中没有用户自定义表，符合恢复条件；反之则终止恢复。

二、具体实现方案 #

方案1：手动检查后恢复 #

如果你习惯手动操作，步骤如下：

1. 连接到目标数据库：

   psql -U your_db_user -d your_target_db
   

2. 执行上面的检查SQL，查看返回的计数。
3. 如果计数为0，退出psql并执行恢复命令：

   psql -U your_db_user -d your_target_db -f your_dumpall_file.sql
   

4. 如果计数大于0，直接终止操作，避免覆盖现有数据。

方案2：自动化脚本检查（推荐） #

如果需要频繁执行或避免手动失误，可以写一个Shell脚本自动完成检查和恢复：

场景A：恢复单个目标数据库

#!/bin/bash

# 配置数据库参数
DB_NAME="your_target_db"
DB_USER="your_superuser"  # 恢复pg_dumpall通常需要超级用户权限
DUMP_FILE="your_dumpall_file.sql"

# 查询用户自定义表数量，去除输出中的多余空格
TABLE_COUNT=$(psql -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND table_type = 'BASE TABLE';" | xargs)

# 判断是否执行恢复
if [ "$TABLE_COUNT" -eq 0 ]; then
    echo "✅ 目标数据库为空，开始执行恢复..."
    psql -U $DB_USER -d $DB_NAME -f $DUMP_FILE
    if [ $? -eq 0 ]; then
        echo "✅ 恢复完成！"
    else
        echo "❌ 恢复过程中出现错误！"
        exit 1
    fi
else
    echo "❌ 错误：目标数据库中存在 $TABLE_COUNT 个用户自定义表，恢复终止。"
    exit 1
fi

场景B：恢复整个集群（所有用户数据库）

如果你用pg_dumpall导出了整个数据库集群，需要确保所有用户数据库都为空，可以用这个脚本：

#!/bin/bash

DB_USER="your_superuser"
DUMP_FILE="your_dumpall_file.sql"

# 获取所有用户数据库（排除系统模板库）
USER_DBS=$(psql -U $DB_USER -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres');" | xargs)

# 逐个检查每个数据库
for DB in $USER_DBS; do
    TABLE_COUNT=$(psql -U $DB_USER -d $DB -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND table_type = 'BASE TABLE';" | xargs)
    if [ "$TABLE_COUNT" -ne 0 ]; then
        echo "❌ 错误：数据库 $DB 中存在 $TABLE_COUNT 个用户自定义表，恢复终止。"
        exit 1
    fi
done

# 所有数据库检查通过，执行恢复
echo "✅ 所有用户数据库均为空，开始集群恢复..."
psql -U $DB_USER -f $DUMP_FILE
if [ $? -eq 0 ]; then
    echo "✅ 集群恢复完成！"
else
    echo "❌ 集群恢复过程中出现错误！"
    exit 1
fi

三、额外注意事项 #

• 权限问题：执行恢复的用户需要拥有足够权限（通常是超级用户），因为pg_dumpall的输出可能包含创建用户、数据库、表空间等全局对象的操作。
• 临时表：上面的SQL不会统计临时表（临时表的table_type为TEMPORARY BASE TABLE），如果需要连临时表也检查，可以去掉AND table_type = 'BASE TABLE'条件。
• 其他对象：如果需要排除视图、序列等非表对象，可以扩展检查逻辑（比如查询information_schema.views、information_schema.sequences等），但根据你的需求，只检查用户表即可。

内容的提问来源于stack exchange，提问作者Nuri Tasdemir