Re: Recovery will take 10 hours

Brendan Duddridge <brendan(at)clickspace(dot)com> writes:
> We had a database issue today that caused us to have to restore to
> our most recent backup. We are using PITR so we have 3120 WAL files
> that need to be applied to the database.
> After 45 minutes, it has restored only 230 WAL files. At this rate,
> it's going to take about 10 hours to restore our database.
> Most of the time, the server is not using very much CPU time or I/O
> time. So I'm wondering what can be done to speed up the process?

That seems a bit odd --- should be eating one or the other, one would
think. Try strace'ing the recovery process to see what it's doing.

> If there were something we could do to speed up the process, would it
> be possible to kill the postgres process, tweak some parameter
> somewhere and then start it up again? Or would we have to restore our
> base backup again and start over?

You could start it up again, but it'd want to read through all the WAL
it's already looked at, so I'd not recommend this until/unless you're
pretty sure you've fixed the performance issue. Right at the moment,
I think this is a golden opportunity to study the performance of WAL
recovery --- it's not something we've tried to optimize particularly.

regards, tom lane