Обсуждение: pg_restore duplicate key violations

Hello,

PostgreSQL 8.1.3, Centos 4.2

I'm having trouble with a dump and restore:

$ pg_dump --format=t --schema=babase --data-only --user babase_admin
babase_test | pg_restore --data-only --disable-triggers --user
babase_admin --dbname=babase
pg_restore: ERROR:  duplicate key violates unique constraint
"activities_pkey"
CONTEXT:  COPY activities, line 1: "B   t       t       Be groomed"
pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:
duplicate key violates unique constraint "activities_pkey"
CONTEXT:  COPY activities, line 1: "B   t       t       Be groomed"

The two databases/schemas are identical.

I bumped around the list archives and found a post that seemed
to indicate locale could be a problem.  When I did an initdb
to initialize the cluster I made sure that the locale was C,
and all the LC_ options in postgresql.conf are shown
as C and "show all" shows them all as C in both databases.

The locale in my shell is all en_US.UTF-8 so I tried setting
LC_CHAR and LC_COLLATE to C in the shell before running
the dump/restore and this did not work.  Likewise with LC_ALL.
I also tried exporting LC_CHAR and LC_COLLATE as C in
/etc/sysconfig/pgsql/postgresql so that the server would run
in the correct locale, just in case, but this did not work either.

If it matters when installing postgres I recompiled the
source rpm so as to make it an i686.

Where do I go from here?

Thanks.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

"Karl O. Pinc" <kop@meme.com> writes:
> I'm having trouble with a dump and restore:

> $ pg_dump --format=t --schema=babase --data-only --user babase_admin
> babase_test | pg_restore --data-only --disable-triggers --user
> babase_admin --dbname=babase
> pg_restore: ERROR:  duplicate key violates unique constraint
> "activities_pkey"
> CONTEXT:  COPY activities, line 1: "B   t       t       Be groomed"

Um ... it looks to me like you're trying to restore into an existing
table that already has the same data loaded ...

            regards, tom lane

On 05/08/2006 06:42:18 PM, Tom Lane wrote:
> "Karl O. Pinc" <kop@meme.com> writes:
> > I'm having trouble with a dump and restore:
>
> > $ pg_dump --format=t --schema=babase --data-only --user babase_admin
>
> > babase_test | pg_restore --data-only --disable-triggers --user
> > babase_admin --dbname=babase
> > pg_restore: ERROR:  duplicate key violates unique constraint
> > "activities_pkey"
> > CONTEXT:  COPY activities, line 1: "B   t       t       Be groomed"
>
> Um ... it looks to me like you're trying to restore into an existing
> table that already has the same data loaded ...

That's what I thought at first, except that I had just created
the db structure with a script.  Just in case I checked with
a select from psql.  I even looked at the dump (as text) output to
check that it wasn't doing something wierd like loading things
twice.  Turns out this table is the first to have data loaded into
it.

I'm not clear on where to start with this.


Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

"Karl O. Pinc" <kop@meme.com> writes:
> On 05/08/2006 06:42:18 PM, Tom Lane wrote:
>> Um ... it looks to me like you're trying to restore into an existing
>> table that already has the same data loaded ...

> That's what I thought at first, except that I had just created
> the db structure with a script.  Just in case I checked with
> a select from psql.  I even looked at the dump (as text) output to
> check that it wasn't doing something wierd like loading things
> twice.  Turns out this table is the first to have data loaded into
> it.

> I'm not clear on where to start with this.

Well, the first thing is to look at the database after the failure and
see if there's already data in the table.  I'm betting you'll find there
is.  Then you would start trying to figure out where it came from.

One thought that comes to mind: maybe the table exists in template1?

            regards, tom lane

On 05/09/2006 10:24:28 AM, Tom Lane wrote:
> "Karl O. Pinc" <kop@meme.com> writes:
> > On 05/08/2006 06:42:18 PM, Tom Lane wrote:
> >> Um ... it looks to me like you're trying to restore into an
> existing
> >> table that already has the same data loaded ...
>
> > I'm not clear on where to start with this.
>
> Well, the first thing is to look at the database after the failure and
> see if there's already data in the table.  I'm betting you'll find
> there
> is.  Then you would start trying to figure out where it came from.

I tried that already.  Nothing in the table.

>
> One thought that comes to mind: maybe the table exists in template1?

Don't think so.  Nothing has gone into template1.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

On 05/08/2006 06:42:18 PM, Tom Lane wrote:
> "Karl O. Pinc" <kop@meme.com> writes:
> > I'm having trouble with a dump and restore:
>
> Um ... it looks to me like you're trying to restore into an existing
> table that already has the same data loaded ...

Thanks everybody, the problem was in the schemas and my default
search path.  Somehow when upgrading to 8.1.3 I wound up with
the public schema put back into all my databases, and my
search paths of the databases set back to the default $user,public.

I can see how the public schema got there, this time I did not
delete the public schema from template1.  I'm not so sure about
the search path.  Obviously, I did not do my pg_restore properly
when reloading databases.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein