Re: Seeking information about backup/recovery

IMHO, while point-in-time recovery would be great to have, there are many
applications that benefit from having online backup and recovery without
needing log roll-forward. For example, Oracle contrasts "Full Database
Point-in-time Recovery", the feature mentioned by Bruce, with "Tablespace
Point in Time Recovery (TSPITR)" [1]. With postgresql and WAL, you can
implement the equivalent of TSPITR. By using filesystem snapshot
capabilities provided by certain storage appliances (NetApp Filer?) or by
operating system service in Linux (Logical Volume Manager, "LVM") to "...
make periodic saves of data files to another disk, a tape or another host
and also archive the WAL log files". Once you have the copy, you can put it
on disk, start a postgresql server (call it the backup server) on it, and
then dump and restore the data to a running server ("production server") on
the same or a different host.

This procedure allows you to recover a recent copy of any database, schema
or table, depending on the frequency with which you take snapshots. By using
rysnc to copy the snapshots over to a backup disk volume you can get
incremental backup capability, which shrinks the backup window to a few
minutes (and less than an hour even for databases with multiple gigabytes
worth of INSERTs). With such a small backup window, one can minimize data
loss in case of disk failure or corruption by increasing backup frequency.

There have been several discussions of this, including here
http://marc.theaimsgroup.com/?l=postgresql-admin&w=2&r=1&s=backup+routine&q=
b
here
http://marc.theaimsgroup.com/?l=postgresql-admin&w=2&r=1&s=LVM+snapshots&q=b
and here
http://marc.theaimsgroup.com/?l=postgresql-general&m=104610149723362&w=4

Cheers,
Murthy

[1]
http://storacle.princeton.edu:9001/oracle8-doc/server.805/a58396/ch13.htm)

>-----Original Message-----
>From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
>Sent: Thursday, September 04, 2003 14:16
>To: Mary Edie Meredith
>Cc: pgsql-admin(at)postgresql(dot)org; osdldbt-general
>Subject: Re: [ADMIN] Seeking information about backup/recovery
>
>
>
>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(at)osdl(dot)org>
>> Open Source Development Lab
>>
>>
>> ---------------------------(end of
>broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>
>--
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square,
>Pennsylvania 19073
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index
>scan if your
> joining column's datatypes do not match
>