Thanks for both of those reviews! I'll push this shortly, with that
stray mention removed from the documentation.
Here's what I came up with for pg_upgrade. It tests each database's
encodings with PG_VALID_BE_ENCODING(), and looks like this when it
fails:
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking for unsupported encodings fatal
Your installation contains databases using encodings that are
no longer supported. Consider dumping and restoring with UTF8.
A list of databases with unsupported encodings is in the file:
pgdata_new/pg_upgrade_output.d/20260408T170229.964/databases_unsupported_encoding.txt
Failure, exiting
$ cat
pgdata_new/pg_upgrade_output.d/20260408T170229.964/databases_unsupported_encoding.txt
postgres
template1
template0
I'll wait a bit longer for this one, on the off chance of reviews at
this late hour.
From dda28c4562af5fc77ec8717ef5829b9225eb7b39 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email protected]>
Date: Wed, 8 Apr 2026 16:51:24 +1200
Subject: [PATCH] pg_upgrade: Check for unsupported encodings.
Since we have dropped MULE_INTERNAL, add a check that all encodings used
in the source cluster are still supported according to
PG_ENCODING_BE_VALID(). This is done generically, in case we decide to
drop another encoding some day.
Suggested-by: Jeff Davis <[email protected]>
Discussion: https://postgr.es/m/CA%2BhUKGKXDXh-FdU0orjfv%2BF08f%3DD91BhV3Ra-4zL-q%2BJmGYqTA%40mail.gmail.com
---
src/bin/pg_upgrade/check.c | 65 ++++++++++++++++++++++++++++++++++++++
1 file changed, 65 insertions(+)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index eb35c68d450..06134cf5d2e 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -13,11 +13,13 @@
#include "catalog/pg_authid_d.h"
#include "catalog/pg_class_d.h"
#include "fe_utils/string_utils.h"
+#include "mb/pg_wchar.h"
#include "pg_upgrade.h"
#include "common/unicode_version.h"
static void check_new_cluster_is_empty(void);
static void check_is_install_user(ClusterInfo *cluster);
+static void check_for_unsupported_encodings(ClusterInfo *cluster);
static void check_for_connection_status(ClusterInfo *cluster);
static void check_for_prepared_transactions(ClusterInfo *cluster);
static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
@@ -601,6 +603,11 @@ check_and_dump_old_cluster(void)
*/
check_for_connection_status(&old_cluster);
+ /*
+ * Check for encodings that are no longer supported.
+ */
+ check_for_unsupported_encodings(&old_cluster);
+
/*
* Validate database, user, role and tablespace names from the old
* cluster. No need to check in 19 or newer as newline and carriage return
@@ -1239,6 +1246,64 @@ check_for_connection_status(ClusterInfo *cluster)
}
+/*
+ * check_for_unsupported_encodings()
+ */
+static void
+check_for_unsupported_encodings(ClusterInfo *cluster)
+{
+ int i_datname;
+ int i_encoding;
+ int ntups;
+ PGresult *res;
+ PGconn *conn;
+ FILE *script = NULL;
+ char output_path[MAXPGPATH];
+
+ prep_status("Checking for unsupported encodings");
+
+ snprintf(output_path, sizeof(output_path), "%s/%s",
+ log_opts.basedir,
+ "databases_unsupported_encoding.txt");
+
+ conn = connectToServer(cluster, "template1");
+
+ res = executeQueryOrDie(conn,
+ "SELECT datname, encoding "
+ "FROM pg_catalog.pg_database");
+ ntups = PQntuples(res);
+ i_datname = PQfnumber(res, "datname");
+ i_encoding = PQfnumber(res, "encoding");
+ for (int rowno = 0; rowno < ntups; rowno++)
+ {
+ char *datname = PQgetvalue(res, rowno, i_datname);
+ int encoding = atoi(PQgetvalue(res, rowno, i_encoding));
+
+ if (!PG_VALID_BE_ENCODING(encoding))
+ {
+ if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
+ pg_fatal("could not open file \"%s\": %m", output_path);
+
+ fprintf(script, "%s\n", datname);
+ }
+ }
+ PQclear(res);
+ PQfinish(conn);
+
+ if (script)
+ {
+ fclose(script);
+ pg_log(PG_REPORT, "fatal");
+ pg_fatal("Your installation contains databases using encodings that are\n"
+ "no longer supported. Consider dumping and restoring with UTF8.\n"
+ "A list of databases with unsupported encodings is in the file:\n"
+ " %s", output_path);
+ }
+ else
+ check_ok();
+}
+
+
/*
* check_for_prepared_transactions()
*
--
2.53.0
