Barman-wal-archive speed

[Geraldine Oo]

Hello All,

I notice that when there is a lot of activity going on in the DB, the WALs don't get archived fast enough and our pg_wal fills up. Is there any way to speed up WAL archiving with barman-wal-archive? 

Thanks

[Israel Barth]

Hello Geraldine,

Have you evaluated using the streaming_archiver (pg_receivewal in the Barman host) instead of archiver (barman-wal-archive as archive_command)?

Depending on the workload and on the latency between your Postgres and Barman hosts, the overhead of opening a SSH connection each time the archive_command is executed might be an issue.

With the streaming archiver, you would have a persistent streaming connection to ship WALs from Postgres to Barman, which would be more efficient.

Best regards,
Israel.

--
--
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.
To view this discussion, visit https://groups.google.com/d/msgid/pgbarman/6cc60b9b-1b5c-4cdf-8ac8-5c26151f7b40n%40googlegroups.com.

[Geraldine Oo]

Hello Israel,

Thanks for the suggestion. Actually I enabled both the WAL streaming and the archiving. I guess, it is a redundant to have both WAL streaming and archiving, but I recall in the documentation (can't remember which version) this is an added safety incase streaming fails for some reason.

So far it works pretty well I think. It's just I find when we have very high activity, the archiving is falling behind and causes our pg_wal to fill up. 

[Jeff Janes]

On Sun, Feb 23, 2025 at 9:58 PM Geraldine Oo <gerald...@gmail.com> wrote:

Hello Israel,

Thanks for the suggestion. Actually I enabled both the WAL streaming and the archiving. I guess, it is a redundant to have both WAL streaming and archiving, but I recall in the documentation (can't remember which version) this is an added safety incase streaming fails for some reason.

It is not much of an added safety.  If you use a slot, streaming failure will keep retrying until it works, it does not just throw up its hands and quit.  If you have persistent network connectivity issues, then both methods are likely to fail together.  If you have persistent authentication issues, then one method might work while the other does not (because they initiate from different ends) but your best solution here is to identify the problem and fix it. You should see messages in the log files about failures. If streaming fails due to authentication issues, the base backup likely will as well, so you still need to get it figured out.

 

So far it works pretty well I think. It's just I find when we have very high activity, the archiving is falling behind and causes our pg_wal to fill up. 

It works pretty well except for when it doesn't?  If you send all the WAL using two different methods, then it will take twice as much bandwidth.  If you don't have the bandwidth to spare, then you will absolutely have a problem caused by your dubious "safety" feature.

If you just use "scp" to send data over the network, how long does it take to send a 1Gig file?  How long to send 64 files of 16 MB, each with a different scp command line?  How does these numbers compare to the usual rate of WAL generation?

Cheers,

Jeff