På onsdag 13. januar 2016 kl. 03:58:27, skrev John McKown < john.archie.mck...@gmail.com <mailto:john.archie.mck...@gmail.com>>: On Tue, Jan 12, 2016 at 4:09 PM, Andreas Joseph Krogh <andr...@visena.com <mailto:andr...@visena.com>> wrote: På tirsdag 12. januar 2016 kl. 22:56:56, skrev John R Pierce < pie...@hogranch.com <mailto:pie...@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 andr...@visena.com <mailto:andr...@visena.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 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
