Fwd: Annoying corruption in PostgreSQL. - Mailing list pgsql-hackers
| From | Kirill Reshke |
|---|---|
| Subject | Fwd: Annoying corruption in PostgreSQL. |
| Date | |
| Msg-id | CALdSSPhW2rhb+7SEmwsWTCDC6nbt1=zTrAB1UKtLE2p5ZGcQFA@mail.gmail.com Whole thread Raw |
| In response to | Annoying corruption in PostgreSQL. (Kirill Reshke <reshkekirill@gmail.com>) |
| Responses |
Re: Fwd: Annoying corruption in PostgreSQL.
Re: Fwd: Annoying corruption in PostgreSQL. |
| List | pgsql-hackers |
---------- Forwarded message ---------
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Sat, 28 Oct 2023 at 02:06
Subject: Re: Annoying corruption in PostgreSQL.
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Sat, 28 Oct 2023 at 02:06
Subject: Re: Annoying corruption in PostgreSQL.
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Hi Tomas!
Thanks for the explanation!
1) 11 to 15. This week there were 14.9 and 12.16 reproductions. Two weeks ago there was 15.4 and 11.21 repro. Unfortunately, there is no info about repro which were month old or more, but I found in our work chats that there was repro on PostgreSQL 13 in April, a minor version unknown. Overall, we observed this issue for over a year on all pgdg supported versions.
2) Searching out bug tracker i have found:
1. missing chunk number 0 for toast value 592966012 in pg_toast_563953150 (some user relation)
2. missing chunk number 0 for toast value 18019714 in pg_toast_17706963 (some user relation)3. missing chunk number 0 for toast value 52677740 in pg_toast_247794
So, this is not always pg_catalog. There toast tables were toast to some user relations.
3) It is always about VACUUM FULL (FREEZE/VERBOSE/ANALYZE) / autovacuum.
We have physical backups and we can PITR. But restoring a cluster to some point in the past is a bit of a different task: we need our client's approval for these operations, since we are a Managed DBs Cloud Provider. Will try to ask someone.
Best regards
On Fri, 27 Oct 2023 at 23:28, Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
On 10/27/23 14:19, Kirill Reshke wrote:
> Hi hackers!
>
> We run a large amount of PostgreSQL clusters in our production. They
> differ by versions (we have 11-16 pg), load, amount of data, schema,
> etc. From time to time, postgresql corruption happens. It says
> ERROR,XX001,"missing chunk number 0 for toast value 18767319 in
> pg_toast_2619",,,,,,"vacuum full ;"
>
> in logs. the missing chunk number almost every is equal to zero, while
> other values vary. There are no known patterns, which triggers this
> issue. Moreover, if trying to rerun the VACUUM statement against
> relations from a log message, it succeeds all the time. So, we just
> ignore these errors. Maybe it is just some wierd data race?
>
> We don't know how to trigger this problem, or why it occurs. I'm not
> asking you to resolve this issue, but to help with debugging. What can
> we do to deduct failure reasons? Maybe we can add more logging somewhere
> (we can deploy a special patched PostgreSQL version everywhere), to have
> more information about the issue, when it happens next time?
>
For starters, it'd be good to know something about the environment, and
stuff that'd tell us if there's some possible pattern:
1) Which exact PG versions are you observing these errors on?
2) In the error example you shared it's pg_toast_2619, which is the
TOAST table for pg_statistic (probably). Is it always this relation? Or
what relations you noticed this for?
3) What kind of commands are triggering this? In the example it seems to
be vacuum full. Did you see it for other commands too? People generally
don't do VACUUM FULL very often, particularly not in environments with
concurrent activity.
Considering you don't know what's causing this, or what to look for, I
think it might be interesting to use pg_waldump, and investigate what
happened to the page containing the TOAST chunk and to the page
referencing it. Do you have physical backups and ability to do PITR?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
pgsql-hackers by date: