是否可以克隆 RedShift 数据库？

您可以执行以下操作：

• UNLOAD您的数据到 S3 ( http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html )
• 针对新数据库运行部署脚本
• COPY将数据导入新数据库 ( http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html )

对于开发大小的数据集，此（卸载/复制）将相当快。

我只是花了很多时间做这件事。这是有问题的，因为 postgres 工具没有捕获 sort 和 dist 键信息。稍后我会将完整的脚本发布到我们的公共 github 存储库，但现在这里有一个脚本，它将所有表定义拉入创建表语句。它还没有解决列压缩类型或主键，但它至少允许我们在另一个数据库中重新创建表。填充它们是一个简单的脚本卸载和加载语句的问题。由于 Redshift sql 的许多限制，该脚本并不漂亮，我花了太多时间查看它，但它运行良好。

select tm.schemaname||'.'||tm.tablename, 'create table '||tm.schemaname||'.'||tm.tablename
  ||' ('
  ||cp.coldef
  -- primary key
  -- dist key
  || nvl(d.distkey,'')
  --sort key 
  || nvl((select 
         ' sortkey(' ||substr(array_to_string(
                     array( select ','||cast(column_name as varchar(100))  as str from
                           (select column_name from information_schema.columns col where  col.table_schema= tm.schemaname and col.table_name=tm.tablename) c2
                            join 
                            (-- gives sort cols
                              select attrelid as tableid, attname as colname, attsortkeyord as sort_col_order from pg_attribute pa where 
                              pa.attnum > 0  AND NOT pa.attisdropped AND pa.attsortkeyord > 0
                            ) st on tm.tableid=st.tableid and c2.column_name=st.colname   order by sort_col_order
                          )
                    ,'')
                  ,2,10000) || ')'
   )
  ,'') ||';'
  from 
-- t  master table list
(
SELECT substring(n.nspname,1,100) as schemaname, substring(c.relname,1,100) as tablename, c.oid as tableid 
FROM pg_namespace n, pg_class c
WHERE n.oid = c.relnamespace 
  AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
) tm 
-- cp  creates the col params for the create string
join
(select 
  substr(str,(charindex('QQQ',str)+3),(charindex('ZZZ',str))-(charindex('QQQ',str)+3)) as tableid
  ,substr(replace(replace(str,'ZZZ',''),'QQQ'||substr(str,(charindex('QQQ',str)+3),(charindex('ZZZ',str))-(charindex('QQQ',str)+3)),''),2,10000) as coldef
from
( select array_to_string(array(
  SELECT  'QQQ'||cast(t.tableid as varchar(10))||'ZZZ'|| ','||column_name||' '|| decode(udt_name,'bpchar','char',udt_name) || decode(character_maximum_length,null,'', '('||cast(character_maximum_length as varchar(9))||')'   )
  -- default
  || decode(substr(column_default,2,8),'identity','',null,'',' default '||column_default||' ')
  -- nullable
  || decode(is_nullable,'YES',' NULL ','NO',' NOT NULL ') 
  -- identity 
  || decode(substr(column_default,2,8),'identity',' identity('||substr(column_default,(charindex('''',column_default)+1), (length(column_default)-charindex('''',reverse(column_default))-charindex('''',column_default)   ) )  ||') ', '') as str 
   from  
  -- ci  all the col info
  (
  select cast(t.tableid as int), cast(table_schema as varchar(100)), cast(table_name as varchar(100)), cast(column_name as varchar(100)), 
  cast(ordinal_position as int), cast(column_default as varchar(100)), cast(is_nullable as varchar(20)) , cast(udt_name as varchar(50))  ,cast(character_maximum_length as int),
   sort_col_order  , decode(d.colname,null,0,1) dist_key 
    from (select * from information_schema.columns c where  c.table_schema= t.schemaname and c.table_name=t.tablename) c
  left join 
  (-- gives sort cols
  select attrelid as tableid, attname as colname, attsortkeyord as sort_col_order from pg_attribute a where 
   a.attnum > 0  AND NOT a.attisdropped AND a.attsortkeyord > 0
  ) s on t.tableid=s.tableid and c.column_name=s.colname
  left join 
  -- gives dist col
  (select attrelid as tableid, attname as colname from pg_attribute a where
   a.attnum > 0 AND NOT a.attisdropped  AND a.attisdistkey = 't'
  ) d on t.tableid=d.tableid and c.column_name=d.colname
  order by ordinal_position
  ) ci 
  -- for the working array funct
  ), '') as str
 from 
 (-- need tableid
 SELECT substring(n.nspname,1,100) as schemaname, substring(c.relname,1,100) as tablename, c.oid as tableid 
 FROM pg_namespace n, pg_class c
 WHERE n.oid = c.relnamespace 
   AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
 ) t 
)) cp on tm.tableid=cp.tableid
-- add in primary key query here
-- dist key
left join
(  select 
  -- close off the col defs after the primary key 
  ')' ||
  ' distkey('|| cast(column_name as varchar(100)) ||')'  as distkey, t.tableid
  from information_schema.columns c
  join 
  (-- need tableid
  SELECT substring(n.nspname,1,100) as schemaname, substring(c.relname,1,100) as tablename, c.oid as tableid 
  FROM pg_namespace n, pg_class c
  WHERE n.oid = c.relnamespace 
    AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  ) t on c.table_schema= t.schemaname and c.table_name=t.tablename
  join 
  -- gives dist col
  (select attrelid as tableid, attname as colname from pg_attribute a where
   a.attnum > 0 AND NOT a.attisdropped  AND a.attisdistkey = 't'
  ) d on t.tableid=d.tableid and c.column_name=d.colname
) d on tm.tableid=d.tableid 

我喜欢将数据库状态捕获为代码的想法，但我认为在备份快照的红移上下文中存在一种更简单的方法并从中恢复。

这个用于将数据库复制到另一个 AWS 账户的 AWS 文档是一个指南，您可以在账户中执行相同的操作，或者在从快照进行紧急恢复时执行此操作。

1. 通过控制台拍摄手动快照
2. 从快照中恢复一个 redshift 集群。

现在您的模式和数据已经存在，下一个任务是将这个数据库缩小为测试环境并调整集群的大小。还有 dns 端点和其他配置要清理，在文档中提到。

瘦身步骤是感兴趣的部分，也许其他人会对此发表评论。