有没有办法从Amazon Redshift进行SQL转储

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

有没有办法从Amazon Redshift进行SQL转储？

Could you use the SQL workbench/J client?

你能使用SQL workbench / J客户端吗？

4 个解决方案

#1

We are currently using Workbench/J successfuly with Redshift.

我们目前正在使用Redshift成功使用Workbench / J.

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.

关于转储，当时Redshift中没有可用的模式导出工具（pg_dump不起作用），尽管数据总是可以通过查询提取。

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:

编辑：请记住，排序和分发键之类的东西不会反映在Workbench / J生成的代码上。查看系统表pg_table_def以查看每个字段的信息。它声明字段是sortkey还是distkey，以及此类信息。该表的文件：

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

#2

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

模式的pg_dump可能在过去没有用，但现在确实如此。

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

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.

CAVEAT EMPTOR：pg_dump仍会产生一些特定于postgres的语法，并且还忽略了表的Redshift SORTKEY和DISTSTYLE定义。

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.

另一个不错的选择是使用已发布的AWS管理脚本视图来生成DDL。它处理SORTKEY / DISTSTYLE，但是我发现它在捕获所有FOREIGN KEY时是错误的，并且不处理表权限/所有者。你的milage可能会有所不同。

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

要获取数据本身的转储，您仍然需要在每个表上使用UNLOAD命令。

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 *语法将失败：

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
;

#3

Yes, you can do so via several ways.

是的，您可以通过多种方式实现。

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)

UNLOAD（）到S3 Bucket-这是最好的。您几乎可以在任何其他计算机上获取数据。（更多信息请访问：http：//docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html）
Pipe the contents of your table to a data file using the Linux instance you have. So, running:

使用您拥有的Linux实例将表的内容传递到数据文件。所以，运行：

$> 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.

$> psql -t -A -F'your_delimiter'-h'主机名'-d'数据库'-U'用户'-c“select * from myTable”>> / home / userA / tableDataFile将为您提供帮助。

#4

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

如果你使用的是Mac，我使用的是Postico，效果很好。只需右键单击该表，然后单击“导出”。

#1