From: | Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com> |
---|---|
To: | Andrey Borodin <x4mmm(at)yandex-team(dot)ru> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Long running backup preventing auto vacuum |
Date: | 2022-07-01 07:41:38 |
Message-ID: | CAFpL5VxcpYUnPbdBdvf-58Q8yFoXHfLO9hB-KF_50hbNAMevOA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi Andrey,
./wal-g7 --version
wal-g version v1.1.2-rc 6af461f 2021.12.14_08:19:15 PostgreSQL
The only probable problem I can imagine is that we open transaction during
> pg_stop_backup() [0] to set a statement_timeout
Do you think this will prevent the autovacuum from kicking in ?
I think this tx can be safely removed
How can I remove this? I can test whether this is the problem
Thanks,
Nikhil
On Thu, Jun 30, 2022 at 5:18 PM Andrey Borodin <x4mmm(at)yandex-team(dot)ru> wrote:
> 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.
>
> [0]
> https://github.com/wal-g/wal-g/blob/8b9b3ca06efb44be4724208dc6f6d11836bd34d9/internal/databases/postgres/queryRunner.go#L243
From | Date | Subject | |
---|---|---|---|
Next Message | Nikhil Shetty | 2022-07-01 08:31:56 | Re: Long running backup preventing auto vacuum |
Previous Message | Doug Reynolds | 2022-06-30 23:29:08 | Re: Quartz Scheduler + pgpool + PostgreSQL 12 = issues |