Subject: Re: pg_upgrade check for invalid databases

From: Daniel Gustafsson <[email-masked]>
Date: Mon, 30 Sep 2024 23:29:35 +0200

> On 30 Sep 2024, at 16:55, Tom Lane <[email-masked]> wrote:

> TBH I'm not finding anything very much wrong with the current
> behavior... this has to be a rare situation, do we need to add
> debatable behavior to make it easier?

One argument would be to make the checks consistent, pg_upgrade generally tries
to report all the offending entries to help the user when fixing the source
database.  Not sure if it's a strong enough argument for carrying code which
really shouldn't see much use though.

--
Daniel Gustafsson

---

From: Tom Lane <[email-masked]>
Date: Mon, 30 Sep 2024 18:20:41 -0400

> really shouldn't see much use though.

OK, but the consistency argument would be to just report and fail.
I don't think there's a precedent in other pg_upgrade checks for
trying to fix problems automatically.

			regards, tom lane

---

From: Thomas Krennwallner <[email-masked]>
Date: Mon, 30 Sep 2024 20:35:12 -0400

> really shouldn't see much use though.
In general, I agree that this situation should be rare for deliberate 
DROP DATABASE interrupted in interactive sessions.

Unfortunately, for (popular) tools that perform automatic "temporary 
database" cleanup, we could recently see an increase in invalid databases.

The additional check for pg_upgrade was made necessary due to several 
unrelated customers having invalid databases that stem from left-over 
Prisma Migrate "shadow databases" [1]. We could not reproduce this 
Prisma Migrate issue yet, as those migrations happened some time ago. 
Maybe this bug really stems from a much older Prisma Migrate version and 
we only see the fallout now. This is still a TODO item.

But it appears that this tool can get interrupted "at the wrong time" 
while it is deleting temporary databases (probably a manual Ctrl-C), and 
clients are unaware that this can then leave behind invalid databases.

Those temporary databases do not cause any harm as they are not used 
anymore. But eventually, PG installations will be upgraded to the next 
major version, and it is only then when those invalid databases 
resurface after pg_upgrade fails to run the checks.

Long story short: interactive DROP DATABASE interrupts are rare (they do 
exist, but customers are usually aware). Automation tools on the other 
hand may run DROP DATABASE and when they get interrupted at the wrong 
time they will then produce several left-over invalid databases. 
pg_upgrade will then fail to run the checks.

[1] 
https://www.prisma.io/docs/orm/prisma-migrate/understanding-prisma-migrate/shadow-database

---

From: Daniel Gustafsson <[email-masked]>
Date: Tue, 1 Oct 2024 09:28:54 +0200

> On 1 Oct 2024, at 00:20, Tom Lane <[email-masked]> wrote:
> 

Correct, sorry for being unclear.  The consistency argument would be to expand
pg_upgrade to report all invalid databases rather than just the first found;
attempting to fix problems would be a new behavior.

--
Daniel Gustafsson

---

From: Daniel Gustafsson <[email-masked]>
Date: Tue, 1 Oct 2024 09:42:22 +0200

> On 1 Oct 2024, at 02:35, Thomas Krennwallner <[email-masked]> wrote:
> 
> In general, I agree that this situation should be rare for deliberate DROP DATABASE interrupted in interactive sessions.
> 
> Unfortunately, for (popular) tools that perform automatic "temporary database" cleanup, we could recently see an increase in invalid databases.
> 
> The additional check for pg_upgrade was made necessary due to several unrelated customers having invalid databases that stem from left-over Prisma Migrate "shadow databases" [1]. We could not reproduce this Prisma Migrate issue yet, as those migrations happened some time ago. Maybe this bug really stems from a much older Prisma Migrate version and we only see the fallout now. This is still a TODO item.
> 
> But it appears that this tool can get interrupted "at the wrong time" while it is deleting temporary databases (probably a manual Ctrl-C), and clients are unaware that this can then leave behind invalid databases.
> 
> Those temporary databases do not cause any harm as they are not used anymore. But eventually, PG installations will be upgraded to the next major version, and it is only then when those invalid databases resurface after pg_upgrade fails to run the checks.

Databases containing transient data no longer needed left by buggy tools is one
thing, but pg_upgrade won't be able to differentiate between those and invalid
databases of legitimate interest.  Allowing pg_upgrade to skip invalid
databases expose the risk of (potentially) valuable data being dropped during
the upgrade due to the user not having realized a rarely-used production
database was invalid.

> Long story short: interactive DROP DATABASE interrupts are rare (they do exist, but customers are usually aware). Automation tools on the other hand may run DROP DATABASE and when they get interrupted at the wrong time they will then produce several left-over invalid databases. pg_upgrade will then fail to run the checks.

Checking and reporting all invalid databases during the check phase seems like
a user-friendly option here, I can agree that the current behaviour isn't great
for users experiencing this issue.

--
Daniel Gustafsson

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 7 Oct 2024 15:37:35 -0400

> attempting to fix problems would be a new behavior.

Yes, historically pg_upgrade will fail if it finds anything unusual,
mostly because what it does normally is already scary enough.  If users
what pg_upgrade to do cleanups, it would be enabled by a separate flag,
or even a new command-line app.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  When a patient asks the doctor, "Am I going to die?", he means 
  "Am I going to die soon?"

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 7 Oct 2024 15:04:22 -0500

> or even a new command-line app.

While I suspect it's rare that someone CTRL-C's out of an accidental DROP
DATABASE and then runs pg_upgrade before trying to recover the data, I
agree with the principle of having pg_upgrade fail by default for things
like this.  If we did add a new flag, the new invalid database report that
Daniel mentions could say something like "try again with
--skip-invalid-databases to have pg_upgrade automatically drop invalid
databases."

-- 
nathan

---

From: Daniel Gustafsson <[email-masked]>
Date: Fri, 11 Oct 2024 10:00:49 +0200

> On 7 Oct 2024, at 22:04, Nathan Bossart <[email-masked]> wrote:
> 

If we are teaching pg_upgrade to handle errors, either by skipping or by
fixing, then I believe this is the right way to go about it.  A successful run
should probably also create a report of the databases which were skipped.

--
Daniel Gustafsson

---

From: Thomas Krennwallner <[email-masked]>
Date: Sun, 13 Oct 2024 08:28:57 -0400

> should probably also create a report of the databases which were skipped.

In v2 I've made changes to the patch incorporating the suggestions here:

* Default behaviour is to just fail with a report of all invalid databases

* A new option --skip-invalid-databases will then skip the checks, and
would not transfer any invalid database to the new cluster. A warning
with a report file will then follow after a successful run.

Dropping invalid databases in the old cluster will make invalid
databases unrecoverable, so I opted for a skip over invalid databases
approach that would leave invalid databases in the old cluster.

Apart from a missing --skip-invalid-databases test, does this attempt look OK?
From e51f581ddc1158a9fb2840dfc04863618a390887 Mon Sep 17 00:00:00 2001
From: Thomas Krennwallner <[email-masked]>
Date: Fri, 20 Sep 2024 17:33:05 -0400
Subject: [PATCH] pg_upgrade: Add check for invalid databases.

Currently, pg_upgrade fails to connect to the first invalid database
it encounters in get_loadable_libraries() and then aborts.  While we
print a fatal message with a hint what should be done, the output is
terse and does not report further invalid databases present in an
installation.

Instead of just exiting on first error, we collect all
pg_database.datconnlimit values in get_db_infos() and order invalid
databases after valid ones in cluster.dbarr. This allows for skipping
over invalid databases in all checks with --skip-invalid-databases, a
new option where we do not transfer such databases from the old
cluster.

Unless we use --skip-invalid-databases, check_for_invalid_dbs()
collects all invalid databases in a report file invalid_databases.txt.
After a successful pg_upgrade run with skipped invalid databases,
create_invalid_databases_report() will collect them in a report file
skipped_invalid_databases.txt for the completion banner output.
---
 doc/src/sgml/ref/pgupgrade.sgml        |  19 ++++
 src/bin/pg_upgrade/check.c             | 131 +++++++++++++++++++++++--
 src/bin/pg_upgrade/info.c              |  20 +++-
 src/bin/pg_upgrade/option.c            |   7 ++
 src/bin/pg_upgrade/pg_upgrade.c        |  18 +++-
 src/bin/pg_upgrade/pg_upgrade.h        |   8 +-
 src/bin/pg_upgrade/t/002_pg_upgrade.pl |   2 +-
 7 files changed, 187 insertions(+), 18 deletions(-)

diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index 4777381dac..a9a6b62e7a 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -253,6 +253,25 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--skip-invalid-databases</option></term>
+      <listitem>
+       <para>
+        If a cluster contains invalid databases, the default behaviour of
+        <application>pg_upgrade</application> is to fail with a report of all
+        such databases found in an installation. Invalid databases, which cannot
+        be connected to anymore, are a consequence of interrupted
+        <command>DROP DATABASE</command> and marked as corrupted databases in
+        <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>datconnlimit</structfield>.
+       </para>
+       <para>
+        When using option <option>--skip-invalid-databases</option>,
+        <application>pg_upgrade</application> instead will skip all invalid
+        databases and do not transfer them over to the new cluster.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--sync-method=</option><replaceable>method</replaceable></term>
       <listitem>
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 12735a4268..e372feff1a 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -12,6 +12,7 @@
 #include "catalog/pg_authid_d.h"
 #include "catalog/pg_class_d.h"
 #include "catalog/pg_collation.h"
+#include "catalog/pg_database.h"
 #include "fe_utils/string_utils.h"
 #include "mb/pg_wchar.h"
 #include "pg_upgrade.h"
@@ -19,6 +20,7 @@
 static void check_new_cluster_is_empty(void);
 static void check_is_install_user(ClusterInfo *cluster);
 static void check_proper_datallowconn(ClusterInfo *cluster);
+static void check_for_invalid_dbs(ClusterInfo *cluster);
 static void check_for_prepared_transactions(ClusterInfo *cluster);
 static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
 static void check_for_user_defined_postfix_ops(ClusterInfo *cluster);
@@ -598,6 +600,11 @@ check_and_dump_old_cluster(void)
 	 */
 	get_db_rel_and_slot_infos(&old_cluster);
 
+	/*
+	 * Verify that the old cluster does not contain invalid databases.
+	 */
+	check_for_invalid_dbs(&old_cluster);
+
 	init_tablespaces();
 
 	get_loadable_libraries();
@@ -691,6 +698,11 @@ check_new_cluster(void)
 {
 	get_db_rel_and_slot_infos(&new_cluster);
 
+	/*
+	 * Verify that the new cluster does not contain invalid databases.
+	 */
+	check_for_invalid_dbs(&new_cluster);
+
 	check_new_cluster_is_empty();
 
 	check_loadable_libraries();
@@ -763,7 +775,7 @@ issue_warnings_and_set_wal_level(void)

 void
-output_completion_banner(char *deletion_script_file_name)
+output_completion_banner(char *deletion_script_file_name, char *skipped_invalid_databases_file_name)
 {
 	PQExpBufferData user_specification;
 
@@ -775,6 +787,14 @@ output_completion_banner(char *deletion_script_file_name)
 		appendPQExpBufferChar(&user_specification, ' ');
 	}
 
+	if (skipped_invalid_databases_file_name)
+	{
+		pg_log(PG_REPORT,
+			   "The following invalid databases were not transferred by pg_upgrade:\n"
+			   "    %s",
+			   skipped_invalid_databases_file_name);
+	}
+
 	pg_log(PG_REPORT,
 		   "Optimizer statistics are not transferred by pg_upgrade.\n"
 		   "Once you start the new server, consider running:\n"
@@ -998,7 +1018,7 @@ create_script_for_old_cluster_deletion(char **deletion_script_file_name)
 
 			fprintf(script, "\n");
 
-			for (dbnum = 0; dbnum < old_cluster.dbarr.ndbs; dbnum++)
+			for (dbnum = 0; dbnum < old_cluster.dbarr.ntotaldbs; dbnum++)
 				fprintf(script, RMDIR_CMD " %c%s%c%u%c\n", PATH_QUOTE,
 						fix_path_separator(os_info.old_tablespaces[tblnum]),
 						PATH_SEPARATOR, old_cluster.dbarr.dbs[dbnum].db_oid,
@@ -1087,6 +1107,96 @@ check_is_install_user(ClusterInfo *cluster)
 	check_ok();
 }
 
+/*
+ *	check_for_invalid_dbs
+ *
+ *	Ensure that all databases are valid as connections to invalid databases
+ *	are not allowed.
+ */
+static void
+check_for_invalid_dbs(ClusterInfo *cluster)
+{
+	int			dbnum;
+	FILE	   *script = NULL;
+	char		output_path[MAXPGPATH];
+
+	prep_status("Checking for invalid databases");
+
+	if (user_opts.skip_invalid_dbs)
+	{
+		pg_log(PG_REPORT, "skipped");
+		return;
+	}
+
+	snprintf(output_path, sizeof(output_path), "%s/%s",
+			 log_opts.basedir,
+			 "invalid_databases.txt");
+
+	/*
+	 * If invalid databases exist in cluster they are ordered after the valid
+	 * ones in dbarr.
+	 */
+	for (dbnum = cluster->dbarr.ndbs; dbnum < cluster->dbarr.ntotaldbs; dbnum++)
+	{
+		if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
+			pg_fatal("could not open file \"%s\": %m", output_path);
+		fprintf(script, "%s\n", cluster->dbarr.dbs[dbnum].db_name);
+	}
+
+	if (script)
+	{
+		fclose(script);
+		pg_log(PG_REPORT, "fatal");
+		pg_fatal("Your installation contains invalid databases as a consequence of\n"
+				 "interrupted DROP DATABASE.  They are now marked as corrupted databases\n"
+				 "that cannot be connected to anymore.  Consider removing them using\n"
+				 "    DROP DATABASE ...;\n"
+				 "or run pg_upgrade with --skip-invalid-databases to skip transferring\n"
+				 "invalid databases to the new cluster.\n"
+				 "A list of invalid databases is in the file:\n"
+				 "    %s", output_path);
+	}
+	else
+		check_ok();
+}
+
+
+/*
+ * create_invalid_databases_report()
+ *
+ *	This is particularly useful for listing skipped invalid databases.
+ */
+void
+create_invalid_databases_report(char **skipped_invalid_databases_file_name)
+{
+	int			dbnum;
+	FILE	   *script = NULL;
+
+	if (!user_opts.skip_invalid_dbs || old_cluster.dbarr.ndbs == old_cluster.dbarr.ntotaldbs)
+		return;
+
+	*skipped_invalid_databases_file_name = psprintf("%sskipped_invalid_databases.txt",
+													SCRIPT_PREFIX);
+
+	prep_status("Creating report for skipped invalid databases");
+
+	for (dbnum = old_cluster.dbarr.ndbs; dbnum < old_cluster.dbarr.ntotaldbs; dbnum++)
+	{
+		if (script == NULL && (script = fopen_priv(*skipped_invalid_databases_file_name, "w")) == NULL)
+			pg_fatal("could not open file \"%s\": %m", *skipped_invalid_databases_file_name);
+		fprintf(script, "%s\n", old_cluster.dbarr.dbs[dbnum].db_name);
+	}
+
+	if (script != NULL)
+	{
+		report_status(PG_WARNING, "warning");
+		pg_log(PG_WARNING,
+			   "\nWARNING:  old cluster contains invalid databases which have been skipped");
+		fclose(script);
+	}
+	else
+		check_ok();
+}
 
 /*
  *	check_proper_datallowconn
@@ -1115,10 +1225,15 @@ check_proper_datallowconn(ClusterInfo *cluster)
 
 	conn_template1 = connectToServer(cluster, "template1");
 
-	/* get database names */
+	/*
+	 * get database names; skip invalid databases as they are handled in
+	 * check_for_invalid_dbs()
+	 */
 	dbres = executeQueryOrDie(conn_template1,
 							  "SELECT	datname, datallowconn "
-							  "FROM	pg_catalog.pg_database");
+							  "FROM	pg_catalog.pg_database "
+							  "WHERE datconnlimit != %d",
+							  DATCONNLIMIT_INVALID_DB);
 
 	i_datname = PQfnumber(dbres, "datname");
 	i_datallowconn = PQfnumber(dbres, "datallowconn");
@@ -1982,7 +2097,9 @@ check_old_cluster_subscription_state(void)
 
 	/*
 	 * Check that all the subscriptions have their respective replication
-	 * origin.  This check only needs to run once.
+	 * origin.  This check only needs to run once.  Skip invalid databases,
+	 * this check will only be performed when user_opts.skip_invalid_dbs is
+	 * true.
 	 */
 	conn = connectToServer(&old_cluster, old_cluster.dbarr.dbs[0].db_name);
 	res = executeQueryOrDie(conn,
@@ -1992,7 +2109,9 @@ check_old_cluster_subscription_state(void)
 							"	ON o.roname = 'pg_' || s.oid "
 							"INNER JOIN pg_catalog.pg_database d "
 							"	ON d.oid = s.subdbid "
-							"WHERE o.roname IS NULL;");
+							"WHERE o.roname IS NULL "
+							"   AND d.datconnlimit != %d",
+							DATCONNLIMIT_INVALID_DB);
 	ntup = PQntuples(res);
 	for (int i = 0; i < ntup; i++)
 	{
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index f83ded89cb..514ed2d612 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -11,6 +11,7 @@
 
 #include "access/transam.h"
 #include "catalog/pg_class_d.h"
+#include "catalog/pg_database.h"
 #include "pg_upgrade.h"
 #include "pqexpbuffer.h"
 
@@ -407,14 +408,16 @@ get_db_infos(ClusterInfo *cluster)
 	PGresult   *res;
 	int			ntups;
 	int			tupnum;
+	int			valid_tups;
 	DbInfo	   *dbinfos;
 	int			i_datname,
+				i_datconnlimit,
 				i_oid,
 				i_spclocation;
 	char		query[QUERY_ALLOC];
 
 	snprintf(query, sizeof(query),
-			 "SELECT d.oid, d.datname, d.encoding, d.datcollate, d.datctype, ");
+			 "SELECT d.oid, d.datname, d.encoding, d.datcollate, d.datctype, d.datconnlimit, ");
 	if (GET_MAJOR_VERSION(cluster->major_version) >= 1700)
 		snprintf(query + strlen(query), sizeof(query) - strlen(query),
 				 "datlocprovider, datlocale, ");
