Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions

PostgreSQL
1

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?

2

Evidently, as per your other posts, you have suffered table damage. The first mitigation technique is to run VACUUM across the entire datacluster.

Judging from your subsequent postings however I’d say you’ve already moved past this point.

Hope this helps.

1 Like