[DOCS] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [DOCS] Database Recovery
--- Alvaro Herrera <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] Database Recovery
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Harry <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [DOCS] Database Recovery
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Harry <[EMAIL PROTECTED]> 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 ---(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
[DOCS] Format for docs
What is the preferred format if writing postgresql documentation? Is plain text OK? = Harry Join team plico. http://www.hjackson.org/cgi-bin/folding/index.pl __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [DOCS] About SET SEARCH_PATH
On Wed, 17 Sep 2003 16:40:32 -0400, [EMAIL PROTECTED] (Tom Lane) wrote: >Christoph Haller <[EMAIL PROTECTED]> writes: >>> The search path may be altered by a run-time setting. >>> The command to use is SET SEARCH_PATH 'schema'[,'schema']... > >> It should be >> SET SEARCH_PATH TO schema[,schema]... > >Actually, you can do it either way: > >regression=# set search_path TO a,b,c; >regression=# set search_path TO 'a','b','c'; > >although not > >regression=# set search_path TO 'a,b,c'; >ERROR: schema "a,b,c" does not exist > But surely not without the 'TO' word, which is how it appears in 6.13 of the User's Guide? Regards, Harry. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
