Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y7fOx-0007rH-Lp for pgsql-admin@arkaria.postgresql.org; Sun, 04 Jan 2015 07:15:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y7fOx-0001BT-2r for pgsql-admin@arkaria.postgresql.org; Sun, 04 Jan 2015 07:15:03 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Y7fOv-00018e-Ca for pgsql-admin@postgresql.org; Sun, 04 Jan 2015 07:15:01 +0000 Received: from mailuogwdur.emc.com ([128.221.224.79]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Y7fOr-0003z2-EU for pgsql-admin@postgresql.org; Sun, 04 Jan 2015 07:14:59 +0000 Received: from maildlpprd51.lss.emc.com (maildlpprd51.lss.emc.com [10.106.48.155]) by mailuogwprd53.lss.emc.com (Sentrion-MTA-4.3.1/Sentrion-MTA-4.3.0) with ESMTP id t047EnN6030023 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Sun, 4 Jan 2015 02:14:50 -0500 X-DKIM: OpenDKIM Filter v2.4.3 mailuogwprd53.lss.emc.com t047EnN6030023 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=emc.com; s=jan2013; t=1420355690; bh=NnrjGeZ+55oxEemN6M09m1vJ3W0=; h=From:To:Subject:Date:Message-ID:References:In-Reply-To: Content-Type:Content-Transfer-Encoding:MIME-Version; b=TqC9B9MDBra4tXc0crbAljd6F3Ro1LwebnMFdDWwYBcnIsf+FS9qnzz34e8bNKHON MB3imLS8PqCQJEfl15ui2HFBbTgGe2fSEP5WHpoF9GOmiJMf6qt7O5rVo2XRkKU/I2 7Q9/CVYpI3OXkNDJjTZ+tpOlSihP5AkVzEfdkM9I= X-DKIM: OpenDKIM Filter v2.4.3 mailuogwprd53.lss.emc.com t047EnN6030023 Received: from mailusrhubprd04.lss.emc.com (mailusrhubprd04.lss.emc.com [10.253.24.22]) by maildlpprd51.lss.emc.com (RSA Interceptor); Sun, 4 Jan 2015 02:14:25 -0500 Received: from mxhub35.corp.emc.com (mxhub35.corp.emc.com [10.254.93.83]) by mailusrhubprd04.lss.emc.com (Sentrion-MTA-4.3.1/Sentrion-MTA-4.3.0) with ESMTP id t047ETxe025605 (version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=FAIL); Sun, 4 Jan 2015 02:14:29 -0500 Received: from MXHUB102.corp.emc.com (10.253.58.15) by mxhub35.corp.emc.com (10.254.93.83) with Microsoft SMTP Server (TLS) id 8.3.327.1; Sun, 4 Jan 2015 02:14:29 -0500 Received: from MX101CL01.corp.emc.com ([169.254.1.90]) by MXHUB102.corp.emc.com ([::1]) with mapi id 14.03.0195.001; Sun, 4 Jan 2015 02:14:29 -0500 From: "Zheng, Wendy" To: Ian Barwick , "pgsql-admin@postgresql.org" Subject: Re: The quickest way to migrate database within the same cluster Thread-Topic: [ADMIN] The quickest way to migrate database within the same cluster Thread-Index: AdAn6lgmQe5mUhB1QmqO6Vtjj6Eb3gALWqSAAApzJuA= Date: Sun, 4 Jan 2015 07:14:28 +0000 Message-ID: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5E2@MX101CL01.corp.emc.com> References: <1F7AF1B52D1DAC439765CCD7F7952AD42CF5BC@MX101CL01.corp.emc.com> <54A8E7F3.8080307@2ndquadrant.com> In-Reply-To: <54A8E7F3.8080307@2ndquadrant.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: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Sentrion-Hostname: mailusrhubprd04.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 I'm using 9.1=20 Will foreign data wrapper have similar performance issue as dblink? Thanks, Wendy -----Original Message----- From: Ian Barwick [mailto:ian@2ndquadrant.com]=20 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 c= luster 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=20 > within the same cluster and same server. I try the pg_dump and=20 > pg_restore commands (with -Fc option), and notice that it costs around=20 > half an hour which is not acceptable. Then I try to move the data=20 > files directly. I create the same tables in the new DB, find out the=20 > directory stores the data file and search for the data files by the=20 > table name, then move the corresponding data file in the old DB to the=20 > new one. This is very quick, and looks like the DB still works. But I=20 > 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 cop= y data files around, even though it might seem to work. > Another workaround is to access the old DB in the new DB with dblink=20 > (I created a view with dblink and use it as if the view as if the=20 > table is in the new DB). But we soon notice that even though we=20 > specify the criteria in the select command, dblink will still retrieve=20 > all the records first, and then apply the search criteria. This brings=20 > 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 forei= gn data wrapper (postgres_fdw) to access data in another database (includin= g on the same cluster). Regards Ian Barwick --=20 Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin