Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y7f3m-0007Bl-4M for pgsql-admin@arkaria.postgresql.org; Sun, 04 Jan 2015 06:53:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y7f3k-0005rL-Q5 for pgsql-admin@arkaria.postgresql.org; Sun, 04 Jan 2015 06:53:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Y7f3i-0005rC-Gh for pgsql-admin@postgresql.org; Sun, 04 Jan 2015 06:53:06 +0000 Received: from mailuogwdur.emc.com ([128.221.224.79]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Y7f3Y-0008UK-Hn for pgsql-admin@postgresql.org; Sun, 04 Jan 2015 06:53:04 +0000 Received: from maildlpprd53.lss.emc.com (maildlpprd53.lss.emc.com [10.106.48.157]) by mailuogwprd51.lss.emc.com (Sentrion-MTA-4.3.1/Sentrion-MTA-4.3.0) with ESMTP id t046qr56030680 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Sun, 4 Jan 2015 01:52:53 -0500 X-DKIM: OpenDKIM Filter v2.4.3 mailuogwprd51.lss.emc.com t046qr56030680 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=emc.com; s=jan2013; t=1420354373; bh=iCERPZV+56wcXFeBHRRqOVaI5fQ=; h=From:To:Subject:Date:Message-ID:Content-Type:MIME-Version; b=bX7LNHWehqB7Iskb3gQegwKBeFtSMmsGE+XMFB7/nhayS9Ny5ssnJweCsLIAtSNnV 9s9VdOpbjQrbczpFY6mP0hYSBT8zyNP8jPyLhcC0B6IL6ZNpCTUVfQO7fxcZBMu+Vo /Rm1A1zoK0aDmS/hqy6kjnq/A78TJh4pkGF1WcyQ= X-DKIM: OpenDKIM Filter v2.4.3 mailuogwprd51.lss.emc.com t046qr56030680 Received: from mailusrhubprd52.lss.emc.com (mailusrhubprd52.lss.emc.com [10.106.48.25]) by maildlpprd53.lss.emc.com (RSA Interceptor) for ; Sun, 4 Jan 2015 01:50:28 -0500 Received: from mxhub27.corp.emc.com (mxhub27.corp.emc.com [10.254.110.183]) by mailusrhubprd52.lss.emc.com (Sentrion-MTA-4.3.1/Sentrion-MTA-4.3.0) with ESMTP id t046qTiV006757 (version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=FAIL) for ; Sun, 4 Jan 2015 01:52:30 -0500 Received: from MXHUB203.corp.emc.com (10.253.68.29) by mxhub27.corp.emc.com (10.254.110.183) with Microsoft SMTP Server (TLS) id 8.3.327.1; Sun, 4 Jan 2015 01:52:29 -0500 Received: from MX101CL01.corp.emc.com ([169.254.1.90]) by MXHUB203.corp.emc.com ([10.253.68.29]) with mapi id 14.03.0195.001; Sun, 4 Jan 2015 01:52:28 -0500 From: "Zheng, Wendy" To: "pgsql-admin@postgresql.org" Subject: The quickest way to migrate database within the same cluster Thread-Topic: The quickest way to migrate database within the same cluster Thread-Index: AdAn6lgmQe5mUhB1QmqO6Vtjj6Eb3g== Date: Sun, 4 Jan 2015 06:52:28 +0000 Message-ID: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5BC@MX101CL01.corp.emc.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.164.25.218] Content-Type: multipart/alternative; boundary="_000_1F7AF1B52D1DAC439765CCD7F7952AD42CF5BCMX101CL01corpemcc_" MIME-Version: 1.0 X-Sentrion-Hostname: mailusrhubprd52.lss.emc.com X-RSA-Classifications: public X-Pg-Spam-Score: -4.3 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_000_1F7AF1B52D1DAC439765CCD7F7952AD42CF5BCMX101CL01corpemcc_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 (w= ith -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 t= ables 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 fil= e in the old DB to the new one. This is very quick, and looks like the DB s= till 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 cre= ated 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 th= e select command, dblink will still retrieve all the records first, and the= n apply the search criteria. This brings poor performance. Do you have any = better idea how can I handle this? Thanks, Wendy --_000_1F7AF1B52D1DAC439765CCD7F7952AD42CF5BCMX101CL01corpemcc_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi PgSql experts,

 

I’m working on a task to move tables from on d= atabase to another within the same cluster and same server. I try the pg_du= mp and pg_restore commands (with –Fc option), and notice that it cost= s around half an hour which is not acceptable. Then I try to move the data files directly. I create the same tables in th= e new DB, find out the directory stores the data file and search for the da= ta files by the table name, then move the corresponding data file in the ol= d DB to the new one. This is very quick, and looks like the DB still works. But I have a concern that whethe= r there will any other problem if I doing this?

 

Another workaround is to access the old DB in the ne= w 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 spe= cify the criteria in the select command, dblink will still retrieve all the records first, and then apply the searc= h criteria. This brings poor performance. Do you have any better idea how c= an I handle this?

 

Thanks,

Wendy

--_000_1F7AF1B52D1DAC439765CCD7F7952AD42CF5BCMX101CL01corpemcc_--