Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
Hi Adrian, so finally I have a workaround which is ok for me. When I seperate the tables and the data (using the -a and -s switch from pg_dump) into 2 sql backup files, everything works ok on the problem-VM. I try to investigate further in the coming weeks, I´m on holiday next week. Regards and thanks for your support, Eric 2014-12-10 15:27 GMT+01:00 Adrian Klaver : > > On 12/10/2014 01:32 AM, Eric Svenson wrote: > >> So, one more success... >> >> I have taken a part of the backup SQL file which fills the table >> >> COPY dev_my_settings (.) from stdin; >> 12345 text text 0 123.345345 >> >> This file ALONE works! (without changing ANYTHING!) >> > > Hmm, almost like the encoding/locale is changing in the complete file. > > >> So if I run the first (huge) SQL file and then the second, which fills >> the dev_my_settings table, everything is ok. >> > > FYI if you do a pg_dump using the custom format(-Fc) you have more control > over the restore. You can run pg_restore -l against the dump file to get a > Table of Contents(TOC), which you can edit by commenting out items you do > not want to restore and then feed back to pg_restore via -L. You also have > the option to 'restore' all or part of the custom file to a text file using > the -f option. The details can be found here: > > http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html > > >> 2014-12-10 10:23 GMT+01:00 Eric Svenson > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
> The restore left you with two empty tables. What happens if you log into Postgres > via psql and then INSERT one set of values containing floats into say, >dev_my_settings? SUCCESS! This works OK! INSERT INTO dev_my_settings(123, 'test', 'test', 'test', 123, 123.345); Value 123.345 can be read from pg_admin. SHOW ALL shows English_United States.1252 for all lc_ settings (but I have tried it with German and C locale with same results) Regards, Eric 2014-12-08 22:57 GMT+01:00 Adrian Klaver : > On 12/08/2014 06:53 AM, Eric Svenson wrote: > >> Hi Adrian, >> >> I try to get access to the non-VM machine, at the moment access is not >> possible for me unfortunately. >> >> You are right, there are more tables in the database which are restored >> correctly but these tables do NOT contain float values. These two tables >> are the only tables in the database which contain floats. >> >> The errors occur with the first float in the table, the restore process >> seems to terminate with that table and seems to continue with the next >> table. The result are completely empty tables for dev_my_settings and >> file_item. >> >> There are float values in the table which can be viewed with pg_admin. >> >> The table definitions for dev_my_settings and file_item contain lots of >> BIGINTS, smallints and integers, and several double precision values. >> All other tables do not contain any double precision values. >> > > Alright a chance to think some more. > > So: > > The restore left you with two empty tables. What happens if you log into > Postgres via psql and then INSERT one set of values containing floats into > say, dev_my_settings? > > While you are in psql, what does SHOW ALL display for the lc_* settings? > > On the Windows server where the Postgres server is running what does SET > show from the command line? > > > >> Regards, >> Eric >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
So, one more success... I have taken a part of the backup SQL file which fills the table COPY dev_my_settings (.) from stdin; 12345 text text 0 123.345345 This file ALONE works! (without changing ANYTHING!) So if I run the first (huge) SQL file and then the second, which fills the dev_my_settings table, everything is ok. 2014-12-10 10:23 GMT+01:00 Eric Svenson : > > The restore left you with two empty tables. What happens if you log into > Postgres > via psql and then INSERT one set of values containing floats > into say, >dev_my_settings? > > SUCCESS! This works OK! > > INSERT INTO dev_my_settings(123, 'test', 'test', 'test', 123, 123.345); > > Value 123.345 can be read from pg_admin. > > SHOW ALL shows English_United States.1252 for all lc_ settings (but I have > tried it with German and C locale with same results) > > Regards, Eric > > 2014-12-08 22:57 GMT+01:00 Adrian Klaver : > >> On 12/08/2014 06:53 AM, Eric Svenson wrote: >> >>> Hi Adrian, >>> >>> I try to get access to the non-VM machine, at the moment access is not >>> possible for me unfortunately. >>> >>> You are right, there are more tables in the database which are restored >>> correctly but these tables do NOT contain float values. These two tables >>> are the only tables in the database which contain floats. >>> >>> The errors occur with the first float in the table, the restore process >>> seems to terminate with that table and seems to continue with the next >>> table. The result are completely empty tables for dev_my_settings and >>> file_item. >>> >>> There are float values in the table which can be viewed with pg_admin. >>> >>> The table definitions for dev_my_settings and file_item contain lots of >>> BIGINTS, smallints and integers, and several double precision values. >>> All other tables do not contain any double precision values. >>> >> >> Alright a chance to think some more. >> >> So: >> >> The restore left you with two empty tables. What happens if you log into >> Postgres via psql and then INSERT one set of values containing floats into >> say, dev_my_settings? >> >> While you are in psql, what does SHOW ALL display for the lc_* settings? >> >> On the Windows server where the Postgres server is running what does SET >> show from the command line? >> >> >> >>> Regards, >>> Eric >>> >>> >>> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > >
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
On 12/10/2014 01:32 AM, Eric Svenson wrote: So, one more success... I have taken a part of the backup SQL file which fills the table COPY dev_my_settings (.) from stdin; 12345 text text 0 123.345345 This file ALONE works! (without changing ANYTHING!) Hmm, almost like the encoding/locale is changing in the complete file. So if I run the first (huge) SQL file and then the second, which fills the dev_my_settings table, everything is ok. FYI if you do a pg_dump using the custom format(-Fc) you have more control over the restore. You can run pg_restore -l against the dump file to get a Table of Contents(TOC), which you can edit by commenting out items you do not want to restore and then feed back to pg_restore via -L. You also have the option to 'restore' all or part of the custom file to a text file using the -f option. The details can be found here: http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html 2014-12-10 10:23 GMT+01:00 Eric Svenson -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
Hi Adrian, here are the results of today: > How where the Postgres instances installed? > From a package? Yes. It is Version 9.2 for Windows, comment of the package is "The PostgreSQL RDBMS, version 9.2, packaged by EnterpriseDB" > Compiled and if so with what compiler and what settings? No. > Use --inserts with pg_dump to get INSERT statements instead of a COPY and then feed to psql. > This will slow the process down, so I would try with a small sample set. Much slower, but still the same error: (part of the output) INSERT 50415934 1 INSERT 50415935 1 INSERT 50415936 1 INSERT 50415937 1 INSERT 50415938 1 INSERT 50415939 1 ERROR: invalid input syntax for type double precision: "0.10001" > Do pg_dump -Fc and then use pg_restore. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2558; 0 5161040 TABLE DATA dev_my_settings my_Database pg_restore: [archiver (db)] COPY failed for table "dev_my_settings": ERROR: invalid input syntax for type double precision: "0.10001" CONTEXT: COPY dev_meas_settings, line 718, column dms_dble_value: "0.10001" pg_restore: [archiver (db)] Error from TOC entry 2552; 0 5160884 TABLE DATA file_item my_Database pg_restore: [archiver (db)] COPY failed for table "file_item": ERROR: invalid input syntax for type double precision: "48.2000829" CONTEXT: COPY file_item, line 54, column fi_latitude: "48.2000829" WARNING: errors ignored on restore: 2 Regards, Eric 2014-12-05 16:22 GMT+01:00 Adrian Klaver : > On 12/05/2014 01:13 AM, Eric Svenson wrote: > >> >> >> Hi Adrian, >> >> Is the above how the message was actually presented or has a >>> partialtranslation taken >place? Just asking because it would seem to >>> indicate >>> >> further confusion about the locale. >> >> This is an exact copy of the screen contents, no translation by me has >> taken place. Indeed strange, "ERROR" (english) "KONTEXT" (german, one >> line below) ?? >> >> To add further confusion: I have a report that the error also appeared >> on a non-VM native PC with Windows 7 Enterprise. Unfortunately, I dont >> have the exact error message, but I try to get it ASAP. >> > > Well nothing came back to me on VMware and locales, but that does not seem > to be the issue if the above is correct. > > So: > > How where the Postgres instances installed? > From a package? > Compiled and if so with what compiler and what settings? > > What happens if you?: > > Use --inserts with pg_dump to get INSERT statements instead of a COPY and > then feed to psql. > This will slow the process down, so I would try with a small sample set. > > Do pg_dump -Fc and then use pg_restore. > > The above are more a way of seeing if the issue is on a particular path or > is generic, than a solution. > > > >> regards, >> Eric Svenson >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
Hi Adrian, I try to get access to the non-VM machine, at the moment access is not possible for me unfortunately. You are right, there are more tables in the database which are restored correctly but these tables do NOT contain float values. These two tables are the only tables in the database which contain floats. The errors occur with the first float in the table, the restore process seems to terminate with that table and seems to continue with the next table. The result are completely empty tables for dev_my_settings and file_item. There are float values in the table which can be viewed with pg_admin. The table definitions for dev_my_settings and file_item contain lots of BIGINTS, smallints and integers, and several double precision values. All other tables do not contain any double precision values. Regards, Eric 2014-12-08 15:22 GMT+01:00 Adrian Klaver : > On 12/08/2014 12:21 AM, Eric Svenson wrote: > >> Hi Adrian, >> >> here are the results of today: >> >> > How where the Postgres instances installed? >> > From a package? >> >> Yes. It is Version 9.2 for Windows, comment of the package is >> "The PostgreSQL RDBMS, version 9.2, packaged by EnterpriseDB" >> >> >> > Compiled and if so with what compiler and what settings? >> No. >> >> >> > Use --inserts with pg_dump to get INSERT statements instead of a COPY >> and then feed to psql. >> > This will slow the process down, so I would try with a small sample >> set. >> >> Much slower, but still the same error: >> >> (part of the output) >> >> INSERT 50415934 1 >> INSERT 50415935 1 >> INSERT 50415936 1 >> INSERT 50415937 1 >> INSERT 50415938 1 >> INSERT 50415939 1 >> ERROR: invalid input syntax for type double precision: >> "0.10001" >> >> > Do pg_dump -Fc and then use pg_restore. >> >> pg_restore: [archiver (db)] Error while PROCESSING TOC: >> pg_restore: [archiver (db)] Error from TOC entry 2558; 0 5161040 TABLE >> DATA dev_my_settings my_Database >> pg_restore: [archiver (db)] COPY failed for table "dev_my_settings": >> ERROR: invalid input syntax >> for type double precision: "0.10001" >> CONTEXT: COPY dev_meas_settings, line 718, column dms_dble_value: >> "0.10001" >> pg_restore: [archiver (db)] Error from TOC entry 2552; 0 5160884 TABLE >> DATA file_item my_Database >> pg_restore: [archiver (db)] COPY failed for table "file_item": ERROR: >> invalid input syntax for type >> double precision: "48.2000829" >> CONTEXT: COPY file_item, line 54, column fi_latitude: >> "48.2000829" >> WARNING: errors ignored on restore: 2 >> > > Well at least it is consistent:) Postgres is deployed to a lot of Windows > machines, so if this was a generic Windows problem I would expect more > reports on this. There is something about this setup that is causing the > problem and we are missing. > > In a previous post you made mention of a possible instance where this > cropped up on a non-VM machine. Did you get a chance to track that down? > > Also the output from pg_restore shows only two errors on restore which I > presume are the two COPY errors with the input syntax. So are there other > tables in the database, with float values, that do restore correctly? > > Also in the errors above, in the first case COPY does not error until line > 718 and in the second case line 54. So are there float values in the data > for those columns that are valid? > > Also what are the table definitions for dev_my_settings and file_item? > > >> Regards, >> Eric >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
On 12/08/2014 06:53 AM, Eric Svenson wrote: Hi Adrian, I try to get access to the non-VM machine, at the moment access is not possible for me unfortunately. You are right, there are more tables in the database which are restored correctly but these tables do NOT contain float values. These two tables are the only tables in the database which contain floats. The errors occur with the first float in the table, the restore process seems to terminate with that table and seems to continue with the next table. The result are completely empty tables for dev_my_settings and file_item. There are float values in the table which can be viewed with pg_admin. The table definitions for dev_my_settings and file_item contain lots of BIGINTS, smallints and integers, and several double precision values. All other tables do not contain any double precision values. Alright a chance to think some more. So: The restore left you with two empty tables. What happens if you log into Postgres via psql and then INSERT one set of values containing floats into say, dev_my_settings? While you are in psql, what does SHOW ALL display for the lc_* settings? On the Windows server where the Postgres server is running what does SET show from the command line? Regards, Eric -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
On 12/08/2014 06:53 AM, Eric Svenson wrote: Hi Adrian, I try to get access to the non-VM machine, at the moment access is not possible for me unfortunately. You are right, there are more tables in the database which are restored correctly but these tables do NOT contain float values. These two tables are the only tables in the database which contain floats. The errors occur with the first float in the table, the restore process seems to terminate with that table and seems to continue with the next table. The result are completely empty tables for dev_my_settings and file_item. There are float values in the table which can be viewed with pg_admin. The table definitions for dev_my_settings and file_item contain lots of BIGINTS, smallints and integers, and several double precision values. All other tables do not contain any double precision values. At the moment I am out of ideas. Regards, Eric -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
On 12/08/2014 12:21 AM, Eric Svenson wrote: Hi Adrian, here are the results of today: > How where the Postgres instances installed? > From a package? Yes. It is Version 9.2 for Windows, comment of the package is "The PostgreSQL RDBMS, version 9.2, packaged by EnterpriseDB" > Compiled and if so with what compiler and what settings? No. > Use --inserts with pg_dump to get INSERT statements instead of a COPY and then feed to psql. > This will slow the process down, so I would try with a small sample set. Much slower, but still the same error: (part of the output) INSERT 50415934 1 INSERT 50415935 1 INSERT 50415936 1 INSERT 50415937 1 INSERT 50415938 1 INSERT 50415939 1 ERROR: invalid input syntax for type double precision: "0.10001" > Do pg_dump -Fc and then use pg_restore. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2558; 0 5161040 TABLE DATA dev_my_settings my_Database pg_restore: [archiver (db)] COPY failed for table "dev_my_settings": ERROR: invalid input syntax for type double precision: "0.10001" CONTEXT: COPY dev_meas_settings, line 718, column dms_dble_value: "0.10001" pg_restore: [archiver (db)] Error from TOC entry 2552; 0 5160884 TABLE DATA file_item my_Database pg_restore: [archiver (db)] COPY failed for table "file_item": ERROR: invalid input syntax for type double precision: "48.2000829" CONTEXT: COPY file_item, line 54, column fi_latitude: "48.2000829" WARNING: errors ignored on restore: 2 Well at least it is consistent:) Postgres is deployed to a lot of Windows machines, so if this was a generic Windows problem I would expect more reports on this. There is something about this setup that is causing the problem and we are missing. In a previous post you made mention of a possible instance where this cropped up on a non-VM machine. Did you get a chance to track that down? Also the output from pg_restore shows only two errors on restore which I presume are the two COPY errors with the input syntax. So are there other tables in the database, with float values, that do restore correctly? Also in the errors above, in the first case COPY does not error until line 718 and in the second case line 54. So are there float values in the data for those columns that are valid? Also what are the table definitions for dev_my_settings and file_item? Regards, Eric -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
On 12/05/2014 01:13 AM, Eric Svenson wrote: Hi Adrian, Is the above how the message was actually presented or has a partialtranslation taken >place? Just asking because it would seem to indicate further confusion about the locale. This is an exact copy of the screen contents, no translation by me has taken place. Indeed strange, "ERROR" (english) "KONTEXT" (german, one line below) ?? To add further confusion: I have a report that the error also appeared on a non-VM native PC with Windows 7 Enterprise. Unfortunately, I dont have the exact error message, but I try to get it ASAP. Well nothing came back to me on VMware and locales, but that does not seem to be the issue if the above is correct. So: How where the Postgres instances installed? From a package? Compiled and if so with what compiler and what settings? What happens if you?: Use --inserts with pg_dump to get INSERT statements instead of a COPY and then feed to psql. This will slow the process down, so I would try with a small sample set. Do pg_dump -Fc and then use pg_restore. The above are more a way of seeing if the issue is on a particular path or is generic, than a solution. regards, Eric Svenson -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
Hi Adrian, >Is the above how the message was actually presented or has a partial translation taken >place? Just asking because it would seem to indicate further confusion about the locale. This is an exact copy of the screen contents, no translation by me has taken place. Indeed strange, "ERROR" (english) "KONTEXT" (german, one line below) ?? To add further confusion: I have a report that the error also appeared on a non-VM native PC with Windows 7 Enterprise. Unfortunately, I dont have the exact error message, but I try to get it ASAP. regards, Eric Svenson 2014-12-04 15:23 GMT+01:00 Adrian Klaver : > On 12/04/2014 02:01 AM, Eric Svenson wrote: > >> So what if you load to the VM Postgres using the psql from the host? >>> >> >> I tried that: On the host I started psql with >> psql -h 192.168.2.55 -U postgres -p 5432 my_Database < myFile.sql >> >> I get the same error >> >> ERROR: invalid input syntax for type double precision "0.2" >> KONTEXT: COPY dev_my_settings, line xxx, column xxx: "0.2" >> > > Is the above how the message was actually presented or has a partial > translation taken place? Just asking because it would seem to indicate > further confusion about the locale. > > >> ERROR: invalid input syntax for type double precision "15.776653623" >> > > Hmm, I'm at a loss. It is not a strict Windows issue as you can restore on > other Windows machines. The remaining suspect would be VMware. VMware and > locales tickles a memory, one that I cannot drag up at the moment. One of > those things where the more you think of it the further it goes away. We > will see if working on other stuff causes it to sneak up on me:) > > > >> Regards, >> Eric Svenson >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Fwd: [GENERAL] Problem with pg_dump and decimal mark
On 12/04/2014 02:01 AM, Eric Svenson wrote: So what if you load to the VM Postgres using the psql from the host? I tried that: On the host I started psql with psql -h 192.168.2.55 -U postgres -p 5432 my_Database < myFile.sql I get the same error ERROR: invalid input syntax for type double precision "0.2" KONTEXT: COPY dev_my_settings, line xxx, column xxx: "0.2" Is the above how the message was actually presented or has a partial translation taken place? Just asking because it would seem to indicate further confusion about the locale. ERROR: invalid input syntax for type double precision "15.776653623" Hmm, I'm at a loss. It is not a strict Windows issue as you can restore on other Windows machines. The remaining suspect would be VMware. VMware and locales tickles a memory, one that I cannot drag up at the moment. One of those things where the more you think of it the further it goes away. We will see if working on other stuff causes it to sneak up on me:) Regards, Eric Svenson -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] Problem with pg_dump and decimal mark
> So what if you load to the VM Postgres using the psql from the host? I tried that: On the host I started psql with psql -h 192.168.2.55 -U postgres -p 5432 my_Database < myFile.sql I get the same error ERROR: invalid input syntax for type double precision "0.2" KONTEXT: COPY dev_my_settings, line xxx, column xxx: "0.2" ERROR: invalid input syntax for type double precision "15.776653623" Regards, Eric Svenson 2014-12-04 11:00 GMT+01:00 Eric Svenson : > > So what if you load to the VM Postgres using the psql from the host? > > I tried that: On the host I started psql with > psql -h 192.168.2.55 -U postgres -p 5432 my_Database < myFile.sql > > I get the same error > > ERROR: invalid input syntax for type double precision "0.2" > KONTEXT: COPY dev_my_settings, line xxx, column xxx: "0.2" > > ERROR: invalid input syntax for type double precision "15.776653623" > > Regards, > Eric Svenson > > 2014-12-03 16:24 GMT+01:00 Adrian Klaver : > >> On 12/03/2014 12:23 AM, Eric Svenson wrote: >> >>> >>> Hi Adrian, >>> >>> thank you for your support. Here are the informations you requested >>> >>> >>> I have already done that and found something strange: >>> >>> On the PC where the backup was done with pg_dump, all locale >>> settings of >>> Postgres were English/United States. (LC_COLLATE, LC_CTYPE, >>> LC_MONETARY, >>> LC_NUMERIC in postgresql.conf) >>> >>> >>> > OS and OS version? >>> >>> Windows Server 2012R2 >>> >>> > Postgres version? >>> >>> Version 9.2.1 (same version used on all machines) >>> >>> > What was the pg_dump command used? >>> >>> pg_dump -p 6789 -EUTF8 -f myFile.sql my_Database >>> >>> >> > How was it loaded via psql? >>> >>> psql -p 6789 -U postgres my_Database < myFile.sql >>> >>> > Was the psql on the same machine and from the same version of >>> Postgres? >>> >>> Same version, but on different machines. >>> >>> >>> On the second PC (Virtual Machine) I had the SAME settings in >>> postgresql.conf (German_Germany) >>> >>> >>> > OS and OS version? >>> >>> Windows 7 Ultimate SP 1 >>> >>> > Postgres version? >>> >>> Version 9.2.1 (same version used on all machines) >>> >>> > How was it loaded via psql? >>> >>> psql -p 6789 -U postgres my_Database < myFile.sql >>> >>> > Did you use the psql on the VM or did you use the psql on the host? >>> >>> psql on the VM >>> >>> >> So what if you load to the VM Postgres using the psql from the host? >> >> >>> Again, thank you for the support. >>> >> >> As Tom said LC_NUMERIC is set to C in main.c: >> >> /* >> * We keep these set to "C" always, except transiently in >> pg_locale.c; see >> * that file for explanations. >> */ >> >> The transiently part intrigued me so I went to pg_locale.c and there was >> a big: >> >> * !!! NOW HEAR THIS !!! >> >> with regards to resetting locales. Honestly the explanation is beyond my >> expertise. I offer it only as a starting point for those that can >> understand it. >> >> >> >>> Regards, >>> Eric Svenson >>> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > >
Re: Fwd: [GENERAL] Problem with pg_dump and decimal mark
On 12/03/2014 12:23 AM, Eric Svenson wrote: Hi Adrian, thank you for your support. Here are the informations you requested I have already done that and found something strange: On the PC where the backup was done with pg_dump, all locale settings of Postgres were English/United States. (LC_COLLATE, LC_CTYPE, LC_MONETARY, LC_NUMERIC in postgresql.conf) > OS and OS version? Windows Server 2012R2 > Postgres version? Version 9.2.1 (same version used on all machines) > What was the pg_dump command used? pg_dump -p 6789 -EUTF8 -f myFile.sql my_Database > How was it loaded via psql? psql -p 6789 -U postgres my_Database < myFile.sql > Was the psql on the same machine and from the same version of Postgres? Same version, but on different machines. On the second PC (Virtual Machine) I had the SAME settings in postgresql.conf (German_Germany) > OS and OS version? Windows 7 Ultimate SP 1 > Postgres version? Version 9.2.1 (same version used on all machines) > How was it loaded via psql? psql -p 6789 -U postgres my_Database < myFile.sql > Did you use the psql on the VM or did you use the psql on the host? psql on the VM So what if you load to the VM Postgres using the psql from the host? Again, thank you for the support. As Tom said LC_NUMERIC is set to C in main.c: /* * We keep these set to "C" always, except transiently in pg_locale.c; see * that file for explanations. */ The transiently part intrigued me so I went to pg_locale.c and there was a big: * !!! NOW HEAR THIS !!! with regards to resetting locales. Honestly the explanation is beyond my expertise. I offer it only as a starting point for those that can understand it. Regards, Eric Svenson -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Problem with pg_dump and decimal mark
Hi Adrian, thank you for your support. Here are the informations you requested > I have already done that and found something strange: > > On the PC where the backup was done with pg_dump, all locale settings of > Postgres were English/United States. (LC_COLLATE, LC_CTYPE, LC_MONETARY, > LC_NUMERIC in postgresql.conf) > > OS and OS version? Windows Server 2012R2 > Postgres version? Version 9.2.1 (same version used on all machines) > What was the pg_dump command used? pg_dump -p 6789 -EUTF8 -f myFile.sql my_Database > On the first PC on which I tried to load the backup file with psql, all > locale settings if Postgres were German_Germany. Everything is ok, the > SQL file with '.' as decimal point was accepted without a problem > > OS and OS version? Windows 7 Enterprise SP 1 > Postgres version? Version 9.2.1 (same version used on all machines) > How was it loaded via psql? psql -p 6789 -U postgres my_Database < myFile.sql > Was the psql on the same machine and from the same version of Postgres? Same version, but on different machines. > On the second PC (Virtual Machine) I had the SAME settings in > postgresql.conf (German_Germany) > > OS and OS version? Windows 7 Ultimate SP 1 > Postgres version? Version 9.2.1 (same version used on all machines) > How was it loaded via psql? psql -p 6789 -U postgres my_Database < myFile.sql > Did you use the psql on the VM or did you use the psql on the host? psql on the VM > Was the Postgres/psql on the host the same as the VM? Same Version (9.2.1) > What are you using for virtualization? VM Ware Player 6.0.2 > What is host OS? Windows 7 Enterprise SP 1 (see above) Again, thank you for the support. Regards, Eric Svenson
