[Sqlgrey-users] Moving postgres from unicode to ascii

[Andrew D. <and...@gm...>]

Hello SQLgrey,

  I ran into problems using a postgres database with unicode encoding,
since sqlgrey isn't designed for unicode.  Pesky Internet standards.
Anyway, this is how I migrated it, for the good of the archives:

1. Create a new database with SQL_ASCII encoding:
andrew@tango:~> createdb -E SQL_ASCII -O sqlgrey sqlgreyascii

2. View that the database really exists:
andrew@tango:~> psql -l
          List of databases
     Name     |  Owner   | Encoding
--------------+----------+-----------
 sqlgrey      | sqlgrey  | UNICODE
 sqlgreyascii | sqlgrey  | SQL_ASCII
 template0    | postgres | UNICODE
 template1    | postgres | UNICODE

3. Dump the existing sqlgrey database:
andrew@tango:~> pg_dump sqlgrey > sqlgrey.20061228

4. Restore it into the new database:
andrew@tango:~> psql sqlgreyascii < sqlgrey.20061228
(database SETs, etc.)

5. Edit /etc/sqlgrey/sqlgrey.conf, adding "db_name = sqlgreyascii" (I
had used the default name previously, so I just set the new name.)

6. Restart sqlgrey:
/etc/init.d/sqlgrey restart

I have a low volume system (a few thousand emails a day) so I left
SQLgrey running during the db work. It's running just fine now, and I
don't expect to get the db reset emails anymore.

-- 
Best regards,
 Andrew