I have found that the following steps have increased the speed of my restores:
1. dropdb <DBNAME>
2. createdb <DBANME>
3. Increase maintenance_work_mem as Tom mentioned. I do this at restore/runtime
4. Increase work_mem as Tom mentioned. I do this at restore/runtime
5. renice -20 -p <pid #> (on a Linux box) <= This step can be performed while the restore is running but you need root privilege
Tim
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, May 24, 2006 2:48 PM
To: Ruairi
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Restore of pg_dump taking a long time...
"Ruairi" <rcarroll@bluemetrix.com> writes:
> I'm currently restoring a fairly large DB from a pg_dump and it's taking
> about 12 hours to finish. The main part of this time is spent creating
> indexes. Is there anyway I can speed up the restore process, or do i just
> have to wait?
There's not much you can do to improve the performance of a restore
already in progress. Next time you might want to think about kicking up
maintenance_work_mem before you start it; that's about the only thing
you can really do to speed up index builds.
(Actually, if there are lots of indexes yet to be built, increasing
the setting in postgresql.conf and SIGHUP'ing the postmaster could
be worth doing, as that should affect the builds yet to be done.)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster