Thread: point-in-time recovery

point-in-time recovery

From
"Gaetano Mendola"
Date:
Hi,
is this features already present in 7.4Devel ?
If yes how is possible try it ?


Ciao
Gaetano



Re: point-in-time recovery

From
Bruce Momjian
Date:
Gaetano Mendola wrote:
> Hi,
> is this features already present in 7.4Devel ?
> If yes how is possible try it ?

It will be in 7.4, but that will out only in a few months --- it isn't
in CVS yet, but should be by May.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Re: point-in-time recovery

From
Rajesh Kumar Mallah
Date:

What is this PITR anyway ?

when it comes will i be able to

1. reduce by pg_dump times ?
2. can afford to run pg_dump less frequently ?
3. Will be able to rollback committed transactions of past 5 mins?

any pointers to discussions?




On Thursday 24 Apr 2003 6:47 am, Gaetano Mendola wrote:
> Hi,
> is this features already present in 7.4Devel ?
> If yes how is possible try it ?
>
>
> Ciao
> Gaetano
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.


Re: point-in-time recovery

From
Bruce Momjian
Date:
Rajesh Kumar Mallah wrote:
>
>
> What is this PITR anyway ?
>
> when it comes will i be able to
>
> 1. reduce by pg_dump times ?
> 2. can afford to run pg_dump less frequently ?
> 3. Will be able to rollback committed transactions of past 5 mins?

You can do a dump, then do continuous logging of all database changes
until the next backup.  If you crash in the middle of the day, you can
recover up to the point of the crash.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Re: point-in-time recovery

From
Rajesh Kumar Mallah
Date:

Thanks for responding , but i still have
more to discuss.

On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> Rajesh Kumar Mallah wrote:
> > What is this PITR anyway ?
> >
> > when it comes will i be able to
> >
> > 1. reduce by pg_dump times ?
> > 2. can afford to run pg_dump less frequently ?
> > 3. Will be able to rollback committed transactions of past 5 mins?
>
> You can do a dump,

Do u mean pg_dump here, or some special command
thats doing a quick snapshot dump?

 then do continuous logging of all database changes
is this logging happening automatically for i have to give some
special command to start logging?

> until the next backup.  If you crash in the middle of the day, you can
> recover up to the point of the crash.

say i do not crash but do a

psql> DELETE from  TABLE_NAME ;
DELETE 1021
psql> Ooops what did i do , damn my last backup is 6 hrs old  ??

does PITR help me here?


And Sorry if its too early to ask


Regds
Mallah.





--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.


Re: point-in-time recovery

From
Bruce Momjian
Date:
Rajesh Kumar Mallah wrote:
>
>
> Thanks for responding , but i still have
> more to discuss.
>
> On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> > Rajesh Kumar Mallah wrote:
> > > What is this PITR anyway ?
> > >
> > > when it comes will i be able to
> > >
> > > 1. reduce by pg_dump times ?
> > > 2. can afford to run pg_dump less frequently ?
> > > 3. Will be able to rollback committed transactions of past 5 mins?
> >
> > You can do a dump,
>
> Do u mean pg_dump here, or some special command
> thats doing a quick snapshot dump?

You do one pg_dump, the start PITR logging.

>
>  then do continuous logging of all database changes
> is this logging happening automatically for i have to give some
> special command to start logging?

Not sure yet --- we arn't done, but it will probably be a special
command.

>
> > until the next backup.  If you crash in the middle of the day, you can
> > recover up to the point of the crash.
>
> say i do not crash but do a
>
> psql> DELETE from  TABLE_NAME ;
> DELETE 1021
> psql> Ooops what did i do , damn my last backup is 6 hrs old  ??
>
> does PITR help me here?

Ideally, you will be able to restore to any point you wish, such as
before the DELETE.  However, I do not know if that control will be
implemented in 7.4, but if not, certainly 7.5.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Re: point-in-time recovery

From
Rajesh Kumar Mallah
Date:
On Friday 25 Apr 2003 8:17 pm, Bruce Momjian wrote:
> Rajesh Kumar Mallah wrote:
> > Thanks for responding , but i still have
> > more to discuss.
> >
> > On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> > > Rajesh Kumar Mallah wrote:
> > > > What is this PITR anyway ?
> > > >
> > > > when it comes will i be able to
> > > >
> > > > 1. reduce by pg_dump times ?
> > > > 2. can afford to run pg_dump less frequently ?
> > > > 3. Will be able to rollback committed transactions of past 5 mins?
> > >
> > > You can do a dump,
> >
> > Do u mean pg_dump here, or some special command
> > thats doing a quick snapshot dump?
>
> You do one pg_dump, the start PITR logging.

I dunno Abt the general case but we have seen
more instances of data loss by our own actions  (faulty SQL eg)
rather than HW's or postgresql's

Say for my live database.
Say i start my pg_dump at 5:00p and it finishes say by 5:30p
then i give the command for PITR logging at 5:31

Then i accidently delete at say 9pm

