Re: Moving a large DB (> 500GB) to another DB with different locale - Mailing list pgsql-general

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