Обсуждение: pg_restore

Dear list

I hope you could help me understand the error message below:
While it looks straight forward, I could NOT find the duplicate key violation,
even though check through the said table (transaction by transaction). So,
I am wondering if the message would means something else.

I had tried re-indexing (force) and even modifying the primary key (manually) but
have not found any new clues.

I am using Postgresql 8.0.7 (Win32 binary version) and pgAdmin III for backup and restore.

Thank you.

CY

Error message:
--------------------
pg_restore: restoring data for table "bf_receipt"
pg_restore: ERROR:  duplicate key violates unique constraint "bf_receipt_pkey"
CONTEXT:  COPY bf_receipt, line 1: "2006/03/14 15:32:46.239           7    SUBANGJAYA              2005-10-15    evonne                                ..."
pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:  duplicate key violates unique constraint "bf_receipt_pkey"
CONTEXT:  COPY bf_receipt, line 1: "2006/03/14 15:32:46.239           7    SUBANGJAYA              2005-10-15    evonne                                ..."
pg_restore: *** aborted because of error

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Infor Gates <info_gates@yahoo.com> writes:
> I hope you could help me understand the error message below:
> While it looks straight forward, I could NOT find the duplicate key violation,

Considering that it's complaining at the very first line of COPY data,
I wonder whether you aren't restoring into a table that already contains
the data you want to load.  pg_restore doesn't (by default) try to
delete data that's already present.

            regards, tom lane

Dear Tom

Thanks for your email.

You are right in saying table is already populated with the data. I was
testing the pg_dump and pg_restore using pg_Admin III.

I am having the impression that pg_restore would over-rides the "old"
data with the current one. Is my thinking wrong?  I have checked the
documentation but it was silence on this.  Does it means that whenever
I performed a pg_restore, I must first drop the "old" database.

I would be glad if you can help to clarify my understanding on
pg_dump and pg_restore functions.

Thank you.

CY


Tom Lane <tgl@sss.pgh.pa.us> wrote:

Infor Gates writes:
> I hope you could help me understand the error message below:
> While it looks straight forward, I could NOT find the duplicate key violation,

Considering that it's complaining at the very first line of COPY data,
I wonder whether you aren't restoring into a table that already contains
the data you want to load. pg_restore doesn't (by default) try to
delete data that's already present.

regards, tom lane

---

Yahoo! Groups gets better. Check out the new email design. Plus there’s much more to come.

Infor Gates <info_gates@yahoo.com> writes:
> I am having the impression that pg_restore would over-rides the "old"
> data with the current one. Is my thinking wrong?

Yeah.  By default, pg_restore will issue a CREATE TABLE (which of course
fails if the table already exists) followed by COPY (which just tries to
insert data in addition to what might be there already).

There's a command line option to ask pg_restore to try to DROP TABLE
before doing the CREATE TABLE.  By and large, though, that's a bad way
to proceed unless you are specifically trying to merge two databases.
The fast and easy way to proceed is to DROP DATABASE, CREATE a fresh
empty database, and pg_restore into that.

            regards, tom lane

I agree.  When restoring a database from back up, I do a drop database and recreate it to make sure everything is properly intact (tables, columns, views, triggers, foreign keys, etc...).  We do this a lot for testing.  We backup the production database, copy to test server and do a restore on the test database.  It helps us test our code rollouts.

-Aaron

Thanks Aaron

I am trying to do exactly the same thing. Now that my understanding of pg_restore is clearer. I was worried by my index key table was corrupted somehow and caused the error message.  This is the cause by my own misunderstanding.

CY

Aaron Bono <postgresql@aranya.com> wrote:

I agree.  When restoring a database from back up, I do a drop database and recreate it to make sure everything is properly intact (tables, columns, views, triggers, foreign keys, etc...).  We do this a lot for testing.  We backup the production database, copy to test server and do a restore on the test database.  It helps us test our code rollouts.

-Aaron

On 6/18/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Infor Gates <info_gates@yahoo.com> writes:
> I am having the impression that pg_restore would over-rides the "old"
> data with the current one. Is my thinking wrong?

Yeah.  By default, pg_restore will issue a CREATE TABLE (which of course
fails if the table already exists) followed by COPY (which just tries to
insert data in addition to what might be there already).

There's a command line option to ask pg_restore to try to DROP TABLE
before doing the CREATE TABLE.  By and large, though, that's a bad way
to proceed unless you are specifically trying to merge two databases.
The fast and easy way to proceed is to DROP DATABASE, CREATE a fresh
empty database, and pg_restore into that.

                        regards, tom lane

---

Do you Yahoo!?
Next-gen email? Have it all with the all-new Yahoo! Mail Beta.