pg_restore - table restoration

From: "Day, David" <dday(at)redcom(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: pg_restore - table restoration
Date: 2014-01-13 21:43:30
Message-ID: 401084E5E73F4241A44F3C9E6FD79428AC786EC2@exch-01
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

This is sort of a continuation of = problems I was working on last week
with selective restorations of an archive file at the schema or table level. ( V9.3)
Given that I dumped the entire database ( pg_dump -Fc my_db -f archive_file )

When I pg_restore an entire schema ( -n ) everything is wonderful.

If I try to attempt two tables in one of the schemas I encounter problems.

I get a success of sort with these option variations:

pg_restore -c -t tbl1 -t tbl2 -U <username> -d my_db archive_file

In this case the tables are recreated with data but all the original constraints for these tables are missing
As are triggers that are associated with the tables. I guess I can understand this.

This variation seems encouranging but ultimately fails:

pg_restore -a -v -c -t translator_sys -t translator_sys_mbr -U <username> -d my_db archive_file

pg_restore: connecting to database for restore
pg_restore: dropping TABLE DATA translator_sys
pg_restore: dropping TABLE DATA translator_sys_mbr
pg_restore: dropping TABLE DATA translator_sys
pg_restore: processing data for table "translator_sys"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4247; 0 332255 TABLE DATA translator_sys redcom
pg_restore: [archiver (db)] COPY failed for table "translator_sys": ERROR: duplicate key value violates unique constraint "translator_sys_pkey"
DETAIL: Key (translator_id)=(1) already exists.
CONTEXT: COPY translator_sys, line 1
pg_restore: processing data for table "translator_sys_mbr"
pg_restore: [archiver (db)] Error from TOC entry 4248; 0 332262 TABLE DATA translator_sys_mbr redcom
pg_restore: [archiver (db)] COPY failed for table "translator_sys_mbr": ERROR: duplicate key value violates unique constraint "translator_sys_mbr_pkey"
DETAIL: Key (translator_id, tid_seq)=(1, 1) already exists.
CONTEXT: COPY translator_sys_mbr, line 1
pg_restore: processing data for table "translator_sys"

It seems like it is geared to TRUNCATE or DELETE the specified table data in this case based on the verbose output.
However I see no SQL commands in the std_out to support this verbose message so it ultimately fails because of the
Duplication of PK associated with the table..

Is this a bug or a mis-understanding on my part?

Regards

Dave Day

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-01-13 22:10:51 Re: pg_restore - table restoration
Previous Message Francisco Olarte 2014-01-13 18:38:08 Re: Index space growing even after cleanup via autovacuum in Postgres 9.2