Re: Postgres recovery time

[David Boreham <david_list@xxxxxxxxxxx>]

Piccarello, James (James) wrote:

Does anyone know what factors affect the recovery time of postgres if it does not shutdown cleanly? With the same size database I've seen  times from a few seconds to a few minutes. The longest time was 33 minutes. The 33 minutes was after a complete system crash and reboot so there are a lot of other things going on as well. 125 seconds was the longest time I could reproduce by just doing a kill -9 on postmaster.

Is it the size of the transaction log? The dead space in files?

I don't know much about postgresql, but typically WAL mechanisms
will exhibit recovery times that are bounded by the amount of log record
data written since the last checkpoint. The 'worst' case will be where
you have continuous writes to the database and a long checkpoint
interval. In that case many log records must be replayed into the
data files upon recovery. The 'best' case would be zero write transactions
since the last checkpoint. In that case recovery would be swift since
there are no live records to recover. In your tests you are probably
exercising this 'best' or near best case.