Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions

On Tue, 5 Sept 2023 at 06:19, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> Regarding PostgreSQL, during the migration from PG 10.20 to 14.7, we will
> perform a sanity check on the migrated tables using source and destination
> row counts. Occasionally, we encounter count mismatches where the source
> database has fewer rows, resulting in a higher count, or more rows,
> resulting in a lower count.

Can we assume there are no concurrent changes being made?

> Upon reindexing, the count(*) query returns the correct results in the
> source DB.

Is it possible that the previous index was created with the
CONCURRENTLY option? There have been a few bugs in that area. I've
not looked exhaustively, but see [1] and [2]. Search for "reindex".

> In the pg_log, we couldn't find any traces related to this behavior. Is
> there a reason for such behavior, and how can this be addressed to prevent
> future issues?

There have been a number of bugs fixed since 10.0 that could have led
to this. The best thing to do to help prevent this is always upgrade
when a minor version is released and follow any relevant instructions
given in the release notes. If you skip minor versions, then you'll
need to look at all the release notes from the minor version you're
going from, up to the version you're upgrading to.

It looks like this practice wasn't being followed as you're on 10.20,
which was released 1.5 years ago. Minor versions are released to
address/fix bugs that are found. If you don't upgrade minor versions
then you don't receive bug fixes.

David

[1] https://www.postgresql.org/docs/release/10.16/
[2] https://www.postgresql.org/docs/release/10.19/