duplicate key errors when restoring 8.4.0 database dump into 9.1.2

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

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 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

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 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.

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

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.

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:

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