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

På onsdag 13. januar 2016 kl. 03:58:27, skrev John McKown <
john(dot)archie(dot)mckown(at)gmail(dot)com <mailto:john(dot)archie(dot)mckown(at)gmail(dot)com>>:
On Tue, Jan 12, 2016 at 4:09 PM, Andreas Joseph Krogh <andreas(at)visena(dot)com
<mailto:andreas(at)visena(dot)com>> wrote:
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.
 
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>

 
​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
<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.

 
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
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>