pg_restore - table restoration - Mailing list pgsql-general

From Day, David
Subject pg_restore - table restoration
Date
Msg-id 401084E5E73F4241A44F3C9E6FD79428AC786EC2@exch-01
Whole thread Raw
Responses Re: pg_restore - table restoration
Re: pg_restore - table restoration
List 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

 

 

pgsql-general by date:

Previous
From: Tirthankar Barari
Date:
Subject: Re: Index space growing even after cleanup via autovacuum in Postgres 9.2
Next
From: Adrian Klaver
Date:
Subject: Re: pg_restore - table restoration