Re: 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