Re: Very Very Slow Database Restore

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(dot)mcclintock(at)antics(dot)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