|
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
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
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
…
ß
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
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
|
