Re: Fastest way to restore a database

Thanks so much!

So... if I am using pg_dump and pg_restore with a compressed backup,
then it is using COPY, correct? And I think that would follow a CREATE
TABLE statement as mentioned in the first link... so no WAL files written?

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.
>
>> * Increase the maintenance_work_mem setting to 512MB
>
> I haven't really seen any real improvement setting that over 256MB.
> If you've got RAM to waste it doesn't really matter if you set it too
> high though.
>
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD