pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReplication - state of the art?
7+ messages / 4 participants
[nested] [flat]
* Replication - state of the art?
@ 2006-03-01 17:51 Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 2 replies; 7+ messages in thread
From: Bryce Nesbitt @ 2006-03-01 17:51 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
I'm interested in creating a mirror database, for use in case one our
primary machine goes down. Can people here help sort out which of the
several replication projects is most viable?
As far as I can tell, the winner is slony1 at
http://gborg.postgresql.org/project/slony1/projdisplay.php , but there
are several contenders.
------------------------------------------
The needs here are very relaxed. We have one main postmaster which runs
24x7x365. There's another machine nearby that can accept a log or
journal of some sort. The alternate never needs DB access, except in
case of main machine failure, and then we can take up to 15 minutes to
switch over and rebuild the DB. "No-lost transaction" is far more
important than switch time.
Anyone here using replication or transaction journaling? Has it proved
reliable, easy to maintain?
Bryce Nesbitt
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Replication - state of the art?
@ 2006-03-01 18:28 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 1 reply; 7+ messages in thread
From: Andrew Sullivan @ 2006-03-01 18:28 UTC (permalink / raw)
To: pgsql-sql
On Wed, Mar 01, 2006 at 09:51:46AM -0800, Bryce Nesbitt wrote:
> switch over and rebuild the DB. "No-lost transaction" is far more
> important than switch time.
You can't guarantee that without two phase commit, no matter what you
do. Log shipping doesn't require you to have an active database
running on the origin (slony-1 does, which is one of its potential
drawbacks). But that won't help you if a transaction committed at
the instant an earthquake hit your datacentre, wiping it out. You
can't get the data off the failed origin no matter what.
> Anyone here using replication or transaction journaling? Has it proved
> reliable, easy to maintain?
Define "easy". Every possible replication system is going to have
slightly grotty corners into which you find yourself wandering. The
question is merely whether the room is octagonal or merely
rectangular.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Replication - state of the art?
@ 2006-03-01 19:28 Bryce Nesbitt <bryce1@obviously.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 7+ messages in thread
From: Bryce Nesbitt @ 2006-03-01 19:28 UTC (permalink / raw)
To: pgsql-sql
Andrew Sullivan wrote:
> On Wed, Mar 01, 2006 at 09:51:46AM -0800, Bryce Nesbitt wrote:
>
>> switch over and rebuild the DB. "No-lost transaction" is far more
>> important than switch time.
>>
>
> You can't guarantee that without two phase commit, no matter what you
> do. Log shipping doesn't require you to have an active database
> running on the origin (slony-1 does, which is one of its potential
> drawbacks). But that won't help you if a transaction committed at
> the instant an earthquake hit your datacentre, wiping it out. You
> can't get the data off the failed origin no matter what.
>
Actually let me loosen that a bit: we don't need two phase commit. We
can loose the most recent transaction, or even the last few seconds of
transactions. What we can't survive is -- on the day of the emergency
-- a long and complicated DB rebuild with mistakes and hard-to-debug
data issues.
>> Anyone here using replication or transaction journaling? Has it proved
>> reliable, easy to maintain?
>>
>
> Define "easy". Every possible replication system is going to have
> slightly grotty corners into which you find yourself wandering. The
> question is merely whether the room is octagonal or merely
> rectangular.
>
There's no fire creating demand for replication, so there is little time
budget.
So is there a sort of padded, no-sharp-corners, playroom that gets us
90% of the way there?
We're looking to reduce what's now a 24 hour window on data loss (since
the most recent
nightly) into something more reasonable (like 500 milliseconds). But
risk -- of data corruption --
and time --too much-- will can the project.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Replication - state of the art?
@ 2006-03-01 19:49 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Sullivan @ 2006-03-01 19:49 UTC (permalink / raw)
To: pgsql-sql
On Wed, Mar 01, 2006 at 11:28:06AM -0800, Bryce Nesbitt wrote:
> Actually let me loosen that a bit: we don't need two phase commit. We
> can loose the most recent transaction, or even the last few seconds of
> transactions. What we can't survive is -- on the day of the emergency
> -- a long and complicated DB rebuild with mistakes and hard-to-debug
> data issues.
Then I suggest you use Slony-I. While it is not plug and play, the
thing it _is_ designed to handle reasonably well is failover and
(better) switchover. Most systems plan to solve that piece of
functionality later, with a script or something, at which point it is
apparent that setting up failover or swichover to be anything
approaching safe is actually very tricky. (Log shipping is probably
not in this category, but AFAIK the promote-to-live support for a
standby database copy is still not all built by anyone. If you like
rolling your own, however, it might be your answer.)
> There's no fire creating demand for replication, so there is little time
> budget.
> So is there a sort of padded, no-sharp-corners, playroom that gets us
> 90% of the way there?
The "no budget" remark here is what makes me strike CMD's Mammoth
Replicator off the list. But I'm sure their administration tools are
far sweeter than the admittedly hackish ones that Slony currently
delivers out of the box.
> nightly) into something more reasonable (like 500 milliseconds). But
> risk -- of data corruption --
> and time --too much-- will can the project.
Another big reason to use a live-standby system like Slony is that
once you have the extra database online, you suddenly think of all
sorts of nifty queries you can move there without destroying your
production performance. Be careful not to get addicted, is all.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Replication - state of the art?
@ 2006-03-01 20:09 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2006-03-01 20:09 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
On Wed, 2006-03-01 at 11:51, Bryce Nesbitt wrote:
> I'm interested in creating a mirror database, for use in case one our
> primary machine goes down. Can people here help sort out which of the
> several replication projects is most viable?
>
> As far as I can tell, the winner is slony1 at
> http://gborg.postgresql.org/project/slony1/projdisplay.php , but there
> are several contenders.
>
> ------------------------------------------
>
> The needs here are very relaxed. We have one main postmaster which runs
> 24x7x365. There's another machine nearby that can accept a log or
> journal of some sort. The alternate never needs DB access, except in
> case of main machine failure, and then we can take up to 15 minutes to
> switch over and rebuild the DB. "No-lost transaction" is far more
> important than switch time.
>
> Anyone here using replication or transaction journaling? Has it proved
> reliable, easy to maintain?
You might want to look at pgpool in mirror replication mode as well.
It's got some limitations due to it's query shipping nature, but may
give you what you need. It knows how to switch off from the dead server
and keep running on the one good one. It's solid software though.
Mammoth may be a better option for you. It's not that horribly
expensive, and setup is supposed to be a snap.
If you use slony, you might want to look at frontending it with pgpool
which makes switching the servers around a little easier, as you can do
it in pgpool instead of your app layer.
Lots of choices. Hard to say which is right.
I really like slony, and use it at work. I'm quite happy with it.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Replication - state of the art?
@ 2006-03-01 22:15 Jim C. Nasby <jnasby@pervasive.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 7+ messages in thread
From: Jim C. Nasby @ 2006-03-01 22:15 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
You could also use WAL shipping and some PITR trickery to keep a 'warm
standby' database up to date. How far behind it falls is up to you,
since you'll be periodically syncing the current WAL file to the backup
machine. Do the sync once a minute, and at most you lose 60 seconds of
data.
On Wed, Mar 01, 2006 at 02:49:18PM -0500, Andrew Sullivan wrote:
> On Wed, Mar 01, 2006 at 11:28:06AM -0800, Bryce Nesbitt wrote:
> > Actually let me loosen that a bit: we don't need two phase commit. We
> > can loose the most recent transaction, or even the last few seconds of
> > transactions. What we can't survive is -- on the day of the emergency
> > -- a long and complicated DB rebuild with mistakes and hard-to-debug
> > data issues.
>
> Then I suggest you use Slony-I. While it is not plug and play, the
> thing it _is_ designed to handle reasonably well is failover and
> (better) switchover. Most systems plan to solve that piece of
> functionality later, with a script or something, at which point it is
> apparent that setting up failover or swichover to be anything
> approaching safe is actually very tricky. (Log shipping is probably
> not in this category, but AFAIK the promote-to-live support for a
> standby database copy is still not all built by anyone. If you like
> rolling your own, however, it might be your answer.)
>
> > There's no fire creating demand for replication, so there is little time
> > budget.
> > So is there a sort of padded, no-sharp-corners, playroom that gets us
> > 90% of the way there?
>
> The "no budget" remark here is what makes me strike CMD's Mammoth
> Replicator off the list. But I'm sure their administration tools are
> far sweeter than the admittedly hackish ones that Slony currently
> delivers out of the box.
>
> > nightly) into something more reasonable (like 500 milliseconds). But
> > risk -- of data corruption --
> > and time --too much-- will can the project.
>
> Another big reason to use a live-standby system like Slony is that
> once you have the extra database online, you suddenly think of all
> sorts of nifty queries you can move there without destroying your
> production performance. Be careful not to get addicted, is all.
>
> A
>
> --
> Andrew Sullivan | ajs@crankycanuck.ca
> Information security isn't a technological problem. It's an economics
> problem.
> --Bruce Schneier
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Replication - state of the art?
@ 2006-03-01 23:23 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2006-03-01 23:23 UTC (permalink / raw)
To: pgsql-sql
On Wed, Mar 01, 2006 at 04:15:18PM -0600, Jim C. Nasby wrote:
> You could also use WAL shipping and some PITR trickery to keep a 'warm
> standby' database up to date. How far behind it falls is up to you,
> since you'll be periodically syncing the current WAL file to the backup
> machine. Do the sync once a minute, and at most you lose 60 seconds of
> data.
Right. But you have to write all of that, and write the failover
scripts, and such like. And the OP suggested that there wasn't time
budget for that. But it'd work.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-03-01 23:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-01 17:51 Replication - state of the art? Bryce Nesbitt <bryce1@obviously.com>
2006-03-01 18:28 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-01 19:28 ` Bryce Nesbitt <bryce1@obviously.com>
2006-03-01 19:49 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-01 22:15 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-01 23:23 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-01 20:09 ` Scott Marlowe <smarlowe@g2switchworks.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox