Core team statement on replication in PostgreSQL

[Marko Kreen-2]

On 5/29/08, Teodor Sigaev <teodor@...> wrote:
> > in this case too.  So each slave just needs to report its own longest
> > open tx as "open" to master.  Yes, it bloats master but no way around it.
>
>  Slaves should not report it every time or every transaction. Vacuum on
> master will ask them before doing a real work.

That is bit too slow if we want to do some page-level cleanup with
regular transactions / bgwriter.  But once or twice per minute update
should work fine.

--
marko

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Andrew Sullivan-5]

On Thu, May 29, 2008 at 12:05:18PM -0700, Robert Hodges wrote:
 
> people are starting to get religion on this issue I would strongly
> advocate a parallel effort to put in a change-set extraction API
> that would allow construction of comprehensive master/slave
> replication.  

You know, I gave a talk in Ottawa just last week about how the last
effort to develop a comprehensive API for replication failed.  I had
some ideas about why, the main one of which is something like this:
"Big features with a roadmap have not historically worked, so unless
we're willing to change the way we work, we won't get that."

I don't think an API is what's needed.  It's clear proposals for
particlar features that can be delivered in small pieces.  That's what
the current proposal offers.  I think any kind of row-based approach
such as what you're proposing would need that kind of proposal too.

That isn't to say that I think an API is impossible or undesirable.
It is to say that the last few times we tried, it went nowhere; and
that I don't think the circumstances have changed.

A
--
Andrew Sullivan
ajs@...
[phone] x104
http://www.commandprompt.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Marko Kreen-2]

On 5/29/08, Tom Lane <tgl@...> wrote:
>  * The proposed approach is trying to get to "real" replication
>  incrementally.  Getting rid of the loss window involved in file-by-file
>  log shipping is step one, and I suspect that step two is going to be
>  fixing performance issues in WAL replay to ensure that slaves can keep
>  up.  After that we'd start thinking about how to let slaves run
>  read-only queries.  But even without read-only queries, this will be
>  a useful improvement for HA/backup scenarios.

I agree with this plan, but I think this extends also for read-only
queries - we don't need to have the perfect, no-overhead solution
as the first step, instead lets have simple and working solution
with some overhead, then improve that one.

And for the first-step solution, I think letting VACUUM keep tuples
around based on slave queries is preferable to letting slaves lag.
This is useful to more situations.

--
marko

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

David Fetter wrote:
> Either one of these would be great, but something that involves
> machines that stay useless most of the time is just not going to work.

Lots of people do use warm standby already anyway, just not based on
mechanisms built into PostgreSQL.  So defining away this need is completely
unrealistic based on my experience.  Even if there were a read-only slave,
lots of applications couldn't make use of it.

Anyway, a common approach to making better use of the hardware is to put some
other service on the otherwise-standby machine, which in turn uses your
master database server machine as its failover target.  Unless you run *only*
a database, there would usually be some candidate that you could set up that
way.

Another common approach is to realize that for some the costs of a downtime
risk are higher than buying some extra hardware.

I think the consensus in the core team was that having synchronous log
shipping in 8.4 would already be a worthwhile feature by itself.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

Jeff Davis wrote:
> It depends on what we mean by synchronous. Do we mean "the WAL record
> has made it to the disk on the slave system," or "the WAL record has
> been applied on the slave system"?

DRBD, which is a common warm standby solution for PostgreSQL at the moment,
provides various levels of synchronicity.  I imagine we could also define, as
need arises, various levels, some of which may be the ones you listed.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

Merlin Moncure wrote:
> Read only slave is the #1 most anticipated feature in the
> circles I run with.

Do these circles not know about slony and londiste?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

Joshua D. Drake wrote:
> What does this give us that Solaris Cluster, RedHat Cluster, DRBD etc..
> doesn't give us?

I personally think that DRBD is a fine solution.  But it only runs on Linux.  
And Solaris Cluster isn't the same as DRBD.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

Andrew Sullivan <ajs@...> writes:
> On Thu, May 29, 2008 at 12:05:18PM -0700, Robert Hodges wrote:
>> people are starting to get religion on this issue I would strongly
>> advocate a parallel effort to put in a change-set extraction API
>> that would allow construction of comprehensive master/slave
>> replication.  

> You know, I gave a talk in Ottawa just last week about how the last
> effort to develop a comprehensive API for replication failed.

Indeed, core's change of heart on this issue was largely driven by
Andrew's talk and subsequent discussion.  We had more or less been
waiting for the various external replication projects to tell us
what they wanted in this line, and it was only the realization that
no such thing was likely to happen that forced us to think seriously
about what could be done within the core project.

As I said originally, we have no expectation that the proposed features
will displace the existing replication projects for "high end"
replication problems ... and I'd characterize all of Robert's concerns
as "high end" problems.  We are happy to let those be solved outside
the core project.

About the only thing that would make me want to consider row-based
replication in core would be if we determine that read-only slave
queries are impractical atop a WAL-log-shipping implementation.
Which could happen; in fact I think that's the main risk of the
proposed development plan.  But I also think that the near-term
steps of the plan are worth doing anyway, for various other reasons,
and so we won't be out too much effort if the plan fails.

                        regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

Andrew Sullivan wrote:
> The big missing piece is lossless failover.  People are currently
> doing it with DRBD, various clustering things, &c., and those are
> complicated to set up and maintain.

Well, we'll see at the end of this (we hope) how a setup procedure of DRBD vs.
PG warm standby works from a standing start.  I wouldn't be overly
optimistic. ;-)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

Mathias Brossard wrote:
>  From what I gather from those slides it seems to me that the NTT solution
> is synchronous not asynchronous. In my opinion it's even better, but I do
> understand that others might prefer asynchronous. I'm going to speculate,
> but I would think it should be possible (without a substancial rewrite) to
> support both modes (or even some intermediate modes, like DRBD on Linux).

Well, we already have asynchronous.  The whole point is adding synchronous.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

Tom Lane wrote:
> We believe that the most appropriate base technology for this is
> probably real-time WAL log shipping, as was demoed by NTT OSS at PGCon.

Now how do we get our hands on their code?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Merlin Moncure-2]

On Thu, May 29, 2008 at 3:59 PM, Peter Eisentraut <peter_e@...> wrote:
> Merlin Moncure wrote:
>> Read only slave is the #1 most anticipated feature in the
>> circles I run with.
>
> Do these circles not know about slony and londiste?

Sure.

For various reasons mentioned elsewhere on this thread, a PITR based
replication solution would have a lot of appeal, assuming it could be
made to work.  The main thing is that it is completely non-invasive to
the master database and all the work is handled on the standby.  It
also operates on a trivial mechanism to transfer the files.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Josh Berkus]

Robert,

> 1.) Partial replication.
> 2.) WAN replication.
> 3.) Bi-directional replication.  (Yes, this is evil but there are
> problems where it is indispensable.)
> 4.) Upgrade support.  Aside from database upgrade (how would this ever
> really work between versions?), it would not support zero-downtime app
> upgrades, which depend on bi-directional replication tricks.
> 5.) Heterogeneous replication.
> 6.) Finally, performance scaling using scale-out over large numbers of
> replicas.  I think it’s possible to get tunnel vision on this—it’s not a
> big requirement in the PG community because people don’t use PG in the
> first place when they want to do this.  They use MySQL, which has very
> good replication for performance scaling, though it’s rather weak for
> availability.  

Let's not try to boil the ocean, hey?

 From my perspective, the above use cases are what complex tools like
Slony, Bucardo, Skytools, Continuent, pgCluster, pgPool2, etc., etc. are
for.  Now, if you're saying that you want to develop row-based
replication so that Continuent will work better, I'm all for it; but
saying that we *shouldn't* implement the current spec which satisfies
large numbers of users because it doesn't support *all* users is a
recipe for self-defeat.  We can't satisfy all users with one
implementation, and we shouldn't try.

I think, for that matter, that work on the common replication hooks
supporting the external replication packages should continue.  We need
these for precisely the reasons you state.  But ... single-master,
single-slave, synch or asynch, whole-installation local network
replication is a case which covers a *lot* of users' needs ... I'd argue
the numerical majority.

--Josh


--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David Fetter]

On Thu, May 29, 2008 at 09:54:03PM +0200, Peter Eisentraut wrote:

> David Fetter wrote:
> > Either one of these would be great, but something that involves
> > machines that stay useless most of the time is just not going to
> > work.
>
> Lots of people do use warm standby already anyway, just not based on
> mechanisms built into PostgreSQL.  So defining away this need is
> completely unrealistic based on my experience.  Even if there were a
> read-only slave, lots of applications couldn't make use of it.
>
> Anyway, a common approach to making better use of the hardware is to
> put some other service on the otherwise-standby machine, which in
> turn uses your master database server machine as its failover
> target.  Unless you run *only* a database, there would usually be
> some candidate that you could set up that way.
>
> Another common approach is to realize that for some the costs of a
> downtime risk are higher than buying some extra hardware.

> I think the consensus in the core team was that having synchronous
> log shipping in 8.4 would already be a worthwhile feature by itself.

If that was in fact the consensus of the core team, and what I've been
seeing from several core members in this thread makes that idea
unclear, it's out of step with the stated goal of the feature.  Having
some kind of half-way, doesn't-actually-quite-work-out-of-the-box
"replication" will make things worse and not better.

Cheers,
David.
--
David Fetter <david@...> http://fetter.org/
Phone: [phone]  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@...

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

David Fetter <david@...> writes:
> On Thu, May 29, 2008 at 09:54:03PM +0200, Peter Eisentraut wrote:
>> I think the consensus in the core team was that having synchronous
>> log shipping in 8.4 would already be a worthwhile feature by itself.

> If that was in fact the consensus of the core team, and what I've been
> seeing from several core members in this thread makes that idea
> unclear, it's out of step with the stated goal of the feature.  Having
> some kind of half-way, doesn't-actually-quite-work-out-of-the-box
> "replication" will make things worse and not better.

