Thread: two postgres server seeing the same data

two postgres server seeing the same data

From
"Jaime Casanova"
Date:
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

Re: two postgres server seeing the same data

From
"Dave Page"
Date:
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

Re: two postgres server seeing the same data

From
"Joshua D. Drake"
Date:
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


Re: two postgres server seeing the same data

From
Andrew Sullivan
Date:
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

Re: two postgres server seeing the same data

From
"Jaime Casanova"
Date:
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

Re: two postgres server seeing the same data

From
"Scott Marlowe"
Date:
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.

Re: two postgres server seeing the same data

From
"Scott Marlowe"
Date:
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

Re: two postgres server seeing the same data

From
"Joshua D. Drake"
Date:
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


Re: two postgres server seeing the same data

From
"Jaime Casanova"
Date:
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

Re: two postgres server seeing the same data

From
"Scott Marlowe"
Date:
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.

Re: two postgres server seeing the same data

From
Andrew Sullivan
Date:
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