Re: [GENERAL] I don't want to back up index files

2009-03-21 Thread Lew
Glen Parker wrote: pg_dump is not acceptable to us because of the potential to lose many hours of valuable data. Why would pg_dump even be relevant to this discussion? PITR offers a benefit that pg_dump does not, a benefit that we, and countless other organizations, obviously find useful. Sug

Re: [GENERAL] I don't want to back up index files

2009-03-13 Thread Glen Parker
Jeremy Harris wrote: Simon Riggs wrote: The idea of auto rebuilding indexes following recovery has already been proposed, so is under consideration. It hasn't been proposed in relation to the use case you mention, so that is new. If we did as you suggest then it would speed up the base backup b

Re: [GENERAL] I don't want to back up index files

2009-03-13 Thread Jeremy Harris
Simon Riggs wrote: The idea of auto rebuilding indexes following recovery has already been proposed, so is under consideration. It hasn't been proposed in relation to the use case you mention, so that is new. If we did as you suggest then it would speed up the base backup but would also add inde

Re: [GENERAL] I don't want to back up index files

2009-03-13 Thread Simon Riggs
On Thu, 2009-03-12 at 12:57 -0700, Glen Parker wrote: > Tom Lane wrote: > > Glen Parker writes: > >> Tom Lane wrote: > >>> ... AFAICS what > >>> Glen is proposing is to not WAL-log index changes, and with that any > >>> crash no matter how minor would have to invalidate indexes. > > > >> Nooo..

Re: [GENERAL] I don't want to back up index files

2009-03-12 Thread Glen Parker
Tom Lane wrote: Glen Parker writes: Mainly because the idea doesn't seem to make sense unless that's part of the package. If you don't cut index changes out of the WAL load then the savings on the base backup alone aren't going to be all that exciting when you consider the total cost of PITR ba

Re: [GENERAL] I don't want to back up index files

2009-03-12 Thread Tom Lane
Alan Hodgson writes: > I have done PITR recoveries (unfortunately). The log replay time dwarfs the > time it takes to copy the index files back over (it is, honestly, slower > than the original transactions were). Yeah :-( ... the problem is that recovery is serialized while the original transa

Re: [GENERAL] I don't want to back up index files

2009-03-12 Thread Tom Lane
Glen Parker writes: > I've re-read my original email and I just can't see how anybody got the > idea I was suggesting to not WAL record index changes. Mainly because the idea doesn't seem to make sense unless that's part of the package. If you don't cut index changes out of the WAL load then th

Re: [GENERAL] I don't want to back up index files

2009-03-12 Thread Alvaro Herrera
Glen Parker escribió: > I've re-read my original email and I just can't see how anybody got the > idea I was suggesting to not WAL record index changes. The subject says that. > That would be insanity IMO. Agreed :-) -- Alvaro Herrerahttp://www.CommandPromp

Re: [GENERAL] I don't want to back up index files

2009-03-12 Thread Glen Parker
Scott Marlowe wrote: That's two people now who have called the idea "silly" without even a hint of a supporting argument. Why would it be "silly" to improve the performance of a highly valuable tool set without compromising its utility? Because it's the size of the WAL files that kills most pe

Re: [GENERAL] I don't want to back up index files

2009-03-12 Thread Joshua D. Drake
On Thu, 2009-03-12 at 12:59 -0700, Glen Parker wrote: > Tom Lane wrote: > > Glen Parker writes: > >> We have yet to recover from a PG disaster. We back up every night, and > >> never use the back ups for anything. To me, it seems perfectly > >> reasonable to get a quicker back up every night,

Re: [GENERAL] I don't want to back up index files

2009-03-12 Thread Glen Parker
Tom Lane wrote: Glen Parker writes: We have yet to recover from a PG disaster. We back up every night, and never use the back ups for anything. To me, it seems perfectly reasonable to get a quicker back up every night, with the remote possibility of ever having to pay the price for it. Wh

Re: [GENERAL] I don't want to back up index files

2009-03-12 Thread Glen Parker
Tom Lane wrote: Glen Parker writes: Tom Lane wrote: ... AFAICS what Glen is proposing is to not WAL-log index changes, and with that any crash no matter how minor would have to invalidate indexes. Nooo...! This has nothing to do with WAL logging index changes. How so? In any PITR-based

Re: [GENERAL] I don't want to back up index files

