Thread: Hot Backup
Hello to all the Doers of Postgres!!! Last time I went through forums, people spoke highly about 7.3 and its capability to do hot backups. My problem is if thedatabase goes down and I lose my main data store, then I will lose all transactions back to the time I did the pg_dump. Other databases (i e Oracle) solves this by retaining their archive logs in some physically separate storage. So, when youlose your data, you can restore the data from back-up, and then apply your archive log, and avoid losing any committedtransactions. Postgresql has been lacking this all along. I've installed postgres 7.3b2 and still don't see any archive's flushed to anyother place. Please let me know how is hot backup procedure implemented in current 7.3 beta(2) release. Thanks.
"Sandeep Chadha" <sandeep@newnetco.com> writes: > Postgresql has been lacking this all along. I've installed postgres > 7.3b2 and still don't see any archive's flushed to any other > place. Please let me know how is hot backup procedure implemented in > current 7.3 beta(2) release. AFAIK no such hot backup feature has been implemented for 7.3 -- you appear to have been misinformed. That said, I agree that would be a good feature to have. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Hmmm, Then are there any new enhancements as far as backups are concerned between current 7.2.x to 7.3.x. Like can we do a tar when database is up and running or another feature. Thanks a bunch in advance. -----Original Message----- From: Neil Conway [mailto:neilc@samurai.com] Sent: Monday, October 07, 2002 1:48 PM To: Sandeep Chadha Cc: Tom Lane; pgsql-hackers@postgresql.org; pgsql-general Subject: Re: [HACKERS] Hot Backup "Sandeep Chadha" <sandeep@newnetco.com> writes: > Postgresql has been lacking this all along. I've installed postgres > 7.3b2 and still don't see any archive's flushed to any other > place. Please let me know how is hot backup procedure implemented in > current 7.3 beta(2) release. AFAIK no such hot backup feature has been implemented for 7.3 -- you appear to have been misinformed. That said, I agree that would be a good feature to have. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
On 7 Oct 2002 at 13:48, Neil Conway wrote: > "Sandeep Chadha" <sandeep@newnetco.com> writes: > > Postgresql has been lacking this all along. I've installed postgres > > 7.3b2 and still don't see any archive's flushed to any other > > place. Please let me know how is hot backup procedure implemented in > > current 7.3 beta(2) release. > AFAIK no such hot backup feature has been implemented for 7.3 -- you > appear to have been misinformed. Is replication an answer to hot backup? Bye Shridhar -- ink, n.: A villainous compound of tannogallate of iron, gum-arabic, and water, chiefly used to facilitate the infection of idiocy and promote intellectual crime. -- H.L. Mencken
Shridhar Daithankar wrote: > On 7 Oct 2002 at 13:48, Neil Conway wrote: > > > "Sandeep Chadha" <sandeep@newnetco.com> writes: > > > Postgresql has been lacking this all along. I've installed postgres > > > 7.3b2 and still don't see any archive's flushed to any other > > > place. Please let me know how is hot backup procedure implemented in > > > current 7.3 beta(2) release. > > AFAIK no such hot backup feature has been implemented for 7.3 -- you > > appear to have been misinformed. > > Is replication an answer to hot backup? We already allow hot backups using pg_dump. If you mean point-in-time recovery, we have a patch for that ready for 7.4. -- 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
Hi Sandeep. What you were calling Hot Backup is really called Point in Time Recovery (PITR). Hot Backup means making a complete backup of the database while it is running, something Postgresql has supported for a very long time. On Mon, 7 Oct 2002, Sandeep Chadha wrote: > Hello to all the Doers of Postgres!!! > > Last time I went through forums, people spoke highly about 7.3 and its > capability to do hot backups. My problem is if the database goes down > and I lose my main data store, then I will lose all transactions back > to the time I did the pg_dump. Let's make it clear that this kind of failure is EXTREMELY rare on real database servers since they almost ALL run their data sets on RAID arrays. While it is possible to lost >1 drive at the same time and all your database, it is probably more likely to have a bad memory chip corrupt your data silently, or a bad query delete data it shouldn't. That said, there IS work ongoing to provide this facility for Postgresql, but I would much rather have work done on making large complex queries run faster, or fix the little issues with foreign keys cause deadlocks. > Other databases (i e Oracle) solves this by retaining their archive > logs in some physically separate storage. So, when you lose your data, > you can restore the data from back-up, and then apply your archive log, > and avoid losing any committed transactions. > > > Postgresql has been lacking this all along. I've installed postgres > 7.3b2 and still don't see any archive's flushed to any other place. > Please let me know how is hot backup procedure implemented in current > 7.3 beta(2) release. Again, you'll get better response to your questions if you call it "point in time recovery" or pitr. Hot backup is the wrong word, and something Postgresql DOES have. It also supports WALs, which stands for Write ahead logs. These files store what the database is about to do before it does it. Should the database crash with transactions pending, the server will come back up and process the pending transactions that are in the WAL files, ensuring the integrity of your database. Point in Time recovery is very nice, but it's the last step in many to ensure a stable, coherent database, and will probably be in 7.4 or somewhere around there. If you're running in a RAID array, then the loss of your datastore should be a very remote possibility.
The world rejoiced as andrew@libertyrms.info (Andrew Sullivan) wrote: > Having undertaken the exercise, I really can say that it is a little > strange to think about what would happen to data I am in charge of in > case a fairly large US centre were completely blown off the map. But > with a little careful planning, you actually _can_ think about that, > and provide strong assurances that things won't get lost. But it > doesn't pay to call such questions "silly", because they are > questions that people will demand answers to before they entrust you > with their millions of dollars of data. I was associated with one data center that has the whole "barbed-wire-fences, 40-foot-underground-bunker, retina-scanning" thing; they apparently /did/ do analysis based on the site being a potential target for nuclear attack. Realistically, two scenarios are much more realistic: a) The site resides in a significant tornado zone where towns occasionally get scraped off the map; b) The site isn't far from a small but busy airport, and they did consciously consider the possibility of aircraft crashinginto the building. Presumably by accident, not by design; the company owns quite a number of jet aircraft, sothat vulnerabilities involving misuse of aircraft would rapidly "fly" to mind... (Painfully and vastly moreso since9/11, of course :-(.) When doing risk analysis, it is certainly necessary to consider these sorts of (admittedly paranoid) scenarios. It's a bit fun, in a way; you get to look for some pretty odd-ball situations; the "server room being overrun by Mongol Hordes." That particular one isn't too likely, of course :-). -- (reverse (concatenate 'string "moc.enworbbc@" "sirhc")) http://www.ntlug.org/~cbbrowne/advocacy.html "I've discovered that P=NP, but the proof is too long to fit within the confines of this signature..."