Re: Teach pg_upgrade to deal with invalid databases

Hi,

On Tue, Sep 22, 2026 at 8:26 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> On 2026-09-21 14:01:09 -0500, Nathan Bossart wrote:
> > On Tue, Sep 08, 2026 at 10:11:23AM -0700, Bharath Rupireddy wrote:
> > > pg_upgrade skipping the invalid databases seems like the right
> > > approach. Whether or not this has to be put behind an option and
> > > retain the current error behavior is something we need to agree on. I
> > > still think pg_upgrade can just skip and the error could be removed,
> > > unless anyone thinks otherwise.
> >
> > I think we ought to simply skip the invalid databases without any sort of
> > option. The only counterargument I'm aware of is that someone might want
> > to try recoving the database, but AFAIK there's no supported recovery
> > mechanism. Even if there was, it seems pretty unlikely that a user would
> > cancel an errant DROP DATABASE and then proceed to run pg_upgrade before
> > attempting recovery. Furthermore, I believe the proposed skipping behavior
> > more closely matches the previous behavior before we started using
> > datconnlimit == -2 to mean the database is invalid.
>
> I kinda wonder if the better answer would be to teach autovac or such to drop
> such databases.

Thanks all for the comments.

I have a WIP proposal to automatically drop invalid databases in core.
The idea is to use a dynamic bg worker launched by the autovacuum
launcher when it loops over all databases and detects them. It could
be the autovacuum launcher itself doing the drop, or another bg
worker, or a new custodian process like
https://postgr.es/m/C1EE64B0-D4DB-40F3-98C8-0CED324D34CB@amazon.com,
but more on which process does this in a separate thread that I plan
to start.

> I don't love pg_upgrade ignoring invalid databases, but I can't actually come
> up with a convincing reason for it, so ...

Even if core drops them automatically, having pg_upgrade deal with
them is useful on its own IMHO, since the upgrade could start before
that happens. So, I prefer pg_upgrade skipping them without any
option, emitting info about the skipped ones, which will not happen
often in practice once core handles the drop.

Thoughts?

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