Thread: Slony vs Longiste
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? From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k.
On Tue, Sep 23, 2008 at 03:36:51PM -0500, Jason Long wrote: > From what I read Longiste is easy to set up while I got a quote for Slony > setup for 5-10k. I can set up Slony for way less than that, FWIW. But Londiste is intended to be easier to set up than Slony. A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
Jason Long <mailing.list@supernovasoftware.com> writes: > 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? You can also consider using pg-pool[1] or sequioa[2] which will automatically handle high-availability if any of the servers fails. Regards. [1] http://pgpool.projects.postgresql.org/ [2] http://sequoia.continuent.org/
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. -- Richard Huxton Archonet Ltd
Richard Huxton wrote:
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.
The database has 10-20 concurrent users so updates are not very heavy.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 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.
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. -- Robert Treat http://www.omniti.com/ Database: Scalability: Consulting
Robert Treat wrote:
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?
I am looking at a combination of hiring someone for setup and advice and them maintaining it myself.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 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?
Hi
SkyTools contains in addition to Londiste and PgQ also walmgr.py that we use quite often for inside colo switchovers. Between colocations we use londiste because of lower bandwith requirements.
walmgr.py --help
usage: WALShipping manager.
walmgr INI COMMAND [-n]
Master commands:
setup Configure PostgreSQL for WAL archiving
sync Copies in-progress WALs to slave
syncdaemon Daemon mode for regular syncing
stop Stop archiving - de-configure PostgreSQL
periodic Run periodic command if configured.
Slave commands:
boot Stop playback, accept queries
pause Just wait, don't play WAL-s
continue Start playing WAL-s again
Common commands:
listbackups List backups.
backup Copies all master data to slave. Will keep backup history
if slave keep_backups is set. EXPERIMENTAL: If run on slave,
creates backup from in-recovery slave data.
restore [set][dst] Stop postmaster, move new data dir to right location and start
postmaster in playback mode. Optionally use [set] as the backupset
name to restore. In this case the directory is copied, not moved.
Internal commands:
xarchive archive one WAL file (master)
xrestore restore one WAL file (slave)
xlock Obtain backup lock (master)
xrelease Release backup lock (master)
xrotate Rotate backup sets, expire and archive oldest if necessary.
xpurgewals Remove WAL files not needed for backup (slave)
SkyTools contains in addition to Londiste and PgQ also walmgr.py that we use quite often for inside colo switchovers. Between colocations we use londiste because of lower bandwith requirements.
walmgr.py --help
usage: WALShipping manager.
walmgr INI COMMAND [-n]
Master commands:
setup Configure PostgreSQL for WAL archiving
sync Copies in-progress WALs to slave
syncdaemon Daemon mode for regular syncing
stop Stop archiving - de-configure PostgreSQL
periodic Run periodic command if configured.
Slave commands:
boot Stop playback, accept queries
pause Just wait, don't play WAL-s
continue Start playing WAL-s again
Common commands:
listbackups List backups.
backup Copies all master data to slave. Will keep backup history
if slave keep_backups is set. EXPERIMENTAL: If run on slave,
creates backup from in-recovery slave data.
restore [set][dst] Stop postmaster, move new data dir to right location and start
postmaster in playback mode. Optionally use [set] as the backupset
name to restore. In this case the directory is copied, not moved.
Internal commands:
xarchive archive one WAL file (master)
xrestore restore one WAL file (slave)
xlock Obtain backup lock (master)
xrelease Release backup lock (master)
xrotate Rotate backup sets, expire and archive oldest if necessary.
xpurgewals Remove WAL files not needed for backup (slave)
On Wed, Sep 24, 2008 at 10:12 PM, Robert Treat <robert@omniti.com> wrote:
Are you planning on hiring someone to do it, or are you going to do itOn 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.
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.
--
Robert Treat
http://www.omniti.com/
Database: Scalability: Consulting
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general