Re: Dumping/Restoring with constraints?

On Wed, Aug 27, 2008 at 11:54:27PM +0800, Phoenix Kiula wrote:

> OLD DB:
> Since 2002. May contain non-UTF8 data. But I thought I had modified it
> all when I changed it to UTF-8 pgsql database (it was originally
> Mysql). The database works very well on a very busy website.
> Everything on that website is now UTF-8. I wish to mirror this
> database locally on my home machine. Server is linux with 6GB ram and
> pgsql 8.2.3, home machine is osx with imac of 4GM ram and 8.3.3 (new
> pg).

Somehow, you're getting non-UTF-8 chars in there, either because your
conversion didn't work, or because there's still bugs in your
application that send non-UTF-8 data. If your database encoding is
not UTF-8, then it is possible to get non-UTF-8 data anyway. That's
why people asked about the database encoding. SQL_ASCII, please note,
does not enforce that you're in the bottom 7 bits: it'll take anything
you put in there. So if someone put (say) ISO 8859-1 in, you'll get
in trouble.

> WHAT I AM DOING:
> 1. On the server, I am executing "pg_dumpall > mydb.out". Simple.
> 2. FTP that mydb.out file to local home machine.
> 3. Here, locally, I do this: "psql -h localhost -d postgres -U
> postgres -f mydb.out". This is what gives me the error.

Right. So the file includes data that doesn't match the encoding of
the target database. AFAIR -- and my memory's not what it used to be,
so check the release notes -- the UTF-8 checking in 8.2 was as good as
in 8.3. One good test of this would be to install 8.2 on your home
machine, and try restoring that too. If it works, then we know more.

> MY QUESTION:
> What can I do to:
> (a) Make sure all data on pgsql on server (OLD DB) is utf-8. Is there
> a command I can execute to convert all data? I thought I had converted
> it all to utf-8 using PHP sometime ago, which went through each and
> every row and column!

The usual advice is to use iconv. Your Mac should have it installed.

> (b) Once that data is utf8-ed, how can I bring it home and have a
> mirror of the db.

If you run iconv on the data dump before you load it, then it should
work. This is not a trivial job, however.

A

--
Andrew Sullivan
ajs(at)commandprompt(dot)com
+1 503 667 4564 x104
http://www.commandprompt.com/