Обсуждение: Encoding problem using pg_dumpall

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