current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to do a pg_restore on some SQL files. Here's the command I used when I dumped from the other database:

pg_dump -f /home/mctools/public_html/cp/upload/manufacturers.sql \
        -Fc -i -v -O -x -t manufacturers \
        -h localhost -p 5432 -U aztools_tools aztools_dbuser

And here is the restore command:

pg_restore -d mctools_dbuser -Fc -c -i -O -x  \
           -h localhost -p 5432 -U mctools_user \ 
           /home/mctools/public_html/cp/upload/manufacturers.sql

Here is the error I get:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2165; 2606 27769168
                            CONSTRAINT manufacturers_pk aztools_tools
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop constraint
                            manufacturers_pk on table manufacturers
                            because other objects depend on it
DETAIL:  constraint products_fk01 on table products depends on index manufacturers_pk
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Command was: ALTER TABLE ONLY public.manufacturers DROP CONSTRAINT manufacturers_pk;

What do I need to do to fix this?

share|improve this question

migrated from stackoverflow.com Nov 29 '12 at 5:19

This question came from our site for professional and enthusiast programmers.
    
I tried to reproduce your problem with no success. The first thing I would check is the output auf pg_dump by dumping plain SQL instead of using the PostgreSQL custom format. If it looks fine, try to restore dumped database objects with psql -f plain_dump.sql. –  Oliver F. Nov 29 '12 at 7:01
    
Do you really need the -c option in pg_restore? Exactly that's why pg_restore tries to drop the objects you dumped. –  dezso Nov 29 '12 at 10:15

1 Answer 1

up vote 2 down vote

The -c option of pg_restore does this:

Clean (drop) database objects before recreating them.

As you seem to only migrate a single table from here to there, you can safely omit -c from your command line. If you only need the data, you should tell it to pg_dump instead, by using the -a option. If your table structure is different as well, then you should prepare an ALTER TABLE statement manually to reflect those changes in the target database. (You could as well use a few tools for the latter, but for only one table the manual solution is usually much faster.)

share|improve this answer
    
thanks for saving my day (y) –  kmonsoor Mar 24 at 7:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.