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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
