Re: Proposed structure for coexisting major versions

On Mon, 2003-10-27 at 10:05, Neil Conway wrote:
> On Sun, 2003-10-26 at 17:24, Oliver Elphick wrote:
> > If it were possible to have two separate versions of the PostgreSQL
> > packages installed simultaneously, it would be simple to do database
> > upgrades by dumping from the old version and uploading to the new.
>
> You'd need some mechanism to prevent concurrent modifications of the
> source DB during the upgrade process, wouldn't you?

Yes. The existing Debian mechanism (upgrading with the same package
names) does it by shutting down the postmaster and restarting the old
postmaster on port 5431 while a dump is done.

An adaptation of that process will be used to do an upgrade of a
particular database cluster:

pg_version_upgrade
------------------

A new program which will replace postgresql-dump [a Debian-only
program].

It will be used to migrate a cluster from one major version to another.

Options:

-c {cluster} the name of the cluster

-v {version} the version to upgrade to (the default is the latest
version installed)

-p {clusterpath} the new clusterpath (default = old clusterpath)

-d {dump directory} the directory in which to put the dump of the old
cluster (default = old clusterpath parent)

-r recover; continue upgrading from a previous failure

Procedure:
1. initdb a new cluster in {clusterpath}.new/data for
the new major version

2. start a postmaster for the new cluster on port 5430

3. stop the postmaster for the old cluster

4. set the status field in cluster_ports to "upgrading"

5. start a postmaster for the old cluster on port 5431

6. pg_dumpall the old cluster > {clustername}.dumpall

7. load the dump in the new cluster > {dbname}.upgrade 2>&1

8. if there are no errors, stop the two postmasters, else exit and
set status to "failed-upgrade"

9. move the old cluster directory to {clusterpath}.old and move
{clusterpath}.new to {clusterpath}; in cluster_ports, set the
status field back to its original value

10. start the postmaster for the new cluster

11. (with administrator approval only) delete the old cluster and
the dump file

(All operations are done with the software version appropriate to the
cluster version.)

Changes to my original proposal:

1. it is not necessary to keep the major version number in
cluster_ports, since it can be read from the cluster's PG_VERSION file.
It seems sensible to avoid duplicating that datum. The pathname held in
that file will not be PGDATA but its parent, and PGDATA will always be
{clusterpath}/data.

2. the "active" field in cluster_ports is renamed "status", with the
values "active", "inactive", "upgrading" or "failed-upgrade".

The latest version of the proposal is to be found at
http://cvs.alioth.debian.org/cgi-bin/cvsweb.cgi/~checkout~/common/postgresql-client.html?rev=1.1&content-type=text/html&cvsroot=pkg-postgresql

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Cast thy burden upon the LORD, and he shall sustain
thee; he shall never allow the righteous to fall."
Psalms 55:22