Re: Teach pg_upgrade to deal with invalid databases

Hello Mr. Bharat,

It looks like you and Nathan now agree that pg_upgrade should always
skip invalid databases, without any option. Because of that, I did not
test v1. i think testing it now would not be very useful.

When you post the updated patch, I would like to test these three
things from your last message, since I don't think anyone has checked
them yet:

- In link mode, the skipped database's files should not appear
anywhere in the new cluster.
- The delete script that pg_upgrade creates should also remove the
skipped database's old files.
- In copy mode, the old cluster should stay exactly the same, so it
can still be used as a backup.

Regards,
Rithvika Devisetti

On Fri, Sep 4, 2026 at 6:54 PM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> 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
>
>
>