Re: Moving a large DB (> 500GB) to another DB with different locale - Mailing list pgsql-general
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
pgsql-general by date: