Core team statement on replication in PostgreSQL

[Andrew Sullivan-5]

On Thu, May 29, 2008 at 07:02:56PM -0400, Tom Lane wrote:

> People want the bits to go from point A to point B; they don't want
> to have to research, design, test, and administer their own solution
> for moving the bits.  

I agree with this.  I think I probably know as well as most people --
perhaps less well than maybe two people in the world -- where most of
the bodies in Slony are hidden, and I still find it a pain to
administer.  Other systems are only somewhat less troublesome; and I
really do know about the concepts involved.  I'm not tripping on
important things.  It's just some work to set up.

Other systems hide that work.

Given that (for instance) psql is really very easy to use once you
know a few things, the ongoing pain of simple replication in Postgres
is a big wart.

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

[Merlin Moncure-2]

On Thu, May 29, 2008 at 9:26 PM, Josh Berkus <josh@...> wrote:
>> I fully accept that it may be the case that it doesn't make technical
>> sense to tackle them in any order besides sync->read-only slaves because
>> of dependencies in the implementation between the two.  If that's the
>> case, it would be nice to explicitly spell out what that was to deflect
>> criticism of the planned prioritization.
>
> There's a very simple reason to prioritize the synchronous log shipping first;
> NTT may open source their solution and we'll get it a lot sooner than the
> other components.

That's a good argument.  I just read the NTT document and the stuff
looks fantastic.  You've convinced me...it just doesn't seem prudent
to forge ahead with hot standby without dealing with all the
syncnronous changes to wal logging first.  I just want you guys to
understand how important hot standby is to a lot of people.  sync
logging maybe less so, but having a proof of concept implementation
significantly alters the bang/buck ratio.

> That is, we expect that synch log shipping is *easier* than read-only slaves
> and will get done sooner.  Since there are quite a number of users who could
> use this, whether or not they can run queries on the slaves, why not ship
> that feature as soon as its done?

I think what dfetter, etc. were saying is that we should elevate the
hot standby stuff to a requirement, or at least a future requirement.
IOW, we should try and avoid doing anything which would make it harder
than it already is.  Please understand that I don't thing people on
the list were trying to be negative...the failure of hot standby to
materialize in the 8.3 cycle was a bitter pill for many people.  I
personally see this new thinking as a hugely positive development.

> There's also a number of issues with using the currently log shipping method
> for replication.  In additon to the previously mentioned setup pains, there's
> the 16MB chunk size for shipping log segments, which is fine for data
> warehouses but kind of sucks for a web application with a 3GB database which
> may take 2 hours to go though 16MB.  So we have to change the shipping method
> anyway, and if we're doing that, why not work on synch?

well, there is the archive_timeout setting...but point taken.  A big
use case for hot standby is OLTP environments where you get to combine
HA and reporting server into a single box.

> Mind you, if someone wanted to get started on read-only slaves *right now* I
> can't imagine anyone would object.  There's a number of problems to solve
> with recovery mode, table locking etc. that can use some work even before we
> deal with changes to log shipping, or XID writeback or any of the other
> issues.  So, volunteers?

As I see it, sync logging, hot standby, and improved setup features
are all mostly orthogonal..  Florian took some pretty decent notes
during his analysis and outlined the problem areas pretty well.  That
would be a starting point.  It just strikes me for all this stuff to
having even remote chance of making 8.4 the work needs to be divided
up into teams and conquered separately.

merlin

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

[Andrew Dunstan]

Josh Berkus wrote:

> Greg,
>
>  
>> I fully accept that it may be the case that it doesn't make technical
>> sense to tackle them in any order besides sync->read-only slaves because
>> of dependencies in the implementation between the two.  If that's the
>> case, it would be nice to explicitly spell out what that was to deflect
>> criticism of the planned prioritization.
>>    
>
> There's a very simple reason to prioritize the synchronous log shipping first;
> NTT may open source their solution and we'll get it a lot sooner than the
> other components.  
>  

I have been reading the slides from the NTT presentation, and I now
really regret not having gone to that talk.

It does seem quite heavy, though, including new background processes,
heartbeat etc.
> That is, we expect that synch log shipping is *easier* than read-only slaves
> and will get done sooner.  Since there are quite a number of users who could
> use this, whether or not they can run queries on the slaves, why not ship
> that feature as soon as its done?
>  

Indeed.