@@ -430,30 +433,36 @@ get_db_infos(ClusterInfo *cluster)
 			 " LEFT OUTER JOIN pg_catalog.pg_tablespace t "
 			 " ON d.dattablespace = t.oid "
 			 "WHERE d.datallowconn = true "
-			 "ORDER BY 1");
+			 "ORDER BY "
+			 " CASE WHEN d.datconnlimit != %d THEN 0 ELSE 1 END, "
+			 " d.oid", DATCONNLIMIT_INVALID_DB);
 
 	res = executeQueryOrDie(conn, "%s", query);
 
 	i_oid = PQfnumber(res, "oid");
 	i_datname = PQfnumber(res, "datname");
+	i_datconnlimit = PQfnumber(res, "datconnlimit");
 	i_spclocation = PQfnumber(res, "spclocation");
 
 	ntups = PQntuples(res);
 	dbinfos = (DbInfo *) pg_malloc0(sizeof(DbInfo) * ntups);
 
-	for (tupnum = 0; tupnum < ntups; tupnum++)
+	for (tupnum = 0, valid_tups = 0; tupnum < ntups; tupnum++)
 	{
 		dbinfos[tupnum].db_oid = atooid(PQgetvalue(res, tupnum, i_oid));
 		dbinfos[tupnum].db_name = pg_strdup(PQgetvalue(res, tupnum, i_datname));
 		snprintf(dbinfos[tupnum].db_tablespace, sizeof(dbinfos[tupnum].db_tablespace), "%s",
 				 PQgetvalue(res, tupnum, i_spclocation));
+		if (atoi(PQgetvalue(res, tupnum, i_datconnlimit)) != DATCONNLIMIT_INVALID_DB)
+			valid_tups++;
 	}
 	PQclear(res);
 
 	PQfinish(conn);
 
 	cluster->dbarr.dbs = dbinfos;
-	cluster->dbarr.ndbs = ntups;
+	cluster->dbarr.ndbs = valid_tups;
+	cluster->dbarr.ntotaldbs = ntups;
 }

