| 02:44.52 | *** join/#udbug SunSparc (n=sparky@71-35-208-217.slkc.qwest.net) |
| 14:55.21 | *** join/#udbug SunSparc (n=sparky@66.7.114.125) |
| 15:11.45 | unum | what's the command to repair a postgresql database |
| 15:25.35 | eggyknap | unum: restore from backup. What's the problem? |
| 15:28.15 | unum | eggyknap: the backup is a day old |
| 15:28.33 | unum | actually restoring just the one table wouldn't be so bad |
| 15:28.48 | unum | I thought there was a command line tool you could run to fix some corruption |
| 15:29.03 | unum | I just need to get rid of page errors on one table |
| 15:29.30 | unum | so restoring from a day old back up is kind of extreme for the small problem |
| 15:29.47 | eggyknap | There's not such a command, that I know of. |
| 15:30.02 | eggyknap | What corrupted the table? |
| 15:30.20 | unum | bad ups on the raid enclosure |
| 15:30.25 | unum | so it lost power |
| 15:30.36 | eggyknap | Nice. |
| 15:31.25 | eggyknap | PITR might help you if you expect the future to hold more such problems. |
| 15:41.53 | fungus | Pg has no way to attempt to repair table corruption?!?!? |
| 15:42.14 | fungus | nothing like myisamchk |
| 15:42.41 | eggyknap | With MyISAM, you expect table corruption to happen regularly. With more data-friendly table types, corruption doesn't happen unless something disastrous goes on. |
| 15:44.38 | fungus | I wouldn't call a power outage disastrous. It is something that happens, and should be expected. |
| 15:44.56 | eggyknap | MyISAM will get corruption if two people look at the table wrong :) |
| 15:45.46 | fungus | I am not comparing corruption rates. My point is that there should be a method of repairing corruption in any table. |
| 15:46.13 | eggyknap | More seriously, with MyISAM, there are several types of corruption that happen regularly, particularly index corruption. You can rebuild an index with the underlying data, so it's pretty easy to fix those kinds of corruption. There are other ways a table can become corrupt, and they can't be fixed by looking at some other always-accessible copy of the data. |
| 15:48.05 | eggyknap | With typical MyISAM corruptions, it's easy to say "This is what's wrong, here's how to fix it, and here are the data I need to fix it." PG tables don't corrupt nearly as often nor in as predicable of ways, so fixing it is best left to the administrator. |
| 15:48.10 | fungus | It doesn't matter. Corruption can happen, and will happen. It should be fixable |
| 15:48.55 | eggyknap | So what would you recommend such a program do to fix the corruption? |
| 15:49.16 | fungus | corruption caused by a power outage should be easy to repair. |
| 15:49.17 | eggyknap | It doesn't have a good copy of the data to look at, except in a backup. It could delete the records in question, but that's hardly optimal. |
| 15:49.49 | fungus | I have *never* lost a myisam table due to power failure, myisamchk always fixes it. |
| 15:50.31 | eggyknap | And I've never had a corrupt PG table. It's an extremely rare thing, even in the case of power failures. Because it's so rare, it's so non-deterministic that you have to let a human fix it. |
| 15:50.47 | eggyknap | "corruption" can mean too many different things to write a program to do it. |
| 15:54.01 | unum | this is atleast my second if not my third table corruption |
| 15:54.17 | unum | actually I'm pretty sure it's atleast my third |
| 15:54.32 | unum | it manager buys too much crap from dell |
| 15:54.47 | unum | doesn't keep the ups running |
| 15:55.11 | eggyknap | Crappy hardware won't make any database happy :) |
| 16:01.32 | fungus | If pg is so great I expect better resiliency than MySQL, even is the space of power failures. |
| 16:03.51 | eggyknap | I consider never getting corruption better than having to run myisamchk all the time :) |
| 16:03.59 | eggyknap | </flippant statement> |
| 16:05.42 | eggyknap | You'll need to restore from backups sometimes on any system -- that's why you take backups. I like PG because it does what I need it to do, and it does an excellent job of it, among other reasons, but not because I've spent lots of time with MySQL and PostgreSQL and decided on PG after lots of careful comparison. |
| 16:06.42 | eggyknap | I started using PG because MySQL (at the time) supported very little of the SQL standard. They're getting better, but PG is still much better in that regard. I got sick of not being able to write subqueries. |
| 16:06.57 | eggyknap | It also didn't (at the time) support transactions, which were critical to everything I wanted to do. |
| 16:07.13 | eggyknap | After starting to use PG, I've never found a reason to switch. |
| 16:07.57 | eggyknap | with Oracle, on the other hand, I have done somewhat of a comparison, and still like PG best. |
| 17:28.05 | *** join/#udbug SunSparc_ (n=sparky@66.7.114.125) |
| 17:37.26 | unum | looks like I probably just had to run fsck to fix it |
| 17:38.30 | eggyknap | Nice. |