Re: Very Very Slow Database Restore - Mailing list pgsql-admin

From Joseph McClintock
Subject Re: Very Very Slow Database Restore
Date
Msg-id 1166723003.4208.6.camel@mcclintock.anticsoffline.com
Whole thread Raw
In response to Re: Very Very Slow Database Restore  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Very Very Slow Database Restore
List pgsql-admin
Thanks for the feed back.  Doubling the work_men from 10240 to 20480
seemed to make the process faster but then I ran out of disk space.
Shared buffers are at 2000 which I can try increasing.

I now need to move the database off the system partition to my working
partition which has more space.  I hope don't have to re-install
Postges, that would be a real bummer.

--Joe

On Thu, 2006-12-21 at 00:47 -0500, Tom Lane wrote:
> Joseph McClintock <joe.mcclintock@antics.com> writes:
> > Now our database has grown and the pg_dump give me a 2.1 GB file which
> > is taking 12 hours or more to restore, Yickes! I've tried compressing
> > the dump file:
>
> Compressing the dump file will make the restore slower, most likely,
> because of the extra CPU effort to decompress.  It certainly won't
> create any great savings.
>
> > The database configuration is pretty much out of the box.
>
> You probably want to increase maintenance_work_mem (to speed index
> creation) and checkpoint_segments (general rule for any update-intensive
> situation) ... and for 8.0 you'd maybe need larger shared_buffers etc.
> The postgresql techdocs website has lots of general advice about
> parameter settings --- the only thing specific to restore is you
> want high maintenance_work_mem.
>
>             regards, tom lane



pgsql-admin by date:

Previous
From: Jeff Frost
Date:
Subject: Re: Suggestions needed about how to dump/restore a database
Next
From: Ivo Rossacher
Date:
Subject: Re: Character codification