pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PITR recovery
10+ messages / 4 participants
[nested] [flat]

* PITR recovery
@ 2007-01-05 18:59  Ben K. <bkim@coe.tamu.edu>
  0 siblings, 2 replies; 10+ messages in thread

From: Ben K. @ 2007-01-05 18:59 UTC (permalink / raw)
  To: pgsql-admin


Maybe this is answered somewhere or maybe self-evident, but I just wanted 
to make sure. I want to know if it's possible to do PITR between different 
platforms. I can try and learn, but if anyone knows, I'd appreciate it.


1. file formats

What is the chance that the file format of files under "data" is platform 
independent? I.e. would it be possible to restore the file system backup 
and use the PITR method from a Solaris/SPARC main to a Linux backup, using 
their respective native file system? What are the minimal conditions to be 
met?

2. sql dump and PITR

Is it possible to use the PITR method with SQL dump? (pg_start_backup -> 
sql dump -> pg_stop_backup) I guess not, but just want to make sure.




Thanks.



Ben K.
Developer
http://benix.tamu.edu



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR recovery
@ 2007-01-05 20:16  Bruno Wolff III <bruno@wolff.to>
  parent: Ben K. <bkim@coe.tamu.edu>
  1 sibling, 1 reply; 10+ messages in thread

From: Bruno Wolff III @ 2007-01-05 20:16 UTC (permalink / raw)
  To: Ben K. <bkim@coe.tamu.edu>; +Cc: pgsql-admin

On Fri, Jan 05, 2007 at 12:59:51 -0600,
  "Ben K." <bkim@coe.tamu.edu> wrote:
> 
> Maybe this is answered somewhere or maybe self-evident, but I just wanted 
> to make sure. I want to know if it's possible to do PITR between different 
> platforms. I can try and learn, but if anyone knows, I'd appreciate it.
> 
> 
> 1. file formats
> 
> What is the chance that the file format of files under "data" is platform 
> independent? I.e. would it be possible to restore the file system backup 
> and use the PITR method from a Solaris/SPARC main to a Linux backup, using 
> their respective native file system? What are the minimal conditions to be 
> met?

No the data is not only dependent on the platform, but also on the configure
options used for the build.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR recovery
@ 2007-01-06 15:37  Simon Riggs <simon@2ndquadrant.com>
  parent: Ben K. <bkim@coe.tamu.edu>
  1 sibling, 1 reply; 10+ messages in thread

From: Simon Riggs @ 2007-01-06 15:37 UTC (permalink / raw)
  To: Ben K. <bkim@coe.tamu.edu>; +Cc: pgsql-admin

On Fri, 2007-01-05 at 12:59 -0600, Ben K. wrote:
> 2. sql dump and PITR
> 
> Is it possible to use the PITR method with SQL dump? (pg_start_backup -> 
> sql dump -> pg_stop_backup) I guess not, but just want to make sure.
> 

No, because there is no reason or benefit.

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR recovery
@ 2007-01-08 15:41  Ben K. <bkim@coe.tamu.edu>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Ben K. @ 2007-01-08 15:41 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-admin

> On Fri, 2007-01-05 at 12:59 -0600, Ben K. wrote:
>> 2. sql dump and PITR
>> Is it possible to use the PITR method with SQL dump? (pg_start_backup ->
>> sql dump -> pg_stop_backup) I guess not, but just want to make sure.
>
> No, because there is no reason or benefit.

Thanks. I guess the PITR plays on a different ground. This is just a 
superficial question, but would it be possible to separate sql and disk 
operations? Eventually, it would be nice if we can have something like 
these as example. (I don't have any experience with this product.)

http://www.red-gate.com/products/SQL_Log_Rescue/

We sometimes have relatively minor but still painful problems (someone or 
some scripts causes undesirable changes) and if we can go from the sql 
dump (backup) and stop at a certain point, it would be really nice. It'd 
also give us more granuality in controlling backup or test servers.




Regards,

Ben K.
Developer
http://benix.tamu.edu



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR recovery
@ 2007-01-08 16:28  Simon Riggs <simon@2ndquadrant.com>
  parent: Ben K. <bkim@coe.tamu.edu>
  0 siblings, 1 reply; 10+ messages in thread

From: Simon Riggs @ 2007-01-08 16:28 UTC (permalink / raw)
  To: Ben K. <bkim@coe.tamu.edu>; +Cc: pgsql-admin

On Mon, 2007-01-08 at 09:41 -0600, Ben K. wrote:
> > On Fri, 2007-01-05 at 12:59 -0600, Ben K. wrote:
> >> 2. sql dump and PITR
> >> Is it possible to use the PITR method with SQL dump? (pg_start_backup ->
> >> sql dump -> pg_stop_backup) I guess not, but just want to make sure.
> >
> > No, because there is no reason or benefit.
> 
> Thanks. I guess the PITR plays on a different ground. This is just a 
> superficial question, but would it be possible to separate sql and disk 
> operations? Eventually, it would be nice if we can have something like 
> these as example. (I don't have any experience with this product.)
> 
> http://www.red-gate.com/products/SQL_Log_Rescue/
> 
> We sometimes have relatively minor but still painful problems (someone or 
> some scripts causes undesirable changes) and if we can go from the sql 
> dump (backup) and stop at a certain point, it would be really nice. It'd 
> also give us more granuality in controlling backup or test servers.

There is a log analysis tool on pgfoundry that does something similar.

You can already stop recovery at a certain point. So there's nothing to
stop you doing a recovery on a development machine up to a certain
point, then dumping the deleted data using pg_dump and re-loading it
into the live server. Then erasing the dev recovered database.

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR recovery
@ 2007-01-09 13:48  Ben K. <bkim@coe.tamu.edu>
  parent: Bruno Wolff III <bruno@wolff.to>
  0 siblings, 1 reply; 10+ messages in thread

From: Ben K. @ 2007-01-09 13:48 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-admin

On Fri, 5 Jan 2007, Bruno Wolff III wrote:

>> I.e. would it be possible to restore the file system backup
>> and use the PITR method from a Solaris/SPARC main to a Linux backup, using
>> their respective native file system? What are the minimal conditions to be
>> met?
>
> No the data is not only dependent on the platform, but also on the configure
> options used for the build.


Thanks.


Ben K.
Developer
http://benix.tamu.edu



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR recovery
@ 2007-01-09 14:37  Ben K. <bkim@coe.tamu.edu>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Ben K. @ 2007-01-09 14:37 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Simon Riggs <simon@2ndquadrant.com>

On Mon, 8 Jan 2007, Simon Riggs wrote:

> There is a log analysis tool on pgfoundry that does something similar.

> You can already stop recovery at a certain point. So there's nothing to
> stop you doing a recovery on a development machine up to a certain
> point, then dumping the deleted data using pg_dump and re-loading it
> into the live server. Then erasing the dev recovered database.

What I looked for was something like "undo" to a state which includes some 
changes from the last backup.

What is the best practice in the following case? I'd appreciate to know 
how this kind of situation is handled in general.

- We keep daily backups.
- In the middle of the day we find there are problems in some of the 
recent delete or update operations
- But we want to keep hours of other insert, delete or updates that preceded the problematic 
delete/update

We can do hourly backup of the production server to minimize the recovery 
efforts, but it seems costly considering this situation is not frequent.

Since the logs contains what's been done, it appears it should be possible 
to go back in time - as PITR does - by relying only on sql dump and the 
statements in the log, with a controlled scope.

I guess it can possibly be done by replacing the basic operations like 
delete so that it inserts the deleted row into another database before 
deleting this row, etc.. Conceptually it seems simple though in 
generalizing it I guess there'll be problems like scalability and speed.



Thanks.


Ben K.
Developer
http://benix.tamu.edu



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR recovery
@ 2007-01-09 16:30  Simon Riggs <simon@2ndquadrant.com>
  parent: Ben K. <bkim@coe.tamu.edu>
  0 siblings, 1 reply; 10+ messages in thread

From: Simon Riggs @ 2007-01-09 16:30 UTC (permalink / raw)
  To: Ben K. <bkim@coe.tamu.edu>; +Cc: pgsql-admin

On Tue, 2007-01-09 at 08:37 -0600, Ben K. wrote:
> On Mon, 8 Jan 2007, Simon Riggs wrote:
> 
> > There is a log analysis tool on pgfoundry that does something similar.
> 
> > You can already stop recovery at a certain point. So there's nothing to
> > stop you doing a recovery on a development machine up to a certain
> > point, then dumping the deleted data using pg_dump and re-loading it
> > into the live server. Then erasing the dev recovered database.
> 
> What I looked for was something like "undo" to a state which includes some 
> changes from the last backup.
> 
> What is the best practice in the following case? I'd appreciate to know 
> how this kind of situation is handled in general.
> 
> - We keep daily backups.
> - In the middle of the day we find there are problems in some of the 
> recent delete or update operations
> - But we want to keep hours of other insert, delete or updates that preceded the problematic 
> delete/update
> 
> We can do hourly backup of the production server to minimize the recovery 
> efforts, but it seems costly considering this situation is not frequent.
> 
> Since the logs contains what's been done, it appears it should be possible 
> to go back in time - as PITR does - by relying only on sql dump and the 
> statements in the log, with a controlled scope.

PITR doesn't go back in time. It only supports roll-forward from a prior
base backup using the logs, stopping at a predefined time/xid/endoflogs.
Going backwards using the logs is mostly impossible because the log
records don't hold enough info to un-erase things.

It is theoretically possible to enhance the server to be able to rewind
the data in a table by uncommitting transactional changes, but again you
can't put back data that has been emoved by VACUUM. That feature would
be similar to flashback, but its more complex then it sounds at first
hearing. I wouldn't hold your breath while waiting for that.

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR recovery
@ 2007-01-09 16:46  Ben K. <bkim@coe.tamu.edu>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Ben K. @ 2007-01-09 16:46 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-admin

> PITR doesn't go back in time. It only supports roll-forward from a prior
> base backup using the logs, stopping at a predefined time/xid/endoflogs.
> Going backwards using the logs is mostly impossible because the log
> records don't hold enough info to un-erase things.
>
> It is theoretically possible to enhance the server to be able to rewind
> the data in a table by uncommitting transactional changes, but again you
> can't put back data that has been emoved by VACUUM. That feature would
> be similar to flashback, but its more complex then it sounds at first
> hearing. I wouldn't hold your breath while waiting for that.

It was not that it's essential. Thanks for sharing the insight.


Regards,

Ben K.
Developer
http://benix.tamu.edu



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR recovery
@ 2007-01-09 17:49  Naomi Walker <nwalke@eldocomp.com>
  parent: Ben K. <bkim@coe.tamu.edu>
  0 siblings, 0 replies; 10+ messages in thread

From: Naomi Walker @ 2007-01-09 17:49 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

Speaking of PITR, it would be great if I could perform a PITR for a
particular database in an instance.
My options otherwise would be to install the instance elsewhere and
extract, or, create an instance for
each database (not reasonable, since we have dozens)....

We backup each database separately each even with a pg_dump.  Is there a
way to segregate WAL's?

Naomi



Ben K. wrote:
> On Fri, 5 Jan 2007, Bruno Wolff III wrote:
>
>>> I.e. would it be possible to restore the file system backup
>>> and use the PITR method from a Solaris/SPARC main to a Linux backup,
>>> using
>>> their respective native file system? What are the minimal conditions
>>> to be
>>> met?
>>
>> No the data is not only dependent on the platform, but also on the
>> configure
>> options used for the build.
>
>
---------------------------------------------------------------------------- 
Naomi Walker                          Chief Information Officer 
Mphasis Healthcare Solutions          nwalker@mhs.mphasis.com 
  ---An EDS Company                   602-604-3100 
---------------------------------------------------------------------------- 
A positive attitude may not solve all your problems, but it will annoy 
enough people to make it worth the effort. --Herm Albright (1876 - 1944)
----------------------------------------------------------------------------

-- CONFIDENTIALITY NOTICE --

Information transmitted by thisĀ e-mail is proprietary to MphasiS and/or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that isĀ privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this e-mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at mailmaster@mphasis.com and delete this mail from your records.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2007-01-09 17:49 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-05 18:59 PITR recovery Ben K. <bkim@coe.tamu.edu>
2007-01-05 20:16 ` Bruno Wolff III <bruno@wolff.to>
2007-01-09 13:48   ` Ben K. <bkim@coe.tamu.edu>
2007-01-09 17:49     ` Naomi Walker <nwalke@eldocomp.com>
2007-01-06 15:37 ` Simon Riggs <simon@2ndquadrant.com>
2007-01-08 15:41   ` Ben K. <bkim@coe.tamu.edu>
2007-01-08 16:28     ` Simon Riggs <simon@2ndquadrant.com>
2007-01-09 14:37       ` Ben K. <bkim@coe.tamu.edu>
2007-01-09 16:30         ` Simon Riggs <simon@2ndquadrant.com>
2007-01-09 16:46           ` Ben K. <bkim@coe.tamu.edu>

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