Error restoring Postgres DB

Question

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?

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.
– Oliver F., Commented Nov 29, 2012 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. — András Váczi
– András Váczi, Commented Nov 29, 2012 at 10:15

András Váczi · Accepted Answer · 2012-11-30 10:31:56Z

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

Stack Exchange Network

Error restoring Postgres DB

1 Answer 1

Hot Network Questions

Error restoring Postgres DB

1 Answer 1

Related

Hot Network Questions