From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Moving a large DB (> 500GB) to another DB with different locale |
Date: | 2016-01-12 22:35:46 |
Message-ID: | VisenaEmail.22.1002b31060a573b2.15237f662dd@tc7-visena |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
På tirsdag 12. januar 2016 kl. 23:28:55, 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:
> 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
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
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2016-01-12 22:43:44 | Re: WIP: CoC V5 |
Previous Message | Cory Tucker | 2016-01-12 22:29:02 | Re: Blocked updates and background writer performance |