Point in time recovery?

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