Hi, I agree this is rather a messy business once you got your slony 
stuff running specially if you got your slony nodes spread all over the 
world like we do here..

we have decided to deal with it in the following manner.

Production database are always running 1 master and currently 4 slaves 
(all in different countries even different continents) and that database 
is about 500 tables and data is not that bad (around 3-5 gigs)

but of course we need to have a fully working development environment 
and in development environment we have no interest to have all the slony 
data.

so we have come up with a procedure for handling all this.

in SVN we got a clean schema dump of the last production release.

we always have an development environment that has all changes (no 
slony) since last release so when we want to update our development 
environment with new data we take a schema dump from the development 
environment and compare it with the latest production schema checked 
into SVN, and the diff is the changes.

So we create a new database, load the production schema from SVN

then take a data dump from the closest production database node with:
pg_dump -F c -N <slony_cluster> -a -Z 9 <database> > datadump.sql

(-N = exclude schema, dont want to get the slony schema data)

then pg_restore it on the new db.

apply the changes

and we got a new fresh development environment without any slony.

this we do every 5-10 days to maintain fresh data in development environment

Once release comes around a diff is generated as above for the changes a 
slony script is generated based on the changes its then applied to a 
slony test cluster (identical to production but all in one location) to 
verify that the schema changes are compatible with slony.

if all is OK, script is applied to production and then the current 
schema on the development environment is checked into SVN as the new 
production schema.

we only allow database schema changes on release because allowing 
changes to schema between release would be a horror to cope with if 
something goes wrong.

If our production database should fail on a slony update, and its not 
fixable right away etc it will take approximately 60h to do a full 
restore.. and of course this is not really an option.

sorry for the long winded response.. but just wanted to give some idea 
how we have come to terms with it.


if this is a bit much, there is always what Bill Moran suggested,

drop schema _slony_cluster CASCADE

works good as well.

Regards
Martin

Diego Algorta Casamayou wrote:
> On Jan 22, 2008 11:16 AM, Raymond O'Donnell <rod at iol.ie> wrote:
>   
>> On 22/01/2008 13:11, Diego Algorta Casamayou wrote:
>>
>>     
>>> What usage could I give to this restored database? May I use it as a
>>> master in case the master died? In that case I think a failover to the
>>> running slave would be faster.
>>>       
>> Well, it's a backup - no more and no less. The whole point of Slony (or
>> any replication system) is that you have a nearly-up-to-date copy of
>> your database to which you can failover if the master goes belly-up. A
>> backup is always going to be less current than a well-running Slony slave.
>>     
>
> I know, I know. But for example, I currently have full backups made
> from my master node. And restoring that backup to use it as an
> independent database doesn't really work because it still has slony
> all over the place. Running UNINSTALL NODE on it doesn't work either.
>
>   
>>> What should I do to use it in a non-replicated environment? A staging
>>> environment, for example for testing purposes where I need
>>> production-like data.
>>>       
>> That's what I do; as well as keeping a copy as a backup, of course. :-)
>>     
>
> OK. But what do you do to get rid of slony on that restored database?
>
> This should be a common issue, but I can't find a clear note on it on
> the documentation.
>
> Thank you.
>   

-- 
*_____________________
Martin Eriksson*
/Albourne Partners (Cyprus) Ltd
Maria House, 1 Avlonos Street, Nicosia 1075, Cyprus
P: +357 22 750652| F: +357 22 750654
m.eriksson at albourne.com
www.albourne.com <http://www.albourne.com>

If you are not an intended recipient of this e-mail, please notify the 
sender, delete it and do not read, act upon, print, disclose, copy, 
retain or redistribute it. Please visit 
http://www.albourne.com/email.html 
<http://www.albourne.com/email.html>for important additional terms 
relating to this e-mail.
/