Re: Teach pg_upgrade to deal with invalid databases

Hi,

On Fri, Sep 4, 2026 at 10:54 AM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>
> On Fri, Sep 04, 2026 at 10:27:00AM -0700, Bharath Rupireddy wrote:
> > I would like to propose an option to skip the invalid databases, with
> > the default being on. This helps unblock upgrade workflows while still
> > preserving them for users who think it is necessary. Please find the
> > attached patch doing this.
>
> IMHO if we are going to have an option, we'd better default it to off,
> because there's probably a low chance of someone remembering to set it.

Thanks for taking a look at it.

If we keep the option, making it off by default keeps the existing
behavior of pg_upgrade erroring out on an invalid database, so
existing upgrade workflows behave the same way. They might be handling
this specific error already. Other than this, I can't think of a
reason to make it default off.

> But I'm not totally convinced we even need an option. The user has already
> decided to drop the database, and IIUC there's no supported recovery
> mechanism to revive a database marked invalid. In the previous thread, it
> was argued that pg_upgrade doesn't fix things and instead leaves it up to
> the user. While I understand the argument, I also don't really see the
> harm in letting pg_upgrade fix this particular problem on the fly.

Assuming fixing is just skipping the invalid databases on the old
cluster, I am not aware of any situation where an invalid database is
used to recover anything. So, instead of erroring out, just skipping
by default without any option seems like a better approach. That said,
I may be missing something here.

> > Dropping the invalid databases during the upgrade is another approach,
> > but it could be costly, especially with large buffer pools and a large
> > number of files to unlink. Skipping them instead is simpler, and the
> > old directory contents would be cleaned up by the removal script that
> > pg_upgrade already generates.
>
> Does dropping the invalid databases provide any advantages here? I can't
> think of any.

Upon thinking more, I don't see any advantage to dropping the database
in the old cluster. pg_upgrade does not drop any objects from the old
cluster today, and I don't think this patch is the place to change
that. In copy/clone mode, one can fall back to the old cluster if
something goes wrong post-upgrade, and skipping leaves the old cluster
exactly as it was. The invalid database was not connectible in the old
cluster before the upgrade either, so leaving it there does not change
the fallback behavior. In link mode, the skipped database's files are
never linked into the new cluster, so the new cluster has no trace of
it. The delete script cleans it up along with everything else once the
new cluster is put to use.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com