Thread: HA best pratices with postgreSQL
~ I am developing a J2EE application that needs for users to only read DB tables. All queries are select ones, no updates, no inserts, no deletes for web users, so I keep this ro DB tables in certain partitions which I mount as ro ~ For performance reasons I keet the DB in the same box as the server ~ Now, the data in those tables need to actually be updated not by web users, but from the back end and not that often at all, say just once of twice a day in a totally controlled way and updates shouldn't take long. Just some insert stats in single tables ~ I had in mind: ~ 1) always keeping two partitions in synch, with exactly the same data ~ 2) once I need to update the data (and while users still access partition1): ~ 2.1) rw mount the standby partition2 ~ 2.2) start another instance of PG running on another port ~ 2.3) update the data on partition2 ~ 2.4) unmount partition2 ~ 2.5) mount partition2 as ro ~ 2.6) let all processes know to redirect all new requests to the new instance running on the new port ~ 2.7) wait for all web users with previously open and ongoing sessions accessing partition1 to phase out (in order to make sure that there are not going to be any inconsistencies duw to the new data) and then ~ 2.8) unmount partition1 ~ 2.9) mount partition1 as rw ~ 2.10) update partition1 (at this time no user should be accessing it anymore) with the data on partition2 ~ 3) unmount partition1 (and possibly even physically unpower the hard drives themselves) ~ partition1 becomes partition2 for the next update cycle ~ Is this safe generally speaking? ~ Has any of you guys heard of something like that or how to basically achieve the same thing by other, more standard means? ~ thanks lbrtchx
On Wed, Jun 18, 2008 at 8:44 PM, Albretch Mueller <lbrtchx@gmail.com> wrote: > ~ > I am developing a J2EE application that needs for users to only read > DB tables. All queries are select ones, no updates, no inserts, no > deletes for web users, so I keep this ro DB tables in certain > partitions which I mount as ro > ~ > For performance reasons I keet the DB in the same box as the server > ~ > Now, the data in those tables need to actually be updated not by web > users, but from the back end and not that often at all, say just once > of twice a day in a totally controlled way and updates shouldn't take > long. Just some insert stats in single tables [very roundabout approach deleted] > ~ > Has any of you guys heard of something like that or how to basically > achieve the same thing by other, more standard means? Create a user for the J2EE app, and configure that app to connect as that user. Grant only SELECT privileges to that user on the tables it needs to see. Create a second user for the backend app and grant it SELECT, INSERT, UPDATE, and DELETE on those tables. There should be no need to mess about with read-only partitions or anything like that. SQL permissions should be all you need. -Doug
On Wed, Jun 18, 2008 at 10:36 PM, Douglas McNaught <doug@mcnaught.org> wrote: > . . . SQL permissions should be all you need. > > -Doug ~ What about the security implications? Is the J2EE server enough to control access to the DB? ~ Java does not allow for buffer overruns and such hacking venues, but what would happen if a hacker somehow gains access to the data directly, bypassing the J2EE server? ~ The thing is that for performance reasons I could not nicely model highly hierarchical data objects using SQL tables, so I have to come up with complicated data structures that I serialize and keep in fields as BLOBs ~ And yes, I know, my approach was very roundabout ;-) I was trying to fancy a hacker-proof scenario and it would all be based on scripts ~ I think SCSI disks even have a switch to -physically- avoid writing to them. I'd wish I could use such features in regular SATA disks. I definitely trust Physics ~ thanx lbrtchx