Subject: pg_upgradecluster and synchronous replication

From: Laurenz Albe <[email-masked]>
Date: Wed, 14 Jan 2026 18:50:00 +0100

I ran into a problem today during a training session when I demonstrated

  pg_upgradecluster -v 18 --method=upgrade --link 17 main

The problem was that I had configured synchronous streaming replication first, so after
pg_upgrade, the synchronous standby was lost, and when pg_upgradecluster ran

  vacuumdb ... --analyze-in-stages --missing-stats-only

processing hung, because the synchronous standby was not available and the transaction
could not commit.

Yes, that was my mistake, and yes, the problem was easily fixed by interrupting
pg_upgradecluster, disabling synchronous_standby_names and running the ANALYZE
and v17 cluster removal manually.

Yet I wonder if pg_upgradecluster could improve things by disabling synchronous_standby_names
when the cluster ist started for the "finish" stage.  I have attached a POC patch how this
could be done.  I didn't test it, and my Perl skills are marginal, but you get the idea.

Perhaps this is too much black magic, not sure.  But I wanted to share my experience.

Yours,
Laurenz Albe
diff --git a/pg_upgradecluster b/pg_upgradecluster
index 09979c3..1698ba8 100755
--- a/pg_upgradecluster
+++ b/pg_upgradecluster
@@ -743,7 +743,7 @@ if (not exists $ENV{'PG_CLUSTER_CONF_ROOT'} and -d '/run/systemd/system' and $>
 $start = ($info{running} or $upgrade_scripts) if ($start == -1);
 if ($start) {
     print "Starting upgraded cluster on port $newinfo{port}...\n";
-    @argv = ('pg_ctlcluster', $newversion, $newcluster, 'start');
+    @argv = ('pg_ctlcluster', $newversion, $newcluster, 'start', '--', '-o', "\"-csynchronous_standby_names=''\"");
     error "Could not start upgraded cluster; please check configuration and log files" if system @argv;
 }

---

From: Christoph Berg <[email-masked]>
Date: Wed, 14 Jan 2026 20:39:35 +0100

Re: Laurenz Albe
> processing hung, because the synchronous standby was not available and the transaction

Interesting find, thanks for sharing.

> could be done.  I didn't test it, and my Perl skills are marginal, but you get the idea.

That's also the command that does the final cluster start for
production, I wouldn't want such a change to persist.

Maybe we should instead change the analyze hook script to do that
internally? Setting PGOPTIONS should be enough:

    PGOPTIONS="-csynchronous_commit=local"

> Perhaps this is too much black magic, not sure.  But I wanted to share my experience.

There is already some code in pg_upgradecluster that works around
black magic problems:

    # ensure we can upgrade DBs with default read-only transactions
    $ENV{PGOPTIONS} .= " -cdefault_transaction_read_only=off";

This would just add one more wart of that kind.

Christoph

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 14 Jan 2026 20:54:43 +0100

On Wed, 2026-01-14 at 20:39 +0100, Christoph Berg wrote:
> > I wonder if pg_upgradecluster could improve things by disabling synchronous_standby_names

That looks like a better way to do it, I agree.

> production, I wouldn't want such a change to persist.

That makes me wonder.  I don't think that it is a great idea to start the cluster for
production with "synchronous_commit" set to a non-standard value.  That would mask the
problem initially, but whenever the cluster is restarted, the parameter is back to its
original value, and suddenly things will stop working.

I can imagine two ways to do better:

1. Set "synchronous_commit=local" during the ANALYZE, then restart the cluster without
   overriding any parameters.  Then the upgrade itself would work, and the problem with
   the missing synchronous standby would manifest right when the user starts using the
   upgraded cluster.  Better than having things start failing after the next restart,
   perhaps months later, when it is harder to trace the problem to its original cause!

2. Add a pre-upgrade check that just aborts with a useful error message if
   "synchronous_standby_names" is set.

Yours,
Laurenz Albe

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 15 Jan 2026 05:40:26 +0100

On Wed, 2026-01-14 at 20:54 +0100, I wrote:
> at makes me wonder.  I don't think that it is a great idea to start the cluster for

Forget that: I missed that with PGOPTIONS, the setting is only changed for
the database session that performs the ANALYZE, not for the entire server
runtime.  Yes, I think that is the proper solution!

Yours,
Laurenz Albe

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 15 Jan 2026 23:41:40 +0100

On Thu, 2026-01-15 at 05:40 +0100, I wrote:
> Yes, I think that is the proper solution!

How about the attached patch?

Yours,
Laurenz Albe
From 8ee74b3f17ab3b9a4f18d585769ba8da05c96fc5 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email-masked]>
Date: Thu, 15 Jan 2026 23:37:00 +0100
Subject: [PATCH v2] Prevent ANALYZE from waiting for synchronous standbys

If the cluster to upgrade has synchronous replication configured,
"vacuumdb --analyze-only" will hang waiting for the feedback of a
synchronous standby, but there is none during an upgrade.

To prevent that, set "synchronous_commit" to "local" while optimizer
statistics are being calculated.

https://postgr.es/m/flat/[email-masked]
---
 pg_upgradecluster.d/analyze | 3 +++
 1 file changed, 3 insertions(+)

diff --git a/pg_upgradecluster.d/analyze b/pg_upgradecluster.d/analyze
index 6480b5e..2a8d145 100755
--- a/pg_upgradecluster.d/analyze
+++ b/pg_upgradecluster.d/analyze
@@ -19,6 +19,9 @@ case $newversion in
         ;;
 esac
 
+# don't hang waiting for a synchronous standby server
+export PGOPTIONS="$PGOPTIONS -csynchronous_commit=local"
+
 case $newversion in
     9.2|9.3)
         vacuumdb $flags --analyze-only
-- 
2.52.0

---

From: Christoph Berg <[email-masked]>
Date: Mon, 19 Jan 2026 15:38:17 +0100

Re: Laurenz Albe
> How about the attached patch?

Thanks, pushed with a minor update - with set -e, we need to use
${PGOPTIONS:-} instead of $PGOPTIONS.

Christoph

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 19 Jan 2026 15:43:22 +0100

> ${PGOPTIONS:-} instead of $PGOPTIONS.

Thank you!

Yours,
Laurenz Albe