Re: pg_restore PostgreSQL 9.3.3 problems - Mailing list pgsql-bugs

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.partia=
l.dmp". =0A=
=0A=
I thought pg_restore would organize the drop, recreate,copy such that child=
 tables would be dropped first and then it's parents.=0A=
=0A=
This is the workflow ... =0A=
=0A=
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=0A=
=0A=
trackdb=3D# =0A=
trackdb=3D# select count(*) from tracker_message;=0A=
 count =0A=
-------=0A=
 99000=0A=
(1 row)=0A=
=0A=
2.) then, somehow a user deletes by mistake some data, 1000 rows for exampl=
e.=0A=
=0A=
trackdb=3D# delete from tracker_message where id in (select id from tracker=
_message limit 1000);=0A=
DELETE 1000=0A=
trackdb=3D# select count(*) from tracker_message;=0A=
 count =0A=
-------=0A=
 98000=0A=
(1 row)=0A=
=0A=
3.) Now I want leverage pg_restore to recover the 1000 rows deleted,  pg_re=
store -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 res=
tore.=0A=
=0A=
The drop on pg_restore fails, since the tracker_message table still has its=
 98000 rows, so the COPY from the backup also fails.=0A=
=0A=
pg_restore: [archiver (db)] Error from TOC entry 8058; 0 618063 TABLE DATA =
tracker_message postgres=0A=
pg_restore: [archiver (db)] COPY failed for table "tracker_message": ERROR:=
  duplicate key value violates unique constraint "tracker_message_pkey"=0A=
DETAIL:  Key (id)=3D(1001) already exists.=0A=
CONTEXT:  COPY tracker_message, line 1=0A=
=0A=
I was hoping that this process was completed automated, but you said that "=
pg_restore doesn't know about those foreign keys".  =0A=
=0A=
I thought I could put the tables listing them in child/parent constraint or=
der in the -t option on the pg_dump, but are saying that this will not matt=
er?=0A=
=0A=
thanks=0A=
=0A=
________________________________________=0A=
From: Tom Lane [tgl@sss.pgh.pa.us]=0A=
Sent: Thursday, June 12, 2014 9:09 PM=0A=
To: Burgess, Freddie=0A=
Cc: pgsql-bugs@postgresql.org=0A=
Subject: Re: [BUGS] pg_restore PostgreSQL 9.3.3 problems=0A=
=0A=
"Burgess, Freddie" <FBurgess@Radiantblue.com> writes:=0A=
> Successful pg_dump:=0A=
> pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f "trackdb.partial.d=
mp" -t tracker_message -t tracker_event_message_y2010m01 trackdb=0A=
=0A=
> Attempted pg_restore:=0A=
=0A=
> pg_restore -c -F c -j 3 -U postgres -d trackdb -v  "trackdb.partial.dmp"=
=0A=
=0A=
> Error Condition:=0A=
=0A=
> pg_restore: [archiver (db)] Error from TOC entry 575; 1259 618063 TABLE t=
racker_message postgres=0A=
> pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop =
table tracker_message because other objects depend on it=0A=
> DETAIL:  constraint tracker_event_message_id_fkey on table tracker_event_=
message_underflow depends on table tracker_message=0A=
> constraint tracker_event_message_id_fkey on table tracker_event_message_y=
2010m01 depends on table tracker_message.=0A=
=0A=
> the -c option; Clean is suppose to (drop) database objects before=0A=
> recreating them, but its not doing it because of referential integrity=0A=
> constraints.=0A=
=0A=
pg_restore -c is only able to drop objects that are listed in the dump=0A=
file.  What seems to be happening here (though you've provided very little=
=0A=
detail) is that there are foreign keys to these tables from other tables=0A=
not included in the partial dump --- tracker_event_message_underflow for=0A=
example.  pg_restore doesn't know about those foreign keys, so it doesn't=
=0A=
drop them, so when it tries to drop the objects it *does* know about,=0A=
those commands fail.  This is designed behavior for -c --- we don't want=0A=
it clobbering stuff it's unable to recreate.=0A=
=0A=
You haven't really explained what results you're hoping to achieve here,=0A=
so it's hard to give advice about what to do instead.  But I don't think=0A=
this is a bug.  It may well be that what you want is outside the=0A=
capabilities of pg_dump/pg_restore ... but that's a feature request=0A=
not a bug fix.=0A=
=0A=
                        regards, tom lane=0A=