Is there a way to do a SQL dump from Amazon Redshift

Question

Is there a way to do a SQL dump from Amazon Redshift?

Could you use the SQL workbench/J client?

Answer 1

pg_dump of schemas may not have worked in the past, but it does now.

pg_dump -Cs -h my.redshift.server.com -p 5439 database_name > database_name.sql

CAVEAT EMPTOR: pg_dump still produces some postgres specific syntax, and also neglects the Redshift SORTKEY and DISTSTYLE definitions for your tables.

Another decent option is to use the published AWS admin script views for generating your DDL. It handles the SORTKEY/DISTSTYLE, but I've found it to be buggy when it comes to capturing all FOREIGN KEYs, and doesn't handle table permissions/owners. Your milage may vary.

To get a dump of the data itself, you still need to use the UNLOAD command on each table unfortunately.

Here's a way to generate it. Be aware that select * syntax will fail if your destination table does not have the same column order as your source table:

select
  ist.table_schema,
  ist.table_name,
  'unload (''select col1,col2,etc from "' || ist.table_schema || '"."' || ist.table_name || '"'')
to ''s3://SOME/FOLDER/STRUCTURE/' || ist.table_schema || '.' || ist.table_name || '__''
credentials ''aws_access_key_id=KEY;aws_secret_access_key=SECRET''
delimiter as '',''
gzip
escape
addquotes
null as ''''
--encrypted
--parallel off
--allowoverwrite
;'
from information_schema.tables ist
where ist.table_schema not in ('pg_catalog')
order by ist.table_schema, ist.table_name
;