Tony Byrne wrote:

>Hi folks,
>
>We've been running a two node Slony cluster successfully for a number
>of months and are very impressed with it.  We now wish to upgrade our nodes'
>version of PostgreSQL from v8.0.3 to v8.1.3.  Our understanding is that we
>first need to upgrade our Slony on both nodes to v1.1.5.
>  
>
Yes, that is correct.

>While I have looked at both the UPGRADING file from the Slony
>distribution and the html document for using Slony to facilitate a
>PostgreSQL update I'm not at all comfortable that I understand the
>full and proper sequence of steps to do this safely.  Other than the
>unicode issue are there any gotchas that I should look out for in this
>upgrade?
>
>If I understand what I've read correctly, I first need to stop slon on
>both nodes, update to the latest version, restart slon and then run the
>slonik script to update the functions.  Our application is a web
>application which would remain connected to the db during the Slony
>update.  Would I need to restart the application post Slony upgrade
>and is there any danger that upgrading Slony as described above could
>interfere with the normal running of our application, e.g. is the
>Slony schema changes in the db, or triggers are modified?
>
>  
>
There is a risk that query plans might be held onto that point to the
old, dropped stored functions.  We experienced this problem with
JDBC-based connections.

We wound up having to restart applications immediately after changing
stored functions.

It is uncertain whether you would experience the same thing.  The
problem isn't with Slony-I, per se; it is that there is no mechanism for
invalidating the old query plans.

>When upgrading PostgreSQL itself, should I first remove the slave from
>the cluster (drop node) or can I simply stop slon on both nodes,
>upgrade the slave (with the help of pg_dump), re-enable slon on both nodes
>and do a switchover and repeat?
>  
>
More like the former...

If you have two nodes, 1 and 2, the approach I'd be inclined to take for
the PG version upgrade is thus...

- Create new nodes 10 and 20, which are on the new version of PostgreSQL
- Replicate from 1/2 to 10 and 20
- Once nodes 10 and 20 seem to be working well, move the set origin to
one of the new nodes, say 10
- Once things seem to be working well with pointing the apps to nodes 10
and 20, drop nodes 1 and 2