Re: Moving a large DB (> 500GB) to another DB with different locale

På tirsdag 12. januar 2016 kl. 23:49:24, skrev Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>:
Andreas Joseph Krogh <andreas(at)visena(dot)com> writes:
> Are you saying that I don't have to re-initdb and can just change collation
> somehow? If so, how?

Collation is really pretty much a per-index property these days; the
DB-level setting only provides a default.  You could imagine some
process along the lines of:

1. For each collation-sensitive index, use CREATE INDEX CONCURRENTLY
to build a new index with same properties except for collation.

2. Change the DB-level setting so that ORDER BY acquires the new
default interpretation (I assume you don't want to attach an explicit
COLLATE to every ORDER BY for the rest of time, else you wouldn't
need to do this).  I think you'd have to poke pg_database.datcollate
and datctype directly as a superuser for this to happen, but AFAIR
there's not any underlying magic that would prevent it from working.

3. Drop all the now-useless indexes with the old collation.

One fly in the ointment is that step 1 would result in indexes marked
with indcollate equal to the explicitly chosen collation.  There was
just some discussion the other day about how the planner wouldn't
recognize that this is equivalent to COLLATE "default", so after 2
you might also need a step that runs through pg_index and updates
the collation OIDs to match the "default" collation.

Obviously, this is untested and you'd be foolish not to try it out
on a test installation before believing that it works.  But I think
it might, and if you are looking at a seriously painful dump+reload
it'd be worth the trouble to debug a process for it.

regards, tom lane
 
Thanks for the explanation, I'll try it out!
 
What about ORDER BY on columns without an index, would they sort correctly?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>