Обсуждение: Moving a large DB (> 500GB) to another DB with different locale

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.



--
john r pierce, recycling bits in santa cruz

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.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Andreas Joseph Krogh <andreas@visena.com> writes:
> På tirsdag 12. januar 2016 kl. 22:56:56, skrev John R Pierce <
> pierce@hogranch.com <mailto:pierce@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

Andreas Joseph Krogh <andreas@visena.com> writes:
> P�� tirsdag 12. januar 2016 kl. 22:56:56, skrev John R Pierce <
> pierce@hogranch.com <mailto:pierce@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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Andreas Joseph Krogh <andreas@visena.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

Andreas Joseph Krogh <andreas@visena.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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Andreas Joseph Krogh <andreas@visena.com> writes:
> What about ORDER BY on columns without an index, would they sort correctly?

Sorting is sorting, it'll just use whatever collation is specified or
implied.

            regards, tom lane

Andreas Joseph Krogh <andreas@visena.com> writes:
> What about ORDER BY on columns without an index, would they sort correctly?

Sorting is sorting, it'll just use whatever collation is specified or
implied.

regards, tom lane

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On Tue, Jan 12, 2016 at 4:09 PM, Andreas Joseph Krogh <andreas@visena.com> wrote:

På tirsdag 12. januar 2016 kl. 22:56:56, skrev John R Pierce <pierce@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.

 

Thanks.

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

 

 

​I install, maintain, and use PostgreSQL on Fedora Linux. But only as a type of "power" user. I'm not a DBA​. Nor do I have good knowledge of PostgreSQL internals. What I gather you are considering is something like:

 

server1: pg_dump one or more data bases to a file on server1 (or on an NFS / CIFS shared NAS box of some sort)

server1: transfer that file to server2 if necessary (i.e. if the file dumped on server1 is not readable directly on server2.

server2: pg_restore the data from the file.

 

What I am wondering is this: Is there some way to make the PostgreSQL instance on server2 be accessable, say via FDW, to PostgreSQL on server1? Or may vice versa. 

ref: http://www.postgresql.org/docs/current/static/postgres-fdw.html

if so, would it be possible to do something like:

 

on server1:

 

CREATE EXTENSION posgres_fdw;

CREATE SERVER server2

    FOREIGN DATA WRAPPER postgres_fdw

    OPTIONS(host 'a.b.c.d', port '5432', db_name 'bubba')

;

CREATE USER MAPPING FOR <local_user>

    SERVER server2

    OPTIONS(user 'remote_user', password 'password')

;

CREATE FOREIGN TABLE server2_sometable (

   -- duplicate definition of "sometable" on server2

) SERVER server2 OPTIONS(schema_name 'PUBLIC', table_name 'sometable')

;

INSERT INTO server2_sometable (COL1, COL2, COL3, ...) SELECT COL1, COL2, COL3, ... FROM sometable;

 

This is most likely a stupid thought. And I don't know if it would address the locale and collation issue or not. If nothing else, someone will explain (hopefully kindly) why this is a bad idea. Such as performance or some such thing.

 

An auxiliary thought, if the communications speed between server1 & server2 is "poor", would be to have two copies of PostgreSQL running on server1. The second Postgres would write to a filesystem on an SSD connected to Server1, either via SATA, eSATA, or maybe USB. I would hope that this would be faster than using a 1Gig (or ever 10 Gig) IP connection. Once the copy is complete, stop the second Postgres instance, unmount the filesystem, move the SSD to "server2" hardware platform.

 

Just some wacky thoughts. They are right at home in my head.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

 

On Tue, Jan 12, 2016 at 4:20 PM, Andreas Joseph Krogh <andreas@visena.com> 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?

 

I recently used slony to move data stored in an SQL_ASCII db to one that was strict UTF-8. I wrote up a program that would fix any invalid byte sequences in the original db first. It went very well, and we had very minimal downtime.

 

It seems to me you could use slony to accomplish your goal as well of just copying the data into a new DB with the different collation setting. Once done, just stop your application for a minute or two to switch the DB it uses, and drop the slony replication. This process will work if you can assure that the dump/restore would have worked too. That is, there are no invalid data.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On 1/13/16 2:03 AM, Andreas Joseph Krogh wrote:
> Seems like pglogical migth be better?
> http://2ndquadrant.com/en/resources/pglogical/
> It's available for 9.4 also.

It would certainly be faster. It's also less tested than Slony is
though, as it's fairly new. If it was me, I'd use pg_logical.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 1/13/16 2:03 AM, Andreas Joseph Krogh wrote:
> Seems like pglogical migth be better?
> http://2ndquadrant.com/en/resources/pglogical/
> It's available for 9.4 also.

It would certainly be faster. It's also less tested than Slony is
though, as it's fairly new. If it was me, I'd use pg_logical.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On 1/13/16 2:39 PM, Andreas Joseph Krogh wrote:
> Where can I find more info about how to use and configure pg_logical to
> replicate a 9.4 DB to 9.5?

http://2ndquadrant.com/en/resources/pglogical/
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 1/13/16 2:39 PM, Andreas Joseph Krogh wrote:
> Where can I find more info about how to use and configure pg_logical to
> replicate a 9.4 DB to 9.5?

http://2ndquadrant.com/en/resources/pglogical/

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On Fri, Jan 15, 2016 at 1:02 PM, Andreas Joseph Krogh <andreas@visena.com> wrote:

På torsdag 14. januar 2016 kl. 00:34:51, skrev Jim Nasby <Jim.Nasby@BlueTreble.com>:

On 1/13/16 2:39 PM, Andreas Joseph Krogh wrote:
> Where can I find more info about how to use and configure pg_logical to
> replicate a 9.4 DB to 9.5?

http://2ndquadrant.com/en/resources/pglogical/

 

Thanks, I found detailed instructions in /usr/share/doc/postgresql-9.5-pglogical/README.md.gz

Any chance of putting in online?

 

I see that wal_level = 'logical', and that is a problem for us as we already use wal_level = 'hot_standby' on this installation as it replicates to another server.

 

Is it possible to use pglogical together with hot_standby streaming-replication?

 

Well, the wal_level change is just a matter of database restart: you got to do that once in a while anyway, e.g. for minor version updates.  I would expect you only need this wal_level on the walsender side, thus for pglogical_output, the logical decoding plugin.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On Fri, Jan 15, 2016 at 3:41 PM, Andreas Joseph Krogh <andreas@visena.com> wrote:

På fredag 15. januar 2016 kl. 14:33:24, skrev Shulgin, Oleksandr <oleksandr.shulgin@zalando.de>:

On Fri, Jan 15, 2016 at 1:02 PM, Andreas Joseph Krogh <andreas@visena.com> wrote:

 

I see that wal_level = 'logical', and that is a problem for us as we already use wal_level = 'hot_standby' on this installation as it replicates to another server.

 

Is it possible to use pglogical together with hot_standby streaming-replication?

 

Well, the wal_level change is just a matter of database restart: you got to do that once in a while anyway, e.g. for minor version updates.  I would expect you only need this wal_level on the walsender side, thus for pglogical_output, the logical decoding plugin.

 

My point is that we cannot not have streaming-replication, so we need to keep wal_level = 'hot_standby' AFAIU. Is there a way to do both streaming-replication and pglogical for just replicating one of may databases in the same cluster?

 

But logical is "greater than" hot_standby, so you can still have streaming replication with wal_level = logical.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com