pg_restore PostgreSQL 9.3.3 problems

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.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@sss.pgh.pa.us]
Sent: Thursday, June 12, 2014 9:09 PM
To: Burgess, Freddie
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] pg_restore PostgreSQL 9.3.3 problems

"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_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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"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 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,

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Unfortunately this is not feasible Tom. The database size is 7.2 TB and currently the pg_dump takes 3-5 days to finish. I am currently running a pg_basebackup 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 includes all of the tables in the schema, but only partition tables less than 2 years old since the data older than that is static and contains about 5TB of the total storage.

pg_restore: creating TABLE tracker_message
pg_restore: [archiver (db)] could not execute query: ERROR: relation "tracker_message" already exists
Command was: CREATE TABLE tracker_message (
id bigint NOT NULL,
uuid uuid NOT NULL,
format_version character varying(255),...

In the documentation this verbiage is misleading, because objects are not being dropped with this option

-c
--clean

Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

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, and then run the pg_restore. This will recreate them if I have to do a recovery, as long as the pg_dump is valid.

Freddie
________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: Friday, June 13, 2014 11:51 AM
To: Burgess, Freddie
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] pg_restore PostgreSQL 9.3.3 problems

"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 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,

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs