Date: Mon, 8 Aug 2022 20:59:29 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "Jonathan S. Katz" <jkatz@postgresql.org>,
	Noah Misch <noah@leadboat.com>, Robert Haas <robertmhaas@gmail.com>,
	Andrew Dunstan <andrew@dunslane.net>,
	Michael Paquier <michael@paquier.xyz>,
	Justin Pryzby <pryzby@telsasoft.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>,
	Shruthi Gowda <gowdashru@gmail.com>
Subject: Re: pg15b2: large objects lost on upgrade
Message-ID: <YvGxcY6RotGTFSjl@momjian.us>
References: <4152163.1659124804@sss.pgh.pa.us>
 <CA+TgmoY=MfT2uc=N!4sAcHf3_teqYBNtmqVrVYv5oEK=4RfxATw@mail.gmail.com>
 <CA+TgmoZX6Zz4KU2enOYyc+aQWA+S5ZaZRGo6BgxW7mNF4btqaw@mail.gmail.com>
 <125444.1659136594@sss.pgh.pa.us>
 <20220730054401.GB3644221@rfd.leadboat.com>
 <351616.1659192034@sss.pgh.pa.us>
 <635c8809-3525-7dae-a1a1-0d9069cd86bc@postgresql.org>
 <2657259.1659460333@sss.pgh.pa.us>
 <d0a4f263-a774-394d-134f-4edce6a980e4@postgresql.org>
 <2684751.1659468725@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <2684751.1659468725@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/YvGxcY6RotGTFSjl%40momjian.us>
Precedence: bulk

On Tue, Aug  2, 2022 at 03:32:05PM -0400, Tom Lane wrote:
> "Jonathan S. Katz" <jkatz@postgresql.org> writes:
> > On 8/2/22 1:12 PM, Tom Lane wrote:
> >> Sadly, we're still not out of the woods.  I see three buildfarm
> >> failures in this test since Robert resolved the "-X" problem [1][2][3]:
> 
> > Looking at the test code, is there anything that could have changed the 
> > relfrozenxid or relminxid independently of the test on these systems?
> 
> Hmmm ... now that you mention it, I see nothing in 002_pg_upgrade.pl
> that attempts to turn off autovacuum on either the source server or
> the destination.  So one plausible theory is that autovac moved the
> numbers since we checked.

Uh, pg_upgrade assumes autovacuum is not running, and tries to enforce
this:

    start_postmaster()
    ...
    /*
     * Use -b to disable autovacuum.
     *
     * Turn off durability requirements to improve object creation speed, and
     * we only modify the new cluster, so only use it there.  If there is a
     * crash, the new cluster has to be recreated anyway.  fsync=off is a big
     * win on ext4.
     *
     * Force vacuum_defer_cleanup_age to 0 on the new cluster, so that
     * vacuumdb --freeze actually freezes the tuples.
     */
    snprintf(cmd, sizeof(cmd),
             "\"%s/pg_ctl\" -w -l \"%s/%s\" -D \"%s\" -o \"-p %d -b%s %s%s\" start",
             cluster->bindir,
             log_opts.logdir,
             SERVER_LOG_FILE, cluster->pgconfig, cluster->port,
             (cluster == &new_cluster) ?
             " -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0" : "",
             cluster->pgopts ? cluster->pgopts : "", socket_string);

Perhaps the test script should do something similar, or this method
doesn't work anymore.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson