Re: re PG 9.6x and found xmin from before relfrozenxid and removal of pg_internal.init file(s)

On Mon, 2020-09-28 at 12:15 -0400, Tom Lane wrote:
> Reid Thompson <Reid.Thompson@omnicell.com> writes:
> > We have a planned upgrade that would permanentlty remedy this.
> > regarding the below errors on our PG 9.6.x instance.
> 
> > 2020-09-28 09:08:15.741 EDT,,,26212,,5f71e03f.6664,1,,2020-09-28 09:08:15 EDT,250/9136136,0,ERROR,XX001,"found xmin
2675436435from before relfrozenxid 321165377",,,,,"automatic vacuum of table
 
> > ""postgres.pg_catalog.pg_authid""",,,,""
> > 2020-09-28 09:08:15.751 EDT,,,26212,,5f71e03f.6664,2,,2020-09-28 09:08:15 EDT,250/9136138,0,ERROR,XX001,"found xmin
2675019557from before relfrozenxid 321165377",,,,,"automatic vacuum of table
 
> > ""postgres.pg_catalog.pg_auth_members""",,,,""
> 
> > I know that a db restart will reset the 'counter' (reset the
> > pg_internal.init files??)  on this issue.  We would rather avoid a
> > restart if possible. Some research on the internet found a page
> > suggesting that removal of pg_internal.init file(s) on a running system
> > would provide the same temporary resolution as a restart???   Is this a
> > valid temporary work around for this issue?  If it is, is there a
> > particular pg_internal.init file that needs to be removed, or just
> > remove all pg_internal.init that can be found?
> 
> I'm a bit dubious that that'd actually help, but it's perfectly safe
> if you want to try it.  pg_internal.init is just a cache file that
> will be rebuilt if it's missing.
> 
>                         regards, tom lane


appears to allow to vacuum to complete... and stops the error messages
to the log file.

[local]:db-server-101:5432 rthompso@xxxx=> set role super;
SET
Time: 0.900 ms
[local]:db-server-101:5432 rthompso@xxxx=# vacuum pg_catalog.pg_authid; vacuum pg_catalog.pg_auth_members;
ERROR:  found xmin 2675436435 from before relfrozenxid 321165377
Time: 14.698 ms
ERROR:  found xmin 2675019557 from before relfrozenxid 321165377
Time: 3.956 ms
[local]:db-server-101:5432 rthompso@xxxx=# vacuum pg_catalog.pg_authid; vacuum pg_catalog.pg_auth_members;
ERROR:  found xmin 2675436435 from before relfrozenxid 321165377
Time: 13.889 ms
ERROR:  found xmin 2675019557 from before relfrozenxid 321165377
Time: 2.479 ms
[local]:db-server-101:5432 rthompso@xxxx=# vacuum pg_catalog.pg_authid; vacuum pg_catalog.pg_auth_members;
VACUUM
Time: 22.778 ms
VACUUM
Time: 4.486 ms
[local]:db-server-101:5432 rthompso@xxxx=# vacuum verbose pg_catalog.pg_authid; vacuum verbose
pg_catalog.pg_auth_members;
INFO:  vacuuming "pg_catalog.pg_authid"
INFO:  index "pg_authid_rolname_index" now contains 123 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_authid_oid_index" now contains 123 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_authid": found 0 removable, 123 nonremovable row versions in 3 out of 3 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 55 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 25.909 ms
INFO:  vacuuming "pg_catalog.pg_auth_members"
INFO:  index "pg_auth_members_role_member_index" now contains 143 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_auth_members_member_role_index" now contains 143 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_auth_members": found 0 removable, 143 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 13 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 6.494 ms


$ grep pg_auth *Mon*csv
...snip...
2020-09-28 14:00:23.564 EDT,,,9998,,5f7224b7.270e,1,,2020-09-28 14:00:23 EDT,271/14248144,0,ERROR,XX001,"found xmin
2675436435from before relfrozenxid 321165377",,,,,"automatic vacuum of table ""postgres.pg_catalog.pg_authid""",,,,""
 
2020-09-28 14:00:23.575 EDT,,,9998,,5f7224b7.270e,2,,2020-09-28 14:00:23 EDT,271/14248146,0,ERROR,XX001,"found xmin
2675019557from before relfrozenxid 321165377",,,,,"automatic vacuum of table
""postgres.pg_catalog.pg_auth_members""",,,,""
2020-09-28 14:00:28.571 EDT,,,10283,,5f7224bc.282b,1,,2020-09-28 14:00:28 EDT,271/14248188,0,ERROR,XX001,"found xmin
2675436435from before relfrozenxid 321165377",,,,,"automatic vacuum of table ""postgres.pg_catalog.pg_authid""",,,,""
 
2020-09-28 14:00:28.581 EDT,,,10283,,5f7224bc.282b,2,,2020-09-28 14:00:28 EDT,271/14248190,0,ERROR,XX001,"found xmin
2675019557from before relfrozenxid 321165377",,,,,"automatic vacuum of table
""postgres.pg_catalog.pg_auth_members""",,,,""
2020-09-28 14:00:33.577 EDT,,,10432,,5f7224c1.28c0,1,,2020-09-28 14:00:33 EDT,271/14248210,0,ERROR,XX001,"found xmin
2675436435from before relfrozenxid 321165377",,,,,"automatic vacuum of table ""postgres.pg_catalog.pg_authid""",,,,""
 
2020-09-28 14:00:33.587 EDT,,,10432,,5f7224c1.28c0,2,,2020-09-28 14:00:33 EDT,271/14248212,0,ERROR,XX001,"found xmin
2675019557from before relfrozenxid 321165377",,,,,"automatic vacuum of table
""postgres.pg_catalog.pg_auth_members""",,,,""
2020-09-28 14:00:37.822 EDT,"rthompso","ateb",8067,"[local]",5f72248e.1f83,4,"VACUUM",2020-09-28 13:59:42
EDT,270/18855606,0,ERROR,XX001,"foundxmin 2675436435 from before relfrozenxid 321165377",,,,,,"vacuum
pg_catalog.pg_authid;",,,"psql"
2020-09-28 14:00:37.824 EDT,"rthompso","ateb",8067,"[local]",5f72248e.1f83,5,"VACUUM",2020-09-28 13:59:42
EDT,270/18855608,0,ERROR,XX001,"foundxmin 2675019557 from before relfrozenxid 321165377",,,,,,"vacuum
pg_catalog.pg_auth_members;",,,"psql"
2020-09-28 14:00:38.586 EDT,,,10565,,5f7224c6.2945,1,,2020-09-28 14:00:38 EDT,271/14248236,0,LOG,00000,"automatic
vacuumof table ""postgres.pg_catalog.pg_authid"": index scans: 1
 
2020-09-28 14:00:38.600 EDT,,,10565,,5f7224c6.2945,2,,2020-09-28 14:00:38 EDT,271/14248238,0,LOG,00000,"automatic
vacuumof table ""postgres.pg_catalog.pg_auth_members"": index scans: 1
 
2020-09-28 14:00:44.011 EDT,,,10578,,5f7224cb.2952,13,,2020-09-28 14:00:43 EDT,271/14248265,0,LOG,00000,"automatic
vacuumof table ""ateb.pg_catalog.pg_authid"": index scans: 0
 
2020-09-28 14:00:46.184 EDT,,,10578,,5f7224cb.2952,68,,2020-09-28 14:00:43 EDT,271/14248373,0,LOG,00000,"automatic
vacuumof table ""ateb.pg_catalog.pg_auth_members"": index scans: 0
 
[postgres@db-server-101 pg_log]$