pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephen Frost <sfrost@snowman.net>
To: Abdul Qoyyuum <aqoyyuum@cardaccess.com.bn>
Cc: Postgres General <pgsql-general@postgresql.org>
Subject: Re: Fixing or Mitigating this ERROR: invalid page in block 35217 of relation base/16421/3192429
Date: Wed, 29 Nov 2023 04:52:58 -0500
Message-ID: <ZWcJ+txba1dQyIC8@tamriel.snowman.net> (raw)
In-Reply-To: <CAA3DN=X-ZT27Knq5BOAcdD1LsiZoBuTm6UVso=n5g0LRUdHsOg@mail.gmail.com>
References: <CAA3DN=X-ZT27Knq5BOAcdD1LsiZoBuTm6UVso=n5g0LRUdHsOg@mail.gmail.com>

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:
> 
> SET zero_damaged_pages = 0; # This is so that we can have the application
> 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
from 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

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (7+ messages)  latest in thread

Message-ID: <ZWcJ+txba1dQyIC8@tamriel.snowman.net>
Permalink:  ../ZWcJ+txba1dQyIC8@tamriel.snowman.net/
Also on:    postgresql.org/message-id/ZWcJ+txba1dQyIC8@tamriel.snowman.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: sfrost@snowman.net, aqoyyuum@cardaccess.com.bn
  Subject: Re: Fixing or Mitigating this ERROR: invalid page in block 35217 of relation base/16421/3192429
  In-Reply-To: <ZWcJ+txba1dQyIC8@tamriel.snowman.net>
  References: <CAA3DN=X-ZT27Knq5BOAcdD1LsiZoBuTm6UVso=n5g0LRUdHsOg@mail.gmail.com> <ZWcJ+txba1dQyIC8@tamriel.snowman.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox