Re: Ways to speed up dump&reload

Quoth Markus(dot)Wollny(at)computec(dot)de ("Markus Wollny"):
> 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?

You might consider using Slony-I to minimize the downtime.

I haven't done an upgrade using, but know that others have, and I've
done the same using eRServer...

The idea is that you set up an 8.0.1 backend, and set up replication
well ahead of time. Replicate from the 7.4 system to the 8.0 one.

It might take several days to get replication up to date if the
databases are large enough, but once they are relatively in sync, they
should stay there pretty easily.

Switching versions is then as easy as using MOVE SET to switch the
origin from the 7.4 system to the 8.0 one. That ought to take mere
seconds, so you wouldn't need a long time to do the upgrade. See the
URL below for more information...
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/versionupgrade.html
"Did you ever walk in a room and forget why you walked in? I think
that's how dogs spend their lives." -- Sue Murphy