Обсуждение: upgrade from 7.4 to 8.3
Hi,
I've just upgrade postgresql from 7.4 to 8.3 on a machine running debian.
And i ran in trouble. I've searched the internet but didn't find an
answer to my solution.
Is it possible to upgrade from 7.4 to 8.3 at once?
I upgraded as follows:
when 7.4 was installed i dumped the data with:
su postgres -c "pg_dumpall --create" > out
when 8.3 was installed i imported the data with:
su postgres -c "psql < out"
Databases in 7.4 were encoded as utf-8. Now when importing postgresql
gives the following error:
ERROR: encoding UTF8 does not match server's locale en_US
DETAIL: The server's LC_CTYPE setting requires encoding LATIN1.
When creating a database from pgadmin (logged in as postgres) with:
CREATE DATABASE "db" WITH OWNER = postgres TEMPLATE = template0 ENCODING
= 'UNICODE'
postgres replies with:
ERROR: encoding UTF8 does not match server's locale en_US
DETAIL: The server's LC_CTYPE setting requires encoding LATIN1.
********** Error **********
ERROR: encoding UTF8 does not match server's locale en_US
SQL state: XX000
Detail: The server's LC_CTYPE setting requires encoding LATIN1.
Can anybody tell me what's wrong?
--
Best regards,
Marc
Marc Cuypers <m.cuypers@mgvd.be> writes:
> Databases in 7.4 were encoded as utf-8. Now when importing postgresql
> gives the following error:
> ERROR: encoding UTF8 does not match server's locale en_US
> DETAIL: The server's LC_CTYPE setting requires encoding LATIN1.
It looks like you need to run initdb with LANG set to en_US.utf8,
not just en_US.
regards, tom lane
On Tue, Mar 10, 2009 at 05:02:59PM +0100, Marc Cuypers wrote: > Is it possible to upgrade from 7.4 to 8.3 at once? > > I upgraded as follows: > when 7.4 was installed i dumped the data with: > su postgres -c "pg_dumpall --create" > out Just a side note in case you didn't know: It is usually recommended to use the later-version (8.3 here, that is) pg_dump(all) for best results. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Thanks Tom, Tom Lane schreef: > Marc Cuypers <m.cuypers@mgvd.be> writes: >> Databases in 7.4 were encoded as utf-8. Now when importing postgresql >> gives the following error: > >> ERROR: encoding UTF8 does not match server's locale en_US >> DETAIL: The server's LC_CTYPE setting requires encoding LATIN1. > > It looks like you need to run initdb with LANG set to en_US.utf8, > not just en_US. > I executed the command: su postgres -c "/usr/lib/postgresql/8.3/bin/initdb -E UNICODE --locale nl_BE.utf8 -D /home/pgsql/8" And now i can use utf-8. Only... One database was in LATIN9. When creating this database i got the same error. Command: CREATE DATABASE "hardsoft" WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'LATIN9'; Error: ERROR: encoding LATIN9 does not match server's locale nl_BE.utf8 SQL state: XX000 Detail: The server's LC_CTYPE setting requires encoding UTF8. Can i only use nl_BE and UTF-8 now? Why can't i use LATIN9 anymore? Is bacula 8.3 stricter in this respect to 7.4? -- best regards, Marc
On Wed, Mar 11, 2009 at 19:01, Marc Cuypers <m.cuypers@mgvd.be> wrote: > Thanks Tom, > > Only... > > One database was in LATIN9. When creating this database i got the same > error. > > Command: > CREATE DATABASE "hardsoft" WITH OWNER = postgres TEMPLATE = template0 > ENCODING = 'LATIN9'; > > Error: > ERROR: encoding LATIN9 does not match server's locale nl_BE.utf8 > SQL state: XX000 > Detail: The server's LC_CTYPE setting requires encoding UTF8. > > Can i only use nl_BE and UTF-8 now? > Why can't i use LATIN9 anymore? > Is bacula 8.3 stricter in this respect to 7.4? yes, you can no longer mix encodings in utf8. That has something to do with the sort (or so). Anyway, for the one DB that you have in Latin1, dump this extra, convert the whole file to utf8 with iconv, replace the Latin1 strings in it to UTF8 and your good to go. -- [ Clemens Schwaighofer -----=====:::::~ ] [ IT Engineer/Manager ] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703 Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] Advertising Age Global Agency of the Year 2008 Adweek Global Agency of the Year 2008 This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is strictly prohibited. All contents are the copyright property of TBWA Worldwide, its agencies or a client of such agencies. If you are not the intended recipient, you are nevertheless bound to respect the worldwide legal rights of TBWA Worldwide, its agencies and its clients. We require that unintended recipients delete the e-mail and destroy all electronic copies in their system, retaining no copies in any media.If you have received this e-mail in error, please immediately notify us via e-mail to disclaimer@tbwaworld.com. We appreciate your cooperation. We make no warranties as to the accuracy or completeness of this e-mail and accept no liability for its content or use. Any opinions expressed in this e-mail are those of the author and do not necessarily reflect the opinions of TBWA Worldwide or any of its agencies or affiliates.
Marc Cuypers <m.cuypers@mgvd.be> writes: > Error: > ERROR: encoding LATIN9 does not match server's locale nl_BE.utf8 > SQL state: XX000 > Detail: The server's LC_CTYPE setting requires encoding UTF8. > > Can i only use nl_BE and UTF-8 now? > Why can't i use LATIN9 anymore? > Is bacula 8.3 stricter in this respect to 7.4? 8.3 is stricter about checking that the configuration makes sense. But even under 7.4 you would have had problems, you just wouldn't have been forewarned so soon. You would still only be able to use nl_BE.utf8 collation but you would have been allowed to tell the server your data was encoded with latin9. So the collation results would have been nonsensical. Ie, comparisons like < and > would have given incorrect results. If this database is still under development and your schedule allows one option might be use 8.4dev from CVS. It should be released sometime in the next 3-6 months and will allow you to have a different encoding and locale for each database. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Thanks Clemens, Schwaighofer Clemens schreef: > On Wed, Mar 11, 2009 at 19:01, Marc Cuypers <m.cuypers@mgvd.be> wrote: >> Thanks Tom, > >> Only... >> >> One database was in LATIN9. When creating this database i got the same >> error. >> >> Command: >> CREATE DATABASE "hardsoft" WITH OWNER = postgres TEMPLATE = template0 >> ENCODING = 'LATIN9'; >> >> Error: >> ERROR: encoding LATIN9 does not match server's locale nl_BE.utf8 >> SQL state: XX000 >> Detail: The server's LC_CTYPE setting requires encoding UTF8. >> >> Can i only use nl_BE and UTF-8 now? >> Why can't i use LATIN9 anymore? >> Is bacula 8.3 stricter in this respect to 7.4? > > yes, you can no longer mix encodings in utf8. That has something to do > with the sort (or so). > > Anyway, for the one DB that you have in Latin1, dump this extra, > convert the whole file to utf8 with iconv, replace the Latin1 strings > in it to UTF8 and your good to go. That's what i'll do. Thanks again. -- Marc
Gregory Stark schreef: > Marc Cuypers <m.cuypers@mgvd.be> writes: > >> Error: >> ERROR: encoding LATIN9 does not match server's locale nl_BE.utf8 >> SQL state: XX000 >> Detail: The server's LC_CTYPE setting requires encoding UTF8. >> >> Can i only use nl_BE and UTF-8 now? >> Why can't i use LATIN9 anymore? >> Is bacula 8.3 stricter in this respect to 7.4? > > 8.3 is stricter about checking that the configuration makes sense. But even > under 7.4 you would have had problems, you just wouldn't have been forewarned > so soon. You would still only be able to use nl_BE.utf8 collation but you > would have been allowed to tell the server your data was encoded with latin9. > So the collation results would have been nonsensical. Ie, comparisons like < > and > would have given incorrect results. > > If this database is still under development and your schedule allows one > option might be use 8.4dev from CVS. It should be released sometime in the > next 3-6 months and will allow you to have a different encoding and locale for > each database. I'd like to stay with the standard version of debian. So 8.4 is not an option. But i'll convert the database in 7.4 to UTF-8. So the problem will be solved. -- Marc
Marc Cuypers <m.cuypers@mgvd.be> writes:
> Can i only use nl_BE and UTF-8 now?
> Why can't i use LATIN9 anymore?
The server-side encoding has to be compatible with the locale.
(7.4 didn't really *work* in this situation, as I'm surprised
you failed to notice.)
What you can do is keep the database encoding utf8, and do
ALTER DATABASE SET client_encoding = latin9
so that it looks like latin9 from the client side.
regards, tom lane