Postgres Pro
Downloads
Home   >   mailing lists

Thread: Can we limit the total size of WAL?

Can we limit the total size of WAL?

From
FattahRozzaq
Date:
Hi all,

I hope this email finds you well. I'm new to PostgreSQL, please
correct me if I'm mislead/incorrect.


I have setup streaming replication using WAL
The archive_command that was generated by Postgres-XL in postgresql.conf was:
archive_command = 'rsync %p
postgres@node03:/home/postgres/PGXL/nodes/datanodearchlog/02/%f'


If I run check the total size of the folder in datanode master, it
contains only 14GB:
# du -sh /home/postgres/PGXL/nodes/datanodemaster/03/
14G     /home/postgres/PGXL/nodes/datanodemaster/03/


But if I check the total size of the archive folder in datanode slave,
it contains 90GB, each file has 16MB size, with total files 5749:
# du -sh /home/postgres/PGXL/nodes/datanodearchlog/03/
90G     /home/postgres/PGXL/nodes/datanodearchlog/03/
# ls -al /home/postgres/PGXL/nodes/datanodearchlog/03/ | grep 16777216 | wc -l
5749


How to limit the amount of files generated by WAL archiving?
Or maybe if we can, how to recycle the files generated by WAL archiving?


Thank you,
FattahRozzaq

Re: Can we limit the total size of WAL?

From
Michael Paquier
Date:
On Wed, Sep 9, 2015 at 3:41 PM, FattahRozzaq <ssoorruu@gmail.com> wrote:
> The archive_command that was generated by Postgres-XL in postgresql.conf was:

When using Postgres-XL, I guess that asking directly the XL guys is
more appropriate than here, but well your question does apply to
Postgres as well...

> But if I check the total size of the archive folder in datanode slave,
> it contains 90GB, each file has 16MB size, with total files 5749:
> # du -sh /home/postgres/PGXL/nodes/datanodearchlog/03/
> 90G     /home/postgres/PGXL/nodes/datanodearchlog/03/
> # ls -al /home/postgres/PGXL/nodes/datanodearchlog/03/ | grep 16777216 | wc -l
> 5749

Normally standbys don't do archiving (Except in 9.5~ but XL has just
synced up with it). So I am not really following. Aren't those files
generated and archived by a master datanode?

> How to limit the amount of files generated by WAL archiving?
> Or maybe if we can, how to recycle the files generated by WAL archiving?

pg_archivecleanup [1] is one method by setting up
archive_cleanup_command in the recovery.conf file of the involved
standby. Now be careful of two things:
1) Is another standby node in need of those files? Be careful to not
cleanup an archive folder when multiple nodes fetch from it.
2) Do you expect a set of base backups to need those files or not?

[1]: http://www.postgresql.org/docs/devel/static/pgarchivecleanup.html
Regards,
--
Michael