Re: Seeking information about backup/recovery

Murthy Kambhampaty wrote:
> 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.

I assume you are contrasting _any_ point-in-time recovery to recover up
to the crash point, right?

Anyway, unfortunately, WAL doesn't contain enough information to recover
without having the file system files in some consistent state, even if
that state is old. In fact, the files have to be consistent as of the
last checkpoint.

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