[PATCH] pg_upgrade: add --initdb option to create the new cluster automatically

My review. Overall, this looks really nice and a much needed feature. I
will definitely be using this a lot in the future :)

It cleanly compiled on top of 97a18c22 (from Fri Jul 10 22:34:24 2026), and
I was able to successfully do the upgrade via the new --initdb flag for
versions 11 through 18. Version 10 did not work, but that is not the fault
of this patch. But the output for completeness, since we still support
upgrading from v10:

Inspecting old cluster locale for new cluster creation
invalid connection option "max_protocol_version"

I also verified that it correctly maintains the checksum setting, the
locale, weird WAL segment sizes, and the new icu and builtin locale
options. However, the tests do none of those checks, and they really ought
to, as that's an important part of what this feature provides.

Extra initdb options can be passed via the existing -O flag and will be
forwarded to the initdb invocation.

I don't like this part, as there is not a 100% overlap between what
postmaster accepts and what initdb accepts. The -c options are fine, but
things like -O "-B 12345" cause a failure. I'd simply not add the -O
options here: if people really need them, they can run pg_upgrade without
the --initdb flag. Alternatively, only allow -c items, but that's a real
pain to code that.

+static void create_new_cluster_via_initdb(void);

Nice, I love descriptive function names.

+ * resolve_new_bindir()

Good idea, well executed.

+/* Refuse to overwrite an existing cluster. */

Move this up to just after the other check that initdb is present?