@@ -774,7 +783,7 @@ free_db_and_rel_infos(DbInfoArr *db_arr)
 {
 	int			dbnum;
 
-	for (dbnum = 0; dbnum < db_arr->ndbs; dbnum++)
+	for (dbnum = 0; dbnum < db_arr->ntotaldbs; dbnum++)
 	{
 		free_rel_infos(&db_arr->dbs[dbnum].rel_arr);
 		pg_free(db_arr->dbs[dbnum].db_name);
@@ -782,6 +791,7 @@ free_db_and_rel_infos(DbInfoArr *db_arr)
 	pg_free(db_arr->dbs);
 	db_arr->dbs = NULL;
 	db_arr->ndbs = 0;
+	db_arr->ntotaldbs = 0;
 }

diff --git a/src/bin/pg_upgrade/option.c b/src/bin/pg_upgrade/option.c
index 6f41d63eed..1937796a72 100644
--- a/src/bin/pg_upgrade/option.c
+++ b/src/bin/pg_upgrade/option.c
@@ -60,6 +60,7 @@ parseCommandLine(int argc, char *argv[])
 		{"copy", no_argument, NULL, 2},
 		{"copy-file-range", no_argument, NULL, 3},
 		{"sync-method", required_argument, NULL, 4},
+		{"skip-invalid-databases", no_argument, NULL, 5},
 
 		{NULL, 0, NULL, 0}
 	};
@@ -69,6 +70,7 @@ parseCommandLine(int argc, char *argv[])
 	DataDirSyncMethod unused;
 
 	user_opts.do_sync = true;
+	user_opts.skip_invalid_dbs = false;
 	user_opts.transfer_mode = TRANSFER_MODE_COPY;
 
 	os_info.progname = get_progname(argv[0]);
@@ -212,6 +214,10 @@ parseCommandLine(int argc, char *argv[])
 				user_opts.sync_method = pg_strdup(optarg);
 				break;
 
+			case 5:
+				user_opts.skip_invalid_dbs = true;
+				break;
+
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
 						os_info.progname);
@@ -306,6 +312,7 @@ usage(void)
 	printf(_("  --clone                       clone instead of copying files to new cluster\n"));
 	printf(_("  --copy                        copy files to new cluster (default)\n"));
 	printf(_("  --copy-file-range             copy files to new cluster with copy_file_range\n"));
