Transaction visibility metadata corruption pg12

1

Hi Experts,

We are experiencing what appears to be transaction visibility metadata corruption in a PostgreSQL 12 database. Accessing a specific row consistently fails with an error indicating PostgreSQL cannot access the status of a transaction because the corresponding file in the pg_xact directory is missing (for example, could not open file "pg_xact/XXXX": No such file or directory). A binary search helped isolate the problem to a specific row, while neighboring rows remain accessible. Since PostgreSQL cannot determine the transaction status for that tuple, any query referencing it fails. Additionally, we are unable to export the affected table using pg_dump or COPY, as both operations terminate when they encounter the corrupted tuple. Has anyone encountered a similar issue involving missing pg_xact segments? What recovery options are available besides restoring from a backup, particularly if the goal is to recover as much data as possible from the affected table?

image
image829×201 52.3 KB

2

Hi VigneshMurugan,

In this kind of situations, you tend to recover from either a backup or a replica because it is usually not possible to recover a corrupted table file.

I would suggest you also check every other table for corruption, and check disk health.

I would also suggest you upgrade the psql instance since psql 12 went EOL in 2024

Regards