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 \ 

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?

  • 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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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