Обсуждение: Single Database Recovery?

El jue, 23-10-2014 a las 06:44 -0700, 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?

No, if you have a physical backup with all archive files, you will able
to restore the whole instance. It's not posible to restore only a
database with a physical backup.

> 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

Zitat von john@jpm-cola.com:

> 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.
>

Have you tried to simple use pg_dump piped to a psql feeding the data
directly in the target QA database? It has the advantage of not using
local temporary storage and should be reasonable fast in the range of
< TB.

Regards

Andreas

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