Subject: Re: Recovery will take 10 hours
From: Simon Riggs <simon@2ndquadrant.com>
To: Brendan Duddridge <brendan@clickspace.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
In-Reply-To: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com>
References: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com>
Content-Type: text/plain
Date: Sun, 23 Apr 2006 17:06:07 +0100
Message-Id: <1145808368.3112.222.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2006-04-20 at 13:29 -0600, Brendan Duddridge wrote:

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

How often are you taking base backups?

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

You can improve the performance of a recovery by making your restore
command overlap retrieval of files. The recovery process waits while the
restore command returns a file, so by doing an asynchronous lookahead of
one file you can be gunzipping the next file while the current one is
being processed.

I'll either document this better, or build an overlap into the restore
command processing itself, so the script doesn't need to do this.

-- 
  Simon Riggs
  EnterpriseDB          http://www.enterprisedb.com/