pg_clog and pg_xlog empty, postgresql refuses to start

[Glen Parker]

First things first: Postgresql 8.4.2 on Fedora Core 2 X86.

Something seems to have happened to my pg_xlog and pg_clog directories after
(I believe) a power outage. In the course of trying to figure out why the
server wouldn't start, I cleaned out pg_clog and pg_xlog, in an obviously
vain attempt to reset things. I was under the impression that the server
could start without those files. Now I seem to be stuck.

So now what?

Here is what postmaster has to say on the subject:

LOG: database system was shut down at 2004-08-16 15:52:01 PDT
LOG: could not open file
"/usr/local/var/lib/pgsql32-broken/pg_xlog/0000000F000000F 3" (log file 15,
segment 243): No such file or directory
LOG: invalid primary checkpoint record
LOG: could not open file
"/usr/local/var/lib/pgsql32-broken/pg_xlog/0000000F000000F 3" (log file 15,
segment 243): No such file or directory
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 9920) was terminated by signal 6
LOG: aborting startup due to startup process failure

Thx
Glen Parker


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

[Tom Lane]

Re: pg_clog and pg_xlog empty, postgresql refuses to start

"Glen Parker" <glenebob@nwlin k.com> writes:[color=blue]
> First things first: Postgresql 8.4.2 on Fedora Core 2 X86.[/color]

I take it this message fell through a time warp ;-)
[color=blue]
> Something seems to have happened to my pg_xlog and pg_clog directories after
> (I believe) a power outage. In the course of trying to figure out why the
> server wouldn't start, I cleaned out pg_clog and pg_xlog, in an obviously
> vain attempt to reset things. I was under the impression that the server
> could start without those files. Now I seem to be stuck.[/color]

That was probably not your best first move...

I'd put back the clog files if at all possible. If not you can
substitute zero-filled 256K files for each clog file the server
complains about not finding, but realize that you *will* be losing
transactions in whole or in part.

As for pg_xlog, you can cons up dummy xlog contents with pg_resetxlog,
but again you'd be better off with the real thing. The dummy files will
not be capable of replaying whatever happened since your last checkpoint.

What failures were you getting *before* you decided to try this?

regards, tom lane

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

[Glen Parker]

Re: pg_clog and pg_xlog empty, postgresql refuses to start

> > First things first: Postgresql 8.4.2 on Fedora Core 2 X86.[color=blue]
>
> I take it this message fell through a time warp ;-)[/color]

Yep :-)
7.4.2.
[color=blue][color=green]
>> ...I cleaned out pg_clog and pg_xlog...[/color]
>
> That was probably not your best first move...[/color]

This is actually a development copy of our real database, so worst-case
scenario is a dump/restore. No big deal.
[color=blue]
> I'd put back the clog files if at all possible. If not you can
> substitute zero-filled 256K files for each clog file the server
> complains about not finding, but realize that you *will* be losing
> transactions in whole or in part.
>
> As for pg_xlog, you can cons up dummy xlog contents with pg_resetxlog,
> but again you'd be better off with the real thing. The dummy files will
> not be capable of replaying whatever happened since your last checkpoint.[/color]

OK, all the files I deleted, I really deleted... See? I'm on a roll today.

pg_resetxlog did the trick, mostly; the server starts again and I seem to be
back to where I started...
[color=blue]
> What failures were you getting *before* you decided to try this?[/color]

I started out, and am now once again, getting this error when issuing a
delete query in psql:

ERROR: could not access status of transaction 655360
DETAIL: could not read from file "/usr/local/var/lib/pgsql32/pg_clog/0000"
at offset 163840: Success

I am now also getting this error when vacuuming a different table:
ERROR: catalog is missing 2 attribute(s) for relid 25297932


Hmm, whatever happened over the weekend that caused this machine to be *off*
this morning must have been a bit nasty... Dump/restore may be my only
option.

Thx again,
Glen Parker


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Re: pg_clog and pg_xlog empty, postgresql refuses to start

"Glen Parker" <glenebob@nwlin k.com> writes:[color=blue]
> I am now also getting this error when vacuuming a different table:
> ERROR: catalog is missing 2 attribute(s) for relid 25297932[/color]

Yeah, this is what I warned you about: partial committal of transactions.
Given that it's only a devel server, it's probably best to initdb ...

regards, tom lane

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