Re: pg_restore PostgreSQL 9.3.3 problems

"Burgess, Freddie" <FBurgess(at)Radiantblue(dot)com> writes:
> Successful pg_dump:
> pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f "trackdb.partial.dmp" -t tracker_message -t tracker_event_message_y2010m01 trackdb

> Attempted pg_restore:

> pg_restore -c -F c -j 3 -U postgres -d trackdb -v "trackdb.partial.dmp"

> Error Condition:

> pg_restore: [archiver (db)] Error from TOC entry 575; 1259 618063 TABLE tracker_message postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop table tracker_message because other objects depend on it
> DETAIL: constraint tracker_event_message_id_fkey on table tracker_event_message_underflow depends on table tracker_message
> constraint tracker_event_message_id_fkey on table tracker_event_message_y2010m01 depends on table tracker_message.

> the -c option; Clean is suppose to (drop) database objects before
> recreating them, but its not doing it because of referential integrity
> constraints.

pg_restore -c is only able to drop objects that are listed in the dump
file. What seems to be happening here (though you've provided very little
detail) is that there are foreign keys to these tables from other tables
not included in the partial dump --- tracker_event_message_underflow for
example. pg_restore doesn't know about those foreign keys, so it doesn't
drop them, so when it tries to drop the objects it *does* know about,
those commands fail. This is designed behavior for -c --- we don't want
it clobbering stuff it's unable to recreate.

You haven't really explained what results you're hoping to achieve here,
so it's hard to give advice about what to do instead. But I don't think
this is a bug. It may well be that what you want is outside the
capabilities of pg_dump/pg_restore ... but that's a feature request
not a bug fix.

regards, tom lane