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

Поиск
Список
Период
Сортировка
От Hilbert, Karin
Тема How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8?
Дата
Msg-id MN2PR02MB6829258E28420A40E699FB0289869@MN2PR02MB6829.namprd02.prod.outlook.com
обсуждение исходный текст
Ответы Re: How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Ryan, Les"
Дата:
Сообщение: RE: WAL File Recovery on Standby Server Stops Before End of WAL Files
Следующее
От: Thomas Kellerer
Дата:
Сообщение: Re: database designs ERDs