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
