Thread: PostgreSQL upgrade -> fails to start server
Hi, After a very recent postgresql update, the postmaster failed to start because of this: | PANIC: The database cluster was initialized with LC_CTYPE | 'nl_NL@euro.UTF-8', which is not recognized by setlocale(). | It looks like you need to initdb. Now I know nl_NL@euro.UTF-8 is an incorrect locale, I fixed that some time ago, but because postgresql kept on running, and the machine didn't reboot in the mean time, I noticed nothing when the new libc's / locales stopped 'supporting' that (faulty) locale. To make a long story short: i can't activate that locale again, therefore the postmaster won't start, and therefore I can't salvage my databases to re-create them & finish the postgresql upgrade. Now I found out the binary file data/global/pg_control matches the faulty locale, so I tried to vi that in a desperate attempt to start the server, but it failed and I had to restore the original. Is there some way that I can get to my precious databases? I fear that downgrading postgresql won't work, because the locale still isn't available, so it probably won't start either. Help? :) I'm running Debian Unstable, libc6/locales 2.3.2, postgresql 7.3.4. Thanks! Sander. -- | A conscience is what hurts when all of your other parts feel so good. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
Ok, IIRC the reason you need to initdb is because indexes use your specific locale to work. Without knowing the locale, indexes can't be used. That said, it seems to be a bit strict to refuse to start the server. In any case, there is a program pg_controldata which dumps the current locale. It may help to determine if you edit pg_control properly. Once you can start it, pg_dumpall and restart. Ofcourse, if you have backups... In any case, as a last resort you can try pgfsck (http://svana.org/kleptog/pgfsck.html). It ignores your locale and so may be able to recover some of your data. Hope this helps, On Fri, Aug 29, 2003 at 11:58:37AM +0200, Sander Smeenk wrote: > Hi, > > After a very recent postgresql update, the postmaster failed to start > because of this: > > | PANIC: The database cluster was initialized with LC_CTYPE > | 'nl_NL@euro.UTF-8', which is not recognized by setlocale(). > | It looks like you need to initdb. > > Now I know nl_NL@euro.UTF-8 is an incorrect locale, I fixed that some > time ago, but because postgresql kept on running, and the machine didn't > reboot in the mean time, I noticed nothing when the new libc's / locales > stopped 'supporting' that (faulty) locale. > > To make a long story short: i can't activate that locale again, > therefore the postmaster won't start, and therefore I can't salvage my > databases to re-create them & finish the postgresql upgrade. > > Now I found out the binary file data/global/pg_control matches the > faulty locale, so I tried to vi that in a desperate attempt to start the > server, but it failed and I had to restore the original. > > Is there some way that I can get to my precious databases? > I fear that downgrading postgresql won't work, because the locale still > isn't available, so it probably won't start either. > > Help? :) > > I'm running Debian Unstable, libc6/locales 2.3.2, postgresql 7.3.4. > > Thanks! > Sander. > -- > | A conscience is what hurts when all of your other parts feel so good. > | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
Attachment
Quoting Martijn van Oosterhout (kleptog@svana.org): > Ok, IIRC the reason you need to initdb is because indexes use your specific > locale to work. Without knowing the locale, indexes can't be used. > That said, it seems to be a bit strict to refuse to start the server. I thought so too. But then I thought maybe the data stored inside the database is affected by this locale setting too? > In any case, there is a program pg_controldata which dumps the current > locale. It may help to determine if you edit pg_control properly. It's impossible to edit pg_control by hand with something like a hex editor because it has checksums to verify it's integrity. I'd need to create a program that constructs the pg_control internal structure to change the locale stored inside. > Once you can start it, pg_dumpall and restart. > Ofcourse, if you have backups... The data is not yet lost. It's still on my disk. It's just that the postmaster won't start because of this locale-thing. To fix it I need the postmaster running, and to get the postmaster running, I need to fix it. It loops :) > In any case, as a last resort you can try pgfsck. It ignores your > locale and so may be able to recover some of your data. I downloaded pgfsck and ran it over the database. According to pgfsck my complete database is fscked-up and no data comes out of the tool's effort. That is, I have not yet tried to turn the hex strings into ascii. > Hope this helps, I guess I need to find out how to construct my own pg_control file ;) Sander. -- | If you look like your passport picture, you probably need the trip. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
Attachment
Sander Smeenk <ssm+pgsql@freshdot.net> writes:
> To make a long story short: i can't activate that locale again,
Why not? Undoing whatever you did to the locale setup seems a lot
easier task than any other option that's open to you.
I would *not* recommend hand-editing pg_control, even if you got the
CRC correct. There is a reason why we put in code to prevent on-the-fly
changes of locale: it corrupts your indexes. You might be able to get
the postmaster to start, but that wouldn't mean you could trust the
output of pg_dump.
regards, tom lane
Quoting Tom Lane (tgl@sss.pgh.pa.us): > > To make a long story short: i can't activate that locale again, > Why not? Undoing whatever you did to the locale setup seems a lot > easier task than any other option that's open to you. The nl_NL@euro.UTF-8 locale I used 'back then' is incorrect. It's not a real locale, and the newer versions of libc6 and locales that I have installed now don't support the generation of this faulty locale. I would have to figure out what release of libc6 and locales did generate this faulty locale, and then install that version, just HOPING that all the other services i'm running still function with that release of libc6. And because this situation is on my colocated machine, i'd rather not have it crash just before the weekend :) And seeing that nl_NL@euro.UTF-8 was an UTF-8 locale, and nl_NL.UTF-8 (which is correct) is an UTF-8 locale too, I thought that it wouldn't really hurt much if I tried replacing that value in pg_control. > I would *not* recommend hand-editing pg_control, even if you got the > CRC correct. It is a desperate attempt to get my data out. > There is a reason why we put in code to prevent on-the-fly > changes of locale: it corrupts your indexes. Yes, well, it also forbids me to save my data now :) And theres no --yes-i-know-this-is-stupid-but-please-let-me-do-this option. Sander. -- | Wat als je je twee keer half dood schrikt ? | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
Sander Smeenk <ssm+pgsql@freshdot.net> writes:
> Quoting Tom Lane (tgl@sss.pgh.pa.us):
>>> To make a long story short: i can't activate that locale again,
>> Why not? Undoing whatever you did to the locale setup seems a lot
>> easier task than any other option that's open to you.
> The nl_NL@euro.UTF-8 locale I used 'back then' is incorrect. It's not a
> real locale, and the newer versions of libc6 and locales that I have
> installed now don't support the generation of this faulty locale.
If you believe that your new locale is functionally equivalent to the
old (which you'd better, if you're going to risk starting the postmaster
under it), why don't you just make a temporary symlink in
/usr/lib/locale/ ?
regards, tom lane
Quoting Tom Lane (tgl@sss.pgh.pa.us): > > The nl_NL@euro.UTF-8 locale I used 'back then' is incorrect. It's not a > > real locale, and the newer versions of libc6 and locales that I have > > installed now don't support the generation of this faulty locale. > If you believe that your new locale is functionally equivalent to the > old (which you'd better, if you're going to risk starting the postmaster > under it), why don't you just make a temporary symlink in > /usr/lib/locale/ ? I think this locale setup is a bit different with Debian installations. /usr/lib/locale/ holds no files except for 'locale-archive' which, according to file, is a PDP11 executable :P I tried specifying 'nl_NL@euro.UTF-8 nl_NL.UTF-8' in locale.alias, but to no extent. It still won't start. Thanks, Sander -- | Junk is something you've kept for years and throw away three weeks | before you need it.
In article <20030829095837.GA24442@freshdot.net>, Sander Smeenk <ssm+pgsql@freshdot.net> wrote: >Hi, > >After a very recent postgresql update, the postmaster failed to start >because of this: > >| PANIC: The database cluster was initialized with LC_CTYPE >| 'nl_NL@euro.UTF-8', which is not recognized by setlocale(). >| It looks like you need to initdb. > >Is there some way that I can get to my precious databases? You have the source. Find where it prints this, make it not panic but continue, recompile, run that server, run psql, drop the indexes, dump the database. Mike. -- RAND USR 16514
On Fri, Aug 29, 2003 at 06:17:43PM +0200, Sander Smeenk wrote: > Quoting Tom Lane (tgl@sss.pgh.pa.us): > > > > The nl_NL@euro.UTF-8 locale I used 'back then' is incorrect. It's not a > > > real locale, and the newer versions of libc6 and locales that I have > > > installed now don't support the generation of this faulty locale. > > If you believe that your new locale is functionally equivalent to the > > old (which you'd better, if you're going to risk starting the postmaster > > under it), why don't you just make a temporary symlink in > > /usr/lib/locale/ ? > > I think this locale setup is a bit different with Debian installations. > /usr/lib/locale/ holds no files except for 'locale-archive' which, > according to file, is a PDP11 executable :P > > I tried specifying 'nl_NL@euro.UTF-8 nl_NL.UTF-8' in locale.alias, but > to no extent. It still won't start. The locales in Debian are stored in either: /usr/lib/locale/en_US /usr/share/i18n/locales/en_US pg_dump/restore recommended after. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
Attachment
On Fri, Aug 29, 2003 at 10:09:15AM -0400, Tom Lane wrote: > Sander Smeenk <ssm+pgsql@freshdot.net> writes: > > To make a long story short: i can't activate that locale again, > > Why not? Undoing whatever you did to the locale setup seems a lot > easier task than any other option that's open to you. > > I would *not* recommend hand-editing pg_control, even if you got the > CRC correct. There is a reason why we put in code to prevent on-the-fly > changes of locale: it corrupts your indexes. You might be able to get > the postmaster to start, but that wouldn't mean you could trust the > output of pg_dump. Postgres already has a -P option to disable system indexes. Would it be feasable to have a safe mode so that if your locale is broken it sets that flag and also sets enable_indexscan to false. This would at least give you enough to pg_dump things. Assuming the locale doesn't affect something else also. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
Attachment
Quoting Miquel van Smoorenburg (miquels@cistron.nl): > >| PANIC: The database cluster was initialized with LC_CTYPE > >| 'nl_NL@euro.UTF-8', which is not recognized by setlocale(). > >| It looks like you need to initdb. > >Is there some way that I can get to my precious databases? > > You have the source. Find where it prints this, make it not panic > but continue, recompile, run that server, run psql, drop the indexes, > dump the database. Hey mike ;) I know I /have/ the source. But I find it rather strange that I have to /hack/ the source to have it start. I'm thankful for the postgresql people to protect me from damaging my databases while i'm /not aware/ of the problem, but in this situation it almost feels windows-like (no offence :P) since there is NO way to have it start now, even if i really really really really really want it to. I hope pgsql-devel people are reading, and willing to implement a system like apt has when you want to remove something important like libc6: | Please type the following line, without the quotation marks, to state | that you really want this, and that you know what you're doing: | "I know what i'm doing. Please let me damage my databases. I won't | complain on mailinglists, and I have recent backups." | | Repeat> btw, postgresql in Debian is built with doogies build system *shiver* Sander. -- | Chinese proverb: Do not meet girl in park, instead park meet in girl! | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
Attachment
Quoting Martijn van Oosterhout (kleptog@svana.org): > Postgres already has a -P option to disable system indexes. Would it be > feasable to have a safe mode so that if your locale is broken it sets that > flag and also sets enable_indexscan to false. This would at least give you > enough to pg_dump things. Also see my post to Miquel on this list. I'd really like to see such an option. Even though i'll try hard not to have this problem ever again. So. I'll be hacking postgresql sources today ;) Sander. -- | You are not getting older, you're just getting closer to death. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
Attachment
Quoting Sander Smeenk (ssm+pgsql@freshdot.net): > > Postgres already has a -P option to disable system indexes. Would it be > > feasable to have a safe mode so that if your locale is broken it sets that > > flag and also sets enable_indexscan to false. This would at least give you > > enough to pg_dump things. > Also see my post to Miquel on this list. I'd really like to see such an > option. Even though i'll try hard not to have this problem ever again. > So. I'll be hacking postgresql sources today ;) Well, not really that day, but today I hacked postgresql :) Line 2261 in src/backend/access/transam/xlog.c changed from - if (setlocale(LC_CTYPE, ControlFile->lc_collate) == NULL) to + if (setlocale(LC_CTYPE, "nl_NL.UTF-8") == NULL) rebuilt the debian package, made backups, installed it and: -rw-r--r-- 1 postgres postgres 8461201 Sep 3 22:38 db.all.sql The dump looks all normal! Guess I was lucky that the 'wrong' locale the db was initialised with was also 100% nl_NL.UTF-8 aparently :) I'll go re-create the database then, and try to import the dump. Thanks everyone for all the ideas! Sander. -- | I must consider my sins... | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D