2009-03-12 Thread Alan Hodgson
On Wednesday 11 March 2009, Glen Parker wrote: > We have yet to recover from a PG disaster. We back up every night, and > never use the back ups for anything. To me, it seems perfectly > reasonable to get a quicker back up every night, with the remote > possibility of ever having to pay the pric

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Joshua D. Drake
On Wed, 2009-03-11 at 20:59 -0600, Scott Marlowe wrote: > On Wed, Mar 11, 2009 at 5:57 PM, Glen Parker wrote: > > Scott Marlowe wrote: > > Suggesting that a > > person who's been managing PG in a commercial setting since version 6.4 > > should just use pg_dump as an alternative to PITR is, well,

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Scott Marlowe
On Wed, Mar 11, 2009 at 5:57 PM, Glen Parker wrote: > Scott Marlowe wrote: > >> pg_dump is a perfectly acceptable backup tool, as is PITR.  They have >> different ways of operating based on what you need.  Trying to make >> PITR act more like pg_dump seems kind of silly to me. > > pg_dump is not a

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Tom Lane
Glen Parker writes: > We have yet to recover from a PG disaster. We back up every night, and > never use the back ups for anything. To me, it seems perfectly > reasonable to get a quicker back up every night, with the remote > possibility of ever having to pay the price for it. Why don't you

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Tom Lane
Glen Parker writes: > Tom Lane wrote: >> ... AFAICS what >> Glen is proposing is to not WAL-log index changes, and with that any >> crash no matter how minor would have to invalidate indexes. > Nooo...! This has nothing to do with WAL logging index changes. How so? In any PITR-based situation

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Glen Parker
Joshua D. Drake wrote: 1. You could put all your indexes into a table space, this would allow you to "try" different things with the indexes. Most of them are, but I still have to back them up in order to have a valid backup, because the PITR code would choke if any are missing. 2. Even tho

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Glen Parker
Tom Lane wrote: Alvaro Herrera writes: FWIW I don't think this idea is silly at all. It's so not-silly, in fact, that we already have some access methods that do this if an index cannot be recovered (I think at least GiST does it). Well, there's a difference between "rebuild the index when i

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Joshua D. Drake
On Wed, 2009-03-11 at 16:57 -0700, Glen Parker wrote: > Scott Marlowe wrote: > That's two people now who have called the idea "silly" without even a > hint of a supporting argument. Why would it be "silly" to improve the > performance of a highly valuable tool set without compromising its > ut

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Tom Lane
Alvaro Herrera writes: > FWIW I don't think this idea is silly at all. It's so not-silly, in > fact, that we already have some access methods that do this if an index > cannot be recovered (I think at least GiST does it). Well, there's a difference between "rebuild the index when it can't be rec

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Alvaro Herrera
Glen Parker escribió: > That's two people now who have called the idea "silly" without even a > hint of a supporting argument. Why would it be "silly" to improve the > performance of a highly valuable tool set without compromising its > utility? Am I missing something here? That's certain

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Glen Parker
Scott Marlowe wrote: pg_dump is a perfectly acceptable backup tool, as is PITR. They have different ways of operating based on what you need. Trying to make PITR act more like pg_dump seems kind of silly to me. pg_dump is not acceptable to us because of the potential to lose many hours of

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Scott Marlowe
On Wed, Mar 11, 2009 at 3:25 PM, Glen Parker wrote: > Grzegorz Jaśkiewicz wrote: >> So like JD said, if you don't want to dump indicies - just use pg_dump... > > If pg_dump were an acceptable backup tool, we wouldn't need PITR, would we? >  We used pg_dump for years.  There's a very good reason we

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Joshua D. Drake
On Wed, 2009-03-11 at 14:25 -0700, Glen Parker wrote: > Grzegorz Jaśkiewicz wrote: > > So like JD said, if you don't want to dump indicies - just use pg_dump... > > If pg_dump were an acceptable backup tool, we wouldn't need PITR, would > we? We used pg_dump for years. There's a very good reas

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Glen Parker
Grzegorz Jaśkiewicz wrote: On Wed, Mar 11, 2009 at 3:03 PM, Joshua D. Drake wrote: Any chance of something like this being done in the future? I am going to go out on a limb here and say, "no". That would probably be possible, by placing all indicies in a separate directory in data, but...

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Grzegorz Jaśkiewicz
On Wed, Mar 11, 2009 at 3:03 PM, Joshua D. Drake wrote: >> Any chance of something like this being done in the future? >> > > I am going to go out on a limb here and say, "no". That would probably be possible, by placing all indicies in a separate directory in data, but Well, that would crea

Re: [GENERAL] I don't want to back up index files

2009-03-11 Thread Joshua D. Drake
On Tue, 2009-03-10 at 18:54 -0700, Glen Parker wrote: > I am wondering the feasibility of having PG continue to work even if > non-essential indexes are gone or corrupt. I brought this basic concept > up at some point in the past, but now I have a different motivation, so > I want to strike up dis