Обсуждение: 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

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