Re: Ways to speed up dump&reload

> Hello!
>
> Finally the time has come for us to upgrade our PostgreSQL
> 7.4.5-servers to 8.0.1 - and though I'm very much looking
> forward to some of the new features, the dump&reload process
> is worrying me a bit this time. I've got one cluster in
> particular which is roughly 9GB in size and features some
> dreadfully large indices of the GiST-type for Tsearch2.
>
> I have already scheduled a nightshift for this upgrade, but
> I'm not all too certain that I'll be up and running again in
> the morning, so I wondered if there might be some
> preparations that would allow for some speed-up during the
> reload process? What kind of tuning could be done in
> postgresql.conf in respect to just this particular workload
> (COPY and finally the CREATE INDEX stuff) with no other
> concurrent access going on?

If you'll be around to babysit the system during the reload, turn off
fsync during the load.
If you have the RAM, consider temporarily moving the WAL logs to a
ramdrive.
Increase sort_mem and maintenance_sort_mem, since you will only have a
single connection.
If you're on hyperthreading CPUs, disable hyperthreading. Since you load
in a single connectino, only one CPU will be used.

Be very careful to remember to turn this back on after the load!

Not sure if there is something specific you can do for tsearch, but this
should help with the general stuff

//Magnus