In response to Dane Miller <dane at greatschools.net>:

> On Sat, 2008-04-12 at 08:55 -0400, Bill Moran wrote:
> > > > I've been putting this together over the last couple weeks for
> > > > the reasons listed on the HTML page:
> > > >
> > http://people.collaborativefusion.com/~wmoran/PostgreSQL/slony_switchover.html
> > > > 
> > > > I'm interested to hear how useful this is to others, and of course
> > > > suggestions for improvement are welcome.
> > 
> > Are you running Linux by any chance?  This script was developed and
> > is deployed for us on FreeBSD, and I've verified that it works as
> > advertised on that platform.  I've _attempted_ to make sure that it
> > will work on Linux as well, but I've not had the opportunity to test
> > it.  Testing and feedback is encouraged.
> 
> I was able to do some testing on Linux this week.  I setup three nodes
> running Debian 4.0 (Etch), Postgresql 8.1, and Slony 1.2.1-1.  In the
> end, I didn't succeed at moving the replication origin between nodes
> using slony_switchover.sh (although slonik_move_set worked).  
> 
> I fixed the first issue I encountered (bug #3 below) by adding extra
> functionality to the script. However, the problem that I got stuck on
> was that sl_path didn't contain connection info between my two
> subscriber nodes, node 30 and node 31.  
> 
> Here's what my sl_path table looks like:
> 
> community=# select pa_server,pa_client,pa_conninfo from sl_path ;
>  pa_server | pa_client |  pa_conninfo (truncated)         
> -----------+-----------+---------------------------------------
>         30 |        29 | host=172.16.32.130 dbname=community 
>         29 |        30 | host=172.16.32.129 dbname=community 
>         29 |        31 | host=172.16.32.129 dbname=community 
>         31 |        29 | host=172.16.32.131 dbname=community 
> (4 rows)
> 
> Notice that there are no entries for (pa_server, pa_client) = (31, 30)
> or (30, 31).  Not sure if sl_path is missing entries due to a mistake I
> made in configuring replication, or perhaps a bug in the version of the
> slonik_* scripts packaged for Debian 4.0.  In any case, on my FreeBSD
> production machines sl_path is complete, so this isn't a blocker for my
> using this in production.

While I consider missing of paths a bug in ones configuration, I also
think this script should handle it gracefully, so I'm adding a check
to have it bail with a sane error message if it misses any.

> Here are the bugs I found.  Only the first is Linux-specific.  Hope this
> is helpful feedback.
> 
> 1. absolute path to psql, line 89

I threw in a `which psql`, which should take care of that in all but the
strangest of cases.

> 2. Comparison operator '-eq' should be '=' at line 102

Oops ... although the fix for #3 obviates the need for that bit of
code altogether.

> 3. slony_switchover.sh fails when node ids don't follow the sequence 1,
> 2, 3, .... For example, my slons have node ids 29, 30, and 31.  As a
> result, the following comparison always fails at line 109:
> 
> for I in `$JOT $NUMNODES`;
> do
>   if [ $ME -eq $I ]   <--- 109
>   then

Yeah.  Don't know why I didn't consider that possibility.  At first I
thought this was going to be really difficult to fix, but it only
took a little bit of banging around to make it extract that list from
the DB.

I've uploaded an updated version.  Testing and feedback are encouraged:
http://people.collaborativefusion.com/~wmoran/PostgreSQL/slony_switchover.html

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran at collaborativefusion.com
Phone: 412-422-3463x4023