Can't import backup from postgres to different server with the same version of postgres because of different monetary format

Question

I'm moving my databases from Postgres 9.1.23 running on Debian 7.11 to a different server with Postgres 9.1.24 running on Debian 10 and I can't import one database that's using money data type because I get input syntax error

COPY failed for table "x": ERROR:  invalid input syntax for type money: "zł4.400,00"

I'm using the same locale on both Postgres servers, on Debian 7.11:

postgres=# show lc_collate;
 lc_collate
-------------
 pl_PL.UTF-8
(1 row)

postgres=# show lc_monetary;
 lc_monetary
-------------
 pl_PL.UTF-8
(1 row)

And on Debian 10:

postgres=# show lc_collate;
 lc_collate
-------------
 pl_PL.UTF-8
(1 wiersz)

postgres=# show lc_monetary;
 lc_monetary
-------------
 pl_PL.UTF-8
(1 wiersz)

Yet when I try to run select '12345'::money; on both servers I get different results, on Debian 7.11:

postgres=# select '12345'::money;
    money
-------------
 zł12.345,00
(1 row)

and on Debian 10:

postgres=# select '12345'::money;
    money
-------------
 zł12 345,00
(1 wiersz)

(in Debian 10 between 2 and 3 there is a narrow no-brake space character - 0x202F UTF-16)

What do I have to successfully import it without errors? Thank you for help.

Answer 1

It must be that the GNU C library must have changed its idea of LC_MONETARY between these versions.

I would dump and restore using the C locale:

PGOPTIONS='-c lc_monetary=C' pg_dump -F c -f dumpfile dbname
PGOPTIONS='-c lc_monetary=C' pg_restore -d newdbname dumpfile