[ADMIN] Postgres Server crashed

[Kevin Grittner]

Lately the postgres server crashed due to space constraints,

The very first thing to do is to copy the entire directory structure
for the database cluster to somewhere you can keep it safe until the
problem has been resolved. If you have not yet done so, I strongly
recommend that you do so before trying anything else for recovery.

pg_control file got lost and the server wouldn't start.

"got lost"? It seems to me that it would be a Very Bad Thing if an
out of disk space situation allowed PostgreSQL to cause that.
Should we be looking for a bug, or is there a chance someone deleted
this in an attempt to free space? If the latter, is there any
chance that other files critical to data integrity were deleted,
like in the pg_xlog or pg_clog directories?

Had a old pg_control file placed in $PGDATA/global/ path.
I was able to start the server, however, the data seems to be a
very old one (2011 Jan 07) (probably the time where pg_control I
had pasted was last updated).

And normal maintenance may be viewing newer data as old, due to
transaction wrap-around from the old pg_control file, and removing
it as part of normal cleanup. So you may have destroyed some of
your more recent data by doing that.

How do I recover the rest of the data?

Well, as I said above, start by trying to preserve whatever you have
now. After you've done that, please provide more information. We
don't even know what version of PostgreSQL you're talking about, for
example.

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

It's somewhat likely that you'll be best off going to your last
known good backup, assuming you've been doing regular backups.

-Kevin

[Scott Marlowe]

On Mon, Oct 10, 2011 at 11:17 AM, Kevin Grittner

Post by Kevin Grittner
And normal maintenance may be viewing newer data as old, due to
transaction wrap-around from the old pg_control file, and removing
it as part of normal cleanup.  So you may have destroyed some of
your more recent data by doing that.

At this point, once you have a backup, would you deploy pg_resetxlog?

[Kevin Grittner]

Post by Scott Marlowe

Post by Kevin Grittner
And normal maintenance may be viewing newer data as old, due to
transaction wrap-around from the old pg_control file, and
removing it as part of normal cleanup. So you may have destroyed
some of your more recent data by doing that.

At this point, once you have a backup, would you deploy
pg_resetxlog?

That's what I was thinking. I shudder to think what autovacuum may
have done while running with the old pg_control file; but I don't
know what else to do at this juncture.

Anyone else have a better idea?

-Kevin

[Scott Marlowe]

On Mon, Oct 10, 2011 at 1:13 PM, Akash Kodibail

I was hopeful of restoring the data from data files in $PGDATA path. I read this article about PITR using the recovery.conf, But I am not aware of the pre-requisites and not entirely sure about the concept beneath this.

With PITR you set up the system ahead of time to do this. It is not a
rescue / recovery procedure for a broken database. I like Kevin fear
that by starting it back up with an old pg_Controldata you may have
made matters much worse, depending on what autovacuum might have done.

Once you have a backup it's time to start experimenting, and I think
pg_resetxlog is the best place to start.

[Kevin Grittner]

Can you please help me out in the options that might have to be
set for pg_resetxlog.

It should normally be able to calculate what it needs if you just
point it at the data directory. I would run it with the -n (no
operation) flag first, to see what it plans to do. If that looks
sane, run again without the -n switch.

I recommend that you read this page closely before proceeding:

http://www.postgresql.org/docs/8.4/interactive/app-pgresetxlog.html

-Kevin

[Scott Marlowe]

On Mon, Oct 10, 2011 at 2:59 PM, Kevin Grittner

Post by Kevin Grittner

Can you please help me out in the options that might have to be
set for pg_resetxlog.

It should normally be able to calculate what it needs if you just
point it at the data directory.  I would run it with the -n (no
operation) flag first, to see what it plans to do.  If that looks
sane, run again without the -n switch.

But it uses pg_controldata to determine what it needs automagically.
So... Since his pg_controldata is not the up to date one he's gonna
have to figure out what number to give it. I have no clue how to do
that at this point.