CODEWITHSUNDEEP
amazon-redshift
dinesh707
dinesh707

Reputation: 12582

How to unload pg_table_def table to s3

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.

Upvotes: 4

Views: 1374

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

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.

Upvotes: 2

Related Questions