Обсуждение: Why standby.max_connections must be higher than primary.max_connections?

<div dir="ltr"><strong><pre><font color="#373791">
Hello hackers.

I am struggling to understand why standby.max_connections must be higher than
<a href="http://primary.max_connections.Do">primary.max_connections.Do</a> someone know the reason why?

I know that standby cluster can not start if standby.max_connections
is higher than primary.max_connections.

<a href="http://www.postgresql.org/docs/9.3/static/hot-standby.html"
target="_blank">http://www.postgresql.org/docs/9.3/static/hot-standby.html</a>
-------
The setting of some parameters on the standby will need reconfiguration 
if they have been changed on the primary. For these parameters, the value on 
the standby must be equal to or greater than the value on the primary. If 
these parameters are not set high enough then the standby will refuse to
start. 
Higher values can then be supplied and the server restarted to begin
recovery again. 
These parameters are:
   max_connections
   max_prepared_transactions
   max_locks_per_transaction

-------

I've also read src/backend/access/transam/xlog.c.
But there are no comments about a reason.
(I cannot find the reason,why standby.max_connections must be higher 
than primary.max_connections.)

I think this restriction obstructs making a flexible load balancing.
I'd like to make standby database to use load balancing.Of course
a role of a standby server is different from one of a master server.
So I think it it natural that I want to set standby.max_connections less than 
primary.max_connections.

thanks & regards</font></pre></strong></div>

On Tue, Dec 10, 2013 at 3:34 AM, 山田聡 <satoshi.yamada.pg@gmail.com> wrote:
> Hello hackers.
>
> I am struggling to understand why standby.max_connections must be higher
> than
> primary.max_connections.Do someone know the reason why?

Because the KnownAssignedXIDs and lock tables on the standby need to
be large enough to contain the largest snapshot and greatest number of
AccessExclusiveLocks that could exist on the master at any given time.

> I think this restriction obstructs making a flexible load balancing.
> I'd like to make standby database to use load balancing.Of course
> a role of a standby server is different from one of a master server.
> So I think it it natural that I want to set standby.max_connections less
> than
> primary.max_connections.

Your load balancer should probably have a configuration setting that
controls how many connections it will try to make to the database, and
you can set that to a lower value than max_connections.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

<div dir="ltr">>> Hello hackers.<br />>><br />>> I am struggling to understand why
standby.max_connectionsmust be higher<br />>> than<br />>> <a
href="http://primary.max_connections.Do">primary.max_connections.Do</a>someone know the reason why?<br /> > <br
/>>Because the KnownAssignedXIDs and lock tables on the standby need to<br />> be large enough to contain the
largestsnapshot and greatest number of<br />> AccessExclusiveLocks that could exist on the master at any given
time.<br/> > <br />>> I think this restriction obstructs making a flexible load balancing.<br />>> I'd
liketo make standby database to use load balancing.Of course<br />>> a role of a standby server is different from
oneof a master server.<br /> >> So I think it it natural that I want to set standby.max_connections less<br
/>>>than<br />>> primary.max_connections.<br />> <br />> Your load balancer should probably have a
configurationsetting that<br /> > controls how many connections it will try to make to the database, and<br />>
youcan set that to a lower value than max_connections.<br />> <br />Hi Robert.<br /><br />I understand the reason
Whystandby.max_connections must be higher<br /> than primary.max_connections.<br /><br />I'll try to restrict sessions
byload balancer.<br /><br />Thanks.<br /></div><div class="gmail_extra"><br /><br /><div class="gmail_quote">2013/12/11
RobertHaas <span dir="ltr"><<a href="mailto:robertmhaas@gmail.com"
target="_blank">robertmhaas@gmail.com</a>></span><br/><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"><div class="im">On Tue, Dec 10, 2013 at 3:34 AM, 山田聡 <<a
href="mailto:satoshi.yamada.pg@gmail.com">satoshi.yamada.pg@gmail.com</a>>wrote:<br /> > Hello hackers.<br />
><br/> > I am struggling to understand why standby.max_connections must be higher<br /> > than<br /> > <a
href="http://primary.max_connections.Do"target="_blank">primary.max_connections.Do</a> someone know the reason why?<br
/><br/></div>Because the KnownAssignedXIDs and lock tables on the standby need to<br /> be large enough to contain the
largestsnapshot and greatest number of<br /> AccessExclusiveLocks that could exist on the master at any given time.<br
/><divclass="im"><br /> > I think this restriction obstructs making a flexible load balancing.<br /> > I'd like
tomake standby database to use load balancing.Of course<br /> > a role of a standby server is different from one of
amaster server.<br /> > So I think it it natural that I want to set standby.max_connections less<br /> > than<br
/>> primary.max_connections.<br /><br /></div>Your load balancer should probably have a configuration setting
that<br/> controls how many connections it will try to make to the database, and<br /> you can set that to a lower
valuethan max_connections.<br /><span class="HOEnZb"><font color="#888888"><br /> --<br /> Robert Haas<br />
EnterpriseDB:<a href="http://www.enterprisedb.com" target="_blank">http://www.enterprisedb.com</a><br /> The Enterprise
PostgreSQLCompany<br /></font></span></blockquote></div><br /></div> 

On Tue, Dec 10, 2013 at 1:17 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Because the KnownAssignedXIDs and lock tables on the standby need to
> be large enough to contain the largest snapshot and greatest number of
> AccessExclusiveLocks that could exist on the master at any given time.

Right. Initially during the development of Hot Standby, it looked like
the "max_connections >= master's" requirement on standbys wasn't going
to be necessary, or could be avoided. However, Simon gave up on that
idea on pragmatic grounds here:

http://www.postgresql.org/message-id/1252002165.2889.467.camel@ebony.2ndQuadrant

I'd thought about revisiting this myself, but I think that the impetus
to do so is lessened by recent work on logical replication.

-- 
Peter Geoghegan

<div dir="ltr">>> Because the KnownAssignedXIDs and lock tables on the standby need to<br />>> be large
enoughto contain the largest snapshot and greatest number of<br />>> AccessExclusiveLocks that could exist on the
masterat any given time.<br /> > <br />> Right. Initially during the development of Hot Standby, it looked
like<br/>> the "max_connections >= master's" requirement on standbys wasn't going<br />> to be necessary, or
couldbe avoided. However, Simon gave up on that<br /> > idea on pragmatic grounds here:<br />> <br />> <a
href="http://www.postgresql.org/message-id/1252002165.2889.467.camel@ebony.2ndQuadrant">http://www.postgresql.org/message-id/1252002165.2889.467.camel@ebony.2ndQuadrant</a><br
/>> <br />> I'd thought about revisiting this myself, but I think that the impetus<br />> to do so is lessened
byrecent work on logical replication.<br />> <br /><br />Hi Peter<br /><br />Your information make my question be
clearly.<br/> I understand the discussions about this restriction.<br /><br />Thanks.<br /><br /></div><div
class="gmail_extra"><br/><br /><div class="gmail_quote">2013/12/12 Peter Geoghegan <span dir="ltr"><<a
href="mailto:pg@heroku.com"target="_blank">pg@heroku.com</a>></span><br /><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">On Tue, Dec 10, 2013 at 1:17 PM,
RobertHaas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>> wrote:<br /> > Because the
KnownAssignedXIDsand lock tables on the standby need to<br /> > be large enough to contain the largest snapshot and
greatestnumber of<br /> > AccessExclusiveLocks that could exist on the master at any given time.<br /><br
/></div>Right.Initially during the development of Hot Standby, it looked like<br /> the "max_connections >=
master's"requirement on standbys wasn't going<br /> to be necessary, or could be avoided. However, Simon gave up on
that<br/> idea on pragmatic grounds here:<br /><br /><a
href="http://www.postgresql.org/message-id/1252002165.2889.467.camel@ebony.2ndQuadrant"
target="_blank">http://www.postgresql.org/message-id/1252002165.2889.467.camel@ebony.2ndQuadrant</a><br/><br /> I'd
thoughtabout revisiting this myself, but I think that the impetus<br /> to do so is lessened by recent work on logical
replication.<br/><span class="HOEnZb"><font color="#888888"><br /> --<br /> Peter Geoghegan<br
/></font></span></blockquote></div><br/></div>