Subject: Re: Improve handling of parameter differences in physical replication
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 masahiko.sawada@2ndquadrant.com
References: 
 <CA+fd4k4ehrboiEB8GFbe-gpiVyFO_cPE57ZpBhPUgJtpCdhC7Q@mail.gmail.com>
 <ecd7ad33-c4dd-7587-ec0e-87729852bc3e@2ndquadrant.com>
 <CA+fd4k4G8pFDha522kPAh_H2d3be6ejc56H932h_xHXPA+mV5Q@mail.gmail.com>
 <20200310.175755.1250389993502025042.horikyota.ntt@gmail.com>
Cc: alvherre@2ndquadrant.com, pgsql-hackers@postgresql.org
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <aa078efe-2d25-fdab-5bdb-158edab5d849@2ndquadrant.com>
Date: Tue, 10 Mar 2020 14:47:47 +0100
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <20200310.175755.1250389993502025042.horikyota.ntt@gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 2020-03-10 09:57, Kyotaro Horiguchi wrote:
>> Well I meant to periodically send warning messages while waiting for
>> parameter change, that is after exhausting resources and stopping
>> recovery. In this situation user need to notice that as soon as
>> possible.
> 
> If we lose connection, standby continues to complain about lost
> connection every 5 seconds.  This is a situation of that kind.

My argument is that it's not really the same.  If a standby is 
disconnected for more than a few minutes, it's really not going to be a 
good standby anymore after a while.  In this case, however, having 
certain parameter discrepancies is really harmless and you can run with 
it for a long time.  I'm not strictly opposed to a periodic warning, but 
it's unclear to me how we would find a good interval.

> By the way, when I reduced max_connection only on master then take
> exclusive locks until standby complains on lock exchaustion, I see a
> WARNING that is saying max_locks_per_transaction instead of
> max_connection.
> 
> 
> WARNING:  insufficient setting for parameter max_connections
> DETAIL:  max_connections = 2 is a lower setting than on the master server (where its value was 3).
> HINT:  Change parameters and restart the server, or there may be resource exhaustion errors sooner or later.
> CONTEXT:  WAL redo at 0/60000A0 for XLOG/PARAMETER_CHANGE: max_connections=3 max_worker_processes=8 max_wal_senders=2 max_prepared_xacts=0 max_locks_per_xact=10 wal_level=replica wal_log_hints=off track_commit_timestamp=off
> WARNING:  recovery paused because of insufficient setting of parameter max_locks_per_transaction (currently 10)
> DETAIL:  The value must be at least as high as on the primary server.
> HINT:  Recovery cannot continue unless the parameter is changed and the server restarted.
> CONTEXT:  WAL redo at 0/6004A80 for Standb

This is all a web of half-truths.  The lock tables are sized based on 
max_locks_per_xact * (MaxBackends + max_prepared_xacts).  So if you run 
out of lock space, we currently recommend (in the single-server case), 
that you raise max_locks_per_xact, but you could also raise 
max_prepared_xacts or something else.  So this is now the opposite case 
where the lock table on the master was bigger because of max_connections.

We could make the advice less specific and just say, in essence, you 
need to make some parameter changes; see earlier for some hints.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services