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 1r8HFn-0098Jp-7m for pgsql-general@arkaria.postgresql.org; Wed, 29 Nov 2023 09:53:11 +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 1r8HFk-009zTn-7N for pgsql-general@arkaria.postgresql.org; Wed, 29 Nov 2023 09:53:08 +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 1r8HFj-009zTe-He for pgsql-general@lists.postgresql.org; Wed, 29 Nov 2023 09:53:07 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r8HFg-0099MO-D9 for pgsql-general@postgresql.org; Wed, 29 Nov 2023 09:53:06 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 777A15F7BA; Wed, 29 Nov 2023 04:52:58 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1701251578; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=wFo7HGTMJX/IO+KlLTTldN5A4NPnS09vOz8ojiGy5aY=; b=jZNrFX48z1Kjdf7BL4n/QZ3tCptY9T3TG4VORVqFObCqAFVzBL/wa2bujsD7JAidAzxebE 8BpUpMbI5yAYETKECFrejJeLAbgpnYsWpR+aLFCBuXgdjdIgMygT8y/9O2oYwCYw6K3G2N 4g6MEwMOICUdNkYOi1Z3qEcaXSjg5o0= Date: Wed, 29 Nov 2023 04:52:58 -0500 From: Stephen Frost To: Abdul Qoyyuum Cc: Postgres General Subject: Re: Fixing or Mitigating this ERROR: invalid page in block 35217 of relation base/16421/3192429 Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="jIOiF1xeqXgU4UqZ" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --jIOiF1xeqXgU4UqZ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Abdul Qoyyuum (aqoyyuum@cardaccess.com.bn) wrote: > Knowing that it's a data corruption issue, the only way to fix this is to > vacuum and reindex the database. What was suggested was the following: >=20 > SET zero_damaged_pages =3D 0; # This is so that we can have the applicati= on > to continue to run > VACUUM FULL VERBOSE ANALYSE; # Do a full vacuum and analyse the problem if > possible. > REINDEX DATABASE "core"; # Then do a reindex and clean it up. This is only going to help if the issue is in an index, which isn't clear from what's been shared. > We're on Postgresql 12. This has worked before it happened (almost exactly > a year ago) and I think this needs a more permanent solution. I've looked > at routine vacuuming and checked the autovacuum is set to on and the > following configurations: This isn't something that should ever happen ... This also doesn't have anything to do with autovacuum, changing settings there won't make any difference. > Can anyone advise if there's anything else we can do? We have no clue what > causes the invalid page block and we are running a High Availability > cluster set up but we are hoping that there may be a way to mitigate it. Was there some kind of hardware fault? Did you do a failover? Restore =66rom a backup? Do you have checksums enabled? How many times has this happened before, and how many pages were impacted? What is the design of your HA solution, are you using PG replication or something else? Thanks, Stephen --jIOiF1xeqXgU4UqZ Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmVnCfMACgkQ7WyKOINH ZFV38Q/9EuNCK10X26g/3NQVAyv6lCAc/3POgA83AjzQQeU3BAsOHsFxvtw1NRGc 8r4CZgwwbSYGQmhFTQ0sGww3+2uK2sYoM3UF6VoQt8PJq5tfj7fiC/V+eFh5w/Da K8s12qj1CSlZdDc2IEdd0qhH+nwa3OmnovbVDeViodsav16Z4zFklQBWIc8v6FPd +Ckl3VCnboq7gfBCpnUdxfbGj/5FG5AfgSId+J7fzkoOY+jRcb6bubinpMBCk5F6 JktA5/Fu5SaXYlKrU4H5dI/g0dQVA3cES4RR2E3f5mhwu5S7ZwjAqnxI9/duJiuN aZBiwKZPhpW4qUcxwGc/poAHoCL4sBUZ858tuk3yZ6hxLiobCHQvdfSogBo3b6IO xA/LpqwUrhtxEjB+4FgGJxDjanFbiqi4967hwu1qycvILwN2YQp3d4/T6Ix4+EOa zzqxqGDepysbhSdKqD7Tu+nuHZpt9oOtRkAUJpMN5mRF/Cp8hok1NBGeLgx6ZLnh e4ulXZlTuEzkM54Z0gVZBUEVNzK0YP3gXVQqwBQmMPqolN+rnIQzM0VtH+mFuEJJ ztkyoCrcdgFXze0hpuu/cozyIJ8xDVZrlkS0WCOAM7R7VWBV5bGUhFk4Pq0eF9IX 8gIjCC7nldeuhVlSQcAcKH3V5XV4Sy4blrtwptGvxJu363RJBBA= =ek/D -----END PGP SIGNATURE----- --jIOiF1xeqXgU4UqZ--