What is your justification for denigrating this plan with that?
Or are you merely complaining because we know we won't be all the
way there in 8.4?

                        regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David Fetter]

On Thu, May 29, 2008 at 04:44:19PM -0400, Tom Lane wrote:
> David Fetter <david@...> writes:
> > On Thu, May 29, 2008 at 09:54:03PM +0200, Peter Eisentraut wrote:
> >> I think the consensus in the core team was that having
> >> synchronous log shipping in 8.4 would already be a worthwhile
> >> feature by itself.

I'm sorry if I was unclear on this.  I don't disagree that it would be
a worthy feature.  I do disagree with the idea, given this goal, that
we can expect to ship 8.4 without a built-in replication and have that
be good enough by itself.

> > If that was in fact the consensus of the core team, and what I've
> > been seeing from several core members in this thread makes that
> > idea unclear, it's out of step with the stated goal of the
> > feature.  Having some kind of half-way,
> > doesn't-actually-quite-work-out-of-the-box "replication" will make
> > things worse and not better.
>
> What is your justification for denigrating this plan with that?  Or
> are you merely complaining because we know we won't be all the way
> there in 8.4?

Again, just my humble opinion, but given the stated goal, which I
agree with, I'd say it's worth holding up 8.4 until some kind of
out-of-the-box replication advances that goal, where Yet Another
Toolkit Suitable For People Who Are Already Database Kernel Hackers To
Build Replication Systems On Top Of does not.

Cheers,
David.
--
David Fetter <david@...> http://fetter.org/
Phone: [phone]  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@...

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian-5]

David Fetter wrote:
> > What is your justification for denigrating this plan with that?  Or
> > are you merely complaining because we know we won't be all the way
> > there in 8.4?
>
> Again, just my humble opinion, but given the stated goal, which I
> agree with, I'd say it's worth holding up 8.4 until some kind of
> out-of-the-box replication advances that goal, where Yet Another
> Toolkit Suitable For People Who Are Already Database Kernel Hackers To
> Build Replication Systems On Top Of does not.

I am confused why we would hold 8.4 when no one said we should hold 8.3
because we didn't have built-in replication.

--
  Bruce Momjian  <bruce@...>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Josh Berkus]

David,

>> I think the consensus in the core team was that having synchronous
>> log shipping in 8.4 would already be a worthwhile feature by itself.
>
> If that was in fact the consensus of the core team,

It is.

> and what I've been
> seeing from several core members in this thread makes that idea
> unclear, it's out of step with the stated goal of the feature.  Having
> some kind of half-way, doesn't-actually-quite-work-out-of-the-box
> "replication" will make things worse and not better.

So, you've got a better implementation up your sleeve?

I really don't get where you're coming from on this.  Frankly, your
e-mails seem gauged to be merely disruptive without any intent of
constructive input.  If you're opposed to working on replication in the
core, then just say so.  If you think that there's an easier way to
develop M-S replication in the core than using WAL, then please present
your solution.

--Josh


--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David Fetter]

On Thu, May 29, 2008 at 04:54:04PM -0400, Bruce Momjian wrote:

> David Fetter wrote:
> > > What is your justification for denigrating this plan with that?
> > > Or are you merely complaining because we know we won't be all
> > > the way there in 8.4?
> >
> > Again, just my humble opinion, but given the stated goal, which I
> > agree with, I'd say it's worth holding up 8.4 until some kind of
> > out-of-the-box replication advances that goal, where Yet Another
> > Toolkit Suitable For People Who Are Already Database Kernel
> > Hackers To Build Replication Systems On Top Of does not.
>
> I am confused why we would hold 8.4 when no one said we should hold
> 8.3 because we didn't have built-in replication.

If people on core had come to the idea that we needed to build in
replication *before* 8.3 came out, they certainly didn't announce it.

Now is a great time to mention this because it gives everybody time to:

1.  Come to a consensus on what the out-of-the-box replication should
be, and

2.  Build, test and debug whatever the consensus out-of-the-box
replication turns out to be.

Cheers,
David.
--
David Fetter <david@...> http://fetter.org/
Phone: [phone]  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@...

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

Bruce Momjian <bruce@...> writes:
> David Fetter wrote:
>> Again, just my humble opinion, but given the stated goal, which I
>> agree with, I'd say it's worth holding up 8.4 until some kind of
>> out-of-the-box replication advances that goal, where Yet Another
>> Toolkit Suitable For People Who Are Already Database Kernel Hackers To
>> Build Replication Systems On Top Of does not.

> I am confused why we would hold 8.4 when no one said we should hold 8.3
> because we didn't have built-in replication.

Possibly I am misinterpreting what David said, but I think his opinion
is that synchronous log shipping in itself is useless, and that we
should hold 8.4 until we have that *and* slave-side query execution.
Suffice it to say that I disagree with the first position, and as for
the second, I will never again be willing to hold a release for one
single feature, no matter what it is.  We've learned the folly of
managing the project that way several times over.

                        regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers