Thread: Changing Character Sets
Is there any way to change the character set of a database and its tables? I did a pg_dumpall to upgrade from Postgres 8.4 to Postgres 9.2, and all the tables came back as UTF-8, and now Bacula is complaining that it wants SQL_ASCII encoding for everything. I don't see a flag on pg_dumpall or pg_restore to set which character encoding I'd like, and Google has failed me. -- Tim Gustafson tjg@ucsc.edu 831-459-5354 Baskin Engineering, Room 313A
On Tue, Feb 12, 2013 at 5:34 PM, Tim Gustafson <tjg@ucsc.edu> wrote: > Is there any way to change the character set of a database and its tables? > > I did a pg_dumpall to upgrade from Postgres 8.4 to Postgres 9.2, and > all the tables came back as UTF-8, and now Bacula is complaining that > it wants SQL_ASCII encoding for everything. I don't see a flag on > pg_dumpall or pg_restore to set which character encoding I'd like, and > Google has failed me. What you're looking for is to change the encoding, right, and not the locale? You can't change the encoding of a database, but you can use a different one when you create it - this can be specified in the CREATE DATABASE statement. You can also ask pg_dump to use a specific encoding using the -E parameter. You can't do it on pg_dumpall, but you can do it if you use pg_dump. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
> What you're looking for is to change the encoding, right, and not the locale? Correct. > You can't change the encoding of a database, but you can use a > different one when you create it - this can be specified in the CREATE > DATABASE statement. That's what I wound up doing. > You can also ask pg_dump to use a specific encoding using the -E > parameter. You can't do it on pg_dumpall, but you can do it if you use > pg_dump. That's good to know. I'm curious why a pg_dumpall from 8.4 followed by a restore in 9.2 caused the character sets to change at all. Was there some change in the default character sets between 8.4 and 9.2? -- Tim Gustafson tjg@ucsc.edu 831-459-5354 Baskin Engineering, Room 313A
On Tue, Feb 12, 2013 at 6:02 PM, Tim Gustafson <tjg@ucsc.edu> wrote: >> What you're looking for is to change the encoding, right, and not the locale? > > Correct. > >> You can't change the encoding of a database, but you can use a >> different one when you create it - this can be specified in the CREATE >> DATABASE statement. > > That's what I wound up doing. > >> You can also ask pg_dump to use a specific encoding using the -E >> parameter. You can't do it on pg_dumpall, but you can do it if you use >> pg_dump. > > That's good to know. > > I'm curious why a pg_dumpall from 8.4 followed by a restore in 9.2 > caused the character sets to change at all. Was there some change in > the default character sets between 8.4 and 9.2? That depends on your platform, but the answer is likely "yes". Or you had manually specified SQL_ASCII in the 8.4 method. SQL_ASCII basically means "don't care at all about encodings". -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Tim Gustafson <tjg@ucsc.edu> writes:
> I'm curious why a pg_dumpall from 8.4 followed by a restore in 9.2
> caused the character sets to change at all. Was there some change in
> the default character sets between 8.4 and 9.2?
I was wondering that too. You did use pg_dumpall, and not individual
pg_dumps per database? In the latter case it's not hard to imagine
making a mistake, but pg_dumpall has been careful about database
properties for a long time.
regards, tom lane
Tim Gustafson <tjg@ucsc.edu> wrote: > Is there any way to change the character set of a database and its tables? > > I did a pg_dumpall to upgrade from Postgres 8.4 to Postgres 9.2, and > all the tables came back as UTF-8, and now Bacula is complaining that > it wants SQL_ASCII encoding for everything. I don't see a flag on > pg_dumpall or pg_restore to set which character encoding I'd like, and > Google has failed me. use pg_dump instead of pg_dumpall, because of the lack of a -E - Swich for pg_dumpall. Found http://postgresql.1045698.n5.nabble.com/Encoding-problem-using-pg-dumpall-td1911202.html it's from 2009, money quote: ,----[ ] | (Hmm, actually it looks like pg_dumpall hasn't got a -E switch, | which seems like an oversight. So you need to fix your locale, | or else use pg_dump directly.) | | regards, tom lane `---- Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°