How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8?

From: "Hilbert, Karin" <ioh1(at)psu(dot)edu>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8?
Date: 2021-10-28 14:44:26
Message-ID: MN2PR02MB6829258E28420A40E699FB0289869@MN2PR02MB6829.namprd02.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2021-10-28 14:45:47 Re: database designs ERDs
Previous Message Ryan, Les 2021-10-28 13:52:36 RE: WAL File Recovery on Standby Server Stops Before End of WAL Files