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

Andreas Joseph Krogh <andreas(at)visena(dot)com> writes:
> P tirsdag 12. januar 2016 kl. 22:56:56, skrev John R Pierce <
> pierce(at)hogranch(dot)com <mailto:pierce(at)hogranch(dot)com>>:
>> On 1/12/2016 1:20 PM, Andreas Joseph Krogh wrote:
>>> I'm moving a > 500GB DB to another server which is initdb'ed with a
>>> different locale (nb_NO.UTF-8 to get correct collation when ORDER BY).
>>> Is there another option than pg_dump/restore for doing this?

>> nope, because the text data has to be converted to the new encoding,
>> indexes rebuilt since the collation order is different, etc.

> I was pretty sure there wasn't, but had to ask before imposing downtime on our
> customers.

If you're only changing collation and not encoding, then in principle you
could move the tables over and then reindex each collation-sensitive
index. pg_upgrade has no ability to do that for you though, which would
make it an error-prone manual process. Also, it'd be far from
zero-downtime since you still gotta rebuild a lot of indexes.

regards, tom lane