Thread: Primary and standby setting cross-checks

Primary and standby setting cross-checks

From

Heikki Linnakangas

Date:

29 August 2024, 21:52:06

Currently, if you configure a hot standby server with a smaller 
max_connections setting than the primary, the server refuses to start up:

LOG:  entering standby mode
FATAL:  recovery aborted because of insufficient parameter settings
DETAIL:  max_connections = 10 is a lower setting than on the primary 
server, where its value was 100.
HINT:  You can restart the server after making the necessary 
configuration changes.

Or if you change the setting in the primary while the standby is 
running, replay pauses:

WARNING:  hot standby is not possible because of insufficient parameter 
settings
DETAIL:  max_connections = 100 is a lower setting than on the primary 
server, where its value was 200.
CONTEXT:  WAL redo at 2/E10000D8 for XLOG/PARAMETER_CHANGE: 
max_connections=200 max_worker_processes=8 max_wal_senders=10 
max_prepared_xacts=0 max_locks_per_xact=64 wal_level=logical 
wal_log_hints=off track_commit_timestamp=off
LOG:  recovery has paused
DETAIL:  If recovery is unpaused, the server will shut down.
HINT:  You can then restart the server after making the necessary 
configuration changes.
CONTEXT:  WAL redo at 2/E10000D8 for XLOG/PARAMETER_CHANGE: 
max_connections=200 max_worker_processes=8 max_wal_senders=10 
max_prepared_xacts=0 max_locks_per_xact=64 wal_level=logical 
wal_log_hints=off track_commit_timestamp=off

Both of these are rather unpleasant behavior.

I thought I could get rid of that limitation with my CSN snapshot patch 
[1], because it gets rid of the fixed-size known-assigned XIDs array, 
but there's a second reason for these limitations. It's also used to 
ensure that the standby has enough space in the lock manager to hold 
possible AccessExclusiveLocks taken by transactions in the primary.

So firstly, I think that's a bad tradeoff. In vast majority of cases, 
you would not run out of lock space anyway, if you just started up the 
system. Secondly, that cross-check of settings doesn't fully prevent the 
problem. It ensures that the lock tables are large enough to accommodate 
all the locks you could possibly hold in the primary, but that doesn't 
take into account any additional locks held by read-only queries in the 
hot standby. So if you have queries running in the standby that take a 
lot of locks, this can happen anyway:

2024-08-29 21:44:32.634 EEST [668327] FATAL:  out of shared memory
2024-08-29 21:44:32.634 EEST [668327] HINT:  You might need to increase 
"max_locks_per_transaction".
2024-08-29 21:44:32.634 EEST [668327] CONTEXT:  WAL redo at 2/FD40FCC8 
for Standby/LOCK: xid 996 db 5 rel 154045
2024-08-29 21:44:32.634 EEST [668327] WARNING:  you don't own a lock of 
type AccessExclusiveLock
2024-08-29 21:44:32.634 EEST [668327] LOG:  RecoveryLockHash contains 
entry for lock no longer recorded by lock manager: xid 996 database 5 
relation 154045
TRAP: failed Assert("false"), File: 
"../src/backend/storage/ipc/standby.c", Line: 1053, PID: 668327
postgres: startup recovering 
0000000100000002000000FD(ExceptionalCondition+0x6e)[0x556a4588396e]
postgres: startup recovering 
0000000100000002000000FD(+0x44156e)[0x556a4571356e]
postgres: startup recovering 
0000000100000002000000FD(StandbyReleaseAllLocks+0x78)[0x556a45712738]
postgres: startup recovering 
0000000100000002000000FD(ShutdownRecoveryTransactionEnvironment+0x15)[0x556a45712685]
postgres: startup recovering 
0000000100000002000000FD(shmem_exit+0x111)[0x556a457062e1]
postgres: startup recovering 
0000000100000002000000FD(+0x434132)[0x556a45706132]
postgres: startup recovering 
0000000100000002000000FD(proc_exit+0x59)[0x556a45706079]
postgres: startup recovering 
0000000100000002000000FD(errfinish+0x278)[0x556a45884708]
postgres: startup recovering 
0000000100000002000000FD(LockAcquireExtended+0xa46)[0x556a45719386]
postgres: startup recovering 
0000000100000002000000FD(StandbyAcquireAccessExclusiveLock+0x11d)[0x556a4571330d]
postgres: startup recovering 
0000000100000002000000FD(standby_redo+0x70)[0x556a45713690]
postgres: startup recovering 
0000000100000002000000FD(PerformWalRecovery+0x7b3)[0x556a4547d313]
postgres: startup recovering 
0000000100000002000000FD(StartupXLOG+0xac3)[0x556a4546dae3]
postgres: startup recovering 
0000000100000002000000FD(StartupProcessMain+0xe8)[0x556a45693558]
postgres: startup recovering 
0000000100000002000000FD(+0x3ba95d)[0x556a4568c95d]
postgres: startup recovering 
0000000100000002000000FD(+0x3bce41)[0x556a4568ee41]
postgres: startup recovering 
0000000100000002000000FD(PostmasterMain+0x116e)[0x556a4568eaae]
postgres: startup recovering 
0000000100000002000000FD(+0x2f960e)[0x556a455cb60e]
/lib/x86_64-linux-gnu/libc.so.6(+0x27c8a)[0x7f10ef042c8a]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0x85)[0x7f10ef042d45]
postgres: startup recovering 
0000000100000002000000FD(_start+0x21)[0x556a453af011]
2024-08-29 21:44:32.641 EEST [668324] LOG:  startup process (PID 668327) 
was terminated by signal 6: Aborted
2024-08-29 21:44:32.641 EEST [668324] LOG:  terminating any other active 
server processes
2024-08-29 21:44:32.654 EEST [668324] LOG:  shutting down due to startup 
process failure
2024-08-29 21:44:32.729 EEST [668324] LOG:  database system is shut down

