How should I update max_wal_size using fly postgres?

Questions / Help
1

I keep getting this in my logs:

LOG: checkpoints are occurring too frequently (2 seconds apart)
HINT: Consider increasing the configuration parameter "max_wal_size".

I tried:

flyctl postgres config update --max-wal-size

But got Error: unknown flag: --max-wal-size

Then I tried in PSQL:

=# ALTER SYSTEM SET max_wal_size = '500MB';
ERROR:  could not fsync file "postgresql.auto.conf": Invalid argument

Any ideas? Thanks!

2

Hi Alex,

I think what’s happening here is that you’re using the Stolon-based Postgres image (14.x without -flex in the name). I wasn’t able to check this conclusively because I don’t know which app this is referring to.

If the above is true, Stolon is managing all the files and will overwrite them if you don’t do the config in a way that Stolon is aware of and can handle; this is very likely because of the need to replicate this config to the entire cluster.

This should work: and has three steps.

  1. Back up your database. Or ensure you can restore from a recent snapshot.
  2. Make sure you do not skip step 1
  3. ssh into one of your Postgres units. Ideally the leader! then:
export $(cat /data/.env | xargs)
# Careful with quoting below, it's inline json, danger danger
stolonctl update --patch -f <(echo '{"pgParameters": {"max_wal_size": "500MB"}}')

After this you can confirm that the value was updated in the Stolon-managed postgres.conf and also in pg_settings. I tried rebooting a few of my units as well and the change seems to stick nicely if done this way.

  • Daniel
3

Hey Alex,

I’ve used the solution Daniel has proposed as well several times. I changed a few other things after using this calculator. https://pgtune.leopard.in.ua/

You’ll have to restart your instance after making the changes.

4

Thank you so much both, these are both extremely helpful

5

From How To to Questions / Help

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.