X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B41DD3A3B81 for ; Mon, 8 Nov 2004 05:49:09 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37834-06 for ; Mon, 8 Nov 2004 05:49:04 +0000 (GMT) Received: from pepe.laptop.tld (unknown [196.40.30.116]) by svr1.postgresql.org (Postfix) with ESMTP id D21FB3A3EF3 for ; Mon, 8 Nov 2004 05:49:01 +0000 (GMT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by pepe.laptop.tld (Postfix) with ESMTP id 1C647409D3 for ; Sun, 7 Nov 2004 23:48:56 -0600 (CST) Message-ID: <418F08C7.60305@libby.com> Date: Sun, 07 Nov 2004 23:48:55 -0600 From: Dan Libby User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040815 X-Accept-Language: en-us, es, es-cr, en, sv, ja, ru MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: dump/restore results in duplicate key violation with 7.4.6. References: <418BD67D.1010108@osc.co.cr> <418BE4A2.1060506@libby.com> In-Reply-To: <418BE4A2.1060506@libby.com> X-Enigmail-Version: 0.85.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/111 X-Sequence-Number: 15495 Update. After Tom mentioned that my issue might be locale related I ran pg_controldata on both servers. On gentoo, LC_COLLATE and LC_CTYPE are set to C locale. On Redhat they are set to en_US.UTF-8. I re-ran initdb on Redhat with the --locale=C param, and performed the import again. This time all data imported correctly. That is great, as it enables me to move forward, but there's still a couple open questions: 1) I don't understand why a difference in locale should cause a duplicate key error, especially when both databases were created using 'UNICODE' encoding. Is this valid behavior or a postgres bug? 2) According to the docs [1], locale is set at initdb time. The redhat machine is a production server and has other databases running for other applications. I could do a dump of all data, then initdb, then import data, but it occurs to me that I might run into a similar "duplicate key" error (or other import strangeness) in one of the other databases. Can anyone shed more light on the implications of moving data from en_US.UTF-8 locale to C locale? regards, Dan Libby [1] http://www.postgresql.org/docs/7.4/static/charset.html