From: Zheng, Wendy <wendy.zheng@emc.com>
To: Rosser Schwarz <rosser.schwarz@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: The quickest way to migrate database within the same cluster
Date: Sun, 4 Jan 2015 07:30:06 +0000
Message-ID: <1F7AF1B52D1DAC439765CCD7F7952AD42CF605@MX101CL01.corp.emc.com> (raw)
In-Reply-To: <CAFnxYwhP1Nv_+n3p1WSqGnW0xCdDSPZ6o9zH+jEURCA2+b_e8A@mail.gmail.com>
References: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5BC@MX101CL01.corp.emc.com>
<CAFnxYwhP1Nv_+n3p1WSqGnW0xCdDSPZ6o9zH+jEURCA2+b_e8A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
The tables have the same schema, but in different DB. Because I create the table in the new DB, so the table of the same name in different DB have different filenode too. For example, the data file of old DB is under /pgsql/data/base/16384/, while the data file of new DB is under /dbdata/uemcp_tblspc/PG_9.1_201105231/16481 (I created the new table in a table space). Then the filenode for my table user_information in old DB is 34576, and that in the new DB is 45663. Then I perform the following to switch the data files:
mv /dbdata/uemcp_tblspc/PG_9.1_201105231/16481/45663 /dbdata/uemcp_tblspc/PG_9.1_201105231/16481/45663_bak
mv /pgsql/data/base/16384/34576 /dbdata/uemcp_tblspc/PG_9.1_201105231/16481/45663
mv /dbdata/uemcp_tblspc/PG_9.1_201105231/16481/45663_bak /pgsql/data/base/16384/34576
mv /dbdata/uemcp_tblspc/PG_9.1_201105231/16481/45663_fsm /dbdata/uemcp_tblspc/PG_9.1_201105231/16481/45663_fsm_bak
mv /pgsql/data/base/16384/34576_fsm /dbdata/uemcp_tblspc/PG_9.1_201105231/16481/45663_fsm
mv /dbdata/uemcp_tblspc/PG_9.1_201105231/16481/45663_fsm_bak /pgsql/data/base/16384/34576_fsm
I do that for all of my tables. Looks like I can still query and insert records.
Thanks,
Wendy
From: Rosser Schwarz [mailto:rosser.schwarz@gmail.com]
Sent: Sunday, January 04, 2015 3:19 PM
To: Zheng, Wendy
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] The quickest way to migrate database within the same cluster
On Sat, Jan 3, 2015 at 10:52 PM, Zheng, Wendy <wendy.zheng@emc.com<mailto: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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: wendy.zheng@emc.com, rosser.schwarz@gmail.com
Subject: Re: The quickest way to migrate database within the same cluster
In-Reply-To: <1F7AF1B52D1DAC439765CCD7F7952AD42CF605@MX101CL01.corp.emc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox