CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
aggis
aggis

Reputation: 698

pg_dump error: 'ONLY relation' not supported

I am currently trying to do a pg_dump but it is throwing an error that I cannot seem to find any information about.

I am trying to dump table archive_table_test with the command:

pg_dump -h xxx -p xxx -d xxx -U xxx -W --table=archive_table_test --column-inserts > ~/dumps/test_dump_5_31.sql

But I am receiving the following error:

pg_dump: [archiver (db)] query failed: ERROR: "ONLY relation" is not supported

I cannot find any examples of Postgres users receiving this error, so I have no idea where to start.

My desired results would be the standard pg_dump output into a .sql file with insert statements and all.

Any help would be gladly appreciated.

PostgreSQL 8.0.2, Redshift 1.0.2369

Upvotes: 1

Views: 822

Answers (1)

Red Boy
Red Boy

Reputation: 5729

In Redshift pg_dump only export schemas, not data.

pg_dump -h XXX -U XXX -d XXX -p 5439 -W --table=XXXX.dim_item_sku > abc.sql

LIMITATION: 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.

If you want to get dump of the data as well, you still need to use the UNLOAD command on each table unfortunately.

Upvotes: 2

Related Questions