pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions
8+ messages / 4 participants
[nested] [flat]
* BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions
@ 2023-09-04 17:55 PG Bug reporting form <noreply@postgresql.org>
2023-09-04 21:12 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions David Rowley <dgrowleyml@gmail.com>
2023-09-05 15:18 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 8+ messages in thread
From: PG Bug reporting form @ 2023-09-04 17:55 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: gitqueries0@gmail.com
The following bug has been logged on the website:
Bug reference: 18084
Logged by: Git Queries
Email address: gitqueries0@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: Microsoft Windows Server 2019 Standard
Description:
Version - PostgreSQL 10.20, compiled by Visual C++ build 1800, 64-bit
Platform - Windows
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.
Note: There are no migration issues, as all data rows have been successfully
migrated without data loss.
ProdDB=> select count(*) from PdtDetailsTable;
count
---------
1297324
(1 row)
ProdDB=> reindex table PdtDetailsTable;
REINDEX
ProdDB=> select count(*) from PdtDetailsTable;
count
---------
1297322
(1 row)
ProdDB=>
Upon reindexing, the count(*) query returns the correct results in the
source DB.
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions
2023-09-04 17:55 BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions PG Bug reporting form <noreply@postgresql.org>
@ 2023-09-04 21:12 ` David Rowley <dgrowleyml@gmail.com>
2023-09-06 17:40 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Git Queries <gitqueries0@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: David Rowley @ 2023-09-04 21:12 UTC (permalink / raw)
To: gitqueries0@gmail.com; pgsql-bugs@lists.postgresql.org
On Tue, 5 Sept 2023 at 06:19, PG Bug reporting form
<noreply@postgresql.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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions
2023-09-04 17:55 BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions PG Bug reporting form <noreply@postgresql.org>
2023-09-04 21:12 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions David Rowley <dgrowleyml@gmail.com>
@ 2023-09-06 17:40 ` Git Queries <gitqueries0@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Git Queries @ 2023-09-06 17:40 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
> 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@gmail.com> wrote:
> On Tue, 5 Sept 2023 at 06:19, PG Bug reporting form
> <noreply@postgresql.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/
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions
2023-09-04 17:55 BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions PG Bug reporting form <noreply@postgresql.org>
@ 2023-09-05 15:18 ` Bruce Momjian <bruce@momjian.us>
2023-09-06 17:41 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Git Queries <gitqueries0@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2023-09-05 15:18 UTC (permalink / raw)
To: gitqueries0@gmail.com; pgsql-bugs@lists.postgresql.org
On Mon, Sep 4, 2023 at 05:55:51PM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18084
> Logged by: Git Queries
> Email address: gitqueries0@gmail.com
> PostgreSQL version: Unsupported/Unknown
> Operating system: Microsoft Windows Server 2019 Standard
> Description:
>
> Version - PostgreSQL 10.20, compiled by Visual C++ build 1800, 64-bit
>
> Platform - Windows
>
> 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.
>
> Note: There are no migration issues, as all data rows have been successfully
> migrated without data loss.
And how did you perform this migration? pg_dump, pg_upgrade, logical
replication?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions
2023-09-04 17:55 BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions PG Bug reporting form <noreply@postgresql.org>
2023-09-05 15:18 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Bruce Momjian <bruce@momjian.us>
@ 2023-09-06 17:41 ` Git Queries <gitqueries0@gmail.com>
2023-09-06 18:02 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: Git Queries @ 2023-09-06 17:41 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-bugs@lists.postgresql.org
> And how did you perform this migration? pg_dump, pg_upgrade, logical
> replication?
Migration was performed using pg_dump.
On Tue, Sep 5, 2023 at 8:48 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Mon, Sep 4, 2023 at 05:55:51PM +0000, PG Bug reporting form wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 18084
> > Logged by: Git Queries
> > Email address: gitqueries0@gmail.com
> > PostgreSQL version: Unsupported/Unknown
> > Operating system: Microsoft Windows Server 2019 Standard
> > Description:
> >
> > Version - PostgreSQL 10.20, compiled by Visual C++ build 1800, 64-bit
> >
> > Platform - Windows
> >
> > 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.
> >
> > Note: There are no migration issues, as all data rows have been
> successfully
> > migrated without data loss.
>
> And how did you perform this migration? pg_dump, pg_upgrade, logical
> replication?
>
> --
> Bruce Momjian <bruce@momjian.us> https://momjian.us
> EDB https://enterprisedb.com
>
> Only you can decide what is important to you.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions
2023-09-04 17:55 BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions PG Bug reporting form <noreply@postgresql.org>
2023-09-05 15:18 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Bruce Momjian <bruce@momjian.us>
2023-09-06 17:41 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Git Queries <gitqueries0@gmail.com>
@ 2023-09-06 18:02 ` Bruce Momjian <bruce@momjian.us>
2023-09-07 00:26 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2023-09-06 18:02 UTC (permalink / raw)
To: Git Queries <gitqueries0@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Wed, Sep 6, 2023 at 11:11:05PM +0530, Git Queries wrote:
> > And how did you perform this migration? pg_dump, pg_upgrade, logical
> > replication?
>
> Migration was performed using pg_dump.
Uh, that is kind of a surprise since pg_dump/restore does a logical dump
--- none of the binary files are transfered, so it should always be
accurate.
---------------------------------------------------------------------------
>
> On Tue, Sep 5, 2023 at 8:48 PM Bruce Momjian <bruce@momjian.us> wrote:
>
> On Mon, Sep 4, 2023 at 05:55:51PM +0000, PG Bug reporting form wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 18084
> > Logged by: Git Queries
> > Email address: gitqueries0@gmail.com
> > PostgreSQL version: Unsupported/Unknown
> > Operating system: Microsoft Windows Server 2019 Standard
> > Description:
> >
> > Version - PostgreSQL 10.20, compiled by Visual C++ build 1800, 64-bit
> >
> > Platform - Windows
> >
> > 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.
> >
> > Note: There are no migration issues, as all data rows have been
> successfully
> > migrated without data loss.
>
> And how did you perform this migration? pg_dump, pg_upgrade, logical
> replication?
>
> --
> Bruce Momjian <bruce@momjian.us> https://momjian.us
> EDB https://enterprisedb.com
>
> Only you can decide what is important to you.
>
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions
2023-09-04 17:55 BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions PG Bug reporting form <noreply@postgresql.org>
2023-09-05 15:18 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Bruce Momjian <bruce@momjian.us>
2023-09-06 17:41 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Git Queries <gitqueries0@gmail.com>
2023-09-06 18:02 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Bruce Momjian <bruce@momjian.us>
@ 2023-09-07 00:26 ` David Rowley <dgrowleyml@gmail.com>
2023-09-07 01:00 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: David Rowley @ 2023-09-07 00:26 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Git Queries <gitqueries0@gmail.com>; pgsql-bugs@lists.postgresql.org
On Thu, 7 Sept 2023 at 11:17, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Wed, Sep 6, 2023 at 11:11:05PM +0530, Git Queries wrote:
> > Migration was performed using pg_dump.
>
> Uh, that is kind of a surprise since pg_dump/restore does a logical dump
> --- none of the binary files are transfered, so it should always be
> accurate.
I can't be certain, but I think the likely reason here is that the
newly restored instance is fine and all indexes match the heap, but
it's the source instance that has indexes with missing entries. Since
the pg_dump queries are most likely to result in seqscans, then all
rows will be returned and the new instance has no missing rows.
David
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions
2023-09-04 17:55 BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions PG Bug reporting form <noreply@postgresql.org>
2023-09-05 15:18 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Bruce Momjian <bruce@momjian.us>
2023-09-06 17:41 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Git Queries <gitqueries0@gmail.com>
2023-09-06 18:02 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions Bruce Momjian <bruce@momjian.us>
2023-09-07 00:26 ` Re: BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions David Rowley <dgrowleyml@gmail.com>
@ 2023-09-07 01:00 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 8+ messages in thread
From: Bruce Momjian @ 2023-09-07 01:00 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Git Queries <gitqueries0@gmail.com>; pgsql-bugs@lists.postgresql.org
On Thu, Sep 7, 2023 at 12:26:53PM +1200, David Rowley wrote:
> On Thu, 7 Sept 2023 at 11:17, Bruce Momjian <bruce@momjian.us> wrote:
> >
> > On Wed, Sep 6, 2023 at 11:11:05PM +0530, Git Queries wrote:
> > > Migration was performed using pg_dump.
> >
> > Uh, that is kind of a surprise since pg_dump/restore does a logical dump
> > --- none of the binary files are transfered, so it should always be
> > accurate.
>
> I can't be certain, but I think the likely reason here is that the
> newly restored instance is fine and all indexes match the heap, but
> it's the source instance that has indexes with missing entries. Since
> the pg_dump queries are most likely to result in seqscans, then all
> rows will be returned and the new instance has no missing rows.
Yes, very good point.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-09-07 01:00 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-04 17:55 BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions PG Bug reporting form <noreply@postgresql.org>
2023-09-04 21:12 ` David Rowley <dgrowleyml@gmail.com>
2023-09-06 17:40 ` Git Queries <gitqueries0@gmail.com>
2023-09-05 15:18 ` Bruce Momjian <bruce@momjian.us>
2023-09-06 17:41 ` Git Queries <gitqueries0@gmail.com>
2023-09-06 18:02 ` Bruce Momjian <bruce@momjian.us>
2023-09-07 00:26 ` David Rowley <dgrowleyml@gmail.com>
2023-09-07 01:00 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox