How to unload pg_table_def table to s3

Question

I'd like to take a dump of schema of redshift and do some comparisons among different environments.

unload('select * from pg_table_def') to 's3://blah/blah/stage.txt' credentials 'aws_access_key_id=XXXXX;aws_secret_access_key=XXXXXX' parallel off allowoverwrite manifest;

But the code above throws me the following error.

INFO: Function "format_type(oid,integer)" not supported. INFO: Function "pg_table_is_visible(oid)" not supported. ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.

Any idea how to make this work ? or is there any way other way to get the schema. i need to know sort key and dist key information as well.

I thought about trying to copy the relevant pg_table_def rows to a temp table, and unloading that, but I can't figure out how to copy that leader node table into a temp table either! — Harlan
– Harlan, Commented Sep 12, 2017 at 21:26

Joe Harris · Accepted Answer · 2015-02-25 15:50:09Z

Redshift keeps certain information in special area on the leader node whereas UNLOAD commands are processed on each slice (AFAIK) and therefore can't use leader node only functions.

You would probably need to extract this from an external machine using psql.

Collectives™ on Stack Overflow

How to unload pg_table_def table to s3

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related