I’m doing postgres pg_basebackup’s (Via barman) on a primary cluster and a read only standby postgres cluster (they are in different data centres)
I’m hitting an issue on DB’s with low amounts of activity though.
Barman switches the WAL when is starts a backup on the primary so that it has a consistent “start point”.
This is not possible however on the standby and it has to wait for the primary to switch a WAL to get a consistent backup
Problem is that on low activity DB’s you could be waiting a few days for a WAL switch, the backup on standby then hangs for days.
Is there is a parameter in Postgres that forces the WALS to switch every 30 mins or so
I’d rather not have a cron that does:
SELECT pg_switch_wal();
For referance in oracle I use:
log_checkpoint_timeout = 1800
Edit:
I already have archive_timeout and and checkpoint_timeout set
archive_command = '/controller/manager wal-archive --log-destination /controller/log/postgres.json %p'
archive_mode = 'on'
archive_timeout = '300'
autovacuum_max_workers = '2'
checkpoint_completion_target = '0.9'
checkpoint_timeout = '10min'
Also: I am using the cloudnativepg operator on K8S
So it would seem that archive_timeout is not absolute, and that if there is 0 data in the current WAL then it will not attempt to archive it.
I tested this by creating a table and adding one row. Within 5 minutes the WAL was archived.
A job to execute:
SELECT pg_switch_wal();
Or to insert and delete 1 row of data into a scratch table is going to be required to fix this issue.