Re: Improve handling of parameter differences in physical replication

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=0max_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