> There's also a number of issues with using the currently log shipping method
> for replication.  In additon to the previously mentioned setup pains, there's
> the 16MB chunk size for shipping log segments, which is fine for data
> warehouses but kind of sucks for a web application with a 3GB database which
> may take 2 hours to go though 16MB.  So we have to change the shipping method
> anyway, and if we're doing that, why not work on synch?
>  

Well, yes, but you do know about archive_timeout, right? No need to wait
2 hours.


cheers

andrew


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

[Tom Lane-2]

Greg Smith <gsmith@...> writes:
> I fully accept that it may be the case that it doesn't make technical
> sense to tackle them in any order besides sync->read-only slaves because
> of dependencies in the implementation between the two.

Well, it's certainly not been my intention to suggest that no one should
start work on read-only-slaves before we finish the other part.  The
point is that I expect the log shipping issues will be done first
because they're easier, and it would be pointless to not release that
feature if we had it.

But since you mention it: one of the plausible answers for fixing the
vacuum problem for read-only slaves is to have the slaves push an xmin
back upstream to the master to prevent premature vacuuming.  The current
design of pg_standby is utterly incapable of handling that requirement.
So there might be an implementation dependency there, depending on how
we want to solve that problem.

                        regards, tom lane

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

[singh.gurjeet]

On Fri, May 30, 2008 at 10:40 AM, Tom Lane <tgl@...> wrote:

But since you mention it: one of the plausible answers for fixing the
vacuum problem for read-only slaves is to have the slaves push an xmin
back upstream to the master to prevent premature vacuuming.  The current
design of pg_standby is utterly incapable of handling that requirement.
So there might be an implementation dependency there, depending on how
we want to solve that problem.

I think it would be best to not make the slave interfere with the master's operations; that's only going to increase the operational complexity of such a solution.

There could be multiple slaves following a master, some serving data-warehousing queries, some for load-balancing reads, some others just for disaster recovery, and then some just to mitigate human errors by re-applying the logs with a delay.

I don't think any one installation would see all of the above mentioned scenarios, but we need to take care of multiple slaves operating off of a single master; something similar to cascaded Slony-I.

My two cents.

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

[Simon Riggs]

On Thu, 2008-05-29 at 10:12 -0400, Tom Lane wrote:

> The Postgres core team met at PGCon to discuss a few issues, the largest
> of which is the need for simple, built-in replication for PostgreSQL.
> Historically the project policy has been to avoid putting replication
> into core PostgreSQL, so as to leave room for development of competing
> solutions, recognizing that there is no "one size fits all" replication
> solution.  However, it is becoming clear that this policy is hindering
> acceptance of PostgreSQL to too great an extent, compared to the benefit
> it offers to the add-on replication projects.  Users who might consider
> PostgreSQL are choosing other database systems because our existing
> replication options are too complex to install and use for simple cases.
> In practice, simple asynchronous single-master-multiple-slave
> replication covers a respectable fraction of use cases, so we have
> concluded that we should allow such a feature to be included in the core
> project.  We emphasize that this is not meant to prevent continued
> development of add-on replication projects that cover more complex use
> cases.
>
> 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.
> We hope that such a feature can be completed for 8.4.  Ideally this
> would be coupled with the ability to execute read-only queries on the
> slave servers, but we see technical difficulties that might prevent that
> from being completed before 8.5 or even further out.  (The big problem
> is that long-running slave-side queries might still need tuples that are
> vacuumable on the master, and so replication of vacuuming actions would
> cause the slave's queries to deliver wrong answers.)
>
> Again, this will not replace Slony, pgPool, Continuent, Londiste, or
> other systems for many users, as it will be not be highly scalable nor
> support long-distance replication nor replicating less than an entire
> installation.  But it is time to include a simple, reliable basic
> replication feature in the core system.

I'm in full support of this and commend the work of the NTT team.

The goals and timescales are realistic and setting a timetable in this
way will help planning for many users,

I'm expecting to lead the charge on the Hot Standby project. The problem
mentioned is just one of the issues, though overall I'm now optimistic
about our eventual success in that area. I'm discussing this now with a
couple of sponsors and would welcome serious financial commitments to
this goal. Please contact me off-list if you agree also.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


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

[Dimitri Fontaine-3]

Le vendredi 30 mai 2008, Tom Lane a écrit :
> No, I think it would be a useless expenditure of energy.  Failover
> includes a lot of things that are not within our purview: switching
> IP addresses to point to the new server, some kind of STONITH solution
> to keep the original master from coming back to life, etc.  Moreover
> there are already projects/products concerned with those issues.

Well, I forgot that there's in fact no active plan to put pgbouncer features
into core at the moment (I'm sure I've read something about this on the lists
though). If it was the case, the slave could proxy queries to the master, and
stop proxying but serve them if the master tells it it's dying.
This way, no need to switch IP addresses, the clients just connect as usual
and get results back and do not have to know whether the host they're qerying
against is a slave or a master. This level of smartness is into -core.

The STONITH part in case of known failure (fatal) does not seem that hard
either, as the master at fatal time could write somewhere it's now a slave
and use this at next startup time (recovery.conf?). If it can't even do that,
well, we're back to crash situation with no provided automatic failover
solution. Not handled failure cases obviously will continue to exist.

I'm not asking for "all cases managed in -core please", just for "some level
of effort" on the topic. Of course, I'm just the one asking questions and
trying to raise ideas, so I'm perfectly fine with your current answer
(useless expenditure of energy) even if somewhat disagreeing on the useless
part of it :)

