På tirsdag 12. januar 2016 kl. 23:28:55, skrev Tom Lane <t...@sss.pgh.pa.us <mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh <andr...@visena.com> writes: > P�� tirsdag 12. januar 2016 kl. 22:56:56, skrev John R Pierce < > pie...@hogranch.com <mailto:pie...@hogranch.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 This is interessting. I'm only changin collation, the encoding (UTF-8) is the same. I'm replacing the packaged PG's version of initdb (resulting in en_US.UTF-8) with: /usr/lib/postgresql/9.5/bin/initdb --locale=nb_NO.UTF-8 -D /var/lib/postgresql/9.5/main Are you saying that I don't have to re-initdb and can just change collation somehow? If so, how? pg_upgrade + re-indexing is much preferred instead of the 10+ hours the pg_dump/restore would take (SSD-disks). Thanks. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
