From: David Steele <david@pgmasters.net>
To: Don Seiler <don@seiler.us>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: WAL restore/recovery fills pg_wal volume
Date: Tue, 2 Aug 2022 14:16:31 -0400
Message-ID: <c8e99868-1151-85c8-0e3a-35ccd3e8b615@pgmasters.net> (raw)
In-Reply-To: <CAHJZqBDxWfcd53jm0bFttuqpK3jV2YKWx=4W7KxNB4zzt++qFg@mail.gmail.com>
References: <CAHJZqBDxWfcd53jm0bFttuqpK3jV2YKWx=4W7KxNB4zzt++qFg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: david@pgmasters.net, don@seiler.us
Subject: Re: WAL restore/recovery fills pg_wal volume
In-Reply-To: <c8e99868-1151-85c8-0e3a-35ccd3e8b615@pgmasters.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox