Message-ID: <696bc97940384f72eca0d410bc069061902e43c9.camel@cybertec.at>
Subject: Re: Silent data corruption in PostgreSQL 17 - how to detect it
 proactively?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Pawel Kudzia <kudzia@gmail.com>, pgsql-general@lists.postgresql.org
Date: Sun, 14 Sep 2025 15:40:08 +0200
In-Reply-To: <CAJYBUS9pUjZbqsndbjdHUNugonfQ4DRr_HEn0QkyTXsds0VLMQ@mail.gmail.com>
References: 
	<CAJYBUS9T=JCVGkb9yXn5GVYC_g-e5mRi4MFoNHKEZV0keuHHwQ@mail.gmail.com>
	 <ba604e993eb0db3353dde283f856f4394e58af2f.camel@cybertec.at>
	 <CAJYBUS9pUjZbqsndbjdHUNugonfQ4DRr_HEn0QkyTXsds0VLMQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/696bc97940384f72eca0d410bc069061902e43c9.camel%40cybertec.at>
Precedence: bulk

On Sun, 2025-09-14 at 14:47 +0200, Pawel Kudzia wrote:
> On Sun, Sep 14, 2025 at 12:35=E2=80=AFPM Laurenz Albe <laurenz.albe@cyber=
tec.at> wrote:
> > On Sun, 2025-09-14 at 10:30 +0200, Pawel Kudzia wrote:
> > > I've hit a silent data corruption for TOAST data - leading to some
> > > infinite loop when accessing bytea column for very particular row. I
> > > did not suffer data loss - data from streaming replica was fine, I've
> > > used it to rebuild the main server.
> > >=20
> > > I'm wondering if there's any proactive way of detecting that type of
> > > an issue rather than discovering pile-up of SELECT queries leading to
> > > CPU starvation or finding hanged backup jobs.
> > >=20
> > > PostgreSQL's log does not have any error messages indicating data cor=
ruption.
> > >=20
> > > This server does not have checksums enabled, but - as I understand -
> > > such checksums cannot be checked online anyway. So - how can I detect
> > > similar corruption?
> >=20
> > A proactive way of detecting TOAST corruption... how about
> >=20
> >   pg_dump -f /dev/null yourdatabase
> >=20
> > If there is TOAST corruption, that should give you an error.
>=20
> Thanks for your answer! pg_dump mydatabase ends with ~the same as the
> SELECT statement.
> It hangs leaving 'postgres: 17/main: postgres application_cache
> [local] COPY' using 100% of single CPU core and no messages in PG's
> logs.
>=20
> I was curious if there's any other tool that can spot that type of
> corruption and state it explicitly rather than guess it by monitoring
> execution time.

Data checksums would certainly be a step in the right direction.
They are checked whenever the data are read from storage.

There is no tool that will reliably detect all kinds of data corruption.

Another tool ar your disposal are the functions from the amcheck
contrib module (or the pg_amcheck executable that calls them).

Yours,
Laurenz Albe