Re: Restore of pg_dump taking a long time...

I would typically drop the indicies before taking the dump, and recreate
in the new instance.  I know the loads go alot faster that way.  Perhaps
the index creation would be the same.

Tom Lane wrote:
> "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


--
----------------------------------------------------------------------------
Naomi Walker                          Chief Information Officer
Eldorado Computing, Inc.              nwalker@eldocomp.com 602-604-3100
----------------------------------------------------------------------------
What lies behind us and what lies before us are tiny matters compared to
what lies within us.     - William Morrow
----------------------------------------------------------------------------

-- CONFIDENTIALITY NOTICE --

Information transmitted by this e-mail is proprietary to MphasiS and/or its Customers and is intended for use only by
theindividual or entity to which it is addressed, and may contain information that is privileged, confidential or
exemptfrom disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been
forwardedto you without proper authority, you are notified that any use or dissemination of this information in any
manneris strictly prohibited. In such cases, please notify us immediately at mailmaster@mphasis.com and delete this
mailfrom your records.