Re: [osdldbt-general] Re: Seeking information about - Mailing list pgsql-admin

On Thu, 2003-09-04 at 22:06, Justin Clift wrote:
> Hi Mary,
>
> Are you talking about something more "realtime" than pg_dumpall?

Yes, definitely.
>
> You may be wondering "why the heck haven't they added this before?"...
> just imagine this scenario... we *very rarely* encounter database
> failures that aren't hardware related, and pretty much any decent admin
> has a good backup schedule in place.

True.

I guess I'm trying to understand what is the "good backup schedule" and
procedure in the case of PostgreSQL.

Let's say I cannot afford to lose more than one day of updates.  It
appears that I need to backup nightly the entire database.  Whether you
use either pg_dumpall or you backup by simply coping the files,  this
approach does not scale for enterprise size databases.  For the copy
scheme the database has to be down, effecting availability.  The restore
for pg_dumpall would be longer than for the copy scheme since the
indexes have to be reconstructed.  An enterprise database system would
have lots equipment to avoid single points of failure, but they still
happen, human mistakes happen (oops I deleted the table!) , etc, etc, so
you still have to do the backups.

If the loss of a day's work is too costly, you will be faced with
backing up even more frequently.
>
> PITR is definitely a needed thing though, so that we can support those
> businesses where every bit of data can't afford to be lost after the
> COMMIT.  There are other solutions presently (replication, various
> hardware things, etc), but PITR is the best move forwards.

Point in time recovery will certainly solve the issue.  However, PITR is
really more than I was asking to get.  I was just asking for a way to
recover to the last commit (RTTLC).

In this scenario (I don't expect this to be big news to anyone, I'm just
explaining what I was hoping to find) there is one full backup made
occasionally.  The updates from that point are saved in an archived
log.  This is usually done with the database up.  In a failure
situation, you have to restore from the full backup and re-run the
updates from the archived logs.  You have to run all of them to get a
consistent database back.   The frequency of full backups is determined
by how long you are willing to wait to do a restore.

PITR is more precise in that you can run the archived logs up to the
point that you want (like before you deleted that table).

So recovering to a specific point in time is still _very desirable and
totally superior, I am just used to seeing RTTLC appear first as
features evolve in database development and was expecting to see it
called out separately in the TODO list.

My conclusion from the responses so far is that the community is
planning to deliver RTTLC as part of PITR.

BTW, thanks to all for your patient and kind responses.  You can't
imagine how _much it is appreciated.

>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Bruce Momjian wrote:
>
> > Right.  We need point-in-time-recovery.  Someone is working on it and we
> > hope to have it for 7.5.
> >
> > ---------------------------------------------------------------------------
> >
> > Mary Edie Meredith wrote:
> >
> >>Dear PostgreSQL admin'ers,
> >>
> >>
> >>Our group at OSDL have been  porting our DBT test kits to PostgreSQL.
> >>In getting up to speed on PostgreSQL, we have not found a way to recover
> >>from a serious database failure (disk corruption, disk/volume failure).
> >>
> >>The following scenario described in the 7.3 docs and the "PostgreSQL
> >>7.4devel Documentation" is exactly what we are looking for:
> >>
> >>
> >>"WAL offers the opportunity for a new method for database on-line backup
> >>and restore (BAR). To use this method, one would have to make periodic
> >>saves of data files to another disk, a tape or another host and also
> >>archive the WAL log files. The database file copy and the archived log
> >>files could be used to restore just as if one were restoring after a
> >>crash. Each time a new database file copy was made the old log files
> >>could be removed. Implementing this facility will require the logging of
> >>data file and index creation and deletion; it will also require
> >>development of a method for copying the data files (operating system
> >>copy commands are not suitable). "
> >>
> >>Since it states that WAL, "offers the opportunity" for what we need, we
> >>concluded this is _not supported at 7.3 and the 7.4 beta releases.
> >>
> >>Is this conclusion correct?
> >>
> >>Of course we would like to do even more - point in time recovery,
> >>incremental backup, but for now just the basics.
> >>
> >>Point in Time recovery is listed as "Urgent" on the TODO list,
> >>incremental backups are listed under "Admin", so it appears that those
> >>items are recognized as important.  What we cannot understand is why the
> >>basic backup/restore described above is not on the TODO list.
> >>
> >>Can anyone enlighten us?
> >>
> >>
> >>
> >>--
> >>Mary Edie Meredith <maryedie@osdl.org>
> >>Open Source Development Lab
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 7: don't forget to increase your free space map settings
> >>
> >
> >
>
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> osdldbt-general mailing list
> osdldbt-general@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/osdldbt-general
--
Mary Edie Meredith <maryedie@osdl.org>
Open Source Development Lab