Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XhO8k-00026F-Sp for pgsql-admin@arkaria.postgresql.org; Thu, 23 Oct 2014 19:33:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XhO8k-00029h-Bq for pgsql-admin@arkaria.postgresql.org; Thu, 23 Oct 2014 19:33:42 +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 1XhO8j-000277-05 for pgsql-admin@postgresql.org; Thu, 23 Oct 2014 19:33:41 +0000 Received: from smtp-out2.electric.net ([72.35.23.34]) by magus.postgresql.org with esmtps (TLS1.1:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XhO8d-0000wv-JR for pgsql-admin@postgresql.org; Thu, 23 Oct 2014 19:33:39 +0000 Received: from 1XhO8Z-00083S-Ug by out2b.electric.net with emc1-ok (Exim 4.80.1) (envelope-from ) id 1XhO8Z-00084A-V3 for pgsql-admin@postgresql.org; Thu, 23 Oct 2014 12:33:31 -0700 Received: by emcmailer; Thu, 23 Oct 2014 12:33:31 -0700 Received: from [10.86.10.83] (helo=fuseout2c.electric.net) by out2b.electric.net with esmtps (TLSv1.1:DHE-RSA-AES256-SHA:256) (Exim 4.80.1) (envelope-from ) id 1XhO8Z-00083S-Ug for pgsql-admin@postgresql.org; Thu, 23 Oct 2014 12:33:31 -0700 Received: from mailanyone.net by fuseout2c.electric.net with esmtpsa (TLSv1:DHE-RSA-CAMELLIA256-SHA:256) (MailAnyone extSMTP inigo@isalvat.es) id 1XhO8X-0002JY-GD for pgsql-admin@postgresql.org; Thu, 23 Oct 2014 12:33:31 -0700 Message-ID: <54495807.80108@isalvat.es> Date: Thu, 23 Oct 2014 21:33:27 +0200 From: =?UTF-8?B?ScOxaWdvIFNhbHZhdA==?= User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.2.0 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Single Database Recovery? References: <20141023064401.569d35d6ab57613ee80d5d1694a397ee.da2ef0c184.wbe@email06.secureserver.net> In-Reply-To: <20141023064401.569d35d6ab57613ee80d5d1694a397ee.da2ef0c184.wbe@email06.secureserver.net> Content-Type: multipart/alternative; boundary="------------000602000808080108060100" X-Outbound-IP: 10.86.10.83 X-Env-From: inigo@isalvat.es X-PolicySMART: 872319 X-Virus-Status: Scanned by VirusSMART (c) X-Pg-Spam-Score: -2.6 (--) 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 This is a multi-part message in MIME format. --------------000602000808080108060100 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi John, Maybe you can recover the complete instance in a new instance, drop the databases you do not need and keep the one you are looking for. Regards, Iñigo El 23/10/14 15:44, john@jpm-cola.com escribió: > > Hi all, > > I'm trying to recover a single database that's part of an instance > with other databases which I do not want to recover. We do a physical > backup and have the WAL archive files available. The purpose of this > is to place a copy of one of the Prod databases onto the QA server > which has other existing databases that we want to keep. > > What I was thinking was that I could recover all of the files to a > separate area, then basically just copy the files from that database's > directory into the QA instance database directory (same oid.) There > was an existing copy on the QA server and I want to replace it with > the new copy. > > Is this possible? I know that I could simply do a pg_dump into this QA > database but this seems to take way too long - days instead of the > hours that it takes to unzip the physical backup file into a directory > on the QA server. > > I also know that I could easily create a new instance but I have a > constraint that the IP addresses and ports cannot be changed. > > If this instance only had a single database it would be a simple > physical restore, but the presence of the additional databases has me > stumped. > > The PostgreSQL version is 9.1.9. The server is Red Hat Enterprise > Linux Server release 6.2 (Santiago) on a VM machine - 8 GB RAM with 2 > CPUs: > > Architecture: x86_64 > CPU op-mode(s): 32-bit, 64-bit > Byte Order: Little Endian > CPU(s): 2 > On-line CPU(s) list: 0,1 > Thread(s) per core: 1 > Core(s) per socket: 2 > CPU socket(s): 1 > NUMA node(s): 1 > Vendor ID: GenuineIntel > CPU family: 6 > Model: 44 > Stepping: 2 > CPU MHz: 2660.000 > BogoMIPS: 5320.00 > L1d cache: 32K > L1i cache: 32K > L2 cache: 256K > L3 cache: 12288K > NUMA node0 CPU(s): 0,1 > > Thanks for any ideas or myth debunking that you can apply to this > conundrum. > > Regards, > > John McDougald --------------000602000808080108060100 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Hi John,

Maybe you can recover the complete instance in a new instance, drop the databases you do not need and keep the one you are looking for.

Regards,

Iñigo


El 23/10/14 15:44, john@jpm-cola.com escribió:

Hi all,

I'm trying to recover a single database that's part of an instance with other databases which I do not want to recover. We do a physical backup and have the WAL archive files available. The purpose of this is to place a copy of one of the Prod databases onto the QA server which has other existing databases that we want to keep.

What I was thinking was that I could recover all of the files to a separate area, then basically just copy the files from that database's directory into the QA instance database directory (same oid.) There was an existing copy on the QA server and I want to replace it with the new copy.

Is this possible? I know that I could simply do a pg_dump into this QA database but this seems to take way too long - days instead of the hours that it takes to unzip the physical backup file into a directory on the QA server.

I also know that I could easily create a new instance but I have a constraint that the IP addresses and ports cannot be changed.

If this instance only had a single database it would be a simple physical restore, but the presence of the additional databases has me stumped. 

The PostgreSQL version is 9.1.9. The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a VM machine - 8 GB RAM with 2 CPUs:

Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                2
On-line CPU(s) list:   0,1
Thread(s) per core:    1
Core(s) per socket:    2
CPU socket(s):         1
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 44
Stepping:              2
CPU MHz:               2660.000
BogoMIPS:              5320.00
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              12288K
NUMA node0 CPU(s):     0,1

Thanks for any ideas or myth debunking that you can apply to this conundrum.

Regards,

John McDougald

--------------000602000808080108060100--