Обсуждение: pg_restore PostgreSQL 9.3.3 problems

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 tra=
cker_message postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop ta=
ble tracker_message because other objects depend on it
DETAIL:  constraint tracker_event_message_id_fkey on table tracker_event_me=
ssage_underflow depends on table tracker_message
constraint tracker_event_message_id_fkey on table tracker_event_message_y20=
10m01 depends on table tracker_message.

the -c option; Clean is suppose to (drop) database objects before recreatin=
g them, but its not doing it because of referential integrity constraints. =
Do I have to list these tables by child and then parent order for this to w=
ork? I thought that pg_restore would automatically order the table dependen=
cies correctly.

The restore is not restoring the data from the backup unless I truncate tab=
le tracker_message cascade first and then restore.

What am I doing wrong?

thanks

On Thu, Jun 12, 2014 at 1:25 PM, Burgess, Freddie <FBurgess@radiantblue.com>
wrote:

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

Does it make a difference  if you omit the "-j3" parallel option on restore?

"Burgess, Freddie" <FBurgess@Radiantblue.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_y2010m01trackdb 

> 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
dependon 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

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=

"Burgess, Freddie" <FBurgess@radiantblue.com> writes:
> This is the workflow ...

> 1.) I execute the pg_dump; with every table in the schema leveraging the -t option, including the tracker_message
tablethat 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,

Sorry, pg_dump/pg_restore aren't designed to solve such a problem.
Even just from the data standpoint, they don't do partial restores
within a table: they can only try to insert all of the rows that
were in the table at dump time.  So it's not surprising you'd get
pkey violations when you try that.  As you say, you could truncate
away all the data in tracker_message, but given all the foreign key
relationships that's going to be a mess.  Not to mention that you'd
lose updates made since the dump.

The -c option is entirely irrelevant to this; that's about dropping
and recreating whole tables, certainly not what you want here.

What I'd try doing is to load the old data into a temporary table and
then copy over just rows that no longer exist in tracker_message,
along the lines of

      insert into tracker_message
        select * from old_tracker_message o
      where not exists (select 1 from tracker_message t where t.id=o.id);

            regards, tom lane

Unfortunately this is not feasible Tom. The database size is 7.2 TB and cur=
rently the pg_dump takes 3-5 days to finish. I am currently running a pg_ba=
sebackup on a standby server and its taking 8-9 days to finish. This table =
in production "tracker_message" actually has approximately 200 million rows=
 so that insert will run a long time. What I am attempting to do is perform=
 a partial restore in effect or incremental backup. The backup list include=
s all of the tables in the schema, but only partition tables less than 2 ye=
ars old since the data older than that is static and contains about 5TB of =
the total storage.=0A=
=0A=
pg_restore: creating TABLE tracker_message=0A=
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "trac=
ker_message" already exists=0A=
    Command was: CREATE TABLE tracker_message (=0A=
    id bigint NOT NULL,=0A=
    uuid uuid NOT NULL,=0A=
    format_version character varying(255),...=0A=
=0A=
In the documentation this verbiage is misleading, because objects are not b=
eing dropped with this option=0A=
 =0A=
-c=0A=
--clean=0A=
=0A=
    Clean (drop) database objects before recreating them. (This might gener=
ate some harmless error messages, if any objects were not present in the de=
stination database.)=0A=
=0A=
Looks like the only way forward is to remove all the foreign keys from the =
target database and then drop all the object listed using drop ..cascade, a=
nd then run the pg_restore.  This will recreate them if I have to do a reco=
very, as long as the pg_dump is valid.=0A=
=0A=
Freddie=0A=
________________________________________=0A=
From: Tom Lane [tgl@sss.pgh.pa.us]=0A=
Sent: Friday, June 13, 2014 11:51 AM=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=
> 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 exam=
ple.=0A=
=0A=
> trackdb=3D# delete from tracker_message where id in (select id from track=
er_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,=0A=
=0A=
Sorry, pg_dump/pg_restore aren't designed to solve such a problem.=0A=
Even just from the data standpoint, they don't do partial restores=0A=
within a table: they can only try to insert all of the rows that=0A=
were in the table at dump time.  So it's not surprising you'd get=0A=
pkey violations when you try that.  As you say, you could truncate=0A=
away all the data in tracker_message, but given all the foreign key=0A=
relationships that's going to be a mess.  Not to mention that you'd=0A=
lose updates made since the dump.=0A=
=0A=
The -c option is entirely irrelevant to this; that's about dropping=0A=
and recreating whole tables, certainly not what you want here.=0A=
=0A=
What I'd try doing is to load the old data into a temporary table and=0A=
then copy over just rows that no longer exist in tracker_message,=0A=
along the lines of=0A=
=0A=
      insert into tracker_message=0A=
        select * from old_tracker_message o=0A=
          where not exists (select 1 from tracker_message t where t.id=3Do.=
id);=0A=
=0A=
                        regards, tom lane=0A=