Thread: PostgreSQL HA questions
Hello<br /><br /> We're looking for HA PostgreSQL solution,so have a couple of questions:<br /><br /> 1. Is it possible formultiply PostgreSQL instances (engines,cores) to use same DATA space? For example,to have two PostgreSQL processes whichwill use same data directory,same files,and same data ? So,if You update some data in some table over postmaster1,theprocess which uses connection to postmaster2 will 'see' the exact same data in the same table ?<br /><br />2. Becouse of vaccuming issues,is it possible to create such a client process which will use two identical tables,and onreceiving a signal,it will switch between those tables.For example,first a client application uses table1,after some time,senda signal to process,it will switch using table2,so You can freely vacuum table1 or whatsoever.After vacuuming done,table1 will sinchronize with table2 and keep up-to-date until You send next signal to application,which will switchusing table1,so You can vacuum table2.<br /><br /> If any one has any ideas,thoughts ?<br /><br /> Sincerely<br /><br/> Dragan<br />
First, I'm moving this to -general, because this is way off topic for -hackers as near as I can tell. On Tue, Sep 26, 2006 at 10:39:18PM +0200, Dragan Zubac wrote: > 1. Is it possible for multiply PostgreSQL instances (engines,cores) to use > same DATA space? No. In fact, this is a very good way to cause corruption. What you _can_ do is set up a "watchdog" process that allows a different machine to take over the filesystem on a shared disk array, for instance, and come back up in recovery mode. So your outage is roughly as long as the time to notice your primary node failed, plus the time to recover from database crash. There are various software packages that will allow you to do this. NOT ALL OF THEM WORK WELL. Go back and read that sentence again. No, I am not saying this because of any painful experiences I have ever had ;-) > 2. Becouse of vaccuming issues,is it possible to create such a client > process which will use two identical tables,and on receiving a signal,it > will switch between those tables.For example,first a client application uses > table1,after some time,send a signal to process,it will switch using > table2,so You can freely vacuum table1 or whatsoever.After vacuuming > done,table 1 will sinchronize with table2 and keep up-to-date until You send > next signal to application,which will switch using table1,so You can vacuum > table2. It isn't clear to me why you think you need to do this: vacuum doesn't block your queries anyway. If the idea is that you have a table that you'd rather TRUNCATE and not have to vacuum, however, that makes sense. There are several strategies for this. My colleague Chris Browne seems really to like this kind of functionality, and has discussed it more than once on the -general list. I think you can find his detailed outlines of how to do this sort of thing by searching for "rotor tables". A -- Andrew Sullivan | ajs@crankycanuck.ca I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin
ajs@crankycanuck.ca (Andrew Sullivan) writes: > It isn't clear to me why you think you need to do this: vacuum > doesn't block your queries anyway. If the idea is that you have a > table that you'd rather TRUNCATE and not have to vacuum, however, > that makes sense. There are several strategies for this. My > colleague Chris Browne seems really to like this kind of > functionality, and has discussed it more than once on the -general > list. I think you can find his detailed outlines of how to do this > sort of thing by searching for "rotor tables". I'd suggest looking at the section in the documentation on Partitioning; the mechanisms there look like the Better Way these days. <http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html> There were some things that were Pretty Neat about rotor tables; as of 8.1, the benefits gotten from constraint propagation with partitioning seems to make that a much more attractive way to go about things. There are always going to be some caveats for whatever mechanism is used to partition data; it looks like 8.1's constraint propagation pushes preference towards using inheritance... -- let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; http://cbbrowne.com/info/linux.html "It is easier to optimize correct code, than correct optimized code" -- Yves Deville