Message-ID: <ba604e993eb0db3353dde283f856f4394e58af2f.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 12:35:52 +0200
In-Reply-To: <CAJYBUS9T=JCVGkb9yXn5GVYC_g-e5mRi4MFoNHKEZV0keuHHwQ@mail.gmail.com>
References: 
	<CAJYBUS9T=JCVGkb9yXn5GVYC_g-e5mRi4MFoNHKEZV0keuHHwQ@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/ba604e993eb0db3353dde283f856f4394e58af2f.camel%40cybertec.at>
Precedence: bulk

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
> Thanks in advance for your suggestions!
>=20
> I was originally running PostgreSQL 17.2 installed from project's deb
> packages, under Debian 12 on amd64. Environment is - without any
> recent crashes, with ECC memory and server-grade hardware. I run
> pg_dumpall every 24h, on a certain day it hanged. On re-run it hanged
> again, each time leaving single PosgreSQL process using 100% of single
> CPU core.
>=20
> [...]
>=20
> PostgreSQL's log does not have any error messages indicating data corrupt=
ion.
>=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?

A proactive way of detecting TOAST corruption... how about

  pg_dump -f /dev/null yourdatabase

If there is TOAST corruption, that should give you an error.

If your standby does not have the same problem, that increases the
likelihood that the cause is a hardware problem.  Of course, it could
still be a software bug.

Yours,
Laurenz Albe