Message-ID: <54A8E7F3.8080307@2ndquadrant.com>
Date: Sun, 04 Jan 2015 16:12:51 +0900
From: Ian Barwick <ian@2ndquadrant.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7;
 rv:31.0) Gecko/20100101 Thunderbird/31.3.0
MIME-Version: 1.0
To: "Zheng, Wendy" <wendy.zheng@emc.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: The quickest way to migrate database within the same
 cluster
References: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5BC@MX101CL01.corp.emc.com>
In-Reply-To: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5BC@MX101CL01.corp.emc.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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