您可以在PostgreSQL 9中尝试以下操作

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'alter table '|| r.tablename ||' owner to newowner;';
    END LOOP;
END$$;

非常简单

Su - postgres psql 重新分配[old_user]到[new_user]; \c[你的资料库] 重新分配[old_user]到[new_user];

完成了。

如果当前所有者不是postgres，你可以使用这个:

REASSIGN OWNED BY old_role [, ...] TO new_role

但如果当前的所有者是postgres，你肯定会得到错误，所以你必须使用@dvanrensburg回答，但如果你想在相同的sql中执行命令，可以根据需要使用这些命令:

数据库

ALTER DATABASE target_database OWNER TO new_onwer;

表

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH temp as (
    SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO newuser' as command
    FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
    ORDER BY schemaname, tablename )
    SELECT command from temp
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

序列

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH temp as (
    SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO newuser;' as command
    FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
    ORDER BY sequence_schema, sequence_name)
    select command from temp
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

的观点

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH temp as (
    SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO newuser;' as command
    FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
    ORDER BY table_schema, table_name)
    select command from temp
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

模式

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH schema_names as(
    SELECT distinct(schemaname) FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
    ORDER BY schemaname)
    SELECT 'ALTER SCHEMA '|| schemaname ||' OWNER TO newuser;' as command
    FROM schema_names
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

还要注意数据库中可能需要更改成员关系的函数和其他组件

函数和触发函数

    DO
    LANGUAGE plpgsql
    $$
    DECLARE
      stmt text;
    BEGIN
      FOR stmt IN
        WITH temp as(
        SELECT 'alter function '||nsp.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to newuser;' as command
        FROM pg_proc p
        JOIN pg_namespace nsp ON p.pronamespace = nsp.oid
        WHERE NOT  nsp.nspname IN ('pg_catalog', 'information_schema'))
        SELECT command FROM temp
      LOOP
        EXECUTE stmt;
      END LOOP;
    END;
    $$;

可以使用REASSIGN OWNED命令。

剧情简介:

重新分配old_role[，…]到new_role

这将把old_role拥有的所有对象更改为新角色。你不需要考虑用户拥有什么样的对象，它们都会被改变。注意，它只应用于单个数据库中的对象。它也不会改变数据库本身的所有者。

它至少可以在8.2版本中使用。他们的网上资料只能追溯到这段时间。

与@AlexSoto的函数方法相同:

IFS=$'\n'  
for fnc in `psql -qAt -c "SELECT  '\"' || p.proname||'\"' || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'public';" YOUR_DB` ; do  psql -c "alter function $fnc owner to NEW_OWNER" YOUR_DB; done

Docker:修改所有表和序列的所有者

export user="your_new_owner"
export dbname="your_db_name"

cat <<EOF | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h \$POSTGRES_PORT_5432_TCP_ADDR -p \$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname" | grep ALTER | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h \$POSTGRES_PORT_5432_TCP_ADDR -p \$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname"
SELECT 'ALTER TABLE '||schemaname||'.'||tablename||' OWNER TO $user;' FROM pg_tables WHERE schemaname = 'public';
SELECT 'ALTER SEQUENCE '||relname||' OWNER TO $user;' FROM pg_class WHERE relkind = 'S';
EOF