Thread: change encoding

Hello,

I have installed a new postgresql server and I need to recover an old database on it.
The database I have to recover has LATIN2 as encoding and when i try to restore it into the new one I get error messages due to encoding.
I have read that from pgsql8-3 is not possible to have different encoding and since by default the encoding for postgres is UTF8 my doubt is how can I restore the old database with no errors.

Thanks in advance.

On Tue, May 13, 2008 at 09:00:49AM +0200, J. Manuel Velasco - UBILIBET wrote:
> Hello,
>
> I have installed a new postgresql server and I need to recover an old
> database on it.
> The database I have to recover has LATIN2 as encoding and when i try to
> restore it into the new one I get error messages due to encoding.
> I have read that from pgsql8-3 is not possible to have different
> encoding and since by default the encoding for postgres is UTF8 my doubt
> is how can I restore the old database with no errors.

The encoding must match the collation, that's all. You don't say what
you are recovering, but pg_dump emits a "SET client_encoding=latin2",
right? In which case it should just work.

If it's something else you need to show us exactly what the error
message is.

If you want everything connecting to the database to think it's latin2,
do:

ALTER DATABASE foo SET client_encoding=latin2;

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Hello,

Thanks for your quick replay, but I had other thing to do and I haven't could check your hint before.

I started a new postgresql-8-3 installation. Set it up, ok

I have the idea to create the database and the role and then load the pg_dump result from the other database. The old databse has encoding LATIN2 so I try to create with this encoding and I get the message(*):
ERROR: la codificacion LATIN2 no coincide con la configuracion regional del servidor es_es.UTF8
DETAIL: la configuracion regional LC_TYPE del servidor requiere la codificacion UTF8

The command I try is:
postgres=# create database gestio_sl with template=template0 owner webadmin encoding='latin2';

(*)That means more or less:
LATIN2 doesn't match with the regional configuration of the server
regional configuration requires UTF8 encoding

So, my question is how can i set the regional configuration of the server to LATIN2

Thanks in advance.

Good luck!

Martijn van Oosterhout escribió:

On Tue, May 13, 2008 at 09:00:49AM +0200, J. Manuel Velasco - UBILIBET wrote:  

Hello,

I have installed a new postgresql server and I need to recover an old 
database on it.
The database I have to recover has LATIN2 as encoding and when i try to 
restore it into the new one I get error messages due to encoding.
I have read that from pgsql8-3 is not possible to have different 
encoding and since by default the encoding for postgres is UTF8 my doubt 
is how can I restore the old database with no errors.    

The encoding must match the collation, that's all. You don't say what
you are recovering, but pg_dump emits a "SET client_encoding=latin2",
right? In which case it should just work.

If it's something else you need to show us exactly what the error
message is.

If you want everything connecting to the database to think it's latin2,
do:

ALTER DATABASE foo SET client_encoding=latin2;

Have a nice day,  

On Tue, May 13, 2008 at 05:37:27PM +0200, J. Manuel Velasco - UBILIBET wrote:
> I have the idea to create the database and the role and then load the
> pg_dump result from the other database. The old databse has encoding
> LATIN2 so I try to create with this encoding and I get the message(*):
> ERROR: la codificacion LATIN2 no coincide con la configuracion regional
> del servidor es_es.UTF8
> DETAIL: la configuracion regional LC_TYPE del servidor requiere la
> codificacion UTF8
>
> The command I try is:
> postgres=# create database gestio_sl with template=template0 owner
> webadmin encoding='latin2';

My point was, the database does not have to be in LATIN2 (so remove
the encoding=latin2 part). If you leave the database in UTF-8 but do
the command I suggested

> >ALTER DATABASE foo SET client_encoding=latin2;

Then everything will work like it used to and you will be able to load
your dump without problems.

If you really truly want to change the encoding you have to
reinitialise the whole cluster. Delete the directory and reinitdb.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Hi Martijn,

Thank you very much for reply. It has worked perfectly.

Have a good life,

All beings be happy

Martijn van Oosterhout escribió:

On Tue, May 13, 2008 at 05:37:27PM +0200, J. Manuel Velasco - UBILIBET wrote:  

I have the idea to create the database and the role and then load the 
pg_dump result from the other database. The old databse has encoding 
LATIN2 so I try to create with this encoding and I get the message(*):
ERROR: la codificacion LATIN2 no coincide con la configuracion regional 
del servidor es_es.UTF8
DETAIL: la configuracion regional LC_TYPE del servidor requiere la 
codificacion UTF8

The command I try is:
postgres=# create database gestio_sl with template=template0 owner 
webadmin encoding='latin2';    

My point was, the database does not have to be in LATIN2 (so remove
the encoding=latin2 part). If you leave the database in UTF-8 but do
the command I suggested
  

ALTER DATABASE foo SET client_encoding=latin2;      

Then everything will work like it used to and you will be able to load
your dump without problems.

If you really truly want to change the encoding you have to
reinitialise the whole cluster. Delete the directory and reinitdb.

Have a nice day,