pg_upgrade fails if vacuum_defer_cleanup_age > 0

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