Re: Issues Outstanding for Point In Time Recovery (PITR)

J. R. Nield wrote:
> ?5 - Individual file consistent recovery
>
> ?5.1 - Problem:
>
> If a file detects corruption, and we restore it from backup, how do
> we know what archived files we need for recovery?
>
> Should file corruption (partial write, bad disk block, etc.) outside
> the system catalog cause us to abort the system, or should we just
> take the relation or database off-line?
>
> Given a backup file, how do we determine the point in the log
> where we should start recovery for the file? What is the highest LSN
> we can use that will fully recover the file?
>
> ?5.2 - Proposal:
>
> Put a file header on each file, and update that header to the last
> checkpoint LSN at least once every 'file_lsn_time_slack' minutes, or
> at least once every dbsize/'file_lsn_log_slack' megabytes of log
> written, where dbsize is the estimated size of the database. Have
> these values be settable from the config file. These updates would be
> distributed throughout the hour, or interspersed between regular
> amounts of log generation.
>
> If we have a database backup program or command, it can update the
> header on the file before backup to the greatest value it can assure
> to be safe.

I know there was discussion about this. The issue was when you are
doing the backup, how do you handle changes in the file that happen
during the backup? I think there was some idea of remembering the WAL
pointer location at the start of the tar backup, and somehow playing
forward from that point. Now, the trick is to know how much of the WAL
is duplicated in the backup, and how much needs to be applied to roll
forward.

Because we have a non-overwriting storage manager, I think one idea was
to just replay everything, knowing that some of it may be unnecessary.
However, VACUUM FULL would complicate that because it does overwrite
tuples and you may get into trouble playing all of that back.

I am sure someone has analyzed this better than me.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026