public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Christoph Berg <[email protected]>
Cc: [email protected]
Subject: Re: pg_upgradecluster and synchronous replication
Date: Wed, 14 Jan 2026 20:54:43 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

On Wed, 2026-01-14 at 20:39 +0100, Christoph Berg wrote:
> >    vacuumdb ... --analyze-in-stages --missing-stats-only
> > 
> > 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 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.
> 
> 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.

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 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

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: pg_upgradecluster and synchronous replication
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox