Re: UTF8 frustrations

On 9/4/07, jesse(dot)waters(at)gmail(dot)com <jesse(dot)waters(at)gmail(dot)com> wrote:
> Trying to do pg_restore from one UTF8 encoded db to another UTF8 encoded db
> DB_source:
> Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
> psql 8.2.4
>
> DB Destination:
> Debian GNU/Linux 4.0
> psql 8.1.9
>
> I've tried:
> pg_dump from the source box & from destination box
>
> from destination server
> pg_dump -i -h source_server db > db.8.1.9.sql

Are you issuing the dump and psql / restore command on the same machine?
As previously mentioned, dumping from newer to older is not supported.
pg_dump from 8.1 might not understand the data structures it finds in
an 8.2 db, and pg_dump from 8.2 might create a dump that 8.1 doesn't
support. Note that going the other way you are encouraged to use
pg_dump from 8.2 to dump the 8.1 database for importing to 8.2.

However, In this case I'm guessing that the problem is that you've got
different client encodings on each end. i.e. you're dumping with one
encoding setting and restoring with another. Note that pgsql
autoconverts from the server's encoding to the client's encoding at
the request of the client. so, you'll need to check your client
encoding from psql on the source and target machines to see if they
match.

show client_encoding ;

will tell you what your client encoding is.