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
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?
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[@]dcc.uchile.cl>) "Entristecido, Wutra (canción de Las Barreras) echa a Freyr a rodar y a nosotros al mar"
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majord...@postgresql.org so that your message can get through to the mailing list cleanly
> 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.
__________________________________ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majord...@postgresql.org so that your message can get through to the mailing list cleanly
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.
__________________________________ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majord...@postgresql.org)
Harry <posti...@yahoo.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
---------------------------(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
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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
> Harry <posti...@yahoo.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.
---------------------------(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
