Thread: pg_upgrade fails if vacuum_defer_cleanup_age > 0
A customer's upgrade failed, and it took me a while to figure out that the problem was that they had set "vacuum_defer_cleanup_age=10000" on the new cluster. The consequence was that the "vacuumdb --freeze" that takes place before copying commit log files failed to freeze "pg_database". That caused later updates to the table to fail with "Could not open file "pg_xact/0000": No such file or directory." I think it would increase the robustness of pg_upgrade to force "vacuum_defer_cleanup_age" to 0 on the new cluster. Suggested patch attached. Yours, Laurenz Albe
Attachment
On Wed, Jun 10, 2020 at 04:07:05PM +0200, Laurenz Albe wrote: > A customer's upgrade failed, and it took me a while to > figure out that the problem was that they had set > "vacuum_defer_cleanup_age=10000" on the new cluster. > > The consequence was that the "vacuumdb --freeze" that > takes place before copying commit log files failed to > freeze "pg_database". > That caused later updates to the table to fail with > "Could not open file "pg_xact/0000": No such file or directory." > > I think it would increase the robustness of pg_upgrade to > force "vacuum_defer_cleanup_age" to 0 on the new cluster. Wow, I can see setting "vacuum_defer_cleanup_age" to a non-zero value as causing big problems for pg_upgrade, and being hard to diagnose. I will apply this patch to all branches. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
On Sat, Jun 13, 2020 at 08:46:36AM -0400, Bruce Momjian wrote: > On Wed, Jun 10, 2020 at 04:07:05PM +0200, Laurenz Albe wrote: > > A customer's upgrade failed, and it took me a while to > > figure out that the problem was that they had set > > "vacuum_defer_cleanup_age=10000" on the new cluster. > > > > The consequence was that the "vacuumdb --freeze" that > > takes place before copying commit log files failed to > > freeze "pg_database". > > That caused later updates to the table to fail with > > "Could not open file "pg_xact/0000": No such file or directory." > > > > I think it would increase the robustness of pg_upgrade to > > force "vacuum_defer_cleanup_age" to 0 on the new cluster. > > Wow, I can see setting "vacuum_defer_cleanup_age" to a non-zero value as > causing big problems for pg_upgrade, and being hard to diagnose. I will > apply this patch to all branches. Thank you, applied to all supported PG versions. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
On Mon, 2020-06-15 at 20:59 -0400, Bruce Momjian wrote: > On Sat, Jun 13, 2020 at 08:46:36AM -0400, Bruce Momjian wrote: > > On Wed, Jun 10, 2020 at 04:07:05PM +0200, Laurenz Albe wrote: > > > A customer's upgrade failed, and it took me a while to > > > figure out that the problem was that they had set > > > "vacuum_defer_cleanup_age=10000" on the new cluster. > > > > > > The consequence was that the "vacuumdb --freeze" that > > > takes place before copying commit log files failed to > > > freeze "pg_database". > > > That caused later updates to the table to fail with > > > "Could not open file "pg_xact/0000": No such file or directory." > > > > > > I think it would increase the robustness of pg_upgrade to > > > force "vacuum_defer_cleanup_age" to 0 on the new cluster. > > Thank you, applied to all supported PG versions. Thanks for picking this up and taking care of it. Yours, Laurenz Albe
On Tue, Jun 16, 2020 at 08:39:57AM +0200, Laurenz Albe wrote:
> On Mon, 2020-06-15 at 20:59 -0400, Bruce Momjian wrote:
> > On Sat, Jun 13, 2020 at 08:46:36AM -0400, Bruce Momjian wrote:
> > > On Wed, Jun 10, 2020 at 04:07:05PM +0200, Laurenz Albe wrote:
> > > > A customer's upgrade failed, and it took me a while to
> > > > figure out that the problem was that they had set
> > > > "vacuum_defer_cleanup_age=10000" on the new cluster.
> > > >
> > > > The consequence was that the "vacuumdb --freeze" that
> > > > takes place before copying commit log files failed to
> > > > freeze "pg_database".
> > > > That caused later updates to the table to fail with
> > > > "Could not open file "pg_xact/0000": No such file or directory."
> > > >
> > > > I think it would increase the robustness of pg_upgrade to
> > > > force "vacuum_defer_cleanup_age" to 0 on the new cluster.
> >
> > Thank you, applied to all supported PG versions.
>
> Thanks for picking this up and taking care of it.
Sure. I never noticed how this setting, when it was added in 2009,
could affect pg_uprade, but is certainly can:
commit efc16ea520
Author: Simon Riggs <simon@2ndQuadrant.com>
Date: Sat Dec 19 01:32:45 2009 +0000
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee