From: Andrey Borodin <x4mmm@yandex-team.ru>
To: Nikhil Shetty <nikhil.dba04@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Long running backup preventing auto vacuum
Date: Thu, 30 Jun 2022 16:48:06 +0500
Message-ID: <8E8CD661-4779-493A-9DA8-F02CFC007757@yandex-team.ru> (raw)
In-Reply-To: <CAFpL5VwzNPONgEcpFYSNgpBX1-SQ4RFoxNJGxC5iNXKRkHxk-A@mail.gmail.com>
References: <CAFpL5VwzNPONgEcpFYSNgpBX1-SQ4RFoxNJGxC5iNXKRkHxk-A@mail.gmail.com>
Hi!
> On 30 Jun 2022, at 14:23, Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
>
> Hi,
>
> PostgreSQL version - 11.7
>
> We are using wal-g for backup/restore. We had a duration of high WAL generation due to application usage due to which archive backup was delayed.
>
> Backup was started through cron schedule and it didn't complete because it waits for the last WAL to be archived. Now, I know that long running transactions will prevent vacuuming of dead tuples but in this case we were able to run manual vacuums successfully but auto-vacuum never starts.
>
> As soon as we terminated the long running backup session, the auto-vacuum started kicking in.
>
> Wanted to understand why a backup operation is blocking the auto-vacuum?
>
> Wal-g uses non-exclusive backups and waits for the last wal to be archived.
As far as I know, WAL-G should not prevent running autovacuums on tables. WAL-G can be run against replication standby, where vacuum is not possible at all, so there must be no conflicts...
The only probable problem I can imagine is that we open transaction during pg_stop_backup() [0] to set a statement_timeout. I think this is superflous and unneeded. I think this tx can be safely removed. But anyway, this tx does not have a xid (otherwise it could not run on Standby).
BTW which version of WAL-G do you use?
Best regards, Andrey Borodin.
[0] https://github.com/wal-g/wal-g/blob/8b9b3ca06efb44be4724208dc6f6d11836bd34d9/internal/databases/post...
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: x4mmm@yandex-team.ru, nikhil.dba04@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Long running backup preventing auto vacuum
In-Reply-To: <8E8CD661-4779-493A-9DA8-F02CFC007757@yandex-team.ru>
* 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