Ok. Here is what it is.
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).
NEW DB:
Just installed fresh. There's nothing in it. I can basically wipe all
data out.
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.
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!
(b) Once that data is utf8-ed, how can I bring it home and have a
mirror of the db.
Thanks.