The quickest way to migrate database within the same cluster

From: "Zheng, Wendy" <wendy(dot)zheng(at)emc(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: The quickest way to migrate database within the same cluster
Date: 2015-01-04 06:52:28
Message-ID: 1F7AF1B52D1DAC439765CCD7F7952AD42CF5BC@MX101CL01.corp.emc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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?

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?

Thanks,
Wendy

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Ian Barwick 2015-01-04 07:12:51 Re: The quickest way to migrate database within the same cluster
Previous Message Edoardo Innocenti - SDB Information Technology Srl 2015-01-03 13:53:24 R: SSL Compression doesn't work