Re: Slony vs Longiste - Mailing list pgsql-general

Robert Treat wrote:

On Wednesday 24 September 2008 12:34:17 Jason Long wrote: 

Richard Huxton wrote:   

Jason Long wrote:     

I need to set up master vs slave replication.

My use case is quite simple.  I need to back up a small but fairly
complex(30 MB data, 175 tables) DB remotely over T1 and be able to
switch to that if the main server fails.  The switch can even be a
script run manually.

Can someone either comment in as much detail as possible or point me to
a comparison of Slony vs Longiste.  Or some other option I have not
heard of?       

Three questions you need to ask yourself.
1. How heavily updated is the database?
2. How often do you change the database's schema?
3. Are there other databases in the installation?

If #1 is "very heavy" then you'll want to do some testing with any
solution you use.

If #2 is "a lot" then you'll want to consider WAL shipping as mentioned
below. Slony can handle schema changes, but you'll need to process them
through its own script. I'm afraid I can't comment on Londiste.

If you just want a backup and the answer to #3 is no, look at WAL
shipping (see the various archive_xxx config settings in the manual and
google a bit).
     

From what I read Longiste is easy to set up while I got a quote for
Slony setup for 5-10k.       

Unless your requirements are strange, that seems a little high, even
assuming USD as a currency. Of course, if you want support and
maintenance that will tend to make things mount.     

The database has 10-20 concurrent users so updates are not very heavy.

The schema changes very frequently.

There are not other databases in the installation.

This quote included initial setup, failure testing, and scripts that
were to automate setup and manage the installation.  It did not include
support and maintenance.   

Are you planning on hiring someone to do it, or are you going to do it 
yourself, because the prices of the solution is completely orthogonal to 
which is the better fit technically. 

In your case, since you do a lot of DDL changes, I'd go with londiste over 
slony if I had to pick from those two. However, given the requirements you 
laid out, PITR is probably your best option (this is what Richard alluded 
too), and certainly the one I would recommend you try first. 
 

I am looking at a combination of hiring someone for setup and advice and them maintaining it myself.

I agree PITR is probably a good fit.  How far time wise would could the fall behind the live server and what would affect that?  Anything else I should consider if I go PITR?

The default size of WAL segment files is 16 MB.  Since my entire DB is only 30 MB(will grow quickly as I am going to add internal document storage soon).

Will I need to recompile Postgres to reduce this?  What will be the repercussions of reducing the size?