Import current Redshift schema to Redshift QA environment

Question

I would like to export my current Redshift DB scheme table structure without data in current DB environment to another redshift DB environment.

The schema would be stored on to my S3 bucket, I would like to move the same scheme table structure without any data from the prod environment to my testing redshift environment. Resulting in both environments having the same structure.

I have found the AWS doc suggesting using the UNLOAD command via S3 bucket: How would i do this without copying the data which i do not need
https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html

What would be the best practice for exporting the current Redshift Schema tables without the data and importing it to another Redshift Cluster?

Answer 1

You can use the following command to generate a DDL command for an existing table and using that command, you'll be able to re-create the table elsewhere with the same schema.

select ddl from v_generate_tbl_ddl where schemaname = 'public' and tablename='product_description' order by seq asc;

Replace product_description with your table name and you'll get a DDL command for that table.

Answer 2

You can use the AdminViews provided in Redshift Utils on GitHub. In particular you can use v_generate_tbl_ddl to generate DDL for existing tables.