pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSingle Database Recovery?
4+ messages / 4 participants
[nested] [flat]
* Single Database Recovery?
@ 2014-10-23 13:44 john@jpm-cola.com
0 siblings, 3 replies; 4+ messages in thread
From: john@jpm-cola.com @ 2014-10-23 13:44 UTC (permalink / raw)
To: pgsql-admin
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div><br></div><div>Hi all,</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>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. </div><div><br></div><div>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:</div><div><br></div><div style=""><div><font face="courier new, monospace">Architecture: x86_64</font></div><div><font face="courier new, monospace">CPU op-mode(s): 32-bit, 64-bit</font></div><div><font face="courier new, monospace">Byte Order: Little Endian</font></div><div><font face="courier new, monospace">CPU(s): 2</font></div><div><font face="courier new, monospace">On-line CPU(s) list: 0,1</font></div><div><font face="courier new, monospace">Thread(s) per core: 1</font></div><div><font face="courier new, monospace">Core(s) per socket: 2</font></div><div><font face="courier new, monospace">CPU socket(s): 1</font></div><div><font face="courier new, monospace">NUMA node(s): 1</font></div><div><font face="courier new, monospace">Vendor ID: GenuineIntel</font></div><div><font face="courier new, monospace">CPU family: 6</font></div><div><font face="courier new, monospace">Model: 44</font></div><div><font face="courier new, monospace">Stepping: 2</font></div><div><font face="courier new, monospace">CPU MHz: 2660.000</font></div><div><font face="courier new, monospace">BogoMIPS: 5320.00</font></div><div><font face="courier new, monospace">L1d cache: 32K</font></div><div><font face="courier new, monospace">L1i cache: 32K</font></div><div><font face="courier new, monospace">L2 cache: 256K</font></div><div><font face="courier new, monospace">L3 cache: 12288K</font></div><div style=""><font face="courier new, monospace" style="">NUMA node0 CPU(s): 0,1</font></div></div><div><br></div><div>Thanks for any ideas or myth debunking that you can apply to this conundrum.</div><div><br></div><div>Regards,</div><div><br></div><div>John McDougald</div></span></body></html>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Single Database Recovery?
@ 2014-10-23 14:26 jaime soler <jaime.soler@gmail.com>
parent: john@jpm-cola.com
2 siblings, 0 replies; 4+ messages in thread
From: jaime soler @ 2014-10-23 14:26 UTC (permalink / raw)
To: john@jpm-cola.com; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Single Database Recovery?
@ 2014-10-23 16:18 lst_hoe02@kwsoft.de
parent: john@jpm-cola.com
2 siblings, 0 replies; 4+ messages in thread
From: lst_hoe02@kwsoft.de @ 2014-10-23 16:18 UTC (permalink / raw)
To: pgsql-admin
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
Attachments:
smime.p7s (application/pkcs7-signature, 5.8K) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Single Database Recovery?
@ 2014-10-23 19:33 Iñigo Salvat <inigo@isalvat.es>
parent: john@jpm-cola.com
2 siblings, 0 replies; 4+ messages in thread
From: Iñigo Salvat @ 2014-10-23 19:33 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-10-23 19:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-23 13:44 Single Database Recovery? john@jpm-cola.com
2014-10-23 14:26 ` jaime soler <jaime.soler@gmail.com>
2014-10-23 16:18 ` lst_hoe02@kwsoft.de
2014-10-23 19:33 ` Iñigo Salvat <inigo@isalvat.es>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox