Re: broken reading on standby (PostgreSQL 16.2)

čt 25. 4. 2024 v 12:53 odesílatel Melanie Plageman <
melanieplageman(at)gmail(dot)com> napsal:

> On Thu, Apr 25, 2024 at 2:13 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >
> > Hi
> >
> > yesterday, I had to fix strange issue on standby server
> >
> > The query to freshly updated data fails
> >
> > select * from seller_success_rate where create_time::date = '2024-04-23';
> > ERROR: 58P01: could not access status of transaction 1393466389
> > DETAIL: Could not open file "pg_xact/0530": No such file or directory.
> > LOCATION: SlruReportIOError, slru.c:947
> >
> > amcheck
> >
> > select * from verify_heapam('seller_success_rate');
> > blkno | offnum | attnum | msg
> >
> -------+--------+--------+-------------------------------------------------------------------
> > 5763 | 111 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 5863 | 109 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 5863 | 110 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 5868 | 110 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 5868 | 111 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 5875 | 111 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 5895 | 109 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 5895 | 110 | | xmin 1439564642 precedes oldest valid
> transaction ID 3:1523885078
> > 6245 | 108 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 6245 | 109 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 6245 | 110 | | xmin 1439564642 precedes oldest valid
> transaction ID 3:1523885078
> > 6245 | 112 | | xmin 1424677216 precedes oldest valid
> transaction ID 3:1523885078
> > 6378 | 109 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 6378 | 110 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 6382 | 110 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 6590 | 110 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 6590 | 111 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 7578 | 112 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 7581 | 112 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 8390 | 112 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 10598 | 109 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> > 10598 | 110 | | xmin 1393466389 precedes oldest valid
> transaction ID 3:1523885078
> >
> > I verified xmin against the primary server, and it was the same. There
> was not any replication gap.
> >
> > I checked the fields from pg_database table, and looks same too
> >
> > These rows were valid (and visible) on primary.
> >
> > On this server there was not any long session (when I was connected),
> unfortunately I cannot test restart of this server. One wal sender is
> executing on standby. Fortunately, there was a possibility to run VACUUM
> FULL, and it fixed the issue.
> >
> > The customer has archived wals.
>
> Did you mention what version of Postgres this is?
>

16.2 and related rows was today one day old

>
> - Melanie
>