PostgreSQL upgrade -> fails to start server

**Martijn van Oosterhout** · Nov 11 '05, 11:34 PM

Re: PostgreSQL upgrade -> fails to start server

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:[color=blue]
> 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[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> "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[/color]

-----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** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

Quoting Martijn van Oosterhout (kleptog@svana. org):
[color=blue]
> 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.[/color]

I thought so too. But then I thought maybe the data stored inside the
database is affected by this locale setting too?
[color=blue]
> 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.[/color]

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.
[color=blue]
> Once you can start it, pg_dumpall and restart.
> Ofcourse, if you have backups...[/color]

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 :)
[color=blue]
> 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.[/color]

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.
[color=blue]
> Hope this helps,[/color]

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** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

Sander Smeenk <ssm+pgsql@fres hdot.net> writes:[color=blue]
> To make a long story short: i can't activate that locale again,[/color]

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 majordomo@postg resql.org

**Sander Smeenk** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

Quoting Tom Lane (tgl@sss.pgh.pa .us):
[color=blue][color=green]
> > To make a long story short: i can't activate that locale again,[/color]
> Why not? Undoing whatever you did to the locale setup seems a lot
> easier task than any other option that's open to you.[/color]

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.
[color=blue]
> I would *not* recommend hand-editing pg_control, even if you got the
> CRC correct.[/color]

It is a desperate attempt to get my data out.
[color=blue]
> There is a reason why we put in code to prevent on-the-fly
> changes of locale: it corrupts your indexes.[/color]

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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Tom Lane** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

Sander Smeenk <ssm+pgsql@fres hdot.net> writes:[color=blue]
> Quoting Tom Lane (tgl@sss.pgh.pa .us):[color=green][color=darkred]
>>> To make a long story short: i can't activate that locale again,[/color][/color][/color]
[color=blue][color=green]
>> Why not? Undoing whatever you did to the locale setup seems a lot
>> easier task than any other option that's open to you.[/color][/color]
[color=blue]
> 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.[/color]

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** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

Quoting Tom Lane (tgl@sss.pgh.pa .us):
[color=blue][color=green]
> > 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.[/color]
> 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/ ?[/color]

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.

---------------------------(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** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

In article <20030829095837 .GA24442@freshd ot.net>,
Sander Smeenk <ssm+pgsql@fres hdot.net> wrote:[color=blue]
>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?[/color]

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?

PostgreSQL: Not Found

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

**Martijn van Oosterhout** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

On Fri, Aug 29, 2003 at 06:17:43PM +0200, Sander Smeenk wrote:[color=blue]
> Quoting Tom Lane (tgl@sss.pgh.pa .us):
> [color=green][color=darkred]
> > > The nl_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.[/color]
> > 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/ ?[/color]
>
> 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.[/color]

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/[color=blue]
> "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[/color]

-----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** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

On Fri, Aug 29, 2003 at 10:09:15AM -0400, Tom Lane wrote:[color=blue]
> Sander Smeenk <ssm+pgsql@fres hdot.net> writes:[color=green]
> > To make a long story short: i can't activate that locale again,[/color]
>
> 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.[/color]

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 <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> "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[/color]

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

**Sander Smeenk** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

Quoting Miquel van Smoorenburg (miquels@cistro n.nl):
[color=blue][color=green]
> >| 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?[/color]
>
> 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.[/color]

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+QQjOyU0 RAhQnAJsGQuPmld HRiSjxHLqvtBQp4 CLvcACeKFGf
xwizzrinIcB0Rpy cbezCL94=
=StGb
-----END PGP SIGNATURE-----

**Sander Smeenk** · Nov 11 '05, 11:35 PM

Re: PostgreSQL upgrade -> fails to start server

Quoting Martijn van Oosterhout (kleptog@svana. org):
[color=blue]
> 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.[/color]

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+QQjOyU0 RAkEwAJ9Ae+vFsr 47bTGI8aSzv5K8l 4IiBQCfYphi
aGJ6MHxu+gjXrhn wuSApb0c=
=lX+q
-----END PGP SIGNATURE-----

**Sander Smeenk** · Nov 11 '05, 11:39 PM

Re: PostgreSQL upgrade -> fails to start server

Quoting Sander Smeenk (ssm+pgsql@fres hdot.net):
[color=blue][color=green]
> > 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.[/color]
> 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 ;)[/color]

Well, not really that day, but today I hacked postgresql :)

Line 2261 in src/backend/access/transam/xlog.c changed from

- if (setlocale(LC_C TYPE, ControlFile->lc_collate) == NULL)

to

+ if (setlocale(LC_C TYPE, "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+QQjOyU0 RAnw4AKCBSzveRR wpImoxdXXkqP5xy IigFACdECGP
4DHfs1TKjN+0Yr0 CFcC0LMU=
=QzgC
-----END PGP SIGNATURE-----