#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    9
    Rep Power
    0

    Encoding snafu, please help!


    We just moved our database from one server running Postgres 8.4 to a new server running 9.1. The 8.4 database had an encoding of LATIN1, and the 9.1 has UTF-8.
    To move the database over, I simply piped the output from pg_dumpall on the 8.4 machine into psql on the new machine. However, I think I created big problems because I didn't run it through iconv.
    Now I have a UTF-8 database with lots of badly-encoded strings.
    How can I fix this? I'd like to make the database LATIN1 again, since this is the encoding used by our whole system.

    Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by jehsom
    I'd like to make the database LATIN1 again, since this is the encoding used by our whole system
    You'll have to drop and re-create the database. There is no option to change the characterset for a database.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by shammat
    You'll have to drop and re-create the database. There is no option to change the characterset for a database.
    I understand that, that's fine, but I want to make sure that when I do that, my data comes out correctly. The problem is that when I restored the dump from the LATIN1 database, I had no client_encoding set in psql (rather it was SQL_ASCII), connected to a UTF8 database, restoring LATIN1 data, so I'm not sure how to get the data out in a way that will make sense.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    You need to either make sure the client_encoding is set correctly for both tools (pg_dumpall and psql).

    I think when piping the data it's quite hard to get the client encoding correct for each step. You are probably better off using pg_dumpall and psql separately (or pg_dump/pg_restore using the custom format, maybe that's more stable regarding different client encodings)

IMN logo majestic logo threadwatch logo seochat tools logo