Thread: Found xmin before relfrozenxid and logical replication
I am running into an issue on Postgres 13.3 on Centos 7.5
--
I have done some reading on the web and almost everything points to a bug back in the 9.x days. However, here is my scenario.
Primary server is PG 10.17
Target server is PG 13.3
On the primary, for testing purposes, the DB is restored from a pg_dump and just for the heck of it, I vacuumed every table in the primary db manually.
The secondary server i create the schema with a pg_dump using the -c option. No other databases are on the target since it's a fresh build and new install. I create a subscription to the publisher on the primary and all is fine while data is initialized. Eventually all is caught up.
Everything looks normal. But, as I look at my log file for errors on the replica, I see hundreds of entries for.
2021-08-16 23:58:14.496 CDT [32191] ERROR: found xmin 387485 from before relfrozenxid 531040
2021-08-16 23:58:31.632 CDT [32204] ERROR: MultiXactId 537919489 has not been created yet -- apparent wraparound
2021-08-16 23:58:31.632 CDT [32204] ERROR: MultiXactId 537919489 has not been created yet -- apparent wraparound
I cannot vacuum the table on the replica without it erroring out due to the xmin condition.
Any clues?
Thanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
On Wed, 18 Aug 2021 at 20:27, J T <jorge.torralba@gmail.com> wrote:
The secondary server i create the schema with a pg_dump using the -c option. No other databases are on the target since it's a fresh build and new install. I create a subscription to the publisher on the primary and all is fine while data is initialized. Eventually all is caught up.
relevant discussion
and the relevant fix was in PostgreSQL: Documentation: 9.3: Release 9.3.2
`Fix multiple bugs in MultiXactId freezing (Andres Freund, Álvaro Herrera)
These bugs could lead to "could not access status of transaction" errors, or to duplicate or vanishing rows. Users upgrading from releases prior to 9.3.0 are not affected.
The issue can be ameliorated by, after upgrading, vacuuming all tables in all databases while having vacuum_freeze_table_age set to zero. This will fix latent corruption but will not be able to fix all pre-existing data errors.`
These bugs could lead to "could not access status of transaction" errors, or to duplicate or vanishing rows. Users upgrading from releases prior to 9.3.0 are not affected.
The issue can be ameliorated by, after upgrading, vacuuming all tables in all databases while having vacuum_freeze_table_age set to zero. This will fix latent corruption but will not be able to fix all pre-existing data errors.`
Everything looks normal. But, as I look at my log file for errors on the replica, I see hundreds of entries for.2021-08-16 23:58:14.496 CDT [32191] ERROR: found xmin 387485 from before relfrozenxid 531040
2021-08-16 23:58:31.632 CDT [32204] ERROR: MultiXactId 537919489 has not been created yet -- apparent wraparound
What is the state of dead tx?
can you check the status of tx id using
select txid_status(<problem txid>);
this is for the pgdevs,
ok, i might be silly talking about this, but I do not have a test setup to reproduce this.
if the dump is fresh and no other consumer of the db exists, can we run a pg_resetwal on the db to clean those dangling references ?
On Wed, 18 Aug 2021 at 21:26, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:
On Wed, 18 Aug 2021 at 20:27, J T <jorge.torralba@gmail.com> wrote:The secondary server i create the schema with a pg_dump using the -c option. No other databases are on the target since it's a fresh build and new install. I create a subscription to the publisher on the primary and all is fine while data is initialized. Eventually all is caught up.relevant discussionand the relevant fix was in PostgreSQL: Documentation: 9.3: Release 9.3.2`Fix multiple bugs in MultiXactId freezing (Andres Freund, Álvaro Herrera)
These bugs could lead to "could not access status of transaction" errors, or to duplicate or vanishing rows. Users upgrading from releases prior to 9.3.0 are not affected.
The issue can be ameliorated by, after upgrading, vacuuming all tables in all databases while having vacuum_freeze_table_age set to zero. This will fix latent corruption but will not be able to fix all pre-existing data errors.`Everything looks normal. But, as I look at my log file for errors on the replica, I see hundreds of entries for.2021-08-16 23:58:14.496 CDT [32191] ERROR: found xmin 387485 from before relfrozenxid 531040
2021-08-16 23:58:31.632 CDT [32204] ERROR: MultiXactId 537919489 has not been created yet -- apparent wraparoundWhat is the state of dead tx?can you check the status of tx id usingselect txid_status(<problem txid>);this is for the pgdevs,ok, i might be silly talking about this, but I do not have a test setup to reproduce this.if the dump is fresh and no other consumer of the db exists, can we run a pg_resetwal on the db to clean those dangling references ?
is it possible to share the result of the below command.
pg_controldata -D <datadir>
Thanks,
Vijay
Mumbai, India