Re: Proposed structure for coexisting major versions - Mailing list pgsql-hackers

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
clusterpathparent)
 

-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.Elphick@lfix.co.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
neverallow the righteous to fall."                                                Psalms 55:22