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 1otbBE-0000tR-8M for pgsql-general@arkaria.postgresql.org; Fri, 11 Nov 2022 21:03:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1otbBC-0000Z2-2T for pgsql-general@arkaria.postgresql.org; Fri, 11 Nov 2022 21:03:14 +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 1otbBA-0000Yr-9X for pgsql-general@lists.postgresql.org; Fri, 11 Nov 2022 21:03:13 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1otbB2-00056z-1h for pgsql-general@lists.postgresql.org; Fri, 11 Nov 2022 21:03:11 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id B02AC5C00CB; Fri, 11 Nov 2022 16:03:00 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Fri, 11 Nov 2022 16:03:00 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm3; t=1668200580; x= 1668286980; bh=EUP9vR5AvRD6OXEnsnciXAFPIO1mBVWPNAkVEeyswSo=; b=M suf4FmOXIis12CwsyVWvt4F/909YmqFf5var7P9cCLJ8zdXIj7btKDOT4KjzBbW5 waMvEBPYKQ4rStykmJxFKMD6EKsj4FpFLPwac/Sqw3s260F/F2iC52fj+KhX6vXb +9Ev6ajz4egdED4LZoG7xaluCjal9i/OoN1bKpIjIhWMK03MFVIAH1DkRV0ZAhYj ScWWlxGCddpAitKxny2+4GdFxcGGQ1jvgxdeRChBZbcq5QmNH0dj5Edd4hH15nCy wFndsG4AUNTDmtAuu9aw7x4SkrbfmmBmtD9zjq52IzCQaHOqXhj8raBNAen9noqb hbKJl41FZbPmucAsZaoSg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:date:feedback-id:feedback-id:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1668200580; x=1668286980; bh=E UP9vR5AvRD6OXEnsnciXAFPIO1mBVWPNAkVEeyswSo=; b=sqOvJjiLhwDpwR5hd egHGoG72SH0OS/ug8Ev2l9JlsohD+ekf/nNvZ92oGtd4to5H9+kpKfhvDnXgSmWj D1V08RpnFDs9+nskiK9ON7rX//UMIP8hxNnQugTtIDrxAgpNVz/GpT42Z+Sy4vk2 VYGonW/tj0tpG7QjqOPeBTzqKiYg2DZ13ozuOEElqkarn/Fh1T9Th472b4e2sRYx QUHvNTepwb2E0lnmS4sbDk94cwOP1g0RMG8zm6XZF6lrlaqIrfk63TpvsMoSI5/s FKAygMTsbn2LCTP+5etgm2jM7skdzC+VFAzBgJJbq1aokHzYVs6K5611h4pzKjXk Dkt+w== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvgedrfeeigddugeehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpedugfeuleegveffhfegleejhefhtedtgeeileeggfeh fefffeevgeejtdeutefftdenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 11 Nov 2022 16:02:59 -0500 (EST) Message-ID: Date: Fri, 11 Nov 2022 13:02:58 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.1 Subject: Re: Upgrading to v12 Content-Language: en-US To: Brad White , pgsql-general@lists.postgresql.org References: From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/11/22 12:43, Brad White wrote: > I'm upgrading from v9.4 to v12.10 as a half step to 15. > > Q1: How do I tell it which database to upgrade? > I only need the primary. > Not the half dozen restored copies. > Or do I need to detach everything I don't want copied? 1) If you are using pg_upgrade then it only works on the entire cluster not individual databases. 2) This is not SQLite there is no detaching of databases. 3) I you want to move a single database then you are looking at pg_dump/pg_restore or logical replication. > > Q2: I get this error, and then at the end, it says "No error." What was the complete pg_upgrade command you used? > > Performing Consistency Checks > ----------------------------- > Checking cluster versions                                   ok > SQL command failed > WITH regular_heap (reloid, indtable, toastheap) AS (   SELECT c.oid, > 0::oid, 0::oid   FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace > n          ON c.relnamespace = n.oid   WHERE relkind IN ('r', 'm') AND >   ((n.nspname !~ '^pg_temp_' AND       n.nspname !~ '^pg_toast_temp_' > AND       n.nspname NOT IN ('pg_catalog', 'information_schema', >                 'binary_upgrade', 'pg_toast') AND       c.oid >= > 16384::pg_catalog.oid) OR      (n.nspname = 'pg_catalog' AND > relname IN ('pg_largeobject') ))),   toast_heap (reloid, indtable, > toastheap) AS (   SELECT c.reltoastrelid, 0::oid, c.oid   FROM > regular_heap JOIN pg_catalog.pg_class c       ON regular_heap.reloid = > c.oid   WHERE c.reltoastrelid != 0),   all_index (reloid, indtable, > toastheap) AS (   SELECT indexrelid, indrelid, 0::oid   FROM > pg_catalog.pg_index   WHERE indisvalid AND indisready     AND indrelid > IN         (SELECT reloid FROM regular_heap          UNION ALL >  SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, > c.relname,   c.relfilenode, c.reltablespace, > pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * > FROM regular_heap       UNION ALL       SELECT * FROM toast_heap > UNION ALL       SELECT * FROM all_index) all_rels   JOIN > pg_catalog.pg_class c       ON all_rels.reloid = c.oid   JOIN > pg_catalog.pg_namespace n      ON c.relnamespace = n.oid   LEFT OUTER > JOIN pg_catalog.pg_tablespace t      ON c.reltablespace = t.oid ORDER BY 1; > ERROR:  could not access status of transaction 22316920 > DETAIL:  Could not read from file "pg_clog/0015" at offset 73728: No error. -- Adrian Klaver adrian.klaver@aklaver.com