DWS运维实战：行存压缩表整改方案

检查方式

业务库执行以下语句

select n.nspname,c.relname from pg_class c,pg_namespace n where c.relnamespace=n.oid and reloptions::text like '%orientation=row%' and reloptions::text  like '%compression=yes%';

整改方案：

{YourSchema.YourTable} 整体替换为你的名字

--1. 提前建表（防止带索引导入，索引先不建）
CREATE TABLE {YourSchema.YourTable}_column ... WITH xxx

--2. 做好授权
SELECT 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || '_column TO ' || grantee || ';' 
FROM information_schema.table_privileges
WHERE concat(table_schema,'.',table_name)='{YourSchema.YourTable}';

--3. 临时参数
set lockwait_timeout = 0; --防止等锁超时
set query_dop = 2; --适当开并行
set maintenance_work_mem = 'XXGB'; --如果建索引需要临时调大这个参数

--4. 开始实施
START TRANSACTION;
LOCK TABLE {YourSchema.YourTable} IN EXCLUSIVE MODE; --七级锁防止DML

INSERT INTO {YourSchema.YourTable}_column SELECT * FROM {YourSchema.YourTable};

SELECT COUNT(1) FROM {YourSchema.YourTable}; --手动校验条数是否一致
SELECT COUNT(1) FROM {YourSchema.YourTable}_column;

ALTER TABLE {YourSchema.YourTable} RENAME TO {YourSchema.YourTable}_old;
ALTER TABLE {YourSchema.YourTable}_column RENAME TO {YourSchema.YourTable};

CREATE INDEX ... --建好必要的索引（列存一般不需索引）

ANALYZE {YourSchema.YourTable};
COMMIT;

--5. 全部校验无误后删除 {YourSchema.YourTable}_old;