GCP Cloud SQL PostgreSQL表禁用触发器/外键约束的解决方案咨询

可行解决方案 #

以下方案均适配GCP Cloud SQL PostgreSQL的权限限制，无需申请SUPERUSER权限即可实现指定表外键约束的临时禁用：

方案1：临时删除外键约束，迁移完成后重建（兼容性最高） #

本方案仅需要对应表的OWNER权限，适合所有版本的Cloud SQL实例：

• 执行如下查询获取目标表（以orders.orders为例）关联的所有外键约束，自动生成删除和重建语句：

SELECT 
    tc.constraint_name,
    'ALTER TABLE '||tc.table_schema||'.'||tc.table_name||' DROP CONSTRAINT '||tc.constraint_name||';' AS drop_command,
    'ALTER TABLE '||tc.table_schema||'.'||tc.table_name||' ADD CONSTRAINT '||tc.constraint_name||' FOREIGN KEY ('||kcu.column_name||') REFERENCES '||ccu.table_schema||'.'||ccu.table_name||'('||ccu.column_name||');' AS create_command
FROM 
    information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
      ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage ccu
      ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE 
    tc.constraint_type = 'FOREIGN KEY' 
    AND (tc.table_schema||'.'||tc.table_name = 'orders.orders' OR ccu.table_schema||'.'||ccu.table_name = 'orders.orders');

• 迁移任务开始前，执行查询结果中所有的drop_command语句，删除关联外键
• 正常执行数据迁移操作，完成后校验数据一致性
• 执行查询结果中所有的create_command语句，恢复外键约束

方案2：授予REPLICATION权限修改会话复制角色（适合10及以上版本Cloud SQL） #

PostgreSQL 10以上版本的Cloud SQL实例支持为迁移用户授予REPLICATION权限，即可修改session_replication_role参数批量禁用触发器：

1. 用默认postgres用户登录实例，执行授权语句：

GRANT REPLICATION TO 迁移任务使用的用户名;

2. 迁移会话中执行命令禁用所有系统触发器（含外键约束触发器）：

SET session_replication_role = replica;

3. 迁移完成后恢复会话配置：

SET session_replication_role = DEFAULT;

注：该配置仅对当前会话生效，不会影响其他业务会话的正常运行

方案3：使用pg_dump内置参数自动处理约束 #

如果是整表数据迁移场景，可直接使用pg_dump命令的--disable-triggers+--data-only参数，工具会自动处理外键约束的临时禁用和恢复逻辑，无需手动执行SQL操作。

内容的提问来源于stack exchange，提问作者Peter Toth