[Pkg-postgresql-public] pg_upgrade support in pg_upgradecluster

[Peter Eisentraut]

On ons, 2012-07-25 at 07:35 +0200, Martin Pitt wrote:
> Can we please keep this out of bzr at least until after the next
> upload, and getting that into testing? This doesn't look like
> something we want to try and get past the release team at this point.

Agreed.

> Let me ask that the other way round: If we are in a situation where we
> can use pg_upgrade (e. g. in Wheezy we'll only support 9.1, so the
> Wheezy -> Wheezy+1 upgrade will be from 9.1), is there any reason not
> to use that as a method instead of the pg_dump/pg_restore method? It
> does seem to retain the old data dir etc., so that the basic
> properties of p-common (running several clusters in parallel, always
> able to go back to the original cluster if your upgrade doesn't
> succeed) are retained.

Well, the exact status of pg_upgrade is still dubious.  It's technically
still a contrib program, after all.  But I agree that by the time we're
done here, copy mode could be the default, and the dump mode be another
option.

I wouldn't remove the dump mode altogether yet.

> This was originally implemented for extensions like PostGIS which need
> custom steps to set up the new cluster before/after dumping, but it
> seems the PostGIS maintainers (or others) never acutally implement
> this. If the "init" phase doesn't work any more with pg_upgrade,
> perhaps we can at least leave the "finish" phase?

Well, without an actual example, it's hard to say.  I'm not advocating
removing the functionality, but I'm just saying, I have no idea what
will happen if you use it.

> > -print "Restarting old cluster with restricted connections...\n";
> > - at argv = ('pg_ctlcluster', $version, $cluster, 'start');
> > -error "Could not restart old cluster" if system @argv;
> > +if ($method eq 'dump') {
> > +    print "Restarting old cluster with restricted connections...\n";
> > +    @argv = ('pg_ctlcluster', $version, $cluster, 'start');
> > +    error "Could not restart old cluster" if system @argv;
> > +}
> 
> Is there no danger of accessing the cluster while it's being upgraded
> with pg_upgrade?

The main point is that the cluster must be down before running
pg_upgrade.  The conditional above just takes out the restarting; the
disabling of connections still happens, although it's probably useless.

> > +	# Make a temporary directory for pg_upgrade to store its
> > +	# reports and log files.  Will be removed on success, but
> > +	# retained on error.
> > +	my $tmpdir = tempdir("pg_upgradecluster-pg_upgrade-$cluster-$version-$newversion.XXXXXX", TMPDIR => 1);
> 
> How about putting that into /var/log/postgresql/ instead, and
> unlinking it on success? That would be more permanent than /tmp, which
> gets removed after a reboot, and it could also get a predictable file
> name.

Come to think of it, I like putting it into /var/log/postgresql and just
leaving it there, as a kind of log output.  Because there could be
things in there that you might want to look at even if successful.

But in terms of predictable file names, what do you suggest to do if
someone runs the same upgrade several times, e.g., after failed
attempts?

> > +	# pg_upgrade doesn't support separate configuration and data
> > +	# directories, so we must fix this up temporarily.
> > +	foreach my $file ('postgresql.conf', 'pg_hba.conf', 'pg_ident.conf') {
> > +	    symlink($info{'configdir'}."/$file", $info{'pgdata'}."/$file") or error 'could not symlink configuration file';
> > +	    symlink($newinfo{'configdir'}."/$file", $newinfo{'pgdata'}."/$file") or error 'could not symlink configuration files';
> > +	}
> 
> Oh, I see. That addresses my question from above :-) Could that cause
> any trouble with relative paths in any of those files, which expect
> ident maps etc. in /etc/postgresql/, and with this pg_upgrade suddenly
> would look for them in the data dir?
> 
> How hard would it be to teach pg_upgrade about data_directory and
> friends?

Actually, the above is a Perl rendition of a shell script that I wrote
about a year ago to upgrade some production databases.  Now I see that
the pg_upgrade man page has some advice to handle this better.  I'll see
about improving that.