Cloud SQL PostgreSQL批量修改表和序列所有者时查询挂起

问题分析 #

批量修改所有权时出现挂起，核心原因是目标表/序列被长事务占用，导致ALTER TABLE/ALTER SEQUENCE请求无法获取排他锁，进而阻塞脚本执行。另外Google Cloud SQL的内置管理员用户（如postgres）虽由平台维护，但具备足够权限完成所有权变更操作，无需直接使用数据库所有者账号。

分步解决方案 #

1. 排查并解除阻塞源 #

在psql中执行以下命令，定位占用目标表/序列的长事务：

SELECT
  pid,
  usename,
  query,
  state,
  now() - query_start AS duration
FROM pg_stat_activity
WHERE
  datname = '你的数据库名'
  AND (relname = 'table10' OR relname = 'table10_id_seq') -- 替换为问题表和对应序列名
  AND state != 'idle';

找到对应的pid后，若确认该事务无业务价值，可通过内置管理员用户终止：

SELECT pg_terminate_backend(目标pid);

2. 优化批量变更脚本 #

避免一次性修改所有对象引发锁竞争，建议分批次处理，同时跳过已完成变更的对象：

批量修改表所有权

DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT tablename
             FROM pg_tables
             WHERE schemaname = 'public' -- 替换为你的业务schema
               AND tableowner != 'migration_user'
               LIMIT 10 -- 单次处理10张表，可根据情况调整
  LOOP
    EXECUTE 'ALTER TABLE ' || quote_ident(rec.tablename) || ' OWNER TO migration_user;';
    RAISE NOTICE '已将表 % 的所有权变更为migration_user', rec.tablename;
  END LOOP;
END $$;

批量修改序列所有权

DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT sequencename
             FROM pg_sequences
             WHERE schemaname = 'public' -- 替换为你的业务schema
               AND sequenceowner != 'migration_user'
               LIMIT 10 -- 单次处理10个序列，可根据情况调整
  LOOP
    EXECUTE 'ALTER SEQUENCE ' || quote_ident(rec.sequencename) || ' OWNER TO migration_user;';
    RAISE NOTICE '已将序列 % 的所有权变更为migration_user', rec.sequencename;
  END LOOP;
END $$;

3. 验证变更结果 #

执行以下查询确认所有对象所有权已统一：

-- 检查表所有权
SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public';

-- 检查序列所有权
SELECT sequencename, sequenceowner FROM pg_sequences WHERE schemaname = 'public';

关键注意事项 #

• 务必在业务低峰期执行操作，避免锁阻塞影响正常业务。
• Cloud SQL的postgres用户默认拥有超级权限，直接用该账号连接数据库即可执行上述脚本，无需数据库所有者账号。
• 若分批次执行仍出现阻塞，可进一步缩小单次处理的对象数量，或针对性处理单个阻塞对象后再继续批量操作。

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