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