From: Rajesh Kumar Mallah <mallah@trade-india.com>
Organization: Infocom Network Limited
To: Bruce Momjian <pgman@candle.pha.pa.us>
Subject: Re: point-in-time recovery
Date: Fri, 25 Apr 2003 20:39:31 +0530
User-Agent: KMail/1.5
Cc: pgsql-admin@postgresql.org
References: <200304251447.h3PEluQ11745@candle.pha.pa.us>
In-Reply-To: <200304251447.h3PEluQ11745@candle.pha.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200304252039.31100.mallah@trade-india.com>

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.