Skip site navigation (1) Skip section navigation (2)

Re: Very Very Slow Database Restore

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: joe(dot)mcclintock(at)antics(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Very Very Slow Database Restore
Date: 2006-12-21 05:47:44
Message-ID: 14149.1166680064@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

Responses

pgsql-admin by date

Next:From: Sandy SpenceDate: 2006-12-21 10:26:29
Subject: Trigger trouble
Previous:From: Bruno Wolff IIIDate: 2006-12-20 21:10:49
Subject: Re: Upgrading from 7.4 to 8.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group