Re: Recommended approach for upgrading DBs with nonmatching encodings

Hi Tom,

Tom Lane [2008-03-30 17:15 -0400]:
> > pg_dump -Fc -E UTF8 -p 5432 latin1test | pg_restore -p 5433 -d template1 -C
>
> Yeah. This will try to create the new latin1test with all the same
> properties it had before, including encoding

I see, so it's intended to behave like this. man pg_dump is a little
unclear on this.

>, and 8.3 intentionally rejects that.

Rightly so.

> 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.

Right, I know. It is the reproducer for the upgrade problems many
people have (see quoted bug reports in initial mail), and what I now
use in the postgresql-common test suite.

> 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.

Thanks for the clarification. So it seems createdb+pg_restore is the
way to go, and pg_restore -C does not DTRT for my purpose (correcting
DB encodings on upgrades).

BTW, many people seem to run the server under C and use different
encodings in their DBs (latin, UTF8). Shouldn't that cause similar
problems with collation, data type checking (ischar(), etc.)? What do
you recommend should the upgrade script do if it encounters an 8.[12]
server running under C?

Thanks,

Martin

--
Martin Pitt | http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)