Possible to disable Barman to archive WAL files for a certain period of time?

[Amanda Simpson]

Hi,

I am in the process of setting up barman and fine tuning it to what we will need in production server. My system spec is Ubuntu 14.04, Postgresql 9.1 and Barman 1.3.0.

I am using two testing servers, one hosts our database and one hosts Barman. No one is using the database server except me.I occasionally delete one record in a table in the database. I ended up deleted 3 rows and postgres generated 3 WAL files at those times. During this experiment, I found out that because a daily vacuum full is performed on the database server, Barman will copy all those WAL files generated on the primary server during that time as it is supposed to. Those WAL files generated during the vacuum process consume a lot of space in the backup server, and we really cannot afford that. (Our database is about 59GiB and those WAL files total up to 41GiB)

My question is that is there any way to either

1. disable Barman to copy those WAL files for a certain period of time, or 

2. delete those WAL files after it got copied over from primary to backup server? 

I can also run a cron job to delete WAL files produced between a certain time of the day, but if I do this, will it affect Barman to perform recovery since WAL are all sequentially named?

I also looked into compressing WAL files, but gzip is about 27% compression, so I will still end up with 10G of those undesired WAL files. WAL compression will help but will not solve my problem. 

On the other note, I am not familiar with performing vacuum full so any input on running vacuum full daily will be appreciated. We did enable autovacuum on postgres server. 

Thank you so much for your help.

Amanda

[Gabriele Bartolini]

Hi Amanda,

  Barman does not support suspension of servers. What you need to do is simply change the 'archive_command' to - for example - '/bin/true' before performing vacuum full, then reinstate it and execute a full backup again.

  Remember that changes to archive_command require reload of Postgres.

Cheers,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support
 gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB

---
You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Amanda Simpson]

Hello Gabriele,

Thank you for your suggestion. I ended up writing a script to change archive_command from remote barman dir to /bin/true' before vacuum full, then switch it back after vacuum full is done. WALs are still generated on the primary server during vacuum full, but not shipped to Barman backup server. (This method will create out-of-sync WALs on Barman backup. )

I also later restored the primary with Barman with out-of-sync WALs with no problem. Noted that if you deleted WALs right in Barman directory, restore will report error.

Thanks again,

Amanda