Thread: Could not open file pg_xact/0E97
Hi Forum,
We see
pg_dump: Dumping the contents of table "transactions_and_fees_2020_01" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: could not access status of transaction 3916900817
DETAIL: Could not open file "pg_xact/0E97": No such file or directory.
pg_dump: Error message from server: ERROR: could not access status of transaction 3916900817
DETAIL: Could not open file "pg_xact/0E97": No such file or directory.
(pg11.8 on centos 7.8, aws ec2 instance)
This pops up upon select from the table on the standby server.
However on the master server, we can dump the table without problem.
What is interesting, when listing the pg_xact folder, according to the filename , it is from already recycled files. I.e. it is older than the oldest file there.
For resolving the error , we created commit file on the standby like
dd if=/dev/zero of=0E97 bs=256k count=1
and the full dump of the database went successfully.
Perhaps this is not quite the correct approach, so we're gonna to run vacuum full on the master
to recreate the table.
Has anyone encountered this issue? Any advice?
Thank you
Rado
Well. the vacuum full failed with
vacuumdb: vacuuming of table "olap.transactions_and_fees_2020_01" in database "db" failed: ERROR: found xmin 3916900817 from before relfrozenxid 80319533
vacuumdb: vacuuming of table "olap.transactions_and_fees_2020_01" in database "db" failed: ERROR: found xmin 3916900817 from before relfrozenxid 80319533
On Sun, Jul 19, 2020 at 12:01 AM Radoslav Nedyalkov <rnedyalkov@gmail.com> wrote:
Hi Forum,We seepg_dump: Dumping the contents of table "transactions_and_fees_2020_01" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: could not access status of transaction 3916900817
DETAIL: Could not open file "pg_xact/0E97": No such file or directory.(pg11.8 on centos 7.8, aws ec2 instance)This pops up upon select from the table on the standby server.However on the master server, we can dump the table without problem.What is interesting, when listing the pg_xact folder, according to the filename , it is from already recycled files. I.e. it is older than the oldest file there.For resolving the error , we created commit file on the standby likedd if=/dev/zero of=0E97 bs=256k count=1and the full dump of the database went successfully.Perhaps this is not quite the correct approach, so we're gonna to run vacuum full on the masterto recreate the table.Has anyone encountered this issue? Any advice?Thank youRado
On Jul 18, 2020, at 14:18, Radoslav Nedyalkov <rnedyalkov@gmail.com> wrote:
Well. the vacuum full failed with
vacuumdb: vacuuming of table "olap.transactions_and_fees_2020_01" in database "db" failed: ERROR: found xmin 3916900817 from before relfrozenxid 80319533
Do you have checksums enabled for this database?
-Jeremy
Sent from my TI-83
Nope. It's an intensive OLAP data-warehouse with queries hanging in waits for hours.
From the vacuum full error and commit file which is gone due to the supposedly moved age mark,
It looks more like a bug and not an IO error.
Rado
On Tue, Jul 21, 2020 at 2:38 AM Jeremy Schneider <schneider@ardentperf.com> wrote:
On Jul 18, 2020, at 14:18, Radoslav Nedyalkov <rnedyalkov@gmail.com> wrote:Well. the vacuum full failed with
vacuumdb: vacuuming of table "olap.transactions_and_fees_2020_01" in database "db" failed: ERROR: found xmin 3916900817 from before relfrozenxid 80319533Do you have checksums enabled for this database?-JeremySent from my TI-83