As for the integrated pgbouncer daemon part, I'm thinking this would allow the
infamous part 3 of the proposal (read-only slave) to get pretty simple to
setup when ready: the slave knows who its master is, and as soon as an XID is
needed the transaction queries are forwarded/proxied to it. Thanks again
Florian !

> It might be useful to document where to find solutions to that problem,
> but we can't take it on as part of core Postgres.

Even the part when it makes sense (provided it does and I'm not completely off
tracks here)?

Regards,
--
dim

[Simon Riggs]

On Fri, 2008-05-30 at 01:10 -0400, Tom Lane wrote:

> Greg Smith <gsmith@...> writes:
> > I fully accept that it may be the case that it doesn't make technical
> > sense to tackle them in any order besides sync->read-only slaves because
> > of dependencies in the implementation between the two.
>
> Well, it's certainly not been my intention to suggest that no one should
> start work on read-only-slaves before we finish the other part.  The
> point is that I expect the log shipping issues will be done first
> because they're easier, and it would be pointless to not release that
> feature if we had it.

Agreed.

I'm arriving late to a thread that seems to have grown out of all
proportion.

AFAICS streaming WAL and hot standby are completely orthogonal features.
Streaming WAL is easier and if NTT can release their code to open source
we may get this in the Sept commit fest. Hot Standby is harder and it
was my viewpoint at PGCon that we may not have a perfect working version
of this by the end of 8.4. We are very likely to have something working,
but maybe not the whole feature set as we might wish to have. I expect
to be actively working on this "soon". I definitely do want to see WAL
streaming going in as early as possible and before end of 8.4, otherwise
code conflicts and other difficulties are likely to push out the 8.4
date and/or Hot Standby.

So as I see it, Tom has only passed on my comments on this, not added or
removed anything. The main part of the announcement was really about
bringing the WAL streaming into core and effectively favouring it over a
range of other projects.

Can we all back off a little on this for now? Various concerns have been
validly expressed, but it will all come good AFAICS.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


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

[Dimitri Fontaine-3]

Le vendredi 30 mai 2008, Dimitri Fontaine a écrit :
> This way, no need to switch IP addresses, the clients just connect as usual
> and get results back and do not have to know whether the host they're
> qerying against is a slave or a master. This level of smartness is into
> -core.

Oh, and if you want clients to connect to a single IP and hit either the
master or the slave with some weights to choose one or the other, and a way
to remove from pool on failure etc, I think using haproxy in TCP mode would
do it. HaProxy is really nice for this purpose.
  http://haproxy.1wt.eu/

Regards,
--
dim

[Andrew Sullivan-5]

On Thu, May 29, 2008 at 01:58:34PM -0700, David Fetter wrote:
 

> 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.

None of that is an argument for why this has to go in 8.4.

I argued in Ottawa that the idea that you have to plan a feature for
_the next release_ is getting less tenable with each release.  This is
because major new features for Postgres are now often big and
complicated.  The days of big gains from single victories are mostly
over (though there are exceptions, like HOT).  Postgres is already
mature.  As for the middle-aged person with a mortgage, longer-term
planning is simply a necessary part of life now.

There are two possibilities here.  One is to have huge releases on
much longer timetables.  I think this is unsustainable in a free
project, because people will get bored and go away if they don't get
to use the results of their work in a reasonably short time frame.
The other is to accept that sometimes, planning and development for
new features will have to start a long time before actual release --
maybe planning and some coding for 2 releases out.  That allows large
features like the one we're discussing to be developed responsibly
without making everything else wait for it.

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/30/08, Gurjeet Singh <singh.gurjeet@...> wrote:

