Thread: Moving a large DB (> 500GB) to another DB with different locale
Hi all.
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?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
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
Attachment
På tirsdag 12. januar 2016 kl. 23:28:55, skrev Tom Lane <tgl@sss.pgh.pa.us>:
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
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
Attachment
På tirsdag 12. januar 2016 kl. 23:49:24, skrev Tom Lane <tgl@sss.pgh.pa.us>:
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
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
Attachment
På onsdag 13. januar 2016 kl. 00:03:18, skrev Tom Lane <tgl@sss.pgh.pa.us>:
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
Right, so poking with pg_database.datcollate would take care of that so no explicit COLLATE has to be issued on ORDER BY?
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
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 KroghCTO / Partner - Visena ASMobile: +47 909 56 963
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.
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.
Werner Heisenberg is driving down the autobahn. A police officer pulls
him over. The officer says, "Excuse me, sir, do you know how fast you
were going?"
"No," replies Dr. Heisenberg, "but I know where I am."
him over. The officer says, "Excuse me, sir, do you know how fast you
were going?"
"No," replies Dr. Heisenberg, "but I know where I am."
Computer Science is the only discipline in which we view adding a new wing to a building as being maintenance -- Jim Horning
Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.
He's about as useful as a wax frying pan.
Maranatha! <><
John McKown
Maranatha! <><
John McKown
Attachment
På onsdag 13. januar 2016 kl. 03:58:27, skrev John McKown <john.archie.mckown@gmail.com>:
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 KroghCTO / Partner - Visena ASMobile: +47 909 56 963I 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.if so, would it be possible to do something like:on server1:CREATE EXTENSION posgres_fdw;CREATE SERVER server2FOREIGN DATA WRAPPER postgres_fdwOPTIONS(host 'a.b.c.d', port '5432', db_name 'bubba');CREATE USER MAPPING FOR <local_user>SERVER server2OPTIONS(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.
Note that I'm searching for a solution for moving a single (out of many) database in a cluster, not just some tables.
This database contains about 400 tables, triggers, constraints (also exclusion-constraints) PL/pgSQL functions etc.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
På onsdag 13. januar 2016 kl. 04:17:03, skrev Vick Khera <vivek@khera.org>:
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.
Seems like pglogical migth be better? http://2ndquadrant.com/en/resources/pglogical/
It's available for 9.4 also.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
På onsdag 13. januar 2016 kl. 19:18:20, skrev Jim Nasby <Jim.Nasby@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
Where can I find more info about how to use and configure pg_logical to replicate a 9.4 DB to 9.5?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
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?
Thank.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
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: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.gzAny 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.
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?
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
På fredag 15. januar 2016 kl. 16:04:00, skrev Shulgin, Oleksandr <oleksandr.shulgin@zalando.de>:
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.
This answers my initial question, thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963