Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTwAs-00BB2t-SF for pgsql-general@arkaria.postgresql.org; Thu, 10 Aug 2023 03:17:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qTwAr-00ARUW-5x for pgsql-general@arkaria.postgresql.org; Thu, 10 Aug 2023 03:17:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTwAq-00ARUO-6c for pgsql-general@lists.postgresql.org; Thu, 10 Aug 2023 03:17:21 +0000 Received: from resdmta-c1p-023853.sys.comcast.net ([2001:558:fd00:56::e]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTwAi-001bZV-59 for pgsql-general@lists.postgresql.org; Thu, 10 Aug 2023 03:17:17 +0000 Received: from resomta-c1p-023269.sys.comcast.net ([96.102.18.227]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by resdmta-c1p-023853.sys.comcast.net with ESMTP id TvZZqAcc5hn8ZTwAaqRNW2; Thu, 10 Aug 2023 03:17:04 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1691637424; bh=nXCX8Bjx+pNH8awxdGfOzuLbNPcIpUVZCJAg9UNsiqM=; h=Received:Received:Received:From:To:Subject:Date:Message-ID: MIME-Version:Content-Type:Xfinity-Spam-Result; b=w+rw7rb/VG7PtbbN+sZR/VnQQtVphKE0gASzVV7NcRmbAMMcJD47HQ/7G0FB4NrYF cOLTSWoVXjkk98XHe3b6/9AJUbZLBCnqptyVyfsuMZu3a+RdZqHsqWdigBYdioZerS YTcAiPDHJdogEEETrIBk1NvpWHVGRw4+MDhEyrJ67ImLwJ+3uEOcabGgAykWBYCXGL T2SXas4mOm7ONzgupGrl2EK2cOl98DpC6a9FdY2jpefRNp0CL9Y7A/wPHQ6m4pGfeZ 7tv5SPPEoNNFmbTZ+A+Zu3B78R0UoO71DxVHcNjoUAI87Ir5A7NJ6Yl7jK40+OblGw LpLHtHyoge44Q== Received: from R9N0YC2TC3.localdomain ([134.238.215.18]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by resomta-c1p-023269.sys.comcast.net with ESMTPA id TwA6qsoBszzfoTwA7q5nNB; Thu, 10 Aug 2023 03:16:40 +0000 X-Xfinity-VAAS: gggruggvucftvghtrhhoucdtuddrgedviedrleehgdeiiecutefuodetggdotefrodftvfcurfhrohhfihhlvgemucevohhmtggrshhtqdftvghsihdpqfgfvfdppffquffrtefokffrnecuuegrihhlohhuthemuceftddunecunecujfgurhephffvuffffgfkgggtsehttdertddtredtnecuhfhrohhmpeflvghrrhihucfuihgvvhgvrhhsuceoghhsihgvvhgvrhhsudelsegtohhmtggrshhtrdhnvghtqeenucggtffrrghtthgvrhhnpeekkeegieehgfejudeuteejgeegteekvdekvdefvdfhjefhiefgieevfeevheffvdenucfkphepudefgedrvdefkedrvdduhedrudeknecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehhvghloheptfelpfdtjgevvdfvveefrdhlohgtrghlughomhgrihhnpdhinhgvthepudefgedrvdefkedrvdduhedrudekpdhmrghilhhfrhhomheptgihsggvrhdrhhhunhhkohestghomhgtrghsthdrnhgvthdpnhgspghrtghpthhtohepuddprhgtphhtthhopehpghhsqhhlqdhgvghnvghrrghlsehlihhsthhsrdhpohhsthhgrhgvshhqlhdrohhrgh X-Xfinity-VMeta: sc=0.00;st=legit Received: by R9N0YC2TC3.localdomain (Postfix, from userid 503) id EB52C20A200D; Wed, 9 Aug 2023 22:16:33 -0500 (CDT) From: Jerry Sievers To: pgsql-general@lists.postgresql.org Subject: ignore_system_indexes=on no help for missing/corrupt pg_class_oid_index? Date: Tue, 08 Aug 2023 17:47:27 -0500 Lines: 52 User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/28.1 (darwin) X-From-Line: nobody Tue Aug 8 17:47:27 2023 Message-ID: MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Originally posted in the Admin list but trying here to get more eyes on it :-) ---- Greetings admins! I found that the ignore_system_indexes flag apparently doesn't get me past this case of a corrupt index as seen below. Or am I doing something silly here? The particular DB is a scratch so we'll just drop it but anyway, comments? I've not had to resort to this anytime lately but IIRC the ignore_system_indexes foo did the expected for us $sometime before. I believe that index 2662::regclass is probably this one but of course I had to probe for it in some other DB. The same one exists in template1 and I presume all other DBS as well. Please advise. Thx! ---- pg is a bash alias for the full path to postgres of the matched version... $ pg --single -D $PWD -c config_file=$cf -c hba_file=$hba -P broken_db ,,,,1969183,2023-08-08 22:25:17.844 GMT,,XX000,64d2c0cd.1e0c1f,1,2023-08-08 22:25:17 GMT,1/1,0 || PANIC: could not open critical system index 2662 Aborted $ pg --single -D $PWD -c config_file=$cf -c hba_file=$hba -P postgres ,,,,1969691,2023-08-08 22:25:51.027 GMT,,00000,64d2c0ee.1e0e1b,1,2023-08-08 22:25:50 GMT,1/0,0 || LOG: database system was interrupted; last known up at 2023-08-08 22:25:17 GMT ,,,,1969691,2023-08-08 22:25:51.045 GMT,,00000,64d2c0ee.1e0e1b,2,2023-08-08 22:25:50 GMT,1/0,0 || LOG: database system was not properly shut down; automatic recovery in progress ,,,,1969691,2023-08-08 22:25:51.048 GMT,,00000,64d2c0ee.1e0e1b,3,2023-08-08 22:25:50 GMT,1/0,0 || LOG: invalid record length at 0/5F175310: wanted 24, got 0 ,,,,1969691,2023-08-08 22:25:51.048 GMT,,00000,64d2c0ee.1e0e1b,4,2023-08-08 22:25:50 GMT,1/0,0 || LOG: redo is not required ,,,,1969691,2023-08-08 22:25:51.055 GMT,,00000,64d2c0ee.1e0e1b,5,2023-08-08 22:25:50 GMT,1/0,0 || LOG: checkpoint starting: end-of-recovery immediate wait ,,,,1969691,2023-08-08 22:25:51.063 GMT,,00000,64d2c0ee.1e0e1b,6,2023-08-08 22:25:50 GMT,1/0,0 || LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.004 s, sync=0.001 s, total=0.010 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB PostgreSQL stand-alone backend 15.3 (Ubuntu 15.3-1.pgdg20.04+1) backend> select relname from pg_class where oid=2662; ,,,,1969691,2023-08-08 22:26:11.183 GMT,,01000,64d2c0ee.1e0e1b,7,2023-08-08 22:25:50 GMT,1/2,0 || WARNING: using index "pg_toast_2619_index" despite IgnoreSystemIndexes 1: relname (typeid = 19, len = 64, typmod = -1, byval = f) ---- 1: relname = "pg_class_oid_index" (typeid = 19, len = 64, typmod = -1, byval = f) ---- backend> select version() 1: version (typeid = 25, len = -1, typmod = -1, byval = f) ---- 1: version = "PostgreSQL 15.3 (Ubuntu 15.3-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit" (typeid = 25, len = -1, typmod = -1, byval = f) ---- backend> show ignore_system_indexes 1: ignore_system_indexes (typeid = 25, len = -1, typmod = -1, byval = f) ---- 1: ignore_system_indexes = "on" (typeid = 25, len = -1, typmod = -1, byval = f) ---- backend>