Teach pg_upgrade to deal with invalid databases
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253678psql -h localhost -U postgresBuilt from patchset v17 (message #17), October 22, 2026 at 04:48 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253678_17 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253678_17 && git checkout t253678_17Patchset v17 (message #17) is on t253678_17
Hi,
Reviving this thread [1]/messages/by-id/f9315bf0-e03e-4490-9f0d-5b6f7a6d9908@postsubmeta.net on the subject for PG20.
Currently, pg_upgrade fails when it encounters an invalid database
(commit c66a7d75e65, a database whose DROP DATABASE got interrupted)
in the source cluster, blocking the whole upgrade workflow. In the
previous thread, various options were discussed such as skip or drop,
and commit f638aafd1ea tightened checking and error reporting for
invalid databases.
I think we can do better here. If a user decided to DROP a database,
the data in it is no longer wanted. Even if it were, the data in the
files is unlikely to be consistent or useful, since DROP was
interrupted mid-operation (while evicting buffer pool pages, unlinking
files, etc.).
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.
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.
Thoughts?
Thanks to Nathan Bossart and Robert Treat for earlier discussions and
design review.
[1]: /messages/by-id/f9315bf0-e03e-4490-9f0d-5b6f7a6d9908@postsubmeta.net
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
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.
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.
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.
--
nathan
Hi,
On Fri, Sep 4, 2026 at 10:54 AM Nathan Bossart <nathandbossart@gmail.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
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.rupireddyforpostgres@gmail.com> wrote:
Show quoted text
Hi,
On Fri, Sep 4, 2026 at 10:54 AM Nathan Bossart <nathandbossart@gmail.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
Hi,
On Mon, Sep 7, 2026 at 11:22 PM Rithvika Devisetti
<devisettirithvika@gmail.com> wrote:
It looks like you and Nathan now agree that pg_upgrade should always
skip invalid databases, without any option.
pg_upgrade dropping the invalid databases does NOT seem like the right
approach. I am clear on this unless anyone thinks otherwise.
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 will give it some more time to hear from others.
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.
Thanks for offering to verify these cases. You can still test with v1
using --invalid-databases=skip.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
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.
--
nathan
On Mon, Sep 21, 2026 at 3:01 PM Nathan Bossart <nathandbossart@gmail.com> 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.
+1 from me; If people are really concerned, I think it'd be enough to
just be a little more chatty about it in the logs; ie. add some
warnings and/or notes when we find/skip an invalid database to the
various logs and instructions during the upgrade process.
Robert Treat
https://xzilla.net
Hi,
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.
I don't love pg_upgrade ignoring invalid databases, but I can't actually come
up with a convincing reason for it, so ...
Greetings,
Andres Freund
Hi,
On Tue, Sep 22, 2026 at 8:26 AM Andres Freund <andres@anarazel.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
/messages/by-id/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
On Tue, Sep 22, 2026 at 09:10:44AM -0700, Bharath Rupireddy wrote:
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
/messages/by-id/C1EE64B0-D4DB-40F3-98C8-0CED324D34CB@amazon.com,
but more on which process does this in a separate thread that I plan
to start.
Maybe I should try to revive the custodian worker for v20...
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.
Yeah, I think we should do both. Automatic deletion is probably the best
thing to do to handle leftover invalid databases, but as you say we can't
rely on that happening before someone upgrades.
--
nathan
On Tue, Sep 22, 2026, at 1:25 PM, Nathan Bossart wrote:
On Tue, Sep 22, 2026 at 09:10:44AM -0700, Bharath Rupireddy wrote:
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.Yeah, I think we should do both. Automatic deletion is probably the best
thing to do to handle leftover invalid databases, but as you say we can't
rely on that happening before someone upgrades.
... or add a detailed error message to pg_upgrade saying that autovacuum or a
custom worker will remove the invalid databases in the next round. I don't like
the idea of having duplicate functionality. You might have to deal with a race
condition if pg_upgrade and autovacuum decide to act at the same time.
--
Euler Taveira
EDB https://www.enterprisedb.com/
Hi,
On Tue, Sep 22, 2026 at 1:52 PM Euler Taveira <euler@eulerto.com> wrote:
On Tue, Sep 22, 2026, at 1:25 PM, Nathan Bossart wrote:
On Tue, Sep 22, 2026 at 09:10:44AM -0700, Bharath Rupireddy wrote:
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.Yeah, I think we should do both. Automatic deletion is probably the best
thing to do to handle leftover invalid databases, but as you say we can't
rely on that happening before someone upgrades.... or add a detailed error message to pg_upgrade saying that autovacuum or a
custom worker will remove the invalid databases in the next round. I don't like
the idea of having duplicate functionality. You might have to deal with a race
condition if pg_upgrade and autovacuum decide to act at the same time.
Thanks for looking. IMHO, this isn't duplicate functionality. When
implemented, core would drop the invalid databases. The proposal in
this thread only makes pg_upgrade skip them instead of failing the
upgrade.
The autovacuum launcher isn't started in binary upgrade mode, so there
is no race with pg_upgrade. A custom worker (if chosen over the
autovacuum launcher) can do the same.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
Hi,
On Tue, Sep 22, 2026 at 9:10 AM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
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.
I implemented the above approach, pg_upgrade skipping them without any
option and emitting info about the skipped ones. I ensured the CI is
happy. Please find the attached v2 patch.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
Attachments:
t253678_13v2-0001-Teach-pg_upgrade-to-skip-invalid-databases.patchapplication/x-patch; name=v2-0001-Teach-pg_upgrade-to-skip-invalid-databases.patchDownload+80-52
On Thu, Sep 24, 2026 at 08:37:00AM -0700, Bharath Rupireddy wrote:
I implemented the above approach, pg_upgrade skipping them without any
option and emitting info about the skipped ones. I ensured the CI is
happy. Please find the attached v2 patch.
Thanks. IMHO we shouldn't bother adding a note to pg_upgrade's
documentation, or even emitting warnings when pg_upgrade skips invalid
databases. For all intents and purposes, the database is already dropped
(for some definition of "dropped"), and there's nothing actionable for the
user.
--
nathan
Hi,
On Fri, Oct 2, 2026 at 9:21 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
On Thu, Sep 24, 2026 at 08:37:00AM -0700, Bharath Rupireddy wrote:
I implemented the above approach, pg_upgrade skipping them without any
option and emitting info about the skipped ones. I ensured the CI is
happy. Please find the attached v2 patch.Thanks. IMHO we shouldn't bother adding a note to pg_upgrade's
documentation, or even emitting warnings when pg_upgrade skips invalid
databases. For all intents and purposes, the database is already dropped
(for some definition of "dropped"), and there's nothing actionable for the
user.
Thanks for looking at it.
Upon thinking more on this, I agree on both. Unless there are
objections, I will drop the warning and the doc note in the next
version.
Once pg_upgrade skips invalid databases, the user has no action to
take. Even if the user reverts to the old cluster, the invalid
databases are still there.
I also think not reporting them matches what other tools do, like
pg_dumpall (dumpDatabases()), vacuumdb (vacuum_all_databases()),
reindexdb (reindex_all_databases()), etc. They skip invalid databases
without emitting any info, and their docs don't mention it either.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
On Sun, Oct 4, 2026 at 8:00 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Fri, Oct 2, 2026 at 9:21 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
On Thu, Sep 24, 2026 at 08:37:00AM -0700, Bharath Rupireddy wrote:
I implemented the above approach, pg_upgrade skipping them without any
option and emitting info about the skipped ones. I ensured the CI is
happy. Please find the attached v2 patch.Thanks. IMHO we shouldn't bother adding a note to pg_upgrade's
documentation, or even emitting warnings when pg_upgrade skips invalid
databases. For all intents and purposes, the database is already dropped
(for some definition of "dropped"), and there's nothing actionable for the
user.Thanks for looking at it.
Upon thinking more on this, I agree on both. Unless there are
objections, I will drop the warning and the doc note in the next
version.Once pg_upgrade skips invalid databases, the user has no action to
take. Even if the user reverts to the old cluster, the invalid
databases are still there.I also think not reporting them matches what other tools do, like
pg_dumpall (dumpDatabases()), vacuumdb (vacuum_all_databases()),
reindexdb (reindex_all_databases()), etc. They skip invalid databases
without emitting any info, and their docs don't mention it either.
I guess I don't object, but it feels a little odd to make a user
visible behavioral change without giving any kinds of heads up; this
would have errored out in previous versions, now it "just works", but
just works might look concerning if a database that used to show up in
your database list now goes missing, or you are wondering why your
cluster size dropped significantly after upgrade. There are some other
weird corner cases that might come up too, but hopefully the release
note entry will be enough for people looking for explanations.
Robert Treat
https://xzilla.net
Hi,
On Tue, Oct 6, 2026 at 11:32 AM Robert Treat <rob@xzilla.net> wrote:
Thanks. IMHO we shouldn't bother adding a note to pg_upgrade's
documentation, or even emitting warnings when pg_upgrade skips invalid
databases.Upon thinking more on this, I agree on both. Unless there are
objections, I will drop the warning and the doc note in the next
version.I also think not reporting them matches what other tools do, like
pg_dumpall (dumpDatabases()), vacuumdb (vacuum_all_databases()),
reindexdb (reindex_all_databases()), etc. They skip invalid databases
without emitting any info, and their docs don't mention it either.I guess I don't object, but it feels a little odd to make a user
visible behavioral change without giving any kinds of heads up; this
would have errored out in previous versions, now it "just works", but
just works might look concerning if a database that used to show up in
your database list now goes missing, or you are wondering why your
cluster size dropped significantly after upgrade. There are some other
weird corner cases that might come up too,
Other tools silently ignoring the invalid databases worked fine so far
(unless I'm missing anything here), so it shouldn't be a big problem
IMHO.
but hopefully the release
note entry will be enough for people looking for explanations.
Yes, this will help.
Please find the attached v3 patch dropping the docs note and the
warning. I self-reviewed it and fixed a couple of things: excluded
invalid databases while checking encodings and global names on the old
cluster, and reworded the commit message.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com