Granted, if you restart the server, it will probably succeed because 
restarting the server will kill all the other queries that were holding 
locks. But yuck. With assertions disabled, it looks a little less scary, 
but not nice anyway.

So how to improve this? I see a few options:

a) Downgrade the error at startup to a warning, and allow starting the 
standby with smaller settings in standby. At least with a smaller 
max_locks_per_transactions. The other settings also affect the size of 
known-assigned XIDs array, but if the CSN snapshots get committed, that 
will get fixed. In most cases there is enough lock memory anyway, and it 
will be fine. Just fix the assertion failure so that the error message 
is a little nicer.

b) If you run out of lock space, kill running queries, and prevent new 
ones from starting. Track the locks in startup process' private memory 
until there is enough space in the lock manager, and then re-open for 
queries. In essence, go from hot standby mode to warm standby, until 
it's possible to go back to hot standby mode again.

Thoughts, better ideas?

[1] https://commitfest.postgresql.org/49/4912/

-- 
Heikki Linnakangas
Neon (https://neon.tech)

Re: Primary and standby setting cross-checks

From

Noah Misch

Date:

25 September 2024, 06:03:45

On Thu, Aug 29, 2024 at 09:52:06PM +0300, Heikki Linnakangas wrote:
> Currently, if you configure a hot standby server with a smaller
> max_connections setting than the primary, the server refuses to start up:
> 
> LOG:  entering standby mode
> FATAL:  recovery aborted because of insufficient parameter settings
> DETAIL:  max_connections = 10 is a lower setting than on the primary server,
> where its value was 100.

> happen anyway:
> 
> 2024-08-29 21:44:32.634 EEST [668327] FATAL:  out of shared memory
> 2024-08-29 21:44:32.634 EEST [668327] HINT:  You might need to increase
> "max_locks_per_transaction".
> 2024-08-29 21:44:32.634 EEST [668327] CONTEXT:  WAL redo at 2/FD40FCC8 for
> Standby/LOCK: xid 996 db 5 rel 154045
> 2024-08-29 21:44:32.634 EEST [668327] WARNING:  you don't own a lock of type
> AccessExclusiveLock
> 2024-08-29 21:44:32.634 EEST [668327] LOG:  RecoveryLockHash contains entry
> for lock no longer recorded by lock manager: xid 996 database 5 relation
> 154045
> TRAP: failed Assert("false"), File: "../src/backend/storage/ipc/standby.c",

> Granted, if you restart the server, it will probably succeed because
> restarting the server will kill all the other queries that were holding
> locks. But yuck.

Agreed.

> So how to improve this? I see a few options:
> 
> a) Downgrade the error at startup to a warning, and allow starting the
> standby with smaller settings in standby. At least with a smaller
> max_locks_per_transactions. The other settings also affect the size of
> known-assigned XIDs array, but if the CSN snapshots get committed, that will
> get fixed. In most cases there is enough lock memory anyway, and it will be
> fine. Just fix the assertion failure so that the error message is a little
> nicer.
> 
> b) If you run out of lock space, kill running queries, and prevent new ones
> from starting. Track the locks in startup process' private memory until
> there is enough space in the lock manager, and then re-open for queries. In
> essence, go from hot standby mode to warm standby, until it's possible to go
> back to hot standby mode again.

Either seems fine.  Having never encountered actual lock exhaustion from this,
I'd lean toward (a) for simplicity.

> Thoughts, better ideas?

I worry about future code assuming a MaxBackends-sized array suffices for
something.  That could work almost all the time, breaking only when a standby
replays WAL from a server having a larger array.  What could we do now to
catch that future mistake promptly?  As a start, 027_stream_regress.pl could
use low settings on its standby.

Re: Primary and standby setting cross-checks

From

Kirill Reshke

Date:

12 March, 21:09:19

On Thu, 29 Aug 2024 at 23:52, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>
> Currently, if you configure a hot standby server with a smaller
> max_connections setting than the primary, the server refuses to start up:
>
> LOG:  entering standby mode
> FATAL:  recovery aborted because of insufficient parameter settings
> DETAIL:  max_connections = 10 is a lower setting than on the primary
> server, where its value was 100.
> HINT:  You can restart the server after making the necessary
> configuration changes.
>
> Or if you change the setting in the primary while the standby is
> running, replay pauses:
>
> WARNING:  hot standby is not possible because of insufficient parameter
> settings
> DETAIL:  max_connections = 100 is a lower setting than on the primary
> server, where its value was 200.
> CONTEXT:  WAL redo at 2/E10000D8 for XLOG/PARAMETER_CHANGE:
> max_connections=200 max_worker_processes=8 max_wal_senders=10
> max_prepared_xacts=0 max_locks_per_xact=64 wal_level=logical
> wal_log_hints=off track_commit_timestamp=off
> LOG:  recovery has paused
> DETAIL:  If recovery is unpaused, the server will shut down.
> HINT:  You can then restart the server after making the necessary
> configuration changes.
> CONTEXT:  WAL redo at 2/E10000D8 for XLOG/PARAMETER_CHANGE:
> max_connections=200 max_worker_processes=8 max_wal_senders=10
> max_prepared_xacts=0 max_locks_per_xact=64 wal_level=logical
> wal_log_hints=off track_commit_timestamp=off
>
> Both of these are rather unpleasant behavior.
>
> I thought I could get rid of that limitation with my CSN snapshot patch
> [1], because it gets rid of the fixed-size known-assigned XIDs array,
> but there's a second reason for these limitations. It's also used to
> ensure that the standby has enough space in the lock manager to hold
> possible AccessExclusiveLocks taken by transactions in the primary.
>
> So firstly, I think that's a bad tradeoff. In vast majority of cases,
> you would not run out of lock space anyway, if you just started up the
> system. Secondly, that cross-check of settings doesn't fully prevent the
> problem. It ensures that the lock tables are large enough to accommodate
> all the locks you could possibly hold in the primary, but that doesn't
> take into account any additional locks held by read-only queries in the
> hot standby. So if you have queries running in the standby that take a
> lot of locks, this can happen anyway:
>
> 2024-08-29 21:44:32.634 EEST [668327] FATAL:  out of shared memory
> 2024-08-29 21:44:32.634 EEST [668327] HINT:  You might need to increase
> "max_locks_per_transaction".
> 2024-08-29 21:44:32.634 EEST [668327] CONTEXT:  WAL redo at 2/FD40FCC8
> for Standby/LOCK: xid 996 db 5 rel 154045
> 2024-08-29 21:44:32.634 EEST [668327] WARNING:  you don't own a lock of
> type AccessExclusiveLock
> 2024-08-29 21:44:32.634 EEST [668327] LOG:  RecoveryLockHash contains
> entry for lock no longer recorded by lock manager: xid 996 database 5
> relation 154045
> TRAP: failed Assert("false"), File:
> "../src/backend/storage/ipc/standby.c", Line: 1053, PID: 668327
> postgres: startup recovering
> 0000000100000002000000FD(ExceptionalCondition+0x6e)[0x556a4588396e]
> postgres: startup recovering
> 0000000100000002000000FD(+0x44156e)[0x556a4571356e]
> postgres: startup recovering
> 0000000100000002000000FD(StandbyReleaseAllLocks+0x78)[0x556a45712738]
> postgres: startup recovering
> 0000000100000002000000FD(ShutdownRecoveryTransactionEnvironment+0x15)[0x556a45712685]
> postgres: startup recovering
> 0000000100000002000000FD(shmem_exit+0x111)[0x556a457062e1]
> postgres: startup recovering
> 0000000100000002000000FD(+0x434132)[0x556a45706132]
> postgres: startup recovering
> 0000000100000002000000FD(proc_exit+0x59)[0x556a45706079]
> postgres: startup recovering
> 0000000100000002000000FD(errfinish+0x278)[0x556a45884708]
> postgres: startup recovering
> 0000000100000002000000FD(LockAcquireExtended+0xa46)[0x556a45719386]
> postgres: startup recovering
> 0000000100000002000000FD(StandbyAcquireAccessExclusiveLock+0x11d)[0x556a4571330d]
> postgres: startup recovering
> 0000000100000002000000FD(standby_redo+0x70)[0x556a45713690]
> postgres: startup recovering
> 0000000100000002000000FD(PerformWalRecovery+0x7b3)[0x556a4547d313]
> postgres: startup recovering
> 0000000100000002000000FD(StartupXLOG+0xac3)[0x556a4546dae3]
> postgres: startup recovering
> 0000000100000002000000FD(StartupProcessMain+0xe8)[0x556a45693558]
> postgres: startup recovering
> 0000000100000002000000FD(+0x3ba95d)[0x556a4568c95d]
> postgres: startup recovering
> 0000000100000002000000FD(+0x3bce41)[0x556a4568ee41]
> postgres: startup recovering
> 0000000100000002000000FD(PostmasterMain+0x116e)[0x556a4568eaae]
> postgres: startup recovering
> 0000000100000002000000FD(+0x2f960e)[0x556a455cb60e]
> /lib/x86_64-linux-gnu/libc.so.6(+0x27c8a)[0x7f10ef042c8a]
> /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0x85)[0x7f10ef042d45]
> postgres: startup recovering
> 0000000100000002000000FD(_start+0x21)[0x556a453af011]
> 2024-08-29 21:44:32.641 EEST [668324] LOG:  startup process (PID 668327)
> was terminated by signal 6: Aborted
> 2024-08-29 21:44:32.641 EEST [668324] LOG:  terminating any other active
> server processes
> 2024-08-29 21:44:32.654 EEST [668324] LOG:  shutting down due to startup
> process failure
> 2024-08-29 21:44:32.729 EEST [668324] LOG:  database system is shut down
>
> Granted, if you restart the server, it will probably succeed because
> restarting the server will kill all the other queries that were holding
> locks. But yuck. With assertions disabled, it looks a little less scary,
> but not nice anyway.
>
> So how to improve this? I see a few options:
>
> a) Downgrade the error at startup to a warning, and allow starting the
> standby with smaller settings in standby. At least with a smaller
> max_locks_per_transactions. The other settings also affect the size of
> known-assigned XIDs array, but if the CSN snapshots get committed, that
> will get fixed. In most cases there is enough lock memory anyway, and it
> will be fine. Just fix the assertion failure so that the error message
> is a little nicer.
>
> b) If you run out of lock space, kill running queries, and prevent new
> ones from starting. Track the locks in startup process' private memory
> until there is enough space in the lock manager, and then re-open for
> queries. In essence, go from hot standby mode to warm standby, until
> it's possible to go back to hot standby mode again.
>
> Thoughts, better ideas?
>
> [1] https://commitfest.postgresql.org/49/4912/
>
> --
> Heikki Linnakangas
> Neon (https://neon.tech)
>
>

Hello! Do you intend to pursue this further?

-- 
Best regards,
Kirill Reshke