Postgres Pro
Downloads
Home   >   mailing lists

Thread: Online recovery of Tablespace

Online recovery of Tablespace

From
Sameer Kumar
Date:
Hi,

I am designing backup strategy for a PostgreSQL database (v9.3). I have a scenario for recovery of tablespaces:

1. Backup of whole database (including individual tablespaces which are stored on different disks) has been taken at 11AM

2. My disk which stores tablespace- tblspc1 crashed at 2:00PM

3. Can I restore the backup of 11AM (only for one tablespace) and then recover that tablespace to 2:00PM state?


Is this possible? I have attached the steps I tried (I believe logically my steps are wrong, since I am using recovery.conf but I am not replacing data directory).

But is there any way to specify in recovery.conf or otherwise that I would allow me to do recovery of transactions of a particular tablespace? A custom solution which occurs to me is using pg_xlogdump contrib. Has anyone tried something similar?

Best Regards,

Sameer Kumar | Database Consultant

ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: +65 8110 0350  T: +65 6438 3504 | www.ashnik.com

icons

 

Email patch

 

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).

Attachment

Re: Online recovery of Tablespace

From
Magnus Hagander
Date:
On Thu, May 22, 2014 at 2:41 AM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
Hi,

I am designing backup strategy for a PostgreSQL database (v9.3). I have a scenario for recovery of tablespaces:

1. Backup of whole database (including individual tablespaces which are stored on different disks) has been taken at 11AM

2. My disk which stores tablespace- tblspc1 crashed at 2:00PM

3. Can I restore the backup of 11AM (only for one tablespace) and then recover that tablespace to 2:00PM state?


Is this possible? I have attached the steps I tried (I believe logically my steps are wrong, since I am using recovery.conf but I am not replacing data directory).

But is there any way to specify in recovery.conf or otherwise that I would allow me to do recovery of transactions of a particular tablespace? A custom solution which occurs to me is using pg_xlogdump contrib. Has anyone tried something similar?


We do not support recovering just one tablespace. You have to recover the whole database.

(I see that you are using EnterpriseDB's propietary version based on the paths, but I don't believe they have made any such extensions either) 


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/