From: Ian Barwick <ian@2ndquadrant.com>
To: Zheng, Wendy <wendy.zheng@emc.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: The quickest way to migrate database within the same cluster
Date: Sun, 04 Jan 2015 16:42:26 +0900
Message-ID: <54A8EEE2.1060708@2ndquadrant.com> (raw)
In-Reply-To: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5E2@MX101CL01.corp.emc.com>
References: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5BC@MX101CL01.corp.emc.com>
<54A8E7F3.8080307@2ndquadrant.com>
<1F7AF1B52D1DAC439765CCD7F7952AD42CF5E2@MX101CL01.corp.emc.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 15/01/04 16:14, Zheng, Wendy wrote:
> 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).
> I'm using 9.1
> Will foreign data wrapper have similar performance issue as dblink?
It won't be slower and depending on your use case should be more efficient,
however you'll need 9.3 or later.
If your aim is to move tables between databases, you should be able to use
dblink to copy the data.
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: ian@2ndquadrant.com, wendy.zheng@emc.com
Subject: Re: The quickest way to migrate database within the same cluster
In-Reply-To: <54A8EEE2.1060708@2ndquadrant.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