+	printf(_("  --skip-invalid-databases      skip transferring invalid databases to the new cluster\n"));
 	printf(_("  --sync-method=METHOD          set method for syncing files to disk\n"));
 	printf(_("  -?, --help                    show this help, then exit\n"));
 	printf(_("\n"
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index 663235816f..b0353327b9 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -41,6 +41,7 @@
 #include <time.h>
 
 #include "catalog/pg_class_d.h"
+#include "catalog/pg_database.h"
 #include "common/file_perm.h"
 #include "common/logging.h"
 #include "common/restricted_token.h"
@@ -83,7 +84,8 @@ char	   *output_files[] = {
 int
 main(int argc, char **argv)
 {
-	char	   *deletion_script_file_name = NULL;
+	char	   *deletion_script_file_name = NULL,
+			   *skipped_invalid_databases_file_name = NULL;
 
 	/*
 	 * pg_upgrade doesn't currently use common/logging.c, but initialize it
@@ -219,6 +221,8 @@ main(int argc, char **argv)
 
 	create_script_for_old_cluster_deletion(&deletion_script_file_name);
 
+	create_invalid_databases_report(&skipped_invalid_databases_file_name);
+
 	issue_warnings_and_set_wal_level();
 
 	pg_log(PG_REPORT,
@@ -226,9 +230,10 @@ main(int argc, char **argv)
 		   "Upgrade Complete\n"
 		   "----------------");
 
-	output_completion_banner(deletion_script_file_name);
+	output_completion_banner(deletion_script_file_name, skipped_invalid_databases_file_name);
 
 	pg_free(deletion_script_file_name);
+	pg_free(skipped_invalid_databases_file_name);
 
 	cleanup_output_dirs();
 
@@ -859,10 +864,15 @@ set_frozenxids(bool minmxid_only)
 							  "SET	datminmxid = '%u'",
 							  old_cluster.controldata.chkpnt_nxtmulti));
 
-	/* get database names */
+	/*
+	 * get database names; skip invalid databases as they are handled in
+	 * check_for_invalid_dbs()
+	 */
 	dbres = executeQueryOrDie(conn_template1,
 							  "SELECT	datname, datallowconn "
-							  "FROM	pg_catalog.pg_database");
+							  "FROM	pg_catalog.pg_database "
+							  "WHERE datconnlimit != %d",
+							  DATCONNLIMIT_INVALID_DB);
 
 	i_datname = PQfnumber(dbres, "datname");
 	i_datallowconn = PQfnumber(dbres, "datallowconn");
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index 53f693c2d4..8abb1688ca 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -213,7 +213,8 @@ typedef struct
 typedef struct
 {
 	DbInfo	   *dbs;			/* array of db infos */
-	int			ndbs;			/* number of db infos */
+	int			ndbs;			/* number of valid db infos */
+	int			ntotaldbs;		/* total number of db infos */
 } DbInfoArr;
 
 /*
@@ -323,6 +324,8 @@ typedef struct
 	bool		check;			/* check clusters only, don't change any data */
 	bool		live_check;		/* check clusters only, old server is running */
 	bool		do_sync;		/* flush changes to disk */
+	bool		skip_invalid_dbs;	/* skip transferring invalid databases to
+									 * the new cluster */
 	transferMode transfer_mode; /* copy files or link them? */
 	int			jobs;			/* number of processes/threads to use */
 	char	   *socketdir;		/* directory to use for Unix sockets */
@@ -371,10 +374,11 @@ void		check_and_dump_old_cluster(void);
 void		check_new_cluster(void);
 void		report_clusters_compatible(void);
 void		issue_warnings_and_set_wal_level(void);
-void		output_completion_banner(char *deletion_script_file_name);
+void		output_completion_banner(char *deletion_script_file_name, char *skipped_invalid_databases_file_name);
 void		check_cluster_versions(void);
 void		check_cluster_compatibility(void);
 void		create_script_for_old_cluster_deletion(char **deletion_script_file_name);
+void		create_invalid_databases_report(char **skipped_invalid_databases_file_name);

 /* controldata.c */
diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
index 17af2ce61e..c7da6d27b2 100644
--- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl
+++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
@@ -424,7 +424,7 @@ SKIP:
 			$mode, '--check',
 		],
 		1,
-		[qr/invalid/],    # pg_upgrade prints errors on stdout :(
+		[qr/invalid_databases\.txt/],    # pg_upgrade prints errors on stdout :(
 		[qr/^$/],
 		'invalid database causes failure');
 	rmtree($newnode->data_dir . "/pg_upgrade_output.d");
-- 
2.46.2

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 14 Oct 2024 12:57:20 -0400

> Apart from a missing --skip-invalid-databases test, does this attempt look OK?

I don't think there is enough demand for the feature of skipping invalid
databases because we have gotten few reports of such problems, and also
because your case is related to an external tool causing this problem.

What might be acceptable would be to add an option that would make
pg_upgrade more tolerant of problems in many areas, that is a lot more
research and discussion.

---

From: Daniel Gustafsson <[email-masked]>
Date: Fri, 25 Oct 2024 13:55:57 +0200

> On 14 Oct 2024, at 18:57, Bruce Momjian <[email-masked]> wrote:

> pg_upgrade more tolerant of problems in many areas, that is a lot more

I agree that the concept of having pg_upgrade perform (opt-in) skipping and/or
repairs of the old cluster warrants a larger discussion in its own thread.
There has been significant amount of energy spent recently to add structure to
the checks, any new feature should be properly designed for the get-go.

In the meantime, the OP has a good point that it's a tad silly that pg_upgrade
fails hard on invalid databases instead of detecting and reporting like how
other errors are handled.  The attached adds this check and expands the report
wording to cover it.

--
Daniel Gustafsson

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 31 Oct 2024 20:36:25 -0400

> other errors are handled.  The attached adds this check and expands the report

Agreed.

---

From: Daniel Gustafsson <[email-masked]>
Date: Wed, 6 Nov 2024 16:03:35 +0100

> On 1 Nov 2024, at 01:36, Bruce Momjian <[email-masked]> wrote:

>> other errors are handled.  The attached adds this check and expands the report

I've applied this part, the discussion on whether or not pg_upgrade should gain
capabilities to skip and/or fix issues should probably be carried over in a new
thread.

--
Daniel Gustafsson