Re: Recommended approach for upgrading DBs with nonmatching encodings

Martin Pitt <martin@piware.de> writes:
> Tom Lane [2008-03-30 16:43 -0400]:
>> Huh?  Please provide a test case.

> Ah, I got it. This fails:

>   pg_dump -Fc -E UTF8 -p 5432  latin1test | pg_restore -p 5433 -d template1 -C

> (5432 is 8.1, 5433 is 8.3, both with locale ru_RU.UTF-8;
> createdb -E latin1 latin1test)

Yeah.  This will try to create the new latin1test with all the same
properties it had before, including encoding, and 8.3 intentionally
rejects that.  Your 8.1 setup is pretty broken too (it will misbehave
in various ways because of the encoding mismatch), but 8.1 fails to
realize that.

> In that case I do not even need to specify -E. Seems that
> pg_dump/pg_restore are clever enough to detect encodings and necessary
> conversions.

Yeah, there's usually little value in -E unless you're planning to
do something else with the dump than just feed it to pg_restore.
(If you wanted to export to some other DBMS, for example, it could
be useful.)  In particular -E has entirely zip bearing on what
database encoding will be assigned during restore.

            regards, tom lane