> On Fri, May 30, 2008 at 10:40 AM, Tom Lane <tgl@...> wrote:
> > But since you mention it: one of the plausible answers for fixing the
> > vacuum problem for read-only slaves is to have the slaves push an xmin
> > back upstream to the master to prevent premature vacuuming.  The current
> > design of pg_standby is utterly incapable of handling that requirement.
> > So there might be an implementation dependency there, depending on how
> > we want to solve that problem.
>
> I think it would be best to not make the slave interfere with the master's
> operations; that's only going to increase the operational complexity of such
> a solution.

I disagree - it's better to consider syncronized WAL-slaves
as equal to master, so having queries there affect master is ok.

You need to remeber this solution tries not to replace 100-node Slony-I
setups.  You can run sanity checks on slaves or use them to load-balance
read-only OLTP queries, but not random stuff.

> There could be multiple slaves following a master, some serving
> data-warehousing queries, some for load-balancing reads, some others just
> for disaster recovery, and then some just to mitigate human errors by
> re-applying the logs with a delay.

To run warehousing queries you better use Slony-I / Londiste.  For
warehousring you want different / more indexes on tables anyway,
so I think it's quite ok to say "don't do it" for complex queries
on WAL-slaves.

> I don't think any one installation would see all of the above mentioned
> scenarios, but we need to take care of multiple slaves operating off of a
> single master; something similar to cascaded Slony-I.

Again, the synchronized WAL replication is not generic solution.
Use Slony/Londiste if you want to get totally independent slaves.

Thankfully the -core has set concrete and limited goals,
that means it is possible to see working code in reasonable time.
I think that should apply to read-only slaves too.

If we try to make it handle any load, it will not be finished in any time.

Now if we limit the scope I've seen 2 variants thus far:

1) Keep slave max in sync, let the load there affect master (xmin).
  - Slave can be used to load-balance OLTP load
  - Slave should not be used for complex queries.

2) If long query is running, let slave lag (avoid applying WAL data).
  - Slave cannot be used to load-balance OLTP load
  - Slave can be used for complex queries (although no new indexes
    or temp tables can be created).

I think 1) is more important (and more easily implementable) case.

For 2) we already have solutions (Slony/Londiste/Bucardo, etc)
so there is no point to make effort to solve this here.

--
marko

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

[Andreas 'ads' Scherbaum]

On Thu, 29 May 2008 23:02:56 -0400 Andrew Dunstan wrote:

> Well, yes, but you do know about archive_timeout, right? No need to wait
> 2 hours.

Then you ship 16 MB binary stuff every 30 second or every minute but
you only have some kbyte real data in the logfile. This must be taken
into account, especially if you ship the logfile over the internet
(means: no high-speed connection, maybe even pay-per-traffic) to the
slave.


Kind regards

--
                                Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

[Andreas 'ads' Scherbaum]

On Thu, 29 May 2008 18:29:01 -0400 Tom Lane wrote:

> Dimitri Fontaine <dim@...> writes:
> > While at it, would it be possible for the "simple" part of the core  
> > team statement to include automatic failover?
>
> No, I think it would be a useless expenditure of energy.  Failover
> includes a lot of things that are not within our purview: switching
> IP addresses to point to the new server, some kind of STONITH solution
> to keep the original master from coming back to life, etc.  Moreover
> there are already projects/products concerned with those issues.

True words. Failover is not and should not be part of PostgreSQL.

But PG can help the failover solution, as example: an easy-to-use
interface about the current slave status comes into my mind. Other
ideas might also be possible.


> It might be useful to document where to find solutions to that problem,
> but we can't take it on as part of core Postgres.

Ack


Kind regards

--
                                Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

[Andreas 'ads' Scherbaum]

On Thu, 29 May 2008 09:22:26 -0700 Steve Atkins wrote:

> On May 29, 2008, at 9:12 AM, 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.
>
> I have customers who are thinking about warm standby functionality, and
> the only thing stopping them deploying it is complexity and maintenance,
> not the cost of the HA hardware. If trivial-to-deploy replication that  
> didn't offer read-only access of the slaves were available today I'd bet
> that most of them would be using it.

Sure, have a similar customer. They are right now using a set of
Perl-scripts which ship the logfiles to the slave, take care of the
status, apply the logfiles, validate checksums ect ect. The whole thing
works very well in combination with RedHat cluster software, but it
took several weeks to implement the current solution.

Not everyone wants to spend the time and the manpower to implement a
"simple" replication.


Kind regards

--
                                Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

[Simon Riggs]

On Fri, 2008-05-30 at 12:31 +0530, Gurjeet Singh wrote:

> On Fri, May 30, 2008 at 10:40 AM, Tom Lane <tgl@...> wrote:
>         But since you mention it: one of the plausible answers for
>         fixing the
>         vacuum problem for read-only slaves is to have the slaves push
>         an xmin
>         back upstream to the master to prevent premature vacuuming.
>          The current
>         design of pg_standby is utterly incapable of handling that
>         requirement.
>         So there might be an implementation dependency there,
>         depending on how
>         we want to solve that problem.
>
> I think it would be best to not make the slave interfere with the
> master's operations; that's only going to increase the operational
> complexity of such a solution.
>
> There could be multiple slaves following a master, some serving
> data-warehousing queries, some for load-balancing reads, some others
> just for disaster recovery, and then some just to mitigate human
> errors by re-applying the logs with a delay.

Agreed.

We ruled that out as the-only-solution a while back. It does have the
beauty of simplicity, so it may exist as an option or possibly the only
way, for 8.4.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


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

[Tom Lane-2]

Simon Riggs <simon@...> writes:
> On Fri, 2008-05-30 at 12:31 +0530, Gurjeet Singh wrote:
>> On Fri, May 30, 2008 at 10:40 AM, Tom Lane <tgl@...> wrote:
>>> But since you mention it: one of the plausible answers for fixing the
>>> vacuum problem for read-only slaves is to have the slaves push an xmin
>>> back upstream to the master to prevent premature vacuuming.
>>
>> I think it would be best to not make the slave interfere with the
>> master's operations; that's only going to increase the operational
>> complexity of such a solution.

> We ruled that out as the-only-solution a while back. It does have the
> beauty of simplicity, so it may exist as an option or possibly the only
> way, for 8.4.

Yeah.  The point is that it's fairly clear that we could make that work.
A solution that doesn't impact the master at all would be nicer, but
it's not at all clear to me that one is possible, unless we abandon
WAL-shipping as the base technology.

                        regards, tom lane

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

[Andrew Dunstan]

Tom Lane wrote:

> Simon Riggs <simon@...> writes:
>  
>> On Fri, 2008-05-30 at 12:31 +0530, Gurjeet Singh wrote:
>>    
>>> On Fri, May 30, 2008 at 10:40 AM, Tom Lane <tgl@...> wrote:
>>>      
>>>> But since you mention it: one of the plausible answers for fixing the
>>>> vacuum problem for read-only slaves is to have the slaves push an xmin
>>>> back upstream to the master to prevent premature vacuuming.
>>>>        
>>> I think it would be best to not make the slave interfere with the
>>> master's operations; that's only going to increase the operational
>>> complexity of such a solution.
>>>      
>
>  
>> We ruled that out as the-only-solution a while back. It does have the
>> beauty of simplicity, so it may exist as an option or possibly the only
>> way, for 8.4.
>>    
>
> Yeah.  The point is that it's fairly clear that we could make that work.
> A solution that doesn't impact the master at all would be nicer, but
> it's not at all clear to me that one is possible, unless we abandon
> WAL-shipping as the base technology.
>
>
>  

Quite. Before we start ruling things out let's know what we think we can
actually do.

I hope that NTT will release their code ASAP so we will have a better
idea of what we have and what we need.

cheers

andrew

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

[Simon Riggs]

On Fri, 2008-05-30 at 11:30 -0400, Andrew Dunstan wrote:

>
> Tom Lane wrote:
> > Simon Riggs <simon@...> writes:
> >  
> >> On Fri, 2008-05-30 at 12:31 +0530, Gurjeet Singh wrote:
> >>    
> >>> On Fri, May 30, 2008 at 10:40 AM, Tom Lane <tgl@...> wrote:
> >>>      
> >>>> But since you mention it: one of the plausible answers for fixing the
> >>>> vacuum problem for read-only slaves is to have the slaves push an xmin
> >>>> back upstream to the master to prevent premature vacuuming.
> >>>>        
> >>> I think it would be best to not make the slave interfere with the
> >>> master's operations; that's only going to increase the operational
> >>> complexity of such a solution.
> >>>      
> >> We ruled that out as the-only-solution a while back. It does have the
> >> beauty of simplicity, so it may exist as an option or possibly the only
> >> way, for 8.4.
> >>    
> > Yeah.  The point is that it's fairly clear that we could make that work.
> > A solution that doesn't impact the master at all would be nicer, but
> > it's not at all clear to me that one is possible, unless we abandon
> > WAL-shipping as the base technology.
> >
> Quite. Before we start ruling things out let's know what we think we can
> actually do.

