Обсуждение: two postgres server seeing the same data
Hi, a client of mine is obsessed with the idea of having two postgres servers looking at the same partition of data... i think i can configure two servers pointing to the same $PGDATADIR and let one off and the other on but is it possible for load balancing, i mean with the two servers active? obviously one of the two should be only for reading... my thought is that it's not that beneficial because there will be the same set of disks (even being an storage) and i'm not sure is at all possible because the contents of shared memory in both servers will be different... thoughts? any one has tried that configuration? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On Wed, Dec 3, 2008 at 5:29 PM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > Hi, > > a client of mine is obsessed with the idea of having two postgres > servers looking at the same partition of data... i think i can > configure two servers pointing to the same $PGDATADIR and let one off > and the other on but is it possible for load balancing, i mean with > the two servers active? obviously one of the two should be only for > reading... It won't work, and will (if you even manage to get the second server to start) cause all manner of pain and suffering. Look for a different solution (Slony? Bucardo?) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Wed, 2008-12-03 at 12:29 -0500, Jaime Casanova wrote: > Hi, > > a client of mine is obsessed with the idea of having two postgres > servers looking at the same partition of data... i think i can > configure two servers pointing to the same $PGDATADIR and let one off > and the other on but is it possible for load balancing, i mean with > the two servers active? obviously one of the two should be only for > reading... No. You may have two servers pointed at $PGDATA but at no point can postgresql be running on both at the same time. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Wed, Dec 03, 2008 at 09:43:24AM -0800, Joshua D. Drake wrote: > You may have two servers pointed at $PGDATA but at no point can > postgresql be running on both at the same time. More importantly, if you do this, you will probably be able to get the two postmasters to start up. This will permanently corrupt the data. I know this partly because of experience with a "failover" system whose interlocks failed. Two postmasters, one data area, and no recoverable data. A -- Andrew Sullivan ajs@crankycanuck.ca
On Wed, Dec 3, 2008 at 1:10 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote: > On Wed, Dec 03, 2008 at 09:43:24AM -0800, Joshua D. Drake wrote: >> You may have two servers pointed at $PGDATA but at no point can >> postgresql be running on both at the same time. > > More importantly, if you do this, you will probably be able to get the > two postmasters to start up. This will permanently corrupt the data. is this true even if one of the server just send SELECTs? > I know this partly because of experience with a "failover" system > whose interlocks failed. Two postmasters, one data area, and no > recoverable data. > the worst part of learning the lesson ;) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On Wed, Dec 3, 2008 at 2:23 PM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > On Wed, Dec 3, 2008 at 1:10 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote: >> On Wed, Dec 03, 2008 at 09:43:24AM -0800, Joshua D. Drake wrote: >>> You may have two servers pointed at $PGDATA but at no point can >>> postgresql be running on both at the same time. >> >> More importantly, if you do this, you will probably be able to get the >> two postmasters to start up. This will permanently corrupt the data. > > is this true even if one of the server just send SELECTs? yes.
On Wed, Dec 3, 2008 at 2:29 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Wed, Dec 3, 2008 at 2:23 PM, Jaime Casanova > <jcasanov@systemguards.com.ec> wrote: >> On Wed, Dec 3, 2008 at 1:10 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote: >>> On Wed, Dec 03, 2008 at 09:43:24AM -0800, Joshua D. Drake wrote: >>>> You may have two servers pointed at $PGDATA but at no point can >>>> postgresql be running on both at the same time. >>> >>> More importantly, if you do this, you will probably be able to get the >>> two postmasters to start up. This will permanently corrupt the data. >> >> is this true even if one of the server just send SELECTs? > > yes. even if it sends NO queries. just starting the second postmaster is enough
On Wed, 2008-12-03 at 16:23 -0500, Jaime Casanova wrote: > On Wed, Dec 3, 2008 at 1:10 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote: > > On Wed, Dec 03, 2008 at 09:43:24AM -0800, Joshua D. Drake wrote: > >> You may have two servers pointed at $PGDATA but at no point can > >> postgresql be running on both at the same time. > > > > More importantly, if you do this, you will probably be able to get the > > two postmasters to start up. This will permanently corrupt the data. > > is this true even if one of the server just send SELECTs? Yes. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Wed, Dec 3, 2008 at 4:30 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Wed, Dec 3, 2008 at 2:29 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> On Wed, Dec 3, 2008 at 2:23 PM, Jaime Casanova >> <jcasanov@systemguards.com.ec> wrote: >>> On Wed, Dec 3, 2008 at 1:10 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote: >>>> On Wed, Dec 03, 2008 at 09:43:24AM -0800, Joshua D. Drake wrote: >>>>> You may have two servers pointed at $PGDATA but at no point can >>>>> postgresql be running on both at the same time. >>>> >>>> More importantly, if you do this, you will probably be able to get the >>>> two postmasters to start up. This will permanently corrupt the data. >>> >>> is this true even if one of the server just send SELECTs? >> >> yes. > > even if it sends NO queries. just starting the second postmaster is enough > ok... actually i see no real value in that config (create a single point of contention) but everyone that has used (or at least read about) oracle think is the best thing to do... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On Wed, Dec 3, 2008 at 2:41 PM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > ok... actually i see no real value in that config (create a single > point of contention) but everyone that has used (or at least read > about) oracle think is the best thing to do... Having dealth with RAC, I can safely say that the application points for such a cluster are really pretty narrow, and often it's far cheaper and easier to solve such a problem by just buying a bigger pair of servers and replicating them than to use the shared storage method. I've seen plenty of problems with RAC, especially on linux, having much worse performance than the two big server setup. Plus, given the cost of Oracle RAC licenses, you can build a couple of pretty farking huge pg servers.
On Wed, Dec 03, 2008 at 04:23:15PM -0500, Jaime Casanova wrote: > > is this true even if one of the server just send SELECTs? Yep. A -- Andrew Sullivan ajs@crankycanuck.ca