AWS Redshift Script export

Question

How can i save all redshift database ddl script into my local drive for my managing repository on bitbucket. It means that I want to export all ddl script from redshift database and want to save in same structure folder in local like databasename => tablename =>tablescript.sql

Thanks in advance

Answer 1

The Redshift Utils project provides a view that you can use to generate DDL for tables, views, schemas, and functions. https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql

Using psql periodically run this and check the output into version control:

psql -d my_database \
   --no-align --field-separator $'\t' \
   --pset footer=off -o all_table_ddl.sql \
   -c "SELECT ddl FROM admin.v_generate_tbl_ddl;"