1

I am having problems disabling the generation of WAL files in a non-productive database.

Although in the postgresql.conf file I have disabled WAL generation, I see that they are still generated with any DML statement executed.

In the configuration file I have modified these options:

wal_level = logical
archive_mode = off
archive_command = false

I have tried to restart the DB and still, WAL files are still generated. If I access the DB, I can see that it is deactivated:

[local] postgres@db1=# select name,setting from pg_settings where name like 'archive%' ;
         name setting
----------------------- ----------
archive_cleanup_command
archive_command (disabled)
archive_mode off
archive_timeout 0
(4 rows)
Improve this question

1 Answer 1

Reset to default
0

That's normal. Disabling archive mode does not keep WAL from being generated (in pg_wal), only from being archived. There is no to avoid generating WAL, but that is no problem, since it will get removed again.

However, perhaps that is not your problem, and your problem is that WAL is not getting removed. In that case, the problem is one of these three reasons:

  • the archiver encounters an error (this cannot be your problem, because you disabled archiving)

  • you have got a stale replication slot

  • you set wal_keep_size too high

Improve this answer
6
  • Indeed, WALs generated in pg_wal are not automatically deleted. This causes me to fill up the Filesystem and crash the DB when the development team launches hundreds of thousands of DML (insert/update/delete) operations on the DB tables. I have checked in the postgresql.conf file the wal_keep_size parameter but it is not defined. I have made a cat of the lines with size and this is what I get: max_wal_size = 1GB min_wal_size = 128MB effective_cache_size = 32MB log_rotation_size = 512MB Do any of them apply to self-deletion?
    – lk2_89
    Nov 16 at 19:50
  • No; I wrote all the possible causes in my answer. Look at the linked article for details. Perhaps you should set wal_level = minimal in a test database, that would reduce the problem.
    – Laurenz Albe
    Nov 16 at 20:00
  • I don't understand then the logic of the "archive_mode = off" parameter in the config file. isn't this supposed to be similar to disabling archivelog mode in Oracle?
    – lk2_89
    Nov 16 at 20:46
  • Yes, that is exactly the same thing.
    – Laurenz Albe
    Nov 16 at 21:01
  • In Oracle, if the DB has the archivelog mode disabled, it does not generate archive files. Maybe that's why I find it hard to understand the Postgre parameter "archive_mode=off".
    – lk2_89
    Nov 17 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.