+ snprintf(verfile, sizeof(verfile), "%s/PG_VERSION",

I suppose this is slightly better than just checking if the dir is empty or
not, as initdb itself will later fail if non-empty but does not contain
PG_VERSION. So a weak +1. Sort of has test coverage (but see below)

+ * Verify that initdb is present and executable before doing any work.

Also not tested at all (may be overkill to do so, but pointing out for
completeness)

+# Copyright (c) 2022-2025, PostgreSQL Global Development Group

s/2025/2026/ - or perhaps just say "2026" as this is a new file

+# --initdb must refuse to clobber an already-populated data directory.
+command_fails(

Would be nice if we confirmed this was failing due to the new PG_VERSION
check, versus the initdb "ain't gonna write to non empty dir" error.

Cheers,
Greg

Hi,

Hi,

This patch adds an --initdb option to pg_upgrade that automates the initdb step currently required before running pg_upgrade.

Problem
—————
Before running pg_upgrade, users must manually run initdb with options that exactly match the old cluster: WAL segment size, data checksum setting, encoding, and locale. Getting these right is error-prone. A mismatch results in a confusing check_control_data() failure after the user has already invested time in setting up the new cluster. A related question was raised before [1], where Jeff Davis discussed whether pg_upgrade should perform initdb itself rather than requiring a pre-initialized cluster.

Solution
————
With --initdb, pg_upgrade handles this automatically. It derives the WAL segment size and checksum setting from pg_control and invokes initdb with the correct flags. The option refuses to proceed if the new cluster already exists.

Thanks for the patch but I have a couple of concerns that will require
further discussion.

Since we include the initdb option to pg_upgrade it can cause orphan
directories that users must manually solve. Partial initdb failures
leave orphaned clusters.

Moreover, in the patch the old cluster is being started/stopped twice
meaning that the pg_upgrade needs to manage the states. This will
cause unpredictable behavior on retry and make things really hard to
debug I believe.

On the other side, the responsibility of pg_upgrade shouldn't contain
initializing a new cluster and deciding the locale,encoding, and
checksums. If the idea is accepted we should focus on documentation
and avoid the risks it will introduce during the upgrade.

Regards,
Demir.

Thanks for the review, Greg. Glad it's useful, and thanks for trying it
across the version range.

Attached is v2, which addresses your points:

- Added test coverage for the settings the feature is really about. The
test now initdb's the old cluster with data checksums and a non-default WAL
segment size, and after the upgrade checks that the new cluster inherited
the checksum setting, WAL segment size, encoding, collation, ctype, and
locale provider.

- The overwrite test now confirms the failure comes from pg_upgrade's own
PG_VERSION check ("already contains a database system") rather than
initdb's non-empty-directory error.

- Added a test for the "initdb not present" case (pointing --new-bindir at
an empty dir), since it was easy enough to cover.

- Reworked the -O handling. Rather than dropping it entirely, I took your
alternative: only "-c name=value" options are forwarded to initdb now, and
anything else is skipped with a warning. So -O "-c work_mem=..." works, and
-O "-B 12345" no longer breaks initdb.

That keeps GUC pass-through working without pretending initdb accepts the
full postmaster option set.

- Moved the "refuse to overwrite" check up to right after the
initdb-present check.

- Fixed the copyright year on the new test file.

On the v10 failure ("invalid connection option max_protocol_version"):
that comes from stock pg_upgrade, which adds max_protocol_version=3.0 to
the connection string for pre-PG11 servers (server.c). It's not introduced
by --initdb, the same connection is made in the normal flow, so I've left
it out of scope for this patch.

The patch is pgindent-clean and the TAP test passes. For convenience, the
branch is also on GitHub:

github.com/LeeBohyun/postgres/tree/pg_upgrade_initdb

Best regards,

Bohyun

On Fri, Jul 10, 2026 at 7:24 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:

I am not convinced that all this complexity and overhead isn't better suited
for external wrappers to pg_upgrade like Debian's pg_upgradecluster etc.

+	if (user_opts.initdb_new_cluster)
+		create_new_cluster_via_initdb();
+
 	adjust_data_dir(&new_cluster);

If pg_upgrade controls how initdb was invoked for the new cluster, shouldn't it
work such that adjust_data_dir isn't required?

+	/*
+	 * get_control_data() selects pg_resetwal vs. pg_resetxlog via
+	 * bin_version, which check_bindir() normally fills in later.  Seed it now
+	 * so the right binary name is used in this early call.
+	 */
+	if (old_cluster.bin_version == 0)
+		old_cluster.bin_version = old_cluster.major_version;
+
+	get_control_data(&old_cluster);

This can't be done unconditionally, the old cluster can still be running at
this point. For example if someone wants to do a live-check:

$ ./bin/pg_upgrade -b ./bin/ -B ./bin/ -d ./data_old/ -D ./data_new/ --check --initdb

The source cluster was not shut down cleanly, state reported as: "in production"
Failure, exiting

get_control_data is big, expensive, and really designed to be run once. I
don't think it's Ok to run it an extra time here without at least being able to
tell the later invocation that it has already been executed. Also,
check_bindir() as referred to in the comment does not exist.

+ prep_status("Inspecting old cluster locale for new cluster creation");
+ start_postmaster(&old_cluster, true);
+ get_template0_info(&old_cluster);
+ stop_postmaster(false);
+ check_ok();

Again, cannot be done unconditionally.

+	 * Users needing options that only the postmaster accepts can create the
+	 * new cluster manually and omit --initdb.

This should probably be expanded upon in the documentation.

--
Daniel Gustafsson

Hi Daniel,

Thanks for the review. v3 attached.

*On external wrapper vs. in-tree:* the point of --initdb is deriving the
new cluster's settings (WAL segment size, checksums, encoding, locale) from
the old cluster, exactly what people get wrong by hand and only discover
when check_control_data() fails. pg_upgrade already reads the old control
data, so doing it here reuses that rather than making a wrapper rediscover
it. I'm open to being convinced otherwise.

The rest:
*- --check --initdb:* you're right it was broken (ran before live-check was
determined). Since --check is read-only and --initdb creates the cluster,
the combination doesn't make sense — v3 rejects it at option parsing, which
removes the live-check problem entirely.
*- get_control_data() twice:* the early --initdb read populates the old
cluster's control data, so the later call in check_cluster_compatibility()
returns early when ctrl_ver is already set. New cluster is still read fresh.
*- adjust_data_dir(): *moved adjust_data_dir(&new_cluster) before the
initdb call so a config-only -D is resolved first.
*- check_bindir(): *meant get_bin_version(). Fixed.
*- Docs:* noted that only initdb-accepted settings are configured,
postmaster-only options need manual creation, and the --check restriction.

TAP test covers the --check rejection. pgindent-clean, tests pass.
https://github.com/LeeBohyun/postgres/tree/pg_upgrade_initdb
Best regards,
Bohyun

On Tue, Jul 14, 2026 at 12:06 PM Daniel Gustafsson <daniel@yesql.se> wrote:

On 14 Jul 2026, at 16:42, Bohyun Lee <bohyun.lee@databricks.com> wrote:

Thanks for the update.

Right now most of the focus is on getting v19 ready to ship, I recommend
registering the patch in the commitfest app to make sure it's not lost (if you
already did I just failed to find it).

