Thread: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Culley Harrelson
Date:
Hello I am trying to migrate a database from 8.4.0 to 9.1.2 on a test server before updating the production server.  When piping the dump file created with pg_dump in psql I am getting duplicate key errors and the primary keys on several large tables do not get created.  I have read all the migration notes and do not see anything specific other than a pg_dump restore is required.  Any clues for me?

Thanks,

culley

Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Adrian Klaver
Date:
On Friday, December 30, 2011 6:32:56 am Culley Harrelson wrote:
> Hello I am trying to migrate a database from 8.4.0 to 9.1.2 on a test
> server before updating the production server.  When piping the dump file
> created with pg_dump in psql I am getting duplicate key errors and the
> primary keys on several large tables do not get created.  I have read all
> the migration notes and do not see anything specific other than a pg_dump
> restore is required.  Any clues for me?

Was there data already in the 9.1 database?
Post some of the error messages.

>
> Thanks,
>
> culley

--
Adrian Klaver
adrian.klaver@gmail.com

Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Culley Harrelson
Date:
They are just your standard sql errors seen in the output of psql mydb < backup.sql


ALTER TABLE
ERROR:  could not create unique index "ht_user_pkey"
DETAIL:  Key (user_id)=(653009) is duplicated.

There is a unique index on user_id in the 8..4.0 system and, of course, only one record for 653009.




On Fri, Dec 30, 2011 at 6:51 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On Friday, December 30, 2011 6:32:56 am Culley Harrelson wrote:
> Hello I am trying to migrate a database from 8.4.0 to 9.1.2 on a test
> server before updating the production server.  When piping the dump file
> created with pg_dump in psql I am getting duplicate key errors and the
> primary keys on several large tables do not get created.  I have read all
> the migration notes and do not see anything specific other than a pg_dump
> restore is required.  Any clues for me?

Was there data already in the 9.1 database?
Post some of the error messages.

>
> Thanks,
>
> culley

--
Adrian Klaver
adrian.klaver@gmail.com

Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Adrian Klaver
Date:
On Friday, December 30, 2011 7:49:31 am Culley Harrelson wrote:
> They are just your standard sql errors seen in the output of psql mydb <
> backup.sql
>
>
> ALTER TABLE
> ERROR:  could not create unique index "ht_user_pkey"
> DETAIL:  Key (user_id)=(653009) is duplicated.
>
> There is a unique index on user_id in the 8..4.0 system and, of course,
> only one record for 653009.
>

http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html

When doing the pg_dump of the 8.4 database you might want to use the  -c option

"
-c
--clean

    Output commands to clean (drop) database objects prior to outputting the
commands for creating them. (Restore might generate some harmless errors.)

    This option is only meaningful for the plain-text format. For the archive
formats, you can specify the option when you call pg_restore.
"

My suspicion is that there is already data in the tables of the 9.1 server from
previous restore attempts.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Culley Harrelson
Date:
There is not any data in the new database.  I have dropped the database, created the database and then piped in the backup every time.


On Fri, Dec 30, 2011 at 8:06 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On Friday, December 30, 2011 7:49:31 am Culley Harrelson wrote:
> They are just your standard sql errors seen in the output of psql mydb <
> backup.sql
>
>
> ALTER TABLE
> ERROR:  could not create unique index "ht_user_pkey"
> DETAIL:  Key (user_id)=(653009) is duplicated.
>
> There is a unique index on user_id in the 8..4.0 system and, of course,
> only one record for 653009.
>

http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html

When doing the pg_dump of the 8.4 database you might want to use the  -c option

"
-c
--clean

   Output commands to clean (drop) database objects prior to outputting the
commands for creating them. (Restore might generate some harmless errors.)

   This option is only meaningful for the plain-text format. For the archive
formats, you can specify the option when you call pg_restore.
"

My suspicion is that there is already data in the tables of the 9.1 server from
previous restore attempts.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Nigel Heron
Date:

On 11-12-30 10:49 AM, Culley Harrelson wrote:
> They are just your standard sql errors seen in the output of psql mydb
> < backup.sql
>
>
> ALTER TABLE
> ERROR:  could not create unique index "ht_user_pkey"
> DETAIL:  Key (user_id)=(653009) is duplicated.
>
> There is a unique index on user_id in the 8..4.0 system and, of
> course, only one record for 653009.
>
>

Are you sure there is just one record? I had this same problem and it
was because there were a few rows that violated the primary key even
though the constraint existed since table creation. The db had a hard
crash once which might explain the bad data though.
run this to check your whole table for duplicates:

select user_id, count(*)
from ht_user
group by user_id
having count(*) > 1;

-nigel.

Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Adrian Klaver
Date:
On Friday, December 30, 2011 8:12:27 am Culley Harrelson wrote:
> There is not any data in the new database.  I have dropped the database,
> created the database and then piped in the backup every time.
>

Well another fine theory shot in the <supply body part>:)
Questions:
1) Have you gone through the plain text dump data to see if the value is indeed
duplicated?
2) What is data type of user_id?


--
Adrian Klaver
adrian.klaver@gmail.com

Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Bèrto ëd Sèra
Date:
Hi!
 
Are you sure there is just one record? 

What happens if you grep the backup file for "653009"?

If you do have more than one such record, the quickest way out is to manually clean it.

Bèrto
 
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Culley Harrelson
Date:
This is just the first of many duplicate key errors that cause primary key creation statements to fail on other tables.  I grepped for the key but it is hard to tell where the problem is with 888 matches.

I will try pg_dump --inserts.  It is a 17G file with copy statements so... this should be interesting.  And take a long time.



On Fri, Dec 30, 2011 at 8:20 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:
Hi!
 
Are you sure there is just one record? 

What happens if you grep the backup file for "653009"?

If you do have more than one such record, the quickest way out is to manually clean it.

Bèrto
 
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

From
Adrian Klaver
Date:
On Friday, December 30, 2011 8:51:17 am Culley Harrelson wrote:
> This is just the first of many duplicate key errors that cause primary key
> creation statements to fail on other tables.  I grepped for the key but it
> is hard to tell where the problem is with 888 matches.
>
> I will try pg_dump --inserts.  It is a 17G file with copy statements so...
> this should be interesting.  And take a long time.

Might want to try just dumping one table to make it a little easier.

>

--
Adrian Klaver
adrian.klaver@gmail.com