6

For some test and development work we would like the ability to routinely clone existing RedShift databases (all schema and all data). Something like a backup-restore.

The best I can find starts by creating a snapshot of the entire cluster, which is not practical at this point in time.

We do have scripts for creating the database and the schema, but we don't have a simple/tidy mechanism for populating the tables from another AWS RedShfit database. (We've found that we can't use INSERT INTO db_new.schema.table SELECT * FROM db_old.schema.table as cross-database syntax is not supported)

Does anyone have any suggestions?

1
  • Can you elaborate on ‘snapshot of cluster not practical part?’ This is a routine action that is probably happening by default. – Merlin May 28 at 17:59
5

You can do the following:

This (UNLOAD/COPY) will be fairly quick for a development size dataset.

1
4

I just spent a bunch of time doing this. It's problematic because the postgres tools don't capture the sort and dist key info. I'll post full scripts to our public github repo in a bit, but for now here's a script that pulls all table definitions into create table statements. It doesn't address column compression types or primary keys yet, but it at least allows us to recreate tables in another database. Populating them is then a simple matter of scripting unload and load statements. The script isn't pretty due to a number of limitations of Redshift sql and me looking at it for too many hours, but it works well.

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 
-1

I like the idea of capturing the db state as code, but i think there is a easier way in the redshift context of backup snapshots and restore from those.

This AWS doc for copying a db to another AWS account is a guide, you can do the same within the account or this what you would do in an emergency restore from snapshot.

  1. Take a manual snapshot via the console
  2. Restore a redshift cluster from the snapshot.

Now your schema and data are there, the next task is slimming this db down as a test environment and resizing the cluster down. Theres also dns endpoints and other configs to clean up, mentioned in the doc.

The slim-down step is the part interested in, maybe someone else will comment on that.

5
  • Paragraph two of the OP : The best I can find starts by creating a snapshot of the entire cluster, which is not practical at this point in time. – MatBailie May 28 at 6:07
  • Yeah that confused... when is a snapshot not practical it’s literally enabled bu default. Taking a snapshot from the console is a single button click. I’m mystified by this. – Merlin May 28 at 17:58
  • When the cluster has multiple databases, some of which are many Many terabytes in size, but the target database is only a few gigabytes in size. Regardless, it doesn't really matter, the OP made it clear that your answer isn't applicable and an alternative is required. Thus, giving an answer of "do what you said isn't applicable" doesn't constitute a remotely useful answer. – MatBailie May 28 at 21:11
  • Thank you Matt, I believe you are incorrect and my answer is extremely useful on this page. In my experience a 250TB snapshot is as easy to manage and a few GB, and someone visiting this question should know that. So far the solutions given are arduous and error probe, OP should reconsider requirements. – Merlin May 29 at 0:27
  • I am the op, and it's from 8 years ago – MatBailie May 29 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.