MIME-Version: 1.0
References: <YQBPR01MB10625A12760CAECAE647F415EABF32@YQBPR01MB10625.CANPRD01.PROD.OUTLOOK.COM>
 <4fbdd9760b329bccbdd62b6645d5977bee957e0d.camel@cybertec.at>
In-Reply-To: <4fbdd9760b329bccbdd62b6645d5977bee957e0d.camel@cybertec.at>
From: Thom Brown <thom@linux.com>
Date: Fri, 31 May 2024 11:14:46 +0100
Message-ID: <CAA-aLv5sNn4M5NwNFZy6d7mG1TrGJm-_6XGHPXX13OHObvUnaw@mail.gmail.com>
Subject: Re: ERROR: found xmin from before relfrozenxid; MultiXactid does no
 longer exist -- apparent wraparound
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Alanoly Andrews <alanolya@invera.com>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000096dc280619bd4179"
Archived-At: <https://www.postgresql.org/message-id/CAA-aLv5sNn4M5NwNFZy6d7mG1TrGJm-_6XGHPXX13OHObvUnaw%40mail.gmail.com>
Precedence: bulk

--00000000000096dc280619bd4179
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, May 31, 2024, 09:29 Laurenz Albe <laurenz.albe@cybertec.at> wrote:

> On Thu, 2024-05-30 at 14:58 +0000, Alanoly Andrews wrote:
> > We have a postgres 10.7 database which reports a number of issues on
> user-created
> > tables as well as system tables. Most errors are one of the following:
> > -- ERROR:  found xmin 1888159934 from before relfrozenxid 1998177448
> > -- ERROR:  MultiXactId 613819197 does no longer exist -- apparent
> wraparound
> > -- ERROR:  could not access status of transaction 1927393975
> >    DETAIL:  Could not open file "pg_xact/072E": No such file or
> directory.
> >
> > Is there a way to repairing the corruption in this database?
> > Postgres Version 10.7 on Linux(Ubuntu).
>
> Perhaps, but you should hire an expert if the data are important for you.
>

Also, while it's too late now, this could be the result of a bug in the
version you are using that was subsequently repaired in 10.15:

Prevent possible data loss from concurrent truncations of SLRU logs (Noah
Misch)

This rare problem would manifest in later =E2=80=9Capparent wraparound=E2=
=80=9D or =E2=80=9Ccould
not access status of transaction=E2=80=9D errors.

This is why it's important to keep up-to-date, but even the latest minor
10.x release is out of date as support was dropped back in 2022.

If you manage to get this up and running again, I strongly recommend
upgrading to the latest major and minor release (16.3 at the time of
writing).

Before you try doing anything though, create a physical backup of your
database as situations like this tend to require invasive action that could
potentially make the situation even worse.

Also, did this problem only happen in the last day or two? How frequently
do you take backups? If you have a backup from just before this issue
starting showing itself, and you can afford losing data changes that have
occured since the backup, you may find it far easier and quicker to resort
to using that backup. Of course, you would need to prove to yourself that
the backup was safe by running a VACUUM FREEZE on each database in that
backup before starting to use it.  If that runs without issue, you're
probably in the clear.

Best of luck.

Thom

>

--00000000000096dc280619bd4179
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D=
"gmail_attr">On Fri, May 31, 2024, 09:29 Laurenz Albe &lt;<a href=3D"mailto=
:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex">On Thu, 2024-05-30 at 14=
:58 +0000, Alanoly Andrews wrote:<br>
&gt; We have a postgres 10.7 database which reports a number of issues on u=
ser-created<br>
&gt; tables as well as system tables. Most errors are one of the following:=
<br>
&gt; -- ERROR: =C2=A0found xmin 1888159934 from before relfrozenxid 1998177=
448<br>
&gt; -- ERROR: =C2=A0MultiXactId 613819197 does no longer exist -- apparent=
 wraparound<br>
&gt; -- ERROR: =C2=A0could not access status of transaction 1927393975<br>
&gt; =C2=A0 =C2=A0DETAIL: =C2=A0Could not open file &quot;pg_xact/072E&quot=
;: No such file or directory.<br>
&gt; <br>
&gt; Is there a way to repairing the corruption in this database?<br>
&gt; Postgres Version 10.7 on Linux(Ubuntu).<br>
<br>
Perhaps, but you should hire an expert if the data are important for you.<b=
r></blockquote></div></div><div dir=3D"auto"><br></div><div dir=3D"auto">Al=
so, while it&#39;s too late now, this could be the result of a bug in the v=
ersion you are using that was subsequently repaired in 10.15:</div><div dir=
=3D"auto"><br></div><div dir=3D"auto">Prevent possible data loss from concu=
rrent truncations of SLRU logs (Noah Misch)</div><div dir=3D"auto"><br></di=
v><div dir=3D"auto">This rare problem would manifest in later =E2=80=9Cappa=
rent wraparound=E2=80=9D or =E2=80=9Ccould not access status of transaction=
=E2=80=9D errors.</div><div dir=3D"auto"><br></div><div dir=3D"auto">This i=
s why it&#39;s important to keep up-to-date, but even the latest minor 10.x=
 release is out of date as support was dropped back in 2022.</div><div dir=
=3D"auto"><br></div><div dir=3D"auto">If you manage to get this up and runn=
ing again, I strongly recommend upgrading to the latest major and minor rel=
ease (16.3 at the time of writing).</div><div dir=3D"auto"><br></div><div d=
ir=3D"auto">Before you try doing anything though, create a physical backup =
of your database as situations like this tend to require invasive action th=
at could potentially make the situation even worse.</div><div dir=3D"auto">=
<br></div><div dir=3D"auto">Also, did this problem only happen in the last =
day or two? How frequently do you take backups? If you have a backup from j=
ust before this issue starting showing itself, and you can afford losing da=
ta changes that have occured since the backup, you may find it far easier a=
nd quicker to resort to using that backup. Of course, you would need to pro=
ve to yourself that the backup was safe by running a VACUUM FREEZE on each =
database in that backup before starting to use it.=C2=A0 If that runs witho=
ut issue, you&#39;re probably in the clear.</div><div dir=3D"auto"><br></di=
v><div dir=3D"auto">Best of luck.</div><div dir=3D"auto"><br></div><div dir=
=3D"auto">Thom</div><div dir=3D"auto"><div class=3D"gmail_quote"><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex">
</blockquote></div></div></div>

--00000000000096dc280619bd4179--