17

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

Could you use the SQL workbench/J client?

Eric
  • 2,636
  • 21
  • 25
Elm
  • 1,355
  • 6
  • 22
  • 33
  • Here's an Amazonian's comments on the topic: https://forums.aws.amazon.com/message.jspa?messageID=428466#428466 – Christopher Manning May 22 '13 at 16:38
  • You can even use an IDE like dBeaver to transfer the data in case you want to transfer without integrity. Use its community edition and you should be able to do so. DO NOTE: IDE data exports are prone to errors; this is a task either accomplished by a programming language such as Python/Bash or other paid utilities to transfer data. – Utsav Jha Jul 06 '18 at 11:48

4 Answers4

33

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
;
mattmc3
  • 17,595
  • 7
  • 83
  • 103
4

We are currently using Workbench/J successfuly with Redshift.

Regarding dumps, at the time there is no schema export tool available in Redshift (pg_dump doesn't work), although data can always be extracted via queries.

Hope to help.

EDIT: Remember that things like sort and distribution keys are not reflected on the code generated by Workbench/J. Take a look to the system table pg_table_def to see info on every field. It states if a field is sortkey or distkey, and such info. Documentation on that table:

http://docs.aws.amazon.com/redshift/latest/dg/r_PG_TABLE_DEF.html

nandilugio
  • 315
  • 2
  • 11
2

Yes, you can do so via several ways.

  1. UNLOAD() to an S3 Bucket- Thats the best. You can get your data on almost any other machine. (More info here: http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html)

  2. Pipe the contents of your table to a data file using the Linux instance you have. So, running:

    $> psql -t -A -F 'your_delimiter' -h 'hostname' -d 'database' -U 'user' -c "select * from myTable" >> /home/userA/tableDataFile will do the trick for you.

Utsav Jha
  • 366
  • 4
  • 16
1

If you're using a Mac, I'm using Postico and it works great. Just right click the table and click export.

Gabe Brown
  • 1,418
  • 3
  • 17
  • 22