[TriLUG] Slony-I

[Jon Carnes]

I don't have any experience that I know of with Slony-I, but I have
backed up quite a number of ornery databases in my life.

In a worse case scenario, you can mirror the HD that the DB runs on,
using either hardware based or software based methods. Try doing it with
an externally attached array or single HD. Once the mirroring is in
place and running, you can pull the mirrored drive out put it on the
shelf as a snap shot of the database up to that moment.

Then hook up a fresh drive or array into the mirror and let the system
re-mirror.  Swap in and out as necessary.

If you have a secondary box with identical hardware, you can load the
drive - repair the database (roll back any transactions that were
stopped in mid process) and then backup the drive at your leisure.

I've used this before for installs that *had* to be 24/7x52. Maintenance
windows were 30 minutes once a year.

We even rolled upgrades into the system using this method:
Pull the mirror
Rebuild the Database
Upgrade
Idle the current system
Pull the transaction logs for the past two hours (the time it took for
the upgrade) and apply those to the upgraded system.
Put the new system in place for the old.
Total down town = ~5minutes, 25 minutes to spare!

Of course today we would simply use a cluster and do a rolling upgrade
across the elements of the cluster (a rolling upgrade that sometimes
takes two months to complete...)

HtH - Jon

On Thu, 2007-02-08 at 16:17 -0500, David Brain wrote:
> Hi,
> 
> Anyone have any experience with Slony-I.  I have a three  problems:
> 
> 1. Large (100GB+) dataset to migrate
> 2. Limited experience of Slony
> 3. It's a production box, and in use 24/7
> 
> I have tried to get replication working but have hit some issues with
> writing data to the master db while the initial copy is running -
> initially with a permission problem for one of the database users
> trying to update one of the slony sequences.  Then once that was fixed
> hit some problems with foreign key errors.
> 
> I'll be at the meeting this evening if you'd rather talk about it
> (which might  be faster..).
> 
> Thanks,
> 
> David.