Let me re-phrase: I'm aware of that possibility and believe we can and
could do it for 8.4. My assessment is that people won't find it
sufficient and I am looking at other alternatives also. There may be a
better one possible for 8.4, there may not. Hence I've said "something
in 8.4, something better later". There is no need to decide that is the
only way forward, yet.

I hope and expect to put some of these ideas into a more concrete form,
but this has not yet happened. Nothing has slipped, not having any
trouble getting on with it, just that my plans were to not start it yet.
I think having a detailed design ready for review by September commit
fest is credible.

> I hope that NTT will release their code ASAP so we will have a better
> idea of what we have and what we need.

That has very little to do with Hot Standby, though there could be patch
conflicts, which is why I'm aiming to get WAL streaming done first.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


--
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 Fri, May 30, 2008 at 9:31 AM, Marko Kreen <markokr@...> wrote:

> On 5/30/08, Gurjeet Singh <singh.gurjeet@...> wrote:
>>
>> I think it would be best to not make the slave interfere with the master's
>> operations; that's only going to increase the operational complexity of such
>> a solution.
>
> I disagree - it's better to consider syncronized WAL-slaves
> as equal to master, so having queries there affect master is ok.
>
> You need to remeber this solution tries not to replace 100-node Slony-I
> setups.  You can run sanity checks on slaves or use them to load-balance
> read-only OLTP queries, but not random stuff.
>
>> There could be multiple slaves following a master, some serving
>> data-warehousing queries, some for load-balancing reads, some others just
>> for disaster recovery, and then some just to mitigate human errors by
>> re-applying the logs with a delay.
>
> To run warehousing queries you better use Slony-I / Londiste.  For
> warehousring you want different / more indexes on tables anyway,
> so I think it's quite ok to say "don't do it" for complex queries
> on WAL-slaves.
>
>> I don't think any one installation would see all of the above mentioned
>> scenarios, but we need to take care of multiple slaves operating off of a
>> single master; something similar to cascaded Slony-I.
>
> Again, the synchronized WAL replication is not generic solution.
> Use Slony/Londiste if you want to get totally independent slaves.

I strongly agree with Gurjeet.  The warm standby replication mechanism
is pretty simple and is wonderfully flexible with the one big
requirement that your clusters have to be mirrors of each other.

Synchronous wal replication obviously needs some communication channel
from the slave back to the master. Hopefully, it will be possible to
avoid this for asynchronous shipping.

merlin

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

[Robert Hodges]

Hi Tom,

Thanks for the reasoned reply.  As you saw from point #2 in my comments, I
think you should do this feature.  I hope this answers Josh Berkus' concern
about my comments.

You make a very interesting comment which seems to go to the heart of this
design approach:

> 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.

It's possible I'm misunderstanding some of the implementation issues, but it
is striking that the detailed responses to your proposal list a number of
low-level dependencies between master and slave states when replicating WAL
records.  It appears that you are designing a replication mechanism that
works effectively between a master and a relatively small number of "nearby"
slaves.  This is clearly an important use case but it also seems clear that
the WAL approach is not a general-purpose approach to replication.  In other
words, you'll incrementally get to that limited end point I describe.  This
will still leave a lot to be desired on read scaling, not to mention many
other cases.

Hence my original comments.  However, rather than harp on that further I
will open up a separate thread to describe a relatively small set of
extensions to PostgreSQL that would be enabling for a wide range of
replication applications.  Contrary to popular opinion these extensions are
actually well understood at the theory level and have been implemented as
prototypes as well as in commercial patches multiple times in different
databases.  Those of us who are deeply involved in replication deserve just
condemnation for not stepping up and getting our thoughts out on the table.

Meanwhile, I would be interested in your reaction to these thoughts on the
scope of the real-time WAL approach.  There's obviously tremendous interest
in this feature.  A general description that goes beyond the NTT slides
would be most helpful for further discussions.

Cheers, Robert

P.s., The NTT slides were really great.  Takahiro and Masao deserve
congratulations on an absolutely first-rate presentation.

On 5/29/08 9:09 PM, "Tom Lane" <tgl@...> wrote:

> 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