Hi,
We often hear customers/users asking questions like - How much time
does it take for postgres to recover if it crashes now? How much time
does it take for a PITR to finish if it's started now with a specific
recovery target? When will the recovery of a postgres server end? It
will be nice if the postgres can "somehow" answer these questions. I
know this is easier said than done. At a bare minimum, the postgres
can scan the WAL from the last checkpoint till end of WAL to see how
many WAL records need to be replayed and count in "some" IO costs,
average redo/replay/apply times etc. and provide an estimate something
like "recovery, if started at this moment, will take approximately X
amount of time". To answer these questions, postgres needs to have
information about the average replay time of WAL records which depends
on the type of WAL record (replay of different WAL records take
different amount of time at different times; for instance, replay of a
WAL record with many FPIs or data blocks touched takes different time
based on the shared buffers hit and misses, disk IO etc.). The
postgres can capture and save average replay time of each WAL record
type over a period of time and use it for estimates which is of course
a costly thing for the postgres to do while it's actually recovering.
Or we can feed in some average disk IO, replay costs, postgres can
scan the WAL records and provide the estimates.
If postgres has a way to estimate recovery times, it can also trigger
checkpoints based on it to keep the RTO/recovery times under limits.
I know there are lots of unclear points for now but I would like to
start a discussion and hear more thoughts from the community. Please
feel free to provide your inputs.
Regards,
Bharath Rupireddy.