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+YRAilFAJwJ4KIE+x6dAB7AgLSYqKVX6sNp+A Cgmr85
Qrlu9lJP1zrpmpccAsDItaQ=
=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+QQjOyU0RAiRgAJ9LGUHG0uUvqizI5HNRSIpvMX/+SACgi+ty
WxvvZ+WSz35RK45o8f59nEc=
=xaAP
-----END PGP SIGNATURE-----

[Tom Lane]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.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*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Sander Smeenk <ss*******@freshdot.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********************@freshdot.net>,
Sander Smeenk <ss*******@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
| '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+YRAhvJAJ9Sl3Jpqkf/sL+hbiPbziV94mkLRQCg0Yjm
e5EEhMHA+hgnxd52FsvnCsQ=
=LrDF
-----END PGP SIGNATURE-----

[Martijn van Oosterhout]

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

Sander Smeenk <ss*******@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 <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+YRArYAAJ9QjGPJmcgl0lOTQcGelqwdxOLkKQCg pQgH
kIeS/3kttCHdc6dIfgfpSeI=
=Dx1s
-----END PGP SIGNATURE-----

[Sander Smeenk]

Quoting Miquel van Smoorenburg (mi*****@cistron.nl):

| 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.

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

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

iD8DBQE/UYm61GN+QQjOyU0RAhQnAJsGQuPmldHRiSjxHLqvtBQp4CLvcA CeKFGf
xwizzrinIcB0RpycbezCL94=
=StGb
-----END PGP SIGNATURE-----

[Sander Smeenk]

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

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

iD8DBQE/UYqs1GN+QQjOyU0RAkEwAJ9Ae+vFsr47bTGI8aSzv5K8l4IiBQ CfYphi
aGJ6MHxu+gjXrhnwuSApb0c=
=lX+q
-----END PGP SIGNATURE-----

[Sander Smeenk]

Quoting Sander Smeenk (ss*******@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

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

iD8DBQE/VlYy1GN+QQjOyU0RAnw4AKCBSzveRRwpImoxdXXkqP5xyIigFA CdECGP
4DHfs1TKjN+0Yr0CFcC0LMU=
=QzgC
-----END PGP SIGNATURE-----