Configure checkpoint timing and Write Ahead Log ( WAL ) limits

[Ivansete-status]

The disk volume that we started to contain the Postgres database is 40GB.

With that, I suggest explicitly configuring the default checkpoint/WAL settings and tuning them if we detect that the WAL files (pg_wal) size increases too much.

For that, I suggest explicitly configuring the following:

checkpoint_timeout = 5min
max_wal_size = 1GB
min_wal_size = 80MB

ℹ️ there is no way to directly limit the WAL size kept by Postgres. We need to make it indirectly through those settings.

• checkpoint_timeout: Determines the maximum time interval between automatic WAL checkpoints.
• max_wal_size: Specifies the maximum size of the WAL files in bytes before a checkpoint is triggered.
• min_wal_size: Specifies the minimum size of the WAL files needed before a checkpoint is triggered.

[jakubgs]

The best way to do this is most probably using the ALTER SYSTEM command which writes to postgresql.auto.conf:
https://www.postgresql.org/docs/current/sql-altersystem.html

We could edit postgresql.conf with Ansible, but that is already being created by the container, so I'd rather avoid that.

[jakubgs]

Done:

• infra-role-postgres-ha#de802298 - reinit: run init scripts after setup
• infra-role-postgres-ha#9fbe3851 - init: script to modify settings using alter system

And shards fleet changes:

• infra-shards#32662b3b - store-db: add Write Ahead Log size limits

If any other fleets need this please tell me.