Обсуждение: point-in-time recovery
Hi, is this features already present in 7.4Devel ? If yes how is possible try it ? Ciao Gaetano
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
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.
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
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.
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
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.
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
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.