Re: Could not open file pg_xact/0E97

[Radoslav Nedyalkov <rnedyalkov@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

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