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

> Can we assume there are no concurrent changes being made?

Yes there are no concurrent changes being made during the migration.

> 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".

No, indexes are not created with the CONCURRENTLY option.

> 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.

Sure, we will take this into account.

On Tue, Sep 5, 2023 at 2:43 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> 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/
>