Re: The quickest way to migrate database within the same cluster - Mailing list pgsql-admin

From Rosser Schwarz
Subject Re: The quickest way to migrate database within the same cluster
Date
Msg-id CAFnxYwhP1Nv_+n3p1WSqGnW0xCdDSPZ6o9zH+jEURCA2+b_e8A@mail.gmail.com
Whole thread Raw
In response to The quickest way to migrate database within the same cluster  ("Zheng, Wendy" <wendy.zheng@emc.com>)
Responses Re: The quickest way to migrate database within the same cluster  ("Zheng, Wendy" <wendy.zheng@emc.com>)
List pgsql-admin
On Sat, Jan 3, 2015 at 10:52 PM, Zheng, Wendy <wendy.zheng@emc.com> wrote: 

I’m working on a task to move tables from on database to another within the same cluster and same server...


Your second idea, of copying the underlying disk files, probably won't work unless you're also copying all the relevant records from all of the system catalog tables (a non-trivial task, itself), and likely not even then. (For instance, if two tables in the different databases have the same 'relfilenode', you're going to overwrite that table in the second when you perform the copy, and because those tables probably have different structures, you'll then see "unexpected" behavior.)

You might instead try piping the output of a "COPY ($query) TO STDOUT" in a psql session against the source database into a "COPY table FROM STDIN" command in a psql session against the destination. That would filter on the source side, but would require you to have empty tables of the expected structure on the destination to receive the data.

rls

--
:wq

pgsql-admin by date:

Previous
From: "Zheng, Wendy"
Date:
Subject: Re: The quickest way to migrate database within the same cluster
Next
From: "Zheng, Wendy"
Date:
Subject: Re: The quickest way to migrate database within the same cluster