Message-ID: <f3d134f55ccaac23c6343d9c0c72a1843e593065.camel@cybertec.at>
Subject: Re: pg_upgradecluster and synchronous replication
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Christoph Berg <myon@debian.org>
Cc: pgsql-pkg-debian@lists.postgresql.org
Date: Wed, 14 Jan 2026 20:54:43 +0100
In-Reply-To: <aWfw90dIjJEui1QF@msg.df7cb.de>
References: <d3f21029c8ea11f6a61cfb8045ac11480787411e.camel@cybertec.at>
	 <aWfw90dIjJEui1QF@msg.df7cb.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.2 (3.58.2-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/f3d134f55ccaac23c6343d9c0c72a1843e593065.camel%40cybertec.at>
Precedence: bulk

On Wed, 2026-01-14 at 20:39 +0100, Christoph Berg wrote:
> > =C2=A0=C2=A0 vacuumdb ... --analyze-in-stages --missing-stats-only
> >=20
> > processing hung, because the synchronous standby was not available and =
the transaction
> > could not commit.
> >
> > I wonder if pg_upgradecluster could improve things by disabling synchro=
nous_standby_names
> > when the cluster ist started for the "finish" stage.=C2=A0 I have attac=
hed a POC patch how this
> > could be done.=C2=A0 I didn't test it, and my Perl skills are marginal,=
 but you get the idea.
>=20
> Maybe we should instead change the analyze hook script to do that
> internally? Setting PGOPTIONS should be enough:
>=20
> =C2=A0=C2=A0=C2=A0 PGOPTIONS=3D"-csynchronous_commit=3Dlocal"
>=20
> > Perhaps this is too much black magic, not sure.=C2=A0 But I wanted to s=
hare my experience.
>=20
> There is already some code in pg_upgradecluster that works around
> black magic problems:
>=20
> =C2=A0=C2=A0=C2=A0 # ensure we can upgrade DBs with default read-only tra=
nsactions
> =C2=A0=C2=A0=C2=A0 $ENV{PGOPTIONS} .=3D " -cdefault_transaction_read_only=
=3Doff";
>=20
> This would just add one more wart of that kind.

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

> That's also the command that does the final cluster start for
> 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 c=
luster for
production with "synchronous_commit" set to a non-standard value.  That wou=
ld 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=3Dlocal" during the ANALYZE, then restart the cl=
uster without
   overriding any parameters.  Then the upgrade itself would work, and the =
problem with
   the missing synchronous standby would manifest right when the user start=
s using the
   upgraded cluster.  Better than having things start failing after the nex=
t restart,
   perhaps months later, when it is harder to trace the problem to its orig=
inal cause!

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

Yours,
Laurenz Albe