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.
(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
On Sun, Jul 19, 2020 at 12:01 AM Radoslav Nedyalkov <rnedyalkov@gmail.com>
wrote:
Show quoted text
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.
(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 youRado
On Jul 18, 2020, at 14:18, Radoslav Nedyalkov <rnedyalkov@gmail.com> wrote:
Well. the vacuum full failed withvacuumdb: 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:
Show quoted text
On Jul 18, 2020, at 14:18, Radoslav Nedyalkov <rnedyalkov@gmail.com>
wrote:
Well. the vacuum full failed withvacuumdb: 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?
-Jeremy
Sent from my TI-83