Then am i expected to :
1. restore the snapshot of 5:00 pm
2. ROLL FORWARD using PITR log till 8:59 or so
   using some special command ?

In that case what happens to the transactions from 5:00pm
to 5:30 pm which were not captured in the pg_dump nor in the
PITR log?

I have read WAL does transaction logging is PITR log
synonymous to WAL log or its different ?

Sorry i do not have RDBMS experience in any other than PostgreSQL
thats why not aware of PITR but i search the web and found this
feature is called tablespace PITR (TSPITR) in Oracle but unless
you have worked on it its difficult to understand.

Regds
Mallah.




>
> >  then do continuous logging of all database changes
> > is this logging happening automatically for i have to give some
> > special command to start logging?
>
> Not sure yet --- we arn't done, but it will probably be a special
> command.
>
> > > until the next backup.  If you crash in the middle of the day, you can
> > > recover up to the point of the crash.
> >
> > say i do not crash but do a
> >
> > psql> DELETE from  TABLE_NAME ;
> > DELETE 1021
> > psql> Ooops what did i do , damn my last backup is 6 hrs old  ??
> >
> > does PITR help me here?
>
> Ideally, you will be able to restore to any point you wish, such as
> before the DELETE.  However, I do not know if that control will be
> implemented in 7.4, but if not, certainly 7.5.

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.


Re: point-in-time recovery

From
Bruce Momjian
Date:
Rajesh Kumar Mallah wrote:
> On Friday 25 Apr 2003 8:17 pm, Bruce Momjian wrote:
> > Rajesh Kumar Mallah wrote:
> > > Thanks for responding , but i still have
> > > more to discuss.
> > >
> > > On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> > > > Rajesh Kumar Mallah wrote:
> > > > > What is this PITR anyway ?
> > > > >
> > > > > when it comes will i be able to
> > > > >
> > > > > 1. reduce by pg_dump times ?
> > > > > 2. can afford to run pg_dump less frequently ?
> > > > > 3. Will be able to rollback committed transactions of past 5 mins?
> > > >
> > > > You can do a dump,
> > >
> > > Do u mean pg_dump here, or some special command
> > > thats doing a quick snapshot dump?
> >
> > You do one pg_dump, the start PITR logging.
>
> I dunno Abt the general case but we have seen
> more instances of data loss by our own actions  (faulty SQL eg)
> rather than HW's or postgresql's
>
> Say for my live database.
> Say i start my pg_dump at 5:00p and it finishes say by 5:30p
> then i give the command for PITR logging at 5:31
>
> Then i accidently delete at say 9pm
>
> Then am i expected to :
> 1. restore the snapshot of 5:00 pm
> 2. ROLL FORWARD using PITR log till 8:59 or so
>    using some special command ?
>
> In that case what happens to the transactions from 5:00pm
> to 5:30 pm which were not captured in the pg_dump nor in the
> PITR log?

You have to start PITR logging probably before the pg_dump so there will
be some overlap, but the system will know how to handle it.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Re: point-in-time recovery

From
Rajesh Kumar Mallah
Date:
On Friday 25 Apr 2003 8:41 pm, Bruce Momjian wrote:
> Rajesh Kumar Mallah wrote:
> > On Friday 25 Apr 2003 8:17 pm, Bruce Momjian wrote:
> > > Rajesh Kumar Mallah wrote:
> > > > Thanks for responding , but i still have
> > > > more to discuss.
> > > >
> > > > On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> > > > > Rajesh Kumar Mallah wrote:
> > > > > > What is this PITR anyway ?
> > > > > >
> > > > > > when it comes will i be able to
> > > > > >
> > > > > > 1. reduce by pg_dump times ?
> > > > > > 2. can afford to run pg_dump less frequently ?
> > > > > > 3. Will be able to rollback committed transactions of past 5
> > > > > > mins?
> > > > >
> > > > > You can do a dump,
> > > >
> > > > Do u mean pg_dump here, or some special command
> > > > thats doing a quick snapshot dump?
> > >
> > > You do one pg_dump, the start PITR logging.
> >
> > I dunno Abt the general case but we have seen
> > more instances of data loss by our own actions  (faulty SQL eg)
> > rather than HW's or postgresql's
> >
> > Say for my live database.
> > Say i start my pg_dump at 5:00p and it finishes say by 5:30p
> > then i give the command for PITR logging at 5:31
> >
> > Then i accidently delete at say 9pm
> >
> > Then am i expected to :
> > 1. restore the snapshot of 5:00 pm
> > 2. ROLL FORWARD using PITR log till 8:59 or so
> >    using some special command ?
> >
> > In that case what happens to the transactions from 5:00pm
> > to 5:30 pm which were not captured in the pg_dump nor in the
> > PITR log?
>
> You have to start PITR logging probably before the pg_dump so there will
> be some overlap, but the system will know how to handle it.


Thanks for this discussion.
I hope to read more abt in in 7.4 Docs.

My congratulations and regds to the PostgreSQL
developers for making it what it is today !

Mallah.



--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.