WAL segment was not archived before the timeout

[rstander]

pgBackRest version: 2.47

PostgreSQL version: PostgreSQL 15.4 (Ubuntu 15.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

Operating system/version - Ubuntu 22.04

Did you install pgBackRest from source or from a package? Yes

Please attach the following as applicable:
Config on backup server
[global]
repo1-path=/backup_data/
repo1-retention-full=2
start-fast=y
archive-timeout=300
compress-type=lz4
log-level-console=info
log-level-file=detail

[global:backup]
process-max=4

[global:archive-get]
process-max=4

[global:archive-push]
process-max=4

[pg15]
pg1-host=prods-linuxpg15a.pragmaproducts.com
pg1-path=/pg_data/15

Config on Postgres Server
[global]
log-level-file=detail
repo1-host=172.28.4.72

[pg15]
pg1-path=/pg_data/15

archive_command: pgbackrest --stanza=pg15 --log-level-console=detail archive-push %p
archive_mode: on
listen_addresses: *
max_wal_senders: 10
wal_level: replica
port: 5432

Describe the issue:
I've got a dedicated backup server and a postgres 15 primary setup with all able to communicate with each other. When I make a full backup all goes well and verify is also good. Wal seems to be pushed correctly, because I can do a PITR to a secondary without any issues. On the backup server I have the following cron scheduled to backup every hour.
30 * * * 1-6 pgbackrest --stanza=pg15 backup

My problem comes in when I start a fairly large (1.2TB) database restore onto the primary server. This is to simulate a new client being onboarded or just moving to this environment. It seems the WAL is being shipped from the primary during the restore, as can be seen from the log entries below.
postgres_log.txt

The moment the next scheduled backup kicks in on the backup server, while the restore is running, I start seeing the following errors in the log. From that point on the backups does not work anymore, even after the restore completes. Only after I force a full backup again does it start working.
pgbackrest_log.txt

I'm not sure if I miss a configuration settings or how to work around this issue. I've though of looking at "Asynchronous archiving", but not sure if it will solve my issue.

[pgstef]

Hi,

First of all, the following settings should go to the PG host config (since archive-push/archive-get are run there):

[global:archive-get]
process-max=4

[global:archive-push]
process-max=4

These settings won't be used without async archiving enabled anyway.

My problem comes in when I start a fairly large (1.2TB) database restore onto the primary server

You mean you load lots of data using pg_restore?

The moment the next scheduled backup kicks in on the backup server, while the restore is running, I start seeing the following errors in the log. From that point on the backups does not work anymore, even after the restore completes. Only after I force a full backup again does it start working.

An hourly backup might be too much given you have the WAL archives. And taking full backups at least once a week is usually a good idea. However, I don't think "forcing" a full backup solves your problem.

It simply seems to me that the archiving isn't fast enough to push all the WAL segments generated by the data import, and it only succeeds when all WALs prior to the backup starting point have finally be archived.

Data import is usually tricky when speaking about WAL archives and backups. It would be good to know how much WAL segments were generated by that import, to try to figure out how much time it takes to archive them.

If you look at your logs, 1 WAL is pushed in 686ms (approx, which is kinda slow). And if you imagine the number of WAL segments (16MB) generated by 1.2TB of imported data, it can take several hours to push it all.

You should monitor the amount of WALs generated and waiting to be archived (.ready). Think about reducing the amount of backups per day too. Trying to speed up the wal archiving system would also be good. Enabling async archiving to use multiple processes for archive-push should help indeed. Switching from SSH to TLS for the link between repo host and pg host could also speed up things.

[rstander]

Hi

Thanks for the feedback.

Yes the "data load" is a pg_restore of a backup in directory format. So the restore is happening with 8 parallel processes.

From that I'll try the following

• Move those settings
• Enable async archiving
• Decrease backup frequency

Does using TLS vs SSH make a large throughput difference?

[dwsteele]

In our tests TLS is up to 50% faster than SSH. But network latency and system load will play a big part in the actual performance.