On external wrapper vs. in-tree: the point of --initdb is deriving the new cluster's settings (WAL segment size, checksums, encoding, locale) from the old cluster, exactly what people get wrong by hand and only discover when check_control_data() fails. pg_upgrade already reads the old control data, so doing it here reuses that rather than making a wrapper rediscover it. I'm open to being convinced otherwise.

Maybe. Since it won't be a mandatory step, we still need to document the
process properly and I see that as the first step. Currently the docs have
"use compatible initdb flags that match the old cluster" which clearly isn't
particularly helpful.

--
Daniel Gustafsson

Second review pass.

Cleanly compiles and all tests pass when applied to 637aa273e (Thu Jul 16
11:56:01 2026 -0400)

/*
* resolve_new_bindir()
*
* Idempotent helper: if new_cluster.bindir has not been set by the user via
* -B, derive it from the path of the currently executing pg_upgrade binary.
* Called early by create_new_cluster_via_initdb() so that the initdb path
* is available before verify_directories() runs.
*/

Do we really need the last two lines? This is also called during the
"normal" path, not just via create_new_cluster_via_initdb, so not sure why
we are pointing it out here.

prep_status("Inspecting old cluster locale for new cluster creation");

We are doing a lot more than locale though. How about:

prep_status("Examining old cluster settings");

# Use non-default settings that --initdb must carry over to the new

cluster

# (derived from the old cluster's pg_control): data checksums and a

non-default

# WAL segment size. We check below that the new cluster inherits them.
my $oldnode = PostgreSQL::Test::Cluster->new('old_node');
$oldnode->init(extra => [ '--data-checksums', '--wal-segsize' => '2' ]);

But checkums ARE the default now, so for a non-default setting, we should
do --no-data-checksums. Would be nice to check locale as well. That gets
tricky, but maybe --locale=C is nicely portable and pretty non-default?

my $conf = $newnode->data_dir . '/postgresql.conf';
open(my $fh, '>>', $conf) or die "could not open $conf: $!";

Easier to use $newnode->append_conf

print $fh "listen_addresses = ''\n";
print $fh "unix_socket_directories = '" . $newnode->host . "'\n";

What about Win32? See .e.g $use_tcp in Cluster.pm

* Forward only "-c name=value" options from -O to initdb. initdb accepts

I still think we just bail on supporting -O. The new code is an
improvement, but fails if there are legitimate spaces in any of the args:

$ pg_upgrade --initdb -O "-c transaction_isolation='repeatable read'"

Creating new cluster with initdb ignoring
non-"-c" option(s) passed via -O; only "-c" settings are forwarded to
initdb for --initdb
sh: 1: Syntax error: Unterminated quoted string

*failure*

Cheers,
Greg

Thanks both for the reviews. v4 attached, addressing all of Greg's
second-pass points and Daniel's documentation point.

*Greg:*

-

resolve_new_bindir(): dropped the last two comment lines.
-

prep_status text is now "Examining old cluster settings".
-

Test: since checksums are now the default, the old cluster is created
with --no-data-checksums (a genuinely non-default value) and --locale=C.
The post-upgrade checks confirm the new cluster inherited both, along with
the WAL segment size, encoding, collation, ctype, and locale provider.
-

Test: replaced open()/print() with append_conf, and the port/socket
settings now follow the framework's TCP-vs-Unix logic ($use_tcp), so
Windows works too.
-

-O: dropped support with --initdb entirely, as you originally suggested.
The partial "-c only" forwarding still broke on quoted values with spaces.
pg_upgrade now rejects -O + --initdb during option parsing, with a test and
a doc note.

*Daniel:*

-

Reworked the manual-initdb docs: the vague "use compatible initdb flags"
is now the specific settings that must match (WAL segment size, checksums,
encoding, locale), with a pointer to pg_controldata.

On in-tree vs. wrapper: these settings have to come from the old cluster's
control data, which pg_upgrade already reads, a wrapper would have to
rediscover the same information, and getting it wrong is exactly the
failure --initdb is meant to prevent.

The patch is pgindent-clean and the full pg_upgrade TAP suite passes
(including the new t/009_initdb_option.pl).

Registered in PG20-2. Branch on GitHub:
https://github.com/LeeBohyun/postgres/tree/pg_upgrade_initdb

Best regards,

Bohyun

On Thu, Jul 16, 2026 at 8:17 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote: