Re: Fast backup/restore

On Tue, Oct 17, 2006 at 02:43:28PM -0400, Vivek Khera wrote:
>
> On Oct 17, 2006, at 2:35 PM, Steve Poe wrote:
>
> >Vivek,
> >
> >What methods of backup do you recommend for medium to large
> >databases? In our example, we have a 20GB database and it takes 2
> >hrs to load from a pg_dump file.
> >
>
> my largest db is about 60Gb with indexes. reloading the data (about
> 30Gb) takes 1 hour from compressed format pg_dump, and another two to
> reindex.
>
> for increasing reload size, bump your checkpoint_segments to
> something big, like 128 or 256 depending on how much disk space you
> can spare for it.

Other hints for restoring from pg_dump:

also increase wal_buffers
fsync=off
set maintenance_work_mem as high as you can (note that values over 1G
generally don't work).
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)