Brian Hirt <bhirt at mobygames.com> writes:
> i've seem people mention that slony can be used to migrate between 
> releases, and i want to start looking at a migation plan from 7.4 to 7.5 
> so that we don't have downtime during our upgrade.
>
> I haven't been able to find out how to do this yet.  Will this be a 
> pretty straight forward process, or are there going to be lots and lots 
> of mantual steps involved.
>
> can someone point me to a howto, or similiar documentation that explains 
> this process?

The process may be more heavily dependent on how you manage your
applications than it will be on the "PostgreSQL" parts.

The set of "task titles" are more or less thus:

Step 1.  Create a replica on a database of the new version

             This may take a long time if the DB is large.  (I saw
             this take the better part of a day, yesterday, for a big
             DB I was syncing across a somewhat slow link...)

Step 2.  Let replication catch up.

             With ERServe, this could be rather time consuming.
             With Slony-I, it is gratifyingly quick.

Step 3.  Stop your applications that are updating the "source"
             database.

Step 4.  Let replication catch up.

             This ought to take mere seconds, even with something
             slow like ERServe

Step 5.  Let the new node become the "master."

             With Slony-I, this involves the slonik command "MOVE SET"
             which ought to run pretty quickly particularly if
             replication was already caught up.

Step 6.  Point your applications to the new database.

Step 7.  Restart applications.

The likely "hairy" parts of this are steps 3 and 6; they depend on how
you configure database access for your applications, which is not
something anyone here can safely characterize.

If your application can cope with "inability to write" reasonably
gracefully, you could do step 6 where I stuck step 3; you'd point the
app at the new server, which is _nearly_ up to date, tell slonik to
move the master to the new node, and the app would stay in read-only
mode for a short while.

A future plan is to do some integration with PGPool, which could
automate this...

--> pgpool would submit the MOVE SET command;

--> pgpool would then wait until the MOVE SET was completed,
     deferring incoming requests, likely for a very short time;

--> When MOVE SET completes, point the requests at the new "master,"
      allowing the change to be fairly much transparent to the
      application.

This won't play well with CURSOR-based requests, or other requests
tied to open transactions, but it would make _plenty_ of cases of
migrations to new servers pretty near transparent.
-- 
let name="cbbrowne" and tld="ca.afilias.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)