Re: pg_restore PostgreSQL 9.3.3 problems

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=