Re: pg_restore PostgreSQL 9.3.3 problems

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, June 13, 2014 11:51 AM
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:
> 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