Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 15.0 \(3693.60.0.1.1\))
Subject: Re: Long running backup preventing auto vacuum
From: Andrey Borodin <x4mmm@yandex-team.ru>
In-Reply-To: 
 <CAFpL5VwzNPONgEcpFYSNgpBX1-SQ4RFoxNJGxC5iNXKRkHxk-A@mail.gmail.com>
Date: Thu, 30 Jun 2022 16:48:06 +0500
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <8E8CD661-4779-493A-9DA8-F02CFC007757@yandex-team.ru>
References: 
 <CAFpL5VwzNPONgEcpFYSNgpBX1-SQ4RFoxNJGxC5iNXKRkHxk-A@mail.gmail.com>
To: Nikhil Shetty <nikhil.dba04@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/8E8CD661-4779-493A-9DA8-F02CFC007757%40yandex-team.ru>
Precedence: bulk

Hi!

> On 30 Jun 2022, at 14:23, Nikhil Shetty <nikhil.dba04@gmail.com> =
wrote:
>=20
> Hi,
>=20
> PostgreSQL version - 11.7
>=20
> 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.=20
>=20
> 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.
>=20
> As soon as we terminated the long running backup session, the =
auto-vacuum started kicking in.
>=20
> Wanted to understand why a backup operation is blocking the =
auto-vacuum?
>=20
> 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/8b9b3ca06efb44be4724208dc6f6d11836bd34=
d9/internal/databases/postgres/queryRunner.go#L243=