Обсуждение: Encoding problem using pg_dumpall
Hi,
I have a database with encoding UTF-8 installed on Windows, and I try to dump it using pg_dumpall, on the machine on which the database is installed. I get the following error message:
C:\Program Files\PostgreSQL\8.3\bin>pg_dumpall -U admint > c:\temp\dbdump.sql
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: character 0xd595 of encoding "UTF8" has no equivalent in "WIN1252"
pg_dump: The command was: COPY public.cms_history_properties (structure_id, propertydef_id, property_mapping_id, property_mapping_type, property_value, publish_tag) TO stdout;
pg_dumpall: pg_dump failed on database "opencms", exiting
it probably means that some UTF-8 characters cannot be represented in WIN1252 which is the encoding of the output file.
Is there any solution to this problem?
Thanks,
Moshe Ben Shoham.
The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you.
************************************************************************************
This footnote confirms that this email message has been scanned by
PineApp Mail-SeCure for the presence of malicious code, vandals & computer viruses.
************************************************************************************
"Moshe Ben-Shoham" <mosheb@nexperience.com> writes: > C:\Program Files\PostgreSQL\8.3\bin>pg_dumpall -U admint > > c:\temp\dbdump.sql > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: character 0xd595 of encoding > "UTF8" has no equivalent in "WIN1252" Apparently you have WIN1252 set as the default client encoding, probably via an environment variable or locale setting. Either get rid of it, or override it by including "-E UTF8" in the pg_dump command. (Hmm, actually it looks like pg_dumpall hasn't got a -E switch, which seems like an oversight. So you need to fix your locale, or else use pg_dump directly.) regards, tom lane
Tom Lane wrote: > "Moshe Ben-Shoham" <mosheb@nexperience.com> writes: >> C:\Program Files\PostgreSQL\8.3\bin>pg_dumpall -U admint > >> c:\temp\dbdump.sql >> pg_dump: SQL command failed >> pg_dump: Error message from server: ERROR: character 0xd595 of encoding >> "UTF8" has no equivalent in "WIN1252" > > Apparently you have WIN1252 set as the default client encoding, probably > via an environment variable or locale setting. Either get rid of it, > or override it by including "-E UTF8" in the pg_dump command. > > (Hmm, actually it looks like pg_dumpall hasn't got a -E switch, > which seems like an oversight. So you need to fix your locale, > or else use pg_dump directly.) IIRC, you can't set the windows console to be UTF8. Thus, your option is to switch to using pg_dump and use -E UTF8 on that one. //Magnus
Magnus Hagander <magnus@hagander.net> writes: > Tom Lane wrote: >> (Hmm, actually it looks like pg_dumpall hasn't got a -E switch, >> which seems like an oversight. So you need to fix your locale, >> or else use pg_dump directly.) > IIRC, you can't set the windows console to be UTF8. Ugh. That seems to raise the priority of having a -E switch quite a lot. I'm surprised no one has complained of this before. I think it should be possible to work around it by setting PGCLIENTENCODING in the environment, but I dunno how to do that on Windows. regards, tom lane
Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: >> Tom Lane wrote: >>> (Hmm, actually it looks like pg_dumpall hasn't got a -E switch, >>> which seems like an oversight. So you need to fix your locale, >>> or else use pg_dump directly.) > >> IIRC, you can't set the windows console to be UTF8. > > Ugh. That seems to raise the priority of having a -E switch quite > a lot. I'm surprised no one has complained of this before. Most people use pg_dump. I know I recommend everbody to use pg_dump to dump the database, because you can use -Fc. Then just use pg_dumpall to dump the globals, and they normally don't have any non-ascii in them. > I think it should be possible to work around it by setting > PGCLIENTENCODING in the environment, but I dunno how to do that > on Windows. That's a simple set PGCLIENTENCODING=UTF8 //Magnus
On Donnerstag 29 Januar 2009 Magnus Hagander wrote: > Most people use pg_dump. I know I recommend everbody to use pg_dump > to dump the database, because you can use -Fc. Then just use > pg_dumpall to dump the globals, and they normally don't have any > non-ascii in them. Why couldn't pg_dumpall get the same behaviour as pg_dump? It could get that -Fc, and couldn't it be implementet as "call pg_dump for each db and once for the system"? Why is it that different at all? mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
Michael Monnerie <michael.monnerie@is.it-management.at> writes: > Why couldn't pg_dumpall get the same behaviour as pg_dump? It could get > that -Fc, and couldn't it be implementet as "call pg_dump for each db > and once for the system"? Why is it that different at all? The -Fc (and -Ft) formats are only designed to hold the contents of a single database; and pg_restore only knows how to restore into a single database. If you feel like fixing that, step right up. regards, tom lane
On Freitag 30 Januar 2009 Tom Lane wrote: > The -Fc (and -Ft) formats are only designed to hold the contents of a > single database; and pg_restore only knows how to restore into a > single database. > > If you feel like fixing that, step right up. Sorry, didn't want to step on toes here. I know that sometimes if you perfectly know a system you get blind over alternatives, and wanted to throw in an idea. The format of pg_dumpall could be a tar of single pg_dumps as well, making pg_dumpall just call pg_dump for each db, putting that output into the tar output from pg_dumpall. But again, just an idea, I don't mind really. I'm just a sysadmin who wants things as easy as possible. Maybe fixing pg_dumpall to include that -E is easier. mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
On Sat, Jan 31, 2009 at 12:23 AM, Michael Monnerie <michael.monnerie@is.it-management.at> wrote: > On Freitag 30 Januar 2009 Tom Lane wrote: >> The -Fc (and -Ft) formats are only designed to hold the contents of a >> single database; and pg_restore only knows how to restore into a >> single database. >> >> If you feel like fixing that, step right up. > > Sorry, didn't want to step on toes here. I know that sometimes if you > perfectly know a system you get blind over alternatives, and wanted to > throw in an idea. > > The format of pg_dumpall could be a tar of single pg_dumps as well, > making pg_dumpall just call pg_dump for each db, putting that output > into the tar output from pg_dumpall. But again, just an idea, I don't > mind really. I'm just a sysadmin who wants things as easy as possible. > Maybe fixing pg_dumpall to include that -E is easier. > You should be able to knock up a simple script in bash, perl or python to do what you want. Sort of like: Use 'psql' to get a list of the databases Sanitize the list. Loop through the list doing backup. At the end zip them all. With a little bit of trickery you should be able to zip them as you go. With regards being too close to the system, while this is true, it also possible to be distant from the system's entrails and not don't realize the implications and difficulties of your brilliant idea for enhancement! Cheers, Cliff
Cliff Pratt написа: [...] > You should be able to knock up a simple script in bash, perl or python > to do what you want. > > Sort of like: > > Use 'psql' to get a list of the databases > Sanitize the list. > Loop through the list doing backup. > At the end zip them all. > > With a little bit of trickery you should be able to zip them as you go. [...] #!/bin/bash PSQL=/usr/bin/psql PG_DUMP=/usr/bin/pg_dump PG_DUMPALL=/usr/bin/pg_dumpall if [ $# -eq 0 ] ; then BACKUPDIR=/home/db_backup/dumps elif [ $# -ne 1 ] ; then echo -e "Usage: $0 [directory]\n" exit 1 else BACKUPDIR=$1 fi if [ ! -d $BACKUPDIR ] ; then if ! mkdir $BACKUPDIR ; then echo -e "Cannot create backup directory: $BACKUPDIR\n" exit 2 fi fi echo Starting at `date "+%Y-%m-%d %H:%M:%S"` echo -e -n "Dumping globals...\t\t" $PG_DUMPALL -U postgres --globals-only > $BACKUPDIR/pg_globals.sql echo "done." for db in `$PSQL -U postgres -d template1 -t -c "SELECT datname FROM pg_catalog.pg_database WHERE datname "\!"~ 'template(0|1)';"` do echo -n -e "Dumping database $db...\t\t" $PG_DUMP -U postgres --format=c $db > $BACKUPDIR/$db.dump echo "done." done echo Ended at `date "+%Y-%m-%d %H:%M:%S"` [...] -- Milen A. Radev