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 1kmn8z-0004Nl-Pr for pgsql-bugs@arkaria.postgresql.org; Wed, 09 Dec 2020 00:15:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmn8w-00067U-9u for pgsql-bugs@arkaria.postgresql.org; Wed, 09 Dec 2020 00:15:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmmwx-0005nP-CF for pgsql-bugs@lists.postgresql.org; Wed, 09 Dec 2020 00:03:19 +0000 Received: from mail-bn8nam11on2106.outbound.protection.outlook.com ([40.107.236.106] helo=NAM11-BN8-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmmws-0004CN-F9 for pgsql-bugs@lists.postgresql.org; Wed, 09 Dec 2020 00:03:17 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=Ov23xoSJoqn2w/ZLoOtNDHqRn1UwVdl2qWo4b3HO+WDjnO5FZ7Bv7dhkw/4MrOEx6IEnn6/zPbrJ4nrj9Z7A6YDE3CmhlpQDd5za+dUcs9SZIP0e+8lJqAgaEplnkMTtO/60ib6h6jiyLWTCtHtJvx2PQ9/OT1A6b52SNbhHkYEVgKYwIB1ku69epDLSwyupWB2U81hf8oxukoAJPXzfaaN0RP41ZkFnDr4LSqgO4Wqhd5V4ABdj/9qg27VaHURfGvnNAFDxLPTIYn10Ewkbl6mKigKEJbJ0pGgwC2nCQeBE/SBV46mKGiA5nMd5q4mhy5ZBCRS5WNUyQDcHiJGIjw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=WuEZ4q0d3DikFHOedGWFC5ZDcgeqaqxda9T7iKclXgg=; b=YWmCYEIEha85N/P0dpx5mWuH5Nr+ZmlyT0zeID63ngiy14tBF8ade7edGD7QW5b7d8PrepJ/GgjoL66RF6L51B6ZH757mr3uyw4H3cbe7sKLwraFiztt6ts2cbwa+JDm8iJOxUU0mQSwwJrC7kj7fD3xEhuTc0Hp0cY/ma+TjEs2BM4Uxpcidh5Aw0EaaMr3FJCGpfRh/C1Xc28felU+k+vQx57/t43YG2wykh0btQyUImCd1JQ/8CF9qqS4OsSBAmogv/C+lR9eLxuh6SQzNkve20BI0A4nC0k6iTe2SojgHHxdBnoQdXSdf7e3LaFtwmPc7hFLHUmziIROgwDDEQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=xmatters.com; dmarc=pass action=none header.from=xmatters.com; dkim=pass header.d=xmatters.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=xmatters.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=WuEZ4q0d3DikFHOedGWFC5ZDcgeqaqxda9T7iKclXgg=; b=G0TWVVQSElwdRWYpLiKC/KDHKOk7Rd9pO11avBPXapt5X41llnvyR2dGt/R/A48uOIfVh+dAdpyk5TL1g0zep+gE3kbCEiJNIDUaCWCX9i6Zn+nLVejrgEF27cPH2uQlgWXYiJfWKSTLSaJQgBfBVpY8yctnAn9CPE1TPLHQgVE= Received: from SA2PR02MB7547.namprd02.prod.outlook.com (2603:10b6:806:143::8) by SN6PR02MB4399.namprd02.prod.outlook.com (2603:10b6:805:a9::23) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3654.12; Wed, 9 Dec 2020 00:03:11 +0000 Received: from SA2PR02MB7547.namprd02.prod.outlook.com ([fe80::746d:2f3f:b32c:79d7]) by SA2PR02MB7547.namprd02.prod.outlook.com ([fe80::746d:2f3f:b32c:79d7%6]) with mapi id 15.20.3632.018; Wed, 9 Dec 2020 00:03:11 +0000 From: Andriy Bartash To: Euler Taveira , "pgsql-bugs@lists.postgresql.org" Subject: Re: BUG #16760: Standby database missed records for at least 1 table Thread-Topic: BUG #16760: Standby database missed records for at least 1 table Thread-Index: AQHWyOjyamBBDARVz0meeETJqtvd36nlsB8AgAg3/7M= Date: Wed, 9 Dec 2020 00:03:11 +0000 Message-ID: References: <16760-6c90634964ab51b0@postgresql.org>, In-Reply-To: Accept-Language: en-US Content-Language: en-CA X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: 2ndquadrant.com; dkim=none (message not signed) header.d=none;2ndquadrant.com; dmarc=none action=none header.from=xmatters.com; x-originating-ip: [70.66.158.110] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 22d3f682-6395-497a-2405-08d89bd5d162 x-ms-traffictypediagnostic: SN6PR02MB4399: x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:7219; x-ms-exchange-senderadcheck: 1 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: XUG57NuVm+2DioDXL4yrtN16r1Ooj7PAT2y3IWrrbgT40L9nYb3wDI+diUc+TGlrJaibVnl582uFzzkvshaaWuI9YUP+n3y/3chLSn9IH970xOYoGHh7WxKBhVvQmpHflPcGJhT4exzpcUA8vT0BfcpqSME+qz+bdHyFJbiKrDXvrQnj5zs6aR+runMIcqcyABk+Xr2YGsvmCg+7wH+nXg380WCQfuBRyK9vASA/u94h2+p+ttj40TpJpUz5SamA4kDCRpOOWvR+EK/ZpfeuEvzHTaFIc8bmSqoNt8svkEU6AzoFUUEXd9Tlo+/Mtq20wzdv/fC7HcY+oUUw28jW6GW/sMlTza211MOxRyVWlm72PJqE2pG/8sZFsfp2JNR5H8Nzdv58rktGP5WS/aZu8zGmCVdUHrUaLQ3VgPU0Y+02QQOPkmnqvrZRPE3R/5M+UW3qqGN4RLJQdawAXmM2pw== x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SA2PR02MB7547.namprd02.prod.outlook.com;PTR:;CAT:NONE;SFS:(4636009)(136003)(376002)(366004)(346002)(53546011)(71200400001)(2906002)(966005)(33656002)(55016002)(5660300002)(166002)(86362001)(9686003)(186003)(76116006)(508600001)(6506007)(66556008)(52536014)(66946007)(66446008)(64756008)(66476007)(4001150100001)(83380400001)(110136005)(8676002)(26005)(8936002)(7696005)(130980200001);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata: =?us-ascii?Q?sCvJBxZPV3Z9wb/0LM6mqoR+oxecoHYhZ/IXKMr59aQd2Ev/PcOE6s+l6t76?= =?us-ascii?Q?AObrfrMh6GUR1tH817mMPUV42wjLxvY3psDpjM1VzTrSBQkr6s2Oeex/DZZL?= =?us-ascii?Q?BBt25hPDlJ58utNZ63Ua+KshRwLzjR0OPV1dbrduB8o9cx5jDvfkeacqtW5C?= =?us-ascii?Q?r0OVTIrlcQlnxRLrLNyaPYJKmCCgbF95mUSUqyY57FHqNtXiAIg79WM0DaHS?= =?us-ascii?Q?ReYQzTt0uLbnPvkFnH6ELXIuvd+dVyy6O+y0WCjjlwpKI4tyYFTqrsurQ4nm?= =?us-ascii?Q?JvuwcH2rol1jEpkcneMqz47d6y6L9xK0huJl9SwwTtA8LBh9F7qKXJ++Vc2D?= =?us-ascii?Q?yXOFaKXfhLPfgM6GxdnU1hRN2Tfz1THooASfPe7tGQIwEailo83hoKZRgONE?= =?us-ascii?Q?NKR7Icf87wS0kpJoz3GIboGhgl55217CJnG5y/JUlS61noHTl3najgEbMlr7?= =?us-ascii?Q?huN2OZIez+FrV1jrw937iXSuN0fyQt9fhkmBvDXuR9fGdOWDiW/ae8Q345KK?= =?us-ascii?Q?jD9CVpsTqIyoCRURf9aKK3hauSC7WSzml2IfOdnae9JrtreAkXq5tKSGS83b?= =?us-ascii?Q?057rT7UhvnJb1W0Zv7NAEjxQY95NmfV+hTRZ1Ww9efnOpFp4qcutRKvzIcvw?= =?us-ascii?Q?o9dY+QfJNtOPe+MTKbFNIbqAffkxQS0rDG/QVlvbNkmsVYZHMNtWXVHKRZOi?= =?us-ascii?Q?f58v++re/XRtj7N4gkxWaOPgHNq3dsowkFwWEXlm2v3EcC4kdXnyquVehILM?= =?us-ascii?Q?laqZrElHyntWSFoaInL4DZG96V4VfcxUE1CszVkO5f7gRbOQ77oyzSI42uwA?= =?us-ascii?Q?9X0HhvL47fsHy6TJcChYFwD63d8hd184RmtXqzEbCjSKeUH+DJFP2y13Iejq?= =?us-ascii?Q?nvRw/HjQ6Ih5La4rb+xe0hhGVwlyxQ4JXgtQFpKd0u1CwfCVQ0XqM0EJttEY?= =?us-ascii?Q?oXxO7gERKpKzAk36atpENWVqs56Gn16i3WBbgHTZYBNVTOiuy9bD1icuUXLt?= =?us-ascii?Q?N9a3?= x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_SA2PR02MB7547D9B3F15931FCDBDAFCBABBCD0SA2PR02MB7547namp_" MIME-Version: 1.0 X-OriginatorOrg: xmatters.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: SA2PR02MB7547.namprd02.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 22d3f682-6395-497a-2405-08d89bd5d162 X-MS-Exchange-CrossTenant-originalarrivaltime: 09 Dec 2020 00:03:11.4989 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 11936241-dfa3-4e68-80db-7bbb994b0de3 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: SVyxPgKTgSqZu4+/vy5dUG7d26kuIV057kAE1KJxQcbNkhUB5JqE8SDVWPt67fUfFDSc6LzfZqJ2rw7G7jVUmw== X-MS-Exchange-Transport-CrossTenantHeadersStamped: SN6PR02MB4399 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_SA2PR02MB7547D9B3F15931FCDBDAFCBABBCD0SA2PR02MB7547namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Euler I inspected pages where I expected to have my data on broken standby. What was done: 1. Identified the page where was my data (on primary cluster) select ctid,audit_ev_id from audit_evs_all where audit_ev_id between 22153= 5154 and 221535162; and it returned ctid,audit_ev_id "(1979,3)",221535154 "(1980,13)",221535155 "(1980,14)",221535156 "(1980,28)",221535157 "(1980,29)",221535158 "(1982,6)",221535159 "(1982,7)",221535160 "(1982,32)",221535161 "(1982,33)",221535162 Found that page # 1982 had 4 records on primary (ev_id 221535159, 221535160= , 221535161 and 221535162) 1. Same query on Broken secondary showed that page # 1982 had only 1 rec= ord (1982,33) | 221535162 (1 row) 1. Managed to restore postgres cluster from physical backup as of 24hr a= fter secondary became corrupted and got all records from page # 1982 SELECT when_created,(ctid::text::point)[0]::bigint AS page_number, audit_ev= _id from audit_evs_all where (ctid::text::point)[0]::bigint =3D 1982 and w= hen_created <'2020-12-02' order by 1 ; when_created | page_number | audit_ev_id -------------------------------+-------------+------------- 2020-11-04 05:39:40.524816+00 | 1982 | 218065111 2020-11-04 05:39:41.551833+00 | 1982 | 218065112 2020-11-09 08:57:29.458528+00 | 1982 | 218711001 2020-11-09 08:57:29.458528+00 | 1982 | 218711000 2020-11-15 00:40:10.2285+00 | 1982 | 219765000 2020-11-15 00:40:10.2285+00 | 1982 | 219765001 2020-11-15 00:40:10.2285+00 | 1982 | 219765003 2020-11-15 00:40:10.2285+00 | 1982 | 219765004 2020-11-15 00:40:10.2285+00 | 1982 | 219765005 2020-11-15 00:40:10.2285+00 | 1982 | 219765006 2020-11-15 00:40:10.2285+00 | 1982 | 219765007 2020-11-15 00:40:10.2285+00 | 1982 | 219765008 2020-11-15 00:40:10.2285+00 | 1982 | 219765009 2020-11-15 00:40:10.2285+00 | 1982 | 219765010 2020-11-15 00:40:10.2285+00 | 1982 | 219765011 2020-11-15 00:40:10.2285+00 | 1982 | 219765002 2020-11-19 23:16:08.365151+00 | 1982 | 220198480 2020-11-19 23:16:08.365151+00 | 1982 | 220198479 2020-11-27 00:33:36.568531+00 | 1982 | 220990024 2020-11-27 00:33:36.568531+00 | 1982 | 220990023 2020-11-27 00:33:36.568531+00 | 1982 | 220990025 2020-12-01 00:00:20.955348+00 | 1982 | 221535161 2020-12-01 00:00:20.955348+00 | 1982 | 221535162 2020-12-01 00:00:20.955348+00 | 1982 | 221535160 2020-12-01 00:00:20.955348+00 | 1982 | 221535159 (25 rows) 1. Same query on broken secondary returns 22 records (same missed record= s with ev_id 221535159, 221535160, 221535161) when_created | page_number | audit_ev_id -------------------------------+-------------+------------- 2020-11-04 05:39:40.524816+00 | 1982 | 218065111 2020-11-04 05:39:41.551833+00 | 1982 | 218065112 2020-11-09 08:57:29.458528+00 | 1982 | 218711001 2020-11-09 08:57:29.458528+00 | 1982 | 218711000 2020-11-15 00:40:10.2285+00 | 1982 | 219765000 2020-11-15 00:40:10.2285+00 | 1982 | 219765001 2020-11-15 00:40:10.2285+00 | 1982 | 219765006 2020-11-15 00:40:10.2285+00 | 1982 | 219765007 2020-11-15 00:40:10.2285+00 | 1982 | 219765008 2020-11-15 00:40:10.2285+00 | 1982 | 219765009 2020-11-15 00:40:10.2285+00 | 1982 | 219765010 2020-11-15 00:40:10.2285+00 | 1982 | 219765011 2020-11-15 00:40:10.2285+00 | 1982 | 219765002 2020-11-15 00:40:10.2285+00 | 1982 | 219765003 2020-11-15 00:40:10.2285+00 | 1982 | 219765004 2020-11-15 00:40:10.2285+00 | 1982 | 219765005 2020-11-19 23:16:08.365151+00 | 1982 | 220198479 2020-11-19 23:16:08.365151+00 | 1982 | 220198480 2020-11-27 00:33:36.568531+00 | 1982 | 220990025 2020-11-27 00:33:36.568531+00 | 1982 | 220990024 2020-11-27 00:33:36.568531+00 | 1982 | 220990023 2020-12-01 00:00:20.955348+00 | 1982 | 221535162 (22 rows) Conclusion: Secondary postgres cluster is corrupted, 3 records missed. Does it make sense? From: Euler Taveira Date: Thursday, December 3, 2020 at 10:20 AM To: Andriy Bartash , pgsql-bugs@lists.postgresql.org= Subject: Re: BUG #16760: Standby database missed records for at least 1 tab= le On Wed, 2 Dec 2020 at 17:44, PG Bug reporting form > wrote: Below output from Primary select audit_ev_id, when_created from audit_evs_all where audit_ev_id between 221535154 and 221535162; audit_ev_id | when_created -------------+------------------------------- 221535154 | 2020-12-01 00:00:20.955348+00 221535155 | 2020-12-01 00:00:20.955348+00 221535156 | 2020-12-01 00:00:20.955348+00 221535157 | 2020-12-01 00:00:20.955348+00 221535158 | 2020-12-01 00:00:20.955348+00 221535159 | 2020-12-01 00:00:20.955348+00 221535160 | 2020-12-01 00:00:20.955348+00 221535161 | 2020-12-01 00:00:20.955348+00 221535162 | 2020-12-01 00:00:20.955348+00 (9 rows) --------------------------------------------------------------- Same query's output from 1st Secondary select audit_ev_id, when_created from audit_evs_all where audit_ev_id between 221535154 and 221535162; audit_ev_id | when_created -------------+------------------------------- 221535162 | 2020-12-01 00:00:20.955348+00 (1 row) I would start this investigation, disabling the index usage for this query. -- check which index has been used explain (analyze) select audit_ev_id, when_created from audit_evs_all wher= e audit_ev_id between 221535154 and 221535162; -- disable index usage begin; set local enable_indexscan to 0; set local enable_indexonlyscan to 0; explain (analyze) select audit_ev_id, when_created from audit_evs_all wher= e audit_ev_id between 221535154 and 221535162; select audit_ev_id, when_created from audit_evs_all where audit_ev_id between 221535154 and 221535162; commit; If the second EXPLAIN isn't using the index shown in the first EXPLAIN and = the SELECT returns all rows, reindex the index that has been used. If the index= is not the culprit, you should inspect the page(s) that contain those rows usi= ng pageinspect. -- Euler Taveira http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --_000_SA2PR02MB7547D9B3F15931FCDBDAFCBABBCD0SA2PR02MB7547namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi Euler

I inspected pages where I expected to have my data o= n broken standby.

What was done:

  1. Identified the page where was my data (on primary cluster)=

select ctid,audit_e= v_id from audit_evs_all  where audit_ev_id between 221535154 and 22153= 5162;

and it returned

ctid,audit_ev_id

"(1979,3)",22153= 5154

"(1980,13)",2215= 35155

"(1980,14)",2215= 35156

"(1980,28)",2215= 35157

"(1980,29)",2215= 35158

"(1982,6)",22153= 5159

"(1982,7)",22153= 5160

"(1982,32)",2215= 35161

"(1982,33)",2215= 35162

Found that page # 1982 had 4 records on prima= ry (ev_id 221535159, 221535160, 221535161 and 221535162)

 

  1. Same query on Broken secondary showed that page # 1982 had only 1 rec= ord

(1982,33) | &n= bsp; 221535162

(1 row)=

  1. Managed to restore postgres cluster from physical backup as of 24hr a= fter secondary became corrupted and got all records from page # 1982

 

SELECT when_created,(ctid:= :text::point)[0]::bigint AS page_number, audit_ev_id from audit_evs_all&nbs= p; where (ctid::text::point)[0]::bigint =3D 1982 and when_created <'2020-12-02' order by 1 ;

    &n= bsp;    when_created      &nbs= p;   | page_number | audit_ev_id

--------------------------= -----+-------------+-------------

2020-11-04 05:39:40.524816= +00 |        1982 |   21806511= 1

2020-11-04 05:39:41.551833= +00 |        1982 |   21806511= 2

2020-11-09 08:57:29.458528= +00 |        1982 |   21871100= 1

2020-11-09 08:57:29.458528= +00 |        1982 |   21871100= 0

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765000

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765001

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765003

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765004

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765005

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765006

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765007

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765008

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765009

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765010

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765011

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765002

2020-11-19 23:16:08.365151= +00 |        1982 |   22019848= 0

2020-11-19 23:16:08.365151= +00 |        1982 |   22019847= 9

2020-11-27 00:33:36.568531= +00 |        1982 |   22099002= 4

2020-11-27 00:33:36.568531= +00 |        1982 |   22099002= 3

2020-11-27 00:33:36.568531= +00 |        1982 |   22099002= 5

2020-12-01 00:00:20.955348= +00 |        1982 |   22153516= 1

2020-12-01 00:00:20.955348= +00 |        1982 |   22153516= 2

2020-12-01 00:00:20.955348= +00 |        1982 |   22153516= 0

2020-12-01 00:00:20.955348= +00 |        1982 |   22153515= 9

(25 rows)

  1. Same query on broken secondary returns 22 records (same missed record= s with ev_id 221535159, 221535160, 221535161)

 

     w= hen_created          | page_nu= mber | audit_ev_id

--------------------------= -----+-------------+-------------

2020-11-04 05:39:40.524816= +00 |        1982 |   21806511= 1

2020-11-04 05:39:41.551833= +00 |        1982 |   21806511= 2

2020-11-09 08:57:29.458528= +00 |        1982 |   21871100= 1

2020-11-09 08:57:29.458528= +00 |        1982 |   21871100= 0

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765000

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765001

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765006

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765007

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765008

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765009

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765010

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765011

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765002

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765003

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765004

2020-11-15 00:40:10.2285+0= 0   |        1982 |  = ; 219765005

2020-11-19 23:16:08.365151= +00 |        1982 |   22019847= 9

2020-11-19 23:16:08.365151= +00 |        1982 |   22019848= 0

2020-11-27 00:33:36.568531= +00 |        1982 |   22099002= 5

2020-11-27 00:33:36.568531= +00 |        1982 |   22099002= 4

2020-11-27 00:33:36.568531= +00 |        1982 |   22099002= 3

2020-12-01 00:00:20.955348= +00 |        1982 |   22153516= 2

(22 rows)

 

Conclusion: Secondary postgres cluster is cor= rupted, 3 records missed.

 

Does it make sense?

 

 

 

 

 

From: Euler Taveira <e= uler.taveira@2ndquadrant.com>
Date: Thursday, December 3, 2020 at 10:20 AM
To: Andriy Bartash <abartash@xmatters.com>, pgsql-bugs@lists.p= ostgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16760: Standby database missed records for at leas= t 1 table

On Wed, 2 Dec 2020 at 17:44, PG Bug reporting form &= lt;noreply@postgresql.org>= wrote:

Below output from Pri= mary
select audit_ev_id, when_created from audit_evs_all  where audit_ev_id=
between 221535154 and 221535162;
 audit_ev_id |         when_created
-------------+-------------------------------
   221535154 | 2020-12-01 00:00:20.955348+00
   221535155 | 2020-12-01 00:00:20.955348+00
   221535156 | 2020-12-01 00:00:20.955348+00
   221535157 | 2020-12-01 00:00:20.955348+00
   221535158 | 2020-12-01 00:00:20.955348+00
   221535159 | 2020-12-01 00:00:20.955348+00
   221535160 | 2020-12-01 00:00:20.955348+00
   221535161 | 2020-12-01 00:00:20.955348+00
   221535162 | 2020-12-01 00:00:20.955348+00
(9 rows)
---------------------------------------------------------------
Same query's output from 1st Secondary
select audit_ev_id, when_created from audit_evs_all  where audit_ev_id=
between 221535154 and 221535162;
 audit_ev_id |         when_created
-------------+-------------------------------
   221535162 | 2020-12-01 00:00:20.955348+00
(1 row)

I would start this investigation, disabling the inde= x usage for this query.

 

-- check which index has been used

explain (analyze) select audit_ev_id, when_created f= rom audit_evs_all  where audit_ev_id

between 221535154 and 221535162;

-- disable index usage

begin;

set local enable_indexscan to 0;

set local enable_indexonlyscan to 0;

explain (analyze) select audit_ev_id, when_created f= rom audit_evs_all  where audit_ev_id

between 221535154 and 221535162;

select audit_ev_id, when_created from audit_evs_all&= nbsp; where audit_ev_id

between 221535154 and 221535162;

commit;

 

If the second EXPLAIN isn't using the index shown in= the first EXPLAIN and the
SELECT returns all rows, reindex the index that has been used. If the index= is
not the culprit, you should inspect the page(s) that contain those rows usi= ng
pageinspect.

 

 

--

Euler Taveira      &nb= sp;          http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--_000_SA2PR02MB7547D9B3F15931FCDBDAFCBABBCD0SA2PR02MB7547namp_--