Postgres Pro
Downloads
Home   >   mailing lists

How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8? - Mailing list pgsql-general

From Hilbert, Karin
Subject How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8?
Date
Msg-id MN2PR02MB6829258E28420A40E699FB0289869@MN2PR02MB6829.namprd02.prod.outlook.com
Whole thread Raw
Responses Re: How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8?
Re: How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8?
List pgsql-general
Tree view
I manage a PostgreSQL databases - we currently have clusters on PostgreSQL v9.6.23 & PostgreSQL v12.8.
Our database clusters are on Linux VMs, with OS:
  Flavor: redhat_7
  Release: 3.10.0-1160.45.1.el7.x86_64

We have repmgr clusters of 1 Primary & 2 Standby servers & use another server with PgBouncer to direct the connections to the current Primary.  

I am in the process of migrating the v9.6.23 databases to the v12.8 cluster, which already has live databases on it, so I'm doing a pg_dump on the v9.6 cluster for the individual databases to be migrated & restoring the backups to the v12.8 cluster.  I'm currently testing in a sandbox cluster.  The restore completes successfully.  

After the restore, I compare the rowcounts of the dbs from both versions to verify that the data loaded correctly.
I also do a pg_dump of just the data from both clusters & compare them with the diff utility.  For one of the databases, I'm discovering some differences in the data.  It looks like some data is being truncated:

5,6c5,6 

< -- Dumped from database version 9.6.23 

< -- Dumped by pg_dump version 9.6.23 

--- 

> -- Dumped from database version 12.8 

> -- Dumped by pg_dump version 12.8 

34085c34085 

< xxxxxxxx      xxxxxxxx        xxx     P       108     xxxxxxx UP      FI      xxxx-xx-xx      53809.6016      53809.6016      52W      0       xxx     0       xxxxx   \N 

--- 

> xxxxxxxx      xxxxxxxx        xxx     P       108     xxxxxxx UP      FI      xxxx-xx-xx      53809.6        53809.6        52W        0       xxx     0       xxxxx   \N 

34088c34088 

< xxxxxxxx      xxxxxxxx        xxx     P       108     xxxxxxx UP      FI      xxxx-xx-xx      53809.6016      53809.6016      52W      0       xxx     0       xxxxx   \N 

--- 

> xxxxxxxx      xxxxxxxx        xxx     P       108     xxxxxxx UP      FI      xxxx-xx-xx      53809.6        53809.6        52W        0       xxx     0       xxxxx   \N  

…        ß data is truncated in new database 

147825,147826c147825,147826 

< xxxxxxxx      \N      \N      \N      46716.8008      \N      \N      \N      \N      \N      \N      \N 

< xxxxxxxx      \N      \N      \N      38729.6016      \N      \N      \N      \N      \N      \N      \N 

--- 

> xxxxxxxx      \N      \N      \N      46716.8          \N      \N      \N      \N      \N      \N      \N 

> xxxxxxxx      \N      \N      \N      38729.6          \N      \N      \N      \N      \N      \N      \N 


When I looked at the table specification, it is the same in both versions & the affected columns are specified as datatype real:

               Table "tablex" 

      Column      |         Type         | Modifiers 

------------------+----------------------+----------- 

 id               | integer              | not null 

 column2          | character(8)         | not null 

 column3          | character(3)         | not null 

 column4          | character(1)         | 

 column5          | character(4)         | 

 column6          | character(10)        | 

 column7          | character(2)         | 

 column8          | date                 | 

 column9          | real                 | 

 column10         | real                 | 


When I do a select on each database version, the results both display the truncated data:

 id         | column9      | column10 

------------+--------------+------------------ 

   xxxxxxxx |      53809.6 |          53809.6 

(1 row) 

And when I try to export the data from both versions, the data also exports with a 1-digit decimal for those columns.
It's only when I do the pg_dump that I can see the extra digits from the v9.6.23 tables.

In other tables, I'm seeing differences with only 2 digits showing for columns where the datatype is real - they are being rounded up.  For example:

xxxxxxxx        19.8199997      \N      \N      3435    \N      1       \N      \N      \N      3435    0       0 

       3435    \N      \N      \N      0 

… 

xxxxxxxx        25.8700008      \N      \N      4484.12988      80      \N      \N      \N      \N      2069.6001 

       0       0       2069.6001       \N      \N      \N      0  vs.

xxxxxxxx        19.82   \N      \N      3435    \N      1       \N      \N      \N      3435    0       0       3435 

    \N      \N      \N      0 

… 

xxxxxxxx        25.87   \N      \N      4484.13 80      \N      \N      \N      \N      2069.6  0       0       2069.6  \N      \N      \N      0 



How can I ensure that the data was migrated correctly - that the data hasn't been truncated or rounded up in the v12.8 tables?
Any help would be greatly appreciated.

Thanks,

Karin Hilbert

pgsql-general by date:

Previous
From: "Ryan, Les"
Date:
Subject: RE: WAL File Recovery on Standby Server Stops Before End of WAL Files
Next
From: Thomas Kellerer
Date:
Subject: Re: database designs ERDs