Re: pg_restore PostgreSQL 9.3.3 problems

I only illustrated a subset in this example, but every table,sequence,view; over 600 objects in the trackdb schema are included in the "trackdb.partial.dmp".

I thought pg_restore would organize the drop, recreate,copy such that child tables would be dropped first and then it's parents.

This is the workflow ...

1.) I execute the pg_dump; with every table in the schema leveraging the -t option, including the tracker_message table that has 99000 rows

trackdb=#
trackdb=# select count(*) from tracker_message;
count
-------
99000
(1 row)

2.) then, somehow a user deletes by mistake some data, 1000 rows for example.

trackdb=# delete from tracker_message where id in (select id from tracker_message limit 1000);
DELETE 1000
trackdb=# select count(*) from tracker_message;
count
-------
98000
(1 row)

3.) Now I want leverage pg_restore to recover the 1000 rows deleted, pg_restore -c -F c -U postgres -d trackdb -v "trackdb.partial.dmp" doesn't do this unless I manually truncate table tracker_message cascade, and then restore.

The drop on pg_restore fails, since the tracker_message table still has its 98000 rows, so the COPY from the backup also fails.

pg_restore: [archiver (db)] Error from TOC entry 8058; 0 618063 TABLE DATA tracker_message postgres
pg_restore: [archiver (db)] COPY failed for table "tracker_message": ERROR: duplicate key value violates unique constraint "tracker_message_pkey"
DETAIL: Key (id)=(1001) already exists.
CONTEXT: COPY tracker_message, line 1

I was hoping that this process was completed automated, but you said that "pg_restore doesn't know about those foreign keys".

I thought I could put the tables listing them in child/parent constraint order in the -t option on the pg_dump, but are saying that this will not matter?

thanks

________________________________________
From: Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, June 12, 2014 9:09 PM
To: Burgess, Freddie
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] 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