PostgreSQL upgrade -> fails to start server

[Sander Smeenk]

Hi,

After a very recent postgresql update, the postmaster failed to start
because of this:

| PANIC: The database cluster was initialized with LC_CTYPE
| 'n****@euro.UTF-8', which is not recognized by setlocale().
| It looks like you need to initdb.

Now I know 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]

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
| 'n****@euro.UTF-8', which is not recognized by setlocale().
| It looks like you need to initdb.

Now I know 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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/TzihY5Twig3Ge+Y RAilFAJwJ4KIE+x 6dAB7AgLSYqKVX6 sNp+ACgmr85
Qrlu9lJP1zrpmpc cAsDItaQ=
=xV8S
-----END PGP SIGNATURE-----

[Sander Smeenk]

Quoting Martijn van Oosterhout (kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/Tz9e1GN+QQjOyU0 RAiRgAJ9LGUHG0u UvqizI5HNRSIpvM X/+SACgi+ty
WxvvZ+WSz35RK45 o8f59nEc=
=xaAP
-----END PGP SIGNATURE-----

[Tom Lane]

Sander Smeenk <ss*******@fres hdot.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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Sander Smeenk]

Quoting Tom Lane (tg*@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***@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***@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Sander Smeenk <ss*******@fres hdot.net> writes:

Quoting Tom Lane (tg*@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***@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Sander Smeenk]

Quoting Tom Lane (tg*@sss.pgh.pa .us):

The 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***@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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Miquel van Smoorenburg]

In article <20************ ********@freshd ot.net>,
Sander Smeenk <ss*******@fres hdot.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
| 'n****@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Martijn van Oosterhout]

On Fri, Aug 29, 2003 at 06:17:43PM +0200, Sander Smeenk wrote:

Quoting Tom Lane (tg*@sss.pgh.pa .us):

The nl***@euro.UTF-8 locale I used 'back then' is incorrect. It's nota
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***@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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/T/TJY5Twig3Ge+YRA hvJAJ9Sl3Jpqkf/sL+hbiPbziV94mk LRQCg0Yjm
e5EEhMHA+hgnxd5 2FsvnCsQ=
=LrDF
-----END PGP SIGNATURE-----

[Martijn van Oosterhout]

On Fri, Aug 29, 2003 at 10:09:15AM -0400, Tom Lane wrote:

Sander Smeenk <ss*******@fres hdot.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_indexsca n 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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/T/VQY5Twig3Ge+YRA rYAAJ9QjGPJmcgl 0lOTQcGelqwdxOL kKQCgpQgH
kIeS/3kttCHdc6dIfgfp SeI=
=Dx1s
-----END PGP SIGNATURE-----