Re: Fastest way to restore a database

On Friday 12 September 2008 14:32:07 Greg Smith wrote:
> On Fri, 12 Sep 2008, William Garrison wrote:
> > Is there a definitive list of things to do?
>
> That section of the documention is pretty good:
> http://www.postgresql.org/docs/current/static/populate.html
>
> The main thing it's missing is a discussion of how to cut down on disk
> commit overhead by either usinc async commit or turning fsync off. If
> you've got a good caching controller that may not be needed though.
>
> The other large chunk of information it doesn't really go into is what
> server tuning you could do to improve general performance, which obviously
> would then help with loading as well.
> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server goes over
> much of that.
>
> > * Turn off full_page_writes
> > Don’t write the WAL archives in a safe way. But we don’t need WAL
> > archives during a restore. Future versions of postgres will let you turn
> > off WAL archives entirely
>
> Ideally you'd be using COPY such that the table was just created or
> truncated before loading, which (if archive_mode is off) keeps them from
> being WAL logged, as described in 14.4.7. If you do that and vastly
> increase checkpoint_segments, full_page_writes has minimal impact.
>
> > * Increase the checkpoint_segments parameter (the default is 3 – so...
> > maybe 10?)
>
> 64-256 is the usual range you'll see people using for bulk loading.
>

Don't forget to bump up checkpoint_timeout along with that... actually, I
blogged a couple of times on this topic:

http://people.planetpostgresql.org/xzilla/index.php?/archives/133-Getting-faster-database-restores-on-postgresql-8.1.html
http://people.planetpostgresql.org/xzilla/index.php?/archives/223-Measuring-database-restore-times.html

A little old, but might be helpful.

--
Robert Treat
http://www.omniti.com
Database: Scalability: Consulting: