From: | Andrey Borodin <x4mmm(at)yandex-team(dot)ru> |
---|---|
To: | Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Long running backup preventing auto vacuum |
Date: | 2022-06-30 11:48:06 |
Message-ID: | 8E8CD661-4779-493A-9DA8-F02CFC007757@yandex-team.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi!
> On 30 Jun 2022, at 14:23, Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrey Borodin | 2022-06-30 11:52:44 | Re: Incremental Backup via WAL archive |
Previous Message | Nikhil Shetty | 2022-06-30 09:23:43 | Long running backup preventing auto vacuum |