On Thu, Dec 29, 2005 at 11:26:11PM +0100, Martin Pitt wrote: > > I haven't checked thoroughly, but I suspect that pg_upgradecluster is not > > executing the operations on the new database within a single transaction. > > The upgrade of a database is done with a pg_dump/pg_restore pipeline; > there aren't any options to influence the speed or transactional > behavior, do you know how it can be improved?
Adding -e to pg_restore (if it isn't there already) should prevent
command errors during the restore from going undetected, even without
transactions. It might be something that needs to be turned off for
a lot of people for the inevitable corner cases that new PostgreSQL
versions deprecate, but it would be a good safe default.
On closer investigation the lack of a single big enclosing transaction
might not the big problem after all. The row data for each table
is loaded in a single transaction (one COPY command), and indexes
are recreated after the tables are populated, so the only transaction
overhead is in the schema updates. Unfortunately this doesn't explain
why one of my pg_upgradecluster sessions took so many hours to run.
I'll have to take a look at what is happening in more depth, when it
bubbles up to the top of my priority queue again.
I have a pair of clusters on different machines which are set up such
that the second machine does a pg_dump/pg_restore session from the first
to the second each day. I used to do this with
pg_dump -h first ... | sed ... | psql -h second ...
where the sed script mangled a few commands and inserted "\set
ON_ERROR_STOP 1" and "begin work;" at the first line and "commit;"
at the end.
That worked for 7.4 and 8.0 (including the case where first ran 7.4 and
second ran 8.0), but it gets a lot more messy around 8.1, mostly because
pg_dump no longer emits nice non-error-generating SQL code to purge and
reload the users table. An option to ignore non-existent users in the
DROP USER command would be nice. Also, pg_dump in SQL mode doesn't
back up large objects at all until 8.1--not a problem for me since I
don't have any, but probably someone out there is using them. ;-)
--
Opinions expressed are my own, I don't speak for my employer, and all that.
Encrypted email preferred. Go ahead, you know you want to. ;-)
OpenPGP at work: 3528 A66A A62D 7ACE 7258 E561 E665 AA6F 263D 2C3D
signature.asc
Description: Digital signature
