Bug #789: Transaction Archival Logging -- Hot Backups

I see in the pg_dump manual page:

pg_dump makes consistent backups even if the database is
being used concurrently. pg_dump does not block other
users accessing the database (readers or writers).

---------------------------------------------------------------------------

pgsql-bugs@postgresql.org wrote:

Jon Watte (postgres.org.nospam@mindcontrol.org) reports a bug
with a severity of 2 The lower the number the more severe it
is.

Short Description Transaction Archival Logging -- Hot Backups

Long Description I see no mention of transaction archival logging
in the documentation.

This means that, even though you support correct transaction
rollback semantics, to back up the database in a consistent
manner, I have to take it offline and backup all the files.

Either I'm missing something (and I did a documentation, FAQ
and Todo search) or it's not currently possibly to actually put
Postgres into a 24/7 production environment?

Sample Code

No file was uploaded with this report

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister
command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
  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

Again, thank you for your reply. I am copying the bugs list in the
hope that some ray of insight will hit.

I looked at this a little more, and it seems pg_dump does not actually
do what I need. 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.

Other databases (i e Oracle) solves this by retaining their transaction
journal for some predetermined time (at least as long as the interval
between data store backups). Typically, you will put this journal on
some physically separate storage with a physically separate controller
(maybe even on tape, or on a remote site). Then, when you lose your
data store, you can restore the data store from back-up, and then re-
play your archive log, and avoid losing any committed transactions. If
you lose your archive log store, the database is still intact, and you
should immediately failover to a new archive store and start a full
data store backup. If you lose both, then you HAVE to accept the fact
that you will lose previously committed transactions, but the likelihood
of this actually happening with the right physical set-up is very very
slim (as opposed to the likelihood of just one part going down, which
is almost inevitable).

For reference, this one lacking feature is preventing the company I work
at from using PostgreSQL, because we have operational requirements that
need this "fast path" recovery in the common case. Unfortunately, we'd
rather pay Oracle lots of money than lose time having to implement it in
the PostgreSQL code :-(

Cheers,

/ h+

We will have point-in-time recovery in 7.4. We are just releaseing
7.3beta now.

---------------------------------------------------------------------------

Jon Watte wrote:

Again, thank you for your reply. I am copying the bugs list in the
hope that some ray of insight will hit.

I looked at this a little more, and it seems pg_dump does not actually
do what I need. 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.

Other databases (i e Oracle) solves this by retaining their transaction
journal for some predetermined time (at least as long as the interval
between data store backups). Typically, you will put this journal on
some physically separate storage with a physically separate controller
(maybe even on tape, or on a remote site). Then, when you lose your
data store, you can restore the data store from back-up, and then re-
play your archive log, and avoid losing any committed transactions. If
you lose your archive log store, the database is still intact, and you
should immediately failover to a new archive store and start a full
data store backup. If you lose both, then you HAVE to accept the fact
that you will lose previously committed transactions, but the likelihood
of this actually happening with the right physical set-up is very very
slim (as opposed to the likelihood of just one part going down, which
is almost inevitable).

For reference, this one lacking feature is preventing the company I work
at from using PostgreSQL, because we have operational requirements that
need this "fast path" recovery in the common case. Unfortunately, we'd
rather pay Oracle lots of money than lose time having to implement it in
the PostgreSQL code :-(

Cheers,

/ h+

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Saturday, September 28, 2002 10:19 PM
To: postgres.org.nospam@mindcontrol.org; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Bug #789: Transaction Archival Logging -- Hot
Backups

I see in the pg_dump manual page:

pg_dump makes consistent backups even if the database is
being used concurrently. pg_dump does not block other
users accessing the database (readers or writers).

------------------------------------------------------------------
---------

pgsql-bugs@postgresql.org wrote:

Jon Watte (postgres.org.nospam@mindcontrol.org) reports a bug
with a severity of 2 The lower the number the more severe it
is.

Short Description Transaction Archival Logging -- Hot Backups

Long Description I see no mention of transaction archival logging
in the documentation.

This means that, even though you support correct transaction
rollback semantics, to back up the database in a consistent
manner, I have to take it offline and backup all the files.

Either I'm missing something (and I did a documentation, FAQ
and Todo search) or it's not currently possibly to actually put
Postgres into a 24/7 production environment?

Sample Code

No file was uploaded with this report

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister
command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
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

-- 
  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