Re: WAL restore/recovery fills pg_wal volume

On 8/2/22 09:26, Don Seiler wrote:
>
> I'm on PG 12 on Ubuntu 18.04.
>
> I have a very large database (~18TB) with which we have a separate
> pg_wal volume configured. The pg_wal volume is around 50GB but typical
> daily usage rarely sees it over 10GB. We use pgbackrest to archive WALs
> as well as performing backups.
>
> The problem is when we are performing restore and recovery (e.g. to set
> up a new physical replica), also via pgbackrest. The DB restore works
> fine but when it comes to the WAL restore and recovery, the pg_wal
> volume will fill up before PG can clear out the already-recovered WAL
> files. This means I have to restart the database and start the recovery
> process over again. The last time I ended up writing a cron job to
> delete around 100 logs per minute just via an `rm` command based on the
> recovery rate I saw.
>
> My understanding is that the recovered WAL files would be cleared when
> the replica hits a recovery start point. My primary is configured with a
> 10 minute checkpoint_timeout. The replica pg_wal will fill up before 10
> minutes. The primary and replica have the same size pg_wal volume but
> the primary never comes close to filling up, as I said before.
>
> I believe two options (aside from the ugly rm cron job) would be to
> either shorten the checkpoint_timeout on the primary, which would be
> hard to do due to the activity level, or make a larger pg_wal volume
> (trial and error to determine just how much larger?).
>
> I'm interested to know if there's anything else I can do to avoid the
> toil when we do these restores and also if maybe there is something
> wrong and that PG shouldn't be filling up the volume blindly.

This appears to be related to [1], which we have been discussing over on
that thread.

Regards,
-David

[1]
https://www.postgresql.org/message-id/flat/20210202151416.GB3304930%40rfd.leadboat.com