I have an Ubuntu system running what claims to be:
root@smallserver:/usr/local/archiveopteryx3/bin# ./aox show build
Archiveopteryx version 3.1.3, http://www.archiveopteryx.org/3.1.3
Built on May 1 2011 16:54:19
Build source: oryx.git/v3.1.3
on postgresql 9.0, from Ubuntu repository.
And, I have a shiny new FreeBSD 9.1 system with postgresql 9.2.2, from
ports.
So, I dump the db from the old server and restore it onto the new one.
I get a couple of issues - there are commands that relate to to
'postgres' which isn't installed by the port (which installs pgsql) -
and is easily fixed by defining the role - but the other is baffling.
I get:
psql:archiveopteryx.backup:1316974: ERROR: could not create unique
index "addresses_nld_key"
DETAIL: Key (name, localpart, lower(domain))=(0amamacint, alert,
date.com) is duplicated.
Now I can understand getting an error like this when messing around
normally using DML, but this is a backup and the index only exists
because the original PostgreSQL thought it did. :-(
Is this a difference in lower(domain) behaviour between the older Ubuntu
and the newer FreeBSD UTF8? Any hint on how to fix?
The database is created as:
CREATE DATABASE archiveopteryx
WITH OWNER = aoxsuper
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_GB.UTF-8'
LC_CTYPE = 'en_GB.UTF-8'
CONNECTION LIMIT = -1;
(which fails on the latest Win32 build of PostgreSQL on my Win7 64 bit
system, which is a bit annoying - it doesn't seem to like the locale,
but that's another matter. Inconvenient, though.)
