How to avoid a big log file with WAL archiving?

Question

I enabled WAL archiving in EDB Postgresql 9.6 for PITR, but now every time a 16MB log file is created and filling Disk volume. How do I avoid that?

These are the changes made to Postgresql.conf to enable the wal archive:

wal_level = replica
archive_mode =  on
archive_command = 'cp %p /postgres/cluster/wals/%f'

(cp from pg_xlogs to wals folder)

Now the wal folder is filling every time.

score 0 · Accepted Answer · answered Apr 06 '18 at 13:03

0

You avoid filling up the destination directory by

PostgreSQL does not automatically delete WAL archives for you — it does not even know where they are.

answered Apr 06 '18 at 13:03

Laurenz Albe

we configured similar in testing environment but without any data in database how come log files are being created everyday. – SQLDBA Apr 06 '18 at 13:11
I don't know about which database your question is, but WAL is generated whenever anything in the database is modified. – Laurenz Albe Apr 06 '18 at 13:18
-rw------- 1 edbpostgres edbpostgres 16M Apr 2 20:00 000000020000000000000018 -rw------- 1 edbpostgres edbpostgres 16M Apr 3 20:00 000000020000000000000019 -rw------- 1 edbpostgres edbpostgres 16M Apr 4 20:00 00000002000000000000001A -rw------- 1 edbpostgres edbpostgres 16M Apr 5 20:00 00000002000000000000001B I know default log file size is 16MB but really dont know why everyday 16MB log file is being created without any transcation.(test environment) – SQLDBA Apr 06 '18 at 13:32
Well, *some* activity is generated. Use `pg_xlogdump` to read the contents of a WAL archive. Set `archive_mode = off` if you don't care. – Laurenz Albe Apr 06 '18 at 13:41

1 Answers1