From: Zheng, Wendy <wendy.zheng@emc.com>
To: Ian Barwick <ian@2ndquadrant.com>
To: 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:14:28 +0000
Message-ID: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5E2@MX101CL01.corp.emc.com> (raw)
In-Reply-To: <54A8E7F3.8080307@2ndquadrant.com>
References: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5BC@MX101CL01.corp.emc.com>
<54A8E7F3.8080307@2ndquadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
I'm using 9.1
Will foreign data wrapper have similar performance issue as dblink?
Thanks,
Wendy
-----Original Message-----
From: Ian Barwick [mailto:ian@2ndquadrant.com]
Sent: Sunday, January 04, 2015 3:13 PM
To: Zheng, Wendy; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] The quickest way to migrate database within the same cluster
On 15/01/04 15:52, Zheng, Wendy wrote:
> Hi PgSql experts,
>
> I'm working on a task to move tables from on database to another
> within the same cluster and same server. I try the pg_dump and
> pg_restore commands (with -Fc option), and notice that it costs around
> half an hour which is not acceptable. Then I try to move the data
> files directly. I create the same tables in the new DB, find out the
> directory stores the data file and search for the data files by the
> table name, then move the corresponding data file in the old DB to the
> new one. This is very quick, and looks like the DB still works. But I
> have a concern that whether there will any other problem if I doing this?
Yes, you'll experience massive data corruption. You can't just manually copy data files around, even though it might seem to work.
> Another workaround is to access the old DB in the new DB with dblink
> (I created a view with dblink and use it as if the view as if the
> table is in the new DB). But we soon notice that even though we
> specify the criteria in the select command, dblink will still retrieve
> all the records first, and then apply the search criteria. This brings
> poor performance. Do you have any better idea how can I handle this?
Which PostgreSQL version are you using? If 9.3 or later you can use a foreign data wrapper (postgres_fdw) to access data in another database (including on the same cluster).
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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, ian@2ndquadrant.com
Subject: Re: The quickest way to migrate database within the same cluster
In-Reply-To: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5E2@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