Thread: Point in time recovery?
Hi! I work at a research institution (about 600 empoyees), and we are currently evaluating open source databases, particularly PostgreSQL for internal use..We currently have a multitude of commercial databases deployed (Oracle, MS SQL, Ingres, Interbase, etc.), but we would like to introduce and officially support an open source alternative to our researchers. In order to recommend and support PostgreSQL, we need to make sure that our data will be safe using PostgreSQL. I find the documentation inadequate with respect to database backup. If an error occur (hardware or software failure) we would be able to load the last backup from pg_dump. However, what about transactions that has occured after the last backup? Would we be able to roll forward to a particular point in time, so that the chances for data loss is minimal? I've heard that this feature is currently missing, but that it will be introduced in version 7.4 which should be out this year. My problem is that we cannot recommend this product unless this feature is present. If this feature won't be available until next release, is it possible to patch the current version or download a development version so that we could test that this feature really works? I really do not have much time, as we need to make a decision real soon, and without being 100% certain that PostgreSQL will meet our needs, we cannot recomment this product. I would very much appreciate any relevant information on this matter. Eivind.
There is no backpatch available --- a beta may be available in June. --------------------------------------------------------------------------- eivind.arnesen@netcom.no wrote: > Hi! > > I work at a research institution (about 600 empoyees), and > we are currently evaluating open source databases, particularly > PostgreSQL for internal use..We currently have a multitude > of commercial databases deployed (Oracle, MS SQL, Ingres, Interbase, > etc.), but we would like to introduce and officially > support an open source alternative to our researchers. > > In order to recommend and support PostgreSQL, we need to make sure > that our data will be safe using PostgreSQL. I find the documentation > inadequate with respect to database backup. > > If an error occur (hardware or software failure) we would be able > to load the last backup from pg_dump. However, what about transactions > that has occured after the last backup? Would we be able to roll > forward to a particular point in time, so that the chances for data > loss is minimal? I've heard that this feature is currently missing, > but that it will be introduced in version 7.4 which should be out this > year. > > My problem is that we cannot recommend this product unless this feature > is present. If this feature won't be available until next release, > is it possible to patch the current version or download a development > version so that we could test that this feature really works? > > I really do not have much time, as we need to make a decision real > soon, and without being 100% certain that PostgreSQL will meet our > needs, we cannot recomment this product. > > I would very much appreciate any relevant information on this matter. > > Eivind. > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- 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
<eivind.arnesen@netcom.no> writes: > If an error occur (hardware or software failure) we would be able > to load the last backup from pg_dump. However, what about transactions > that has occured after the last backup? Would we be able to roll > forward to a particular point in time, so that the chances for data > loss is minimal? I've heard that this feature is currently missing, > but that it will be introduced in version 7.4 which should be out this > year. This is the current plan, but it's not definite. Whether it makes it into 7.4 depends on whether it's done before the beta cutoff. > My problem is that we cannot recommend this product unless this feature > is present. If this feature won't be available until next release, > is it possible to patch the current version or download a development > version so that we could test that this feature really works? From what I've seen on the -hackers list, it's not that far along yet. > I really do not have much time, as we need to make a decision real > soon, and without being 100% certain that PostgreSQL will meet our > needs, we cannot recomment this product. It's definitely not 100% certain at this point. -Doug
On Sat, Mar 15, 2003 at 10:25:29AM +0000, eivind.arnesen@netcom.no wrote:
> In order to recommend and support PostgreSQL, we need to make sure
> that our data will be safe using PostgreSQL.
[. . .]
> to load the last backup from pg_dump. However, what about transactions
> that has occured after the last backup? Would we be able to roll
> forward to a particular point in time, so that the chances for data
> loss is minimal? I've heard that this feature is currently missing,
> but that it will be introduced in version 7.4 which should be out this
> year.
>
> My problem is that we cannot recommend this product unless this feature
> is present.
While point-in-time is not available, you mentioned that you are
already using an assortment of other products, including Oracle and
SQL Server.
You could use replication to do some of what you want, instead.
PostgreSQL has a number of possible replication approaches. All of
these are asynchronous, at the moment. Some are free, and some
require a commercial license.
You don't need a sophisticated machine to be a replication target:
you need enough disk, of course, but that's relatively cheap these
days. So you could set up a replicated system for most medium-sized
databases reasonably inexpensively -- certainly, for less than the
cost of an annual commercial Oracle license.
This answer is not perfect, and it may still require some additional
work on the part of a dba. But if your clients want a free database,
and really need PITR, then this could be an answer.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Mon, 2003-03-24 at 12:13, Andrew Sullivan wrote: > On Sat, Mar 15, 2003 at 10:25:29AM +0000, eivind.arnesen@netcom.no wrote: > > In order to recommend and support PostgreSQL, we need to make sure > > that our data will be safe using PostgreSQL. > > [. . .] > > > to load the last backup from pg_dump. However, what about transactions > > that has occured after the last backup? Would we be able to roll > > forward to a particular point in time, so that the chances for data > > loss is minimal? I've heard that this feature is currently missing, > > but that it will be introduced in version 7.4 which should be out this > > year. > > > > My problem is that we cannot recommend this product unless this feature > > is present. > <snip good suggestions on replication> ISTM that this thread misses the state of how resilient postgresql is to data corruption in the first place. Problems like power outages and server crashes (at either the os/kernel/application level) are almost certainly not going to cause data loss. I could see possible issues with hardware problems, but quality ram and raid can go a long way toward minimizing these risks. While I suppose it's not 100%, nothing ever is. Robert Treat
On Mon, Mar 24, 2003 at 04:06:46PM -0500, Robert Treat wrote:
> data corruption in the first place. Problems like power outages and
> server crashes (at either the os/kernel/application level) are almost
> certainly not going to cause data loss. I could see possible issues with
This is certainly true. We have never, not once, had a problem with
PostgreSQL losing production data. It is hard to break even if you
try (although I've managed a couple times in tests, with enough
ingenuity. It took more ingenuity than other things I've had to
break, mind you). But insurance is insurance, and telling managers,
clients, or regulatory bodies, "Don't worry, PostgreSQL never
breaks," is not going to fly.
One PITR-provided advantage that you cannot get with the replication
approach, by the way, is this one: "Junior DBA issued 'DELETE FROM
million_dollar_table;' at 05:00 on Saturday. I want to go back to
04:59." Your replicated database will have the same problem as the
main one.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110