Changing Character Sets

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general