Re: Bug #789: Transaction Archival Logging -- Hot Backups - Mailing list pgsql-bugs

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
>