Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o7PqB-0005Lj-5T for pgsql-hackers@arkaria.postgresql.org; Fri, 01 Jul 2022 23:14:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1o7Pq9-0006Ig-1X for pgsql-hackers@arkaria.postgresql.org; Fri, 01 Jul 2022 23:14:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o7Pq8-0006IB-KR for pgsql-hackers@lists.postgresql.org; Fri, 01 Jul 2022 23:14:20 +0000 Received: from mail-qt1-x833.google.com ([2607:f8b0:4864:20::833]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1o7Pq4-0003I9-LF for pgsql-hackers@postgresql.org; Fri, 01 Jul 2022 23:14:20 +0000 Received: by mail-qt1-x833.google.com with SMTP id ck6so1711313qtb.7 for ; Fri, 01 Jul 2022 16:14:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=date:from:to:cc:subject:message-id:mime-version:content-disposition :user-agent; bh=ky03qkE9NYgprCA7Y6Dk6z4S0RdgdEKdeTLVkKOW0R4=; b=0Yh4GPuPZvLVHQwg/aVRddTxoBxfMRYJygPgoA7TLNaSjSdtyxWTPDSbusV3pgkXZB y53HsbfE+JfuKFWKDzZlacoTuZQcsML6gbzaoKidATTRd23GMdcvlDF93lb/RMbUN30Q lIgCnJ1m3KnFHkKsm1GiGfLDBuZ881czwr/AdoIL/cP40yHwc5pDK7TFqwAC+nC1g8rl 7C0ryIPwSbOnOFFJTo6ecSDfPY+ENzwWW/dra94MexYN84Yp4AtIVq1E8ju9NwbEXwsL B04fC5Lhndmu/HP+/x6hnEb9vmDKrf9KGqK40zj98TkSXO28koZP5sYNCdWUjqqAHsps C1AQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:mime-version :content-disposition:user-agent; bh=ky03qkE9NYgprCA7Y6Dk6z4S0RdgdEKdeTLVkKOW0R4=; b=Aknex4bAKjZ85N7YkqNUdJXX8DbRAfkIllA4fbgfejCqCMZO86BznvR33RIEzUDn9c 2P07tHUIK1rC4mzj5f43m/7N7q1/HqsnMpIQtm7/tIw9w76RTxEwkEQnUWQOJa4cFYHH osfgOQV8CYaHG0eERcTdQtlRaqbLx03sK6kYo5WWB4SmaH+UnCRWMWsod8Ju7aG1iWiz 3OWW8J+Mal2So2iMRrzkSUoDyP4DXq7P5YTBqg6jmw8c9llKtrNA1QO8p81GgBXBAqIL T2T6M7F5HC58BMIN00nIojvGejmtnQiJhEkLoaAwrvD+Bw377DRPJ+TmkgMj36YC64ib 1F0Q== X-Gm-Message-State: AJIora8i3S3q1GgWdKvARRzNAV9rrebx5EhS+lPDAwgOaXhVjyvm839b jatGd2lBX1XTGu9dNavC/ItM8/KMvGRoEw== X-Google-Smtp-Source: AGRyM1ul2ExZzgCq/000n9aAZjQnv6TyC7L5xJk9kIgfI/xGzYhAqrCQboYMXE8UxSTuC7xTYi4zLw== X-Received: by 2002:ac8:5d92:0:b0:31b:91c0:b0e0 with SMTP id d18-20020ac85d92000000b0031b91c0b0e0mr14778018qtx.139.1656717254786; Fri, 01 Jul 2022 16:14:14 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id r13-20020a05620a298d00b006afc53e0be2sm8920335qkp.117.2022.07.01.16.14.14 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 01 Jul 2022 16:14:14 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id C9A4D800740; Fri, 1 Jul 2022 18:14:13 -0500 (CDT) Date: Fri, 1 Jul 2022 18:14:13 -0500 From: Justin Pryzby To: pgsql-hackers@postgresql.org Cc: Robert Haas Subject: pg15b2: large objects lost on upgrade Message-ID: <20220701231413.GI13040@telsasoft.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I noticed this during beta1, but dismissed the issue when it wasn't easily reproducible. Now, I saw the same problem while upgrading from beta1 to beta2, so couldn't dismiss it. It turns out that LOs are lost if VACUUM FULL was run. | /usr/pgsql-15b1/bin/initdb --no-sync -D pg15b1.dat -k | /usr/pgsql-15b1/bin/postgres -D pg15b1.dat -c logging_collector=no -p 5678 -k /tmp& | psql -h /tmp postgres -p 5678 -c '\lo_import /etc/shells' -c 'VACUUM FULL pg_largeobject' | rm -fr pg15b2.dat && /usr/pgsql-15b2/bin/initdb --no-sync -k -D pg15b2.dat && /usr/pgsql-15b2/bin/pg_upgrade -d pg15b1.dat -D pg15b2.dat -b /usr/pgsql-15b1/bin | /usr/pgsql-15b2/bin/postgres -D pg15b2.dat -c logging_collector=no -p 5678 -k /tmp& Or, for your convenience, with paths in tmp_install: | ./tmp_install/usr/local/pgsql/bin/initdb --no-sync -D pg15b1.dat -k | ./tmp_install/usr/local/pgsql/bin/postgres -D pg15b1.dat -c logging_collector=no -p 5678 -k /tmp& | psql -h /tmp postgres -p 5678 -c '\lo_import /etc/shells' -c 'VACUUM FULL pg_largeobject' | rm -fr pg15b2.dat && ./tmp_install/usr/local/pgsql/bin/initdb --no-sync -k -D pg15b2.dat && ./tmp_install/usr/local/pgsql/bin/pg_upgrade -d pg15b1.dat -D pg15b2.dat -b ./tmp_install/usr/local/pgsql/bin | ./tmp_install/usr/local/pgsql/bin/postgres -D pg15b2.dat -c logging_collector=no -p 5678 -k /tmp& postgres=# table pg_largeobject_metadata ; 16384 | 10 | postgres=# \lo_list 16384 | pryzbyj | postgres=# \lo_export 16384 /dev/stdout lo_export postgres=# table pg_largeobject; postgres=# \dt+ pg_largeobject pg_catalog | pg_largeobject | table | pryzbyj | permanent | heap | 0 bytes | I reproduced the problem at 9a974cbcba but not its parent commit. commit 9a974cbcba005256a19991203583a94b4f9a21a9 Author: Robert Haas Date: Mon Jan 17 13:32:44 2022 -0500 pg_upgrade: Preserve relfilenodes and tablespace OIDs. -- Justin