Thread: BUG #18648: When I restore the database on rockylinux9, I will use amcheck to check that there will be an abnorm

The following bug has been logged on the website:

Bug reference:      18648
Logged by:          weijie JL
Email address:      weijie1006jl@gmail.com
PostgreSQL version: 14.12
Operating system:   RockyLinux 9
Description:

I used dnf to install PostgreSQL on a physical machine running Rocky Linux
9, and then performed a database restore using the pgBackRest tool. After
multiple experiments, I still found that there were index corruption issues
after the restore. Specifically, using amcheck to check the indexes resulted
in the following error: ERROR: XX002: item order invariant violated for
index "idx_template_xxxx_unique". However, this restore process does not
reproduce the issue on CentOS 7.9. The business impact of this index anomaly
is that when the query performs a full table scan, it can retrieve the
required data, but when using the index, the query results are empty.

On 09/10/2024 03:54, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      18648
> Logged by:          weijie JL
> Email address:      weijie1006jl@gmail.com
> PostgreSQL version: 14.12
> Operating system:   RockyLinux 9
> Description:
> 
> I used dnf to install PostgreSQL on a physical machine running Rocky Linux
> 9, and then performed a database restore using the pgBackRest tool. After
> multiple experiments, I still found that there were index corruption issues
> after the restore. Specifically, using amcheck to check the indexes resulted
> in the following error: ERROR: XX002: item order invariant violated for
> index "idx_template_xxxx_unique". However, this restore process does not
> reproduce the issue on CentOS 7.9. The business impact of this index anomaly
> is that when the query performs a full table scan, it can retrieve the
> required data, but when using the index, the query results are empty.

Could be this issue: https://wiki.postgresql.org/wiki/Locale_data_changes

-- 
Heikki Linnakangas
Neon (https://neon.tech)

It is indeed this issue that caused the problem. Thank you very much.


> 2024年10月9日 08:54，PG Bug reporting form <noreply@postgresql.org> 写道：
>
> The following bug has been logged on the website:
>
> Bug reference:      18648
> Logged by:          weijie JL
> Email address:      weijie1006jl@gmail.com
> PostgreSQL version: 14.12
> Operating system:   RockyLinux 9
> Description:
>
> I used dnf to install PostgreSQL on a physical machine running Rocky Linux
> 9, and then performed a database restore using the pgBackRest tool. After
> multiple experiments, I still found that there were index corruption issues
> after the restore. Specifically, using amcheck to check the indexes resulted
> in the following error: ERROR: XX002: item order invariant violated for
> index "idx_template_xxxx_unique". However, this restore process does not
> reproduce the issue on CentOS 7.9. The business impact of this index anomaly
> is that when the query performs a full table scan, it can retrieve the
> required data, but when using the index, the query results are empty.
>