0

Edb postgresql instance is producing more number of WAL file segments ( each 16 MB ) in size and mount point is getting filled up .

How can the WAL file size be reduced and increase the checkpoint

Shog9
  • 156,901
  • 35
  • 231
  • 235
Dheeraj Nambiar
  • 21
  • 1
  • walsize is set on compile. you cant change it on working cluster. you can swith off level to minimal if you really dont need wals - but you have to be sure of it. alse there is archive_command that can help you moving wals to some other location – Vao Tsun Apr 04 '18 at 11:40
  • Do you really want to increase checkpoint execution? It can degrade performance of your server instance. PostgreSQL has a warning in log talking just about it [here](https://github.com/postgres/postgres/blob/9d4649ca49416111aee2c84b7e4441a0b7aa2fac/src/backend/postmaster/checkpointer.c#L464) – Michel Milezzi Apr 04 '18 at 12:10
  • Does switching wal_level to minimal effect the point in time backup ? Currently we have set it on replica – Dheeraj Nambiar Apr 04 '18 at 12:50
  • you can't pitr with minimal level, and btw it is covered in first link in my question – Vao Tsun Apr 04 '18 at 15:09

1 Answers1

1

you can change the amount of data to be written to wal with:

https://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-WAL-LEVEL

wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. minimal removes all logging except the information required to recover from a crash or immediate shutdown. Finally, logical adds information necessary to support logical decoding. Each level includes the information logged at all lower levels. This parameter can only be set at server start.

also, you might want to write same amount of data, but configure archival command (so you would get rid of archived wals and release space without loosing them):

https://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL

also check wal_keep_segments and max_wal_size...

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132