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