Database Recovery

I have just had a bit of a disaster with a database and found very
little material online about how to recover from a corrupted database.
This is not the first time I have had to recoover data from a postgres
database which was caused either by some hardware failure or my own
error (not sure which is worse).

Is there any material online on how to recover a corrupt postgresql
database?

If not I would be happy to take a shot at writing something based on my
own experiences. The most recent of which (I wrote it tonight) can be
found at

http://www.hjackson.org/blog/archives/2004/12/postgresql_data.html

Its a bit long winded but if there are no docs detailing various
recovery procedures and there are people willing to answer questions
and make sure I was on the right track then I would be willing to write
it?

Has it already been written? Thoughts?

=====
Harry
Join team plico.
http://www.hjackson.org/cgi-bin/folding/index.pl

__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail

On Sun, Dec 19, 2004 at 04:11:17PM -0800, Harry wrote:
> I have just had a bit of a disaster with a database and found very
> little material online about how to recover from a corrupted database.
> This is not the first time I have had to recoover data from a postgres
> database which was caused either by some hardware failure or my own
> error (not sure which is worse).

> http://www.hjackson.org/blog/archives/2004/12/postgresql_data.html

Huh, this sounds like transaction Id wraparound to me. Do you regularly
run vacuums on the whole database? Did you ask for expert help on the
lists before running to do whatever you did?

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"Entristecido, Wutra (canción de Las Barreras)
echa a Freyr a rodar
y a nosotros al mar"

Harry <postituk(at)yahoo(dot)com> writes:
> Is there any material online on how to recover a corrupt postgresql
> database?

There are dozens if not hundreds of case histories in the mailing list
archives; the latest example is this thread:
http://archives.postgresql.org/pgsql-hackers/2004-12/msg00479.php
Feel free to try to pull something together from that info.

However, the rule of thumb is "every problem is different". If we could
think of a cookbook procedure then we'd build an automated recovery tool
... so you need to think more in terms of teaching than of giving
recipes.

regards, tom lane

--- Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> wrote:
> > http://www.hjackson.org/blog/archives/2004/12/postgresql_data.html
>
> Huh, this sounds like transaction Id wraparound to me. Do you
> regularly run vacuums on the whole database? Did you ask for expert
> help on the lists before running to do whatever you did?

I didn't run to do anything ;) I had a good think and a good google
before I done anything and I have all my data back because of it.
Luckily for me the data was neither life or job threatening so I was
able to take a few more risks than necessary. I was actually
volunteering to write the database recovery section of the docs, not
asking for help.

As per TID wraparound. I have been lucky enough never to have received
anything similar to the following warning (taken from 7.4 docs)

play=# vacuum;
WARNING: Some databases have not been vacuumed in 1613770184
transactions.
Better vacuum them within 533713463 transactions,
or you may have a wraparound failure.
VACUUM

you will also notice that I used a TID of less than 100 million to
recover the database. I was nowhere near 0.5 billion as recommended by
the docs.

Harry

=====
Harry
Join team plico.
http://www.hjackson.org/cgi-bin/folding/index.pl

__________________________________
Do you Yahoo!?
Yahoo! Mail - Easier than ever with enhanced search. Learn more.
http://info.mail.yahoo.com/mail_250

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Harry <postituk(at)yahoo(dot)com> writes:
> > Is there any material online on how to recover a corrupt postgresql
> > database?
>
> There are dozens if not hundreds of case histories in the mailing
> list archives; the latest example is this thread:
> http://archives.postgresql.org/pgsql-hackers/2004-12/msg00479.php
> Feel free to try to pull something together from that info.

It was all the threads and a lot of googling that enabled me to get the
data back.

> However, the rule of thumb is "every problem is different". If we
> could think of a cookbook procedure then we'd build an automated
> recovery tool ... so you need to think more in terms of teaching than

> of giving recipes.

I agree, I am not a believer in recipes either. However, for most
people they have no idea where to start or what to do next. The first
place I went looking was postgres.org and I got more info peppered
through the mailing lists than in the docs (not a bad thing).

I spent all day yesterday reading about what other people had done and
trying to figure out what I could use to determine what/where my
problem was and how to go about *starting* to fix it. I found little on
the use of or how to use pg_filedump or pg_resetxlog, luckily for me
the latter has a man page. I also used a post from yourself to
determine that I had to use the "-l" option to pg_resetxlog to fix my
problem.

In hindsight I would have done some things differently, like posting to
one of the lists. If you think that it would be better for people to
ask the list and run from there then I will leave it.

Harry

=====
Harry
Join team plico.
http://www.hjackson.org/cgi-bin/folding/index.pl

__________________________________
Do you Yahoo!?
Yahoo! Mail - Easier than ever with enhanced search. Learn more.
http://info.mail.yahoo.com/mail_250

Harry <postituk(at)yahoo(dot)com> writes:
> In hindsight I would have done some things differently, like posting to
> one of the lists. If you think that it would be better for people to
> ask the list and run from there then I will leave it.

Well, we should certainly encourage people to post such problems to
the lists; that's the only way we'd ever find out about common-mode
failures that we might be able to fix or defend against. But I don't
see any reason that we can't start to pull together some collected
wisdom. The idea has been discussed before but no one's really stepped
up to do the writing. If you want to give it a go, by all means ...

regards, tom lane

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> On Sun, Dec 19, 2004 at 04:11:17PM -0800, Harry wrote:
>> http://www.hjackson.org/blog/archives/2004/12/postgresql_data.html

> Huh, this sounds like transaction Id wraparound to me.

Given the mention of a drive glitch, I'm mentally comparing it to our
present theory about Joe Conway's recent troubles. That is, I wonder
if he had a mistakenly-reinitialized pg_control.

Harry, are you using a Postgres start script that will automatically
run initdb if it doesn't see a valid data directory at $PGDATA?

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Harry <postituk(at)yahoo(dot)com> writes:
> > In hindsight I would have done some things differently, like
> posting to
> > one of the lists. If you think that it would be better for people
> to
> > ask the list and run from there then I will leave it.
>
> Well, we should certainly encourage people to post such problems to
> the lists; that's the only way we'd ever find out about common-mode
> failures that we might be able to fix or defend against. But I don't
> see any reason that we can't start to pull together some collected
> wisdom. The idea has been discussed before but no one's really
> stepped up to do the writing. If you want to give it a go, by all
> means ...

I will start putting together some basic guidlelines on what to do when
someone has a suspected corruption ie get on the mailing lists and
start asking questions, what constitutes a sensible question, what not
to do etc. I will keep it very basic to start with and we will see
where it goes from there.

If anyone has any sugestions on things that would be good to add to a
database recovery chapter then let me know and I will add it to the list.

=====
Harry
Join team plico.
http://www.hjackson.org/cgi-bin/folding/index.pl

__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail