Re: Single Database Recovery? - Mailing list pgsql-admin

From Iñigo Salvat
Subject Re: Single Database Recovery?
Date
Msg-id 54495807.80108@isalvat.es
Whole thread Raw
In response to Single Database Recovery?  (<john@jpm-cola.com>)
List pgsql-admin
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

pgsql-admin by date:

Previous
From: lst_hoe02@kwsoft.de
Date:
Subject: Re: Single Database Recovery?
Next
From: Borys Gogulski
Date:
Subject: PostgreSQL 9.3 - pg_stat_database - tup_returned got "too high" value