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

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

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

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