如何通过编程方式获取含Distkey、Sortkey的Redshift完整DDL？

由于pg_dump原生不支持识别Redshift特有的Distkey、Sortkey属性，你可以通过以下几种方式获取包含所有所需信息的完整DDL：

方法1：查询Redshift系统表手动拼接 #

Redshift的系统表存储了所有表的分布键、排序键、注释、权限元数据，通过查询这些表可以生成完整DDL：

1.1 生成带Distkey/Sortkey的CREATE TABLE语句 #

SELECT 
    'CREATE TABLE ' || n.nspname || '.' || c.relname || ' (' || 
    string_agg(
        a.attname || ' ' || format_type(a.atttypid, a.atttypmod) || 
        CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END ||
        CASE WHEN con.contype = 'p' THEN ' PRIMARY KEY' ELSE '' END,
        ', ' ORDER BY a.attnum
    ) || 
    ') ' ||
    CASE WHEN c.relkind = 'r' THEN 
        CASE WHEN d.diststyle IS NOT NULL THEN 
            'DISTSTYLE ' || d.diststyle || ' ' || 
            CASE WHEN d.diststyle != 'ALL' THEN 'DISTKEY(' || d.distkey || ') ' ELSE '' END 
        ELSE '' END ||
        CASE WHEN s.sortkey1 IS NOT NULL THEN 
            'SORTKEY(' || string_agg(s.sortkey, ', ' ORDER BY s.sortseq) || ') ' 
        ELSE '' END 
    ELSE '' END ||
    ';' AS create_table_ddl
FROM 
    pg_class c
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
JOIN 
    pg_attribute a ON c.oid = a.attrelid
LEFT JOIN 
    pg_constraint con ON c.oid = con.conrelid AND con.contype = 'p' AND a.attnum = ANY(con.conkey)
LEFT JOIN 
    (SELECT 
         attrelid, 
         CASE WHEN attisdistkey THEN attname END AS distkey,
         CASE WHEN attisdistkey THEN 
             CASE WHEN attisdistkey AND attsortkeyord = 0 THEN 'KEY' 
                  WHEN attisdistkey THEN 'ALL' 
             END 
         END AS diststyle
     FROM pg_attribute
     WHERE attisdistkey = TRUE) d ON c.oid = d.attrelid
LEFT JOIN 
    (SELECT 
         attrelid, 
         attname AS sortkey, 
         attsortkeyord AS sortseq
     FROM pg_attribute
     WHERE attsortkeyord > 0) s ON c.oid = s.attrelid
WHERE 
    c.relkind = 'r' 
    AND a.attnum > 0 
    AND NOT a.attisdropped
GROUP BY 
    n.nspname, c.relname, c.relkind, d.diststyle, d.distkey;

1.2 生成表和字段注释 #

-- 表注释
SELECT 
    'COMMENT ON TABLE ' || n.nspname || '.' || c.relname || ' IS ''' || d.description || ''';' AS comment_ddl
FROM 
    pg_class c
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
JOIN 
    pg_description d ON c.oid = d.objoid AND d.objsubid = 0
WHERE 
    c.relkind = 'r';

-- 字段注释
SELECT 
    'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || d.description || ''';' AS comment_ddl
FROM 
    pg_class c
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
JOIN 
    pg_attribute a ON c.oid = a.attrelid
JOIN 
    pg_description d ON c.oid = d.objoid AND d.objsubid = a.attnum
WHERE 
    c.relkind = 'r' 
    AND a.attnum > 0 
    AND NOT a.attisdropped;

1.3 生成授权语句 #

SELECT 
    'GRANT ' || string_agg(privilege_type, ', ') || ' ON TABLE ' || n.nspname || '.' || c.relname || ' TO ' || grantee || ';' AS grant_ddl
FROM 
    information_schema.table_privileges
JOIN 
    pg_class c ON table_name = c.relname
JOIN 
    pg_namespace n ON table_schema = n.nspname
WHERE 
    c.relkind = 'r'
GROUP BY 
    n.nspname, c.relname, grantee;

将以上查询结果导出后，即可拼接成完整的DDL脚本。

方法2：使用Redshift专用工具rs_dump #

rs_dump是Redshift官方提供的增强版导出工具（属于Redshift Utils套件），它兼容pg_dump的参数，同时支持导出Distkey、Sortkey等Redshift特有属性，以及注释、授权信息。

基本命令格式：

rs_dump -h <redshift-host> -U <username> -d <database> -t <schema.table> --schema-only > output_ddl.sql

• -t：指定单个或多个要导出的表（多次使用可导出多表），不添加则导出整个数据库的DDL
• --schema-only：仅导出结构，不导出数据

方法3：Redshift控制台单表导出 #

在AWS Redshift控制台进入目标表的详情页，点击生成DDL按钮，可直接获取带Distkey、Sortkey和注释的CREATE语句；在权限标签页可查看并复制授权语句。此方法适合单表导出，批量操作效率较低。

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