Vastbase（海量数据库）标准实施检查单

A. 架构规划与硬件确认 致命/严重

B. 操作系统与基线 严重

C. 安装前环境（互信） 严重

D. 数据库安装与集群参数 中

gs_guc reload -N all -c "stats_temp_directory='/stat_tmp/pg_stat_tmp'"

ALTER database 库名称 SET search_path TO dbo, public;

vsql -c "SHOW sql_compatibility;"

E. 数据迁移（exBase） 严重

SELECT format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE TIMESTAMP;', n.nspname, c.relname, a.attname) AS sql
FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid JOIN pg_attribute a ON a.attrelid=c.oid
WHERE n.nspname NOT IN ('pg_catalog','information_schema','sys','pg_toast')
  AND c.relkind='r' AND a.attnum>0 AND NOT a.attisdropped
  AND format_type(a.atttypid,a.atttypmod) LIKE 'mssql_datetime%'
ORDER BY n.nspname,c.relname,a.attname;

F. 跨库查询（dblink） 最高频复发

gs_guc generate -S XXX -D $GAUSSHOME/bin -o usermapping

CREATE EXTENSION postgres_fdw;   -- 跨 Vastbase
-- tds_fdw 用于连 SQL Server 外部表

GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO vastbaselink;

SET enable_set_variable_mssql_format=on;
SET mssql_format_behavior_compat_options='use_mssql_execute_gram';

EXEC sp_addlinkedserver @server='link_ods', @srvproduct='vastbase', @datasrc='localhost,5432', @catalog='目标库';
EXEC sp_addlinkedsrvlogin @rmtsrvname='link_ods', @useself='false', @rmtuser='sa', @rmtpassword='密码';

-- postgres_fdw
ALTER USER MAPPING FOR current_user SERVER kptframe OPTIONS (SET password '原明文密码');
-- tds_fdw（连 SQL Server）
ALTER USER MAPPING FOR current_user SERVER yserp OPTIONS (SET password '原明文密码');

G. 账户与权限 严重

vsql -c "ALTER USER vbadmin ACCOUNT UNLOCK;"

H. 备份还原 中

I. 集群健康与验收 中

gs_om -t status --detail

# 1) 账户解锁（主库执行）
vsql -c "ALTER USER vbadmin ACCOUNT UNLOCK;"

# 2) 设置 search_path
ALTER database 库名 SET search_path TO dbo, public;

# 3) mssql_datetime 批量改 TIMESTAMP（先 SELECT 出语句再执行）
SELECT format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE TIMESTAMP;', n.nspname, c.relname, a.attname)
FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid JOIN pg_attribute a ON a.attrelid=c.oid
WHERE n.nspname NOT IN ('pg_catalog','information_schema','sys','pg_toast')
  AND c.relkind='r' AND a.attnum>0 AND NOT a.attisdropped
  AND format_type(a.atttypid,a.atttypmod) LIKE 'mssql_datetime%';

# 4) dblink 前置四步（密钥+扩展+授权+参数）
gs_guc generate -S XXX -D $GAUSSHOME/bin -o usermapping
CREATE EXTENSION postgres_fdw;
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO vastbaselink;
SET enable_set_variable_mssql_format=on;
SET mssql_format_behavior_compat_options='use_mssql_execute_gram';

# 5) 映射密码失效重置（复发第一响应）
ALTER USER MAPPING FOR current_user SERVER kptframe OPTIONS (SET password '原明文密码');  -- postgres_fdw
ALTER USER MAPPING FOR current_user SERVER yserp    OPTIONS (SET password '原明文密码');  -- tds_fdw

# 6) 跨库 schema 批量授权（替换 vastbaselink 为实际用户）
DO $$
DECLARE db_name text;
  system_dbs text[] := ARRAY['vastbase','postgres','template0','template1'];
  grant_sql text;
BEGIN
  FOR db_name IN SELECT datname FROM pg_database WHERE datname NOT IN (SELECT unnest(system_dbs)) LOOP
    EXECUTE format('GRANT CONNECT ON DATABASE %I TO vastbaselink;', db_name);
    grant_sql := format('
      DO $do$ DECLARE s text; BEGIN
        FOR s IN SELECT nspname FROM pg_namespace WHERE nspname NOT IN (''pg_catalog'',''information_schema'',''pg_toast'') LOOP
          EXECUTE format(''GRANT USAGE ON SCHEMA %%I TO vastbaselink;'', s);
          EXECUTE format(''GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA %%I TO vastbaselink;'', s);
          EXECUTE format(''GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA %%I TO vastbaselink;'', s);
          EXECUTE format(''ALTER DEFAULT PRIVILEGES IN SCHEMA %%I GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO vastbaselink;'', s);
          EXECUTE format(''ALTER DEFAULT PRIVILEGES IN SCHEMA %%I GRANT USAGE,SELECT ON SEQUENCES TO vastbaselink;'', s);
        END LOOP; END $do$;', db_name);
    EXECUTE grant_sql;
    RAISE NOTICE 'done %', db_name;
  END LOOP;
END $$;

# 7) 集群状态
gs_om -t status --detail

# 8) 查看已有 dblink server
SELECT s.srvname, s.srvowner, u.rolname AS owner FROM pg_foreign_server s LEFT JOIN pg_authid u ON s.srvowner=u.oid;