Thread: question about readonly instances

On Wed, May 18, 2011 at 7:21 AM, Szymon Guz <mabewlun@gmail.com> wrote:
> I've got a question about quite a strange configuration.
> I was asked if we can have one storage, with one data directory where one
> postgresql instance writes data, and many other instances read those.
> Is that possible without any replication and copying data?

no.

W dniu 2011-05-18 13:21, Szymon Guz pisze:
> Hi,
> I've got a question about quite a strange configuration.
> I was asked if we can have one storage, with one data directory where one postgresql instance
> writes data, and many other instances read those.
> Is that possible without any replication and copying data?

Why do they think they need that?

The basic setup can do that.

Configure one Postgres user to have read/write access to all database tables
Configure another Postgres user to have read-only access to all database tables

Clients that need read-only access are given the read-only user credentials while the writers are given the read-write
usercredentials. 

With the way PostgreSQL works the "data directory" is the limiting factor so any multiple-server/single-data
configurationis undesirable even if it were technically possible (which I do not believe it is). 

David J.


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Ireneusz Pluta
> Sent: Wednesday, May 18, 2011 4:22 PM
> To: Szymon Guz
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] question about readonly instances
>
> W dniu 2011-05-18 13:21, Szymon Guz pisze:
> > Hi,
> > I've got a question about quite a strange configuration.
> > I was asked if we can have one storage, with one data directory where
> > one postgresql instance writes data, and many other instances read those.
> > Is that possible without any replication and copying data?
>
> Why do they think they need that?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 05/19/2011 04:33 AM, Szymon Guz wrote:
>
>
> On 18 May 2011 22:22, Ireneusz Pluta <ipluta@wp.pl
> <mailto:ipluta@wp.pl>> wrote:
>
>     W dniu 2011-05-18 13:21, Szymon Guz pisze:
>
>         Hi,
>         I've got a question about quite a strange configuration.
>         I was asked if we can have one storage, with one data directory
>         where one postgresql instance writes data, and many other
>         instances read those.
>         Is that possible without any replication and copying data?
>
>
>     Why do they think they need that?
>
>
> They've got some quite nice and huge storage and it would be nice to use
> it from many different machines running postgreses.
> Another option is Oracle which can do that.

If you're thinking of Oracle RAC: be careful. Anecdotal reports I've
heard suggest that a RAC cluster needs to be about 3 machines before it
equals the performance of a single standalone Oracle instance on same
kind of hardware. I have no personal experience with this, though, and
am under the impression that the people I've heard talking about it were
referring to multi-master setups. It's possible that single-master
setups with read-only slaves are more efficient. It's also possible that
they were just wrong. All I'm saying is that you should investigate
carefully.

--
Craig Ringer

On 05/18/11 11:39 PM, Szymon Guz wrote:
>
> thanks for the answer. It is not a problem to have 3 oracle instances,
> in fact there will be hundreds of them probably, but could also be
> hundreds of Postgres instances :)

do you have any idea how EXPENSIVE 100s of Oracle RAC nodes are annually?

to share block storage between cluster nodes requires a cluster-aware
file system, as conventional file systems do not expect the disk files
to change behind their back.       these cluster-aware file systems have
a bunch of overhead in maintaining cache coherency between nodes, and such.

as far as I know, postgres can not be run on a read only file system,
further its shared_buffer caches couldn't possibly be aware of other
nodes modifying blocks that happen to be cached.

> I have no idea how expensive it is, but fortunately that's not my
> problem :) I was only asked about the possibility of running Postgres
> that way.
> And by the way: how expensive is that?

something like US$23000 per CPU core for the RAC option for Oracle
Enterprise.  so a 100 node cluster with dual quadcore cpus would be 800
CPUs would be ....  my head hurts.   oh, thats on top of the oracle
enterprise license ($47K/core), and features like Partitioning are extra
too.    support is about 25%/year additional.

see
http://www.oracle.com/us/corporate/pricing/technology-price-list-070617.pdf
for the base pricing.  if you're a big corporation that uses a *LOT* of
oracle, you might get this cut in half.  or not, depending on Oracle's
accountants estimations of your ability to be bled for more.

On 05/19/2011 03:25 PM, John R Pierce wrote:
>
>> I have no idea how expensive it is, but fortunately that's not my
>> problem :) I was only asked about the possibility of running Postgres
>> that way.
>> And by the way: how expensive is that?
>
> something like US$23000 per CPU core for the RAC option for Oracle
> Enterprise. so a 100 node cluster with dual quadcore cpus would be 800
> CPUs would be .... my head hurts. oh, thats on top of the oracle
> enterprise license ($47K/core), and features like Partitioning are extra
> too. support is about 25%/year additional.

Surely that has to be "marketing prices" ... you know, the ones they
offer to knock down to 25% early in negotiations to give you a "special
deal" and you land up paying about 5% of by the time you sign, so you're
convinced you're getting some amazingly good price when you're just
paying what everyone pays.

Surely. Please tell me it's so. Our government (Australia) uses lots of
Oracle, and while I knew it was expensive, it's a whole new level of
horror to think it's that kind of money.

Hopefully it turns out to be more like Microsoft. Here in Australia,
Windows 7 Pro OEM costs $149 and Office 2010 pro costs 395.00 (!!) . On
the Microsoft website, a windows 7 pro *upgrade* costs $399 and Office
2010 Pro costs 899.00. The pricing differences are jaw-droppingly
insane. Yet the OEM prices I listed are the _most_ that almost anybody
will pay; most people pay much less than that by buying Windows and
Office via a large tier-1 OEM vendor that will often pay $50 or less for
Windows. I'm not saying it's fair or reasonable, just that the list
price on the vendor website has nothing to do with the real price anyone
actually pays.

In addition to allowing people to feel like they're getting big
discounts, the other reason for insanely high list prices appears to be
tax ... er ... adjustment. If you "donate" 100 licenses of your software
to a school/non-profit/etc, you want to put the largest possible dollar
value on that donation to claim it as a tax write-off. So you publish
jaw-droppingly inflated RRPs, and use those to calculate your donation.
Wow, we just donated 100 * 899 = $89,000 of software to that school for
a real cost of - essentially nothing.

Health care pricing in the USA seems to be similarly affected by
list-price inflation to permit discounting. Insurers want to claim "95%
discounts!" so official list prices are massively, monsterously inflated
to make such discounts possible. Pity for you if you don't have insurance...

--
Craig Ringer

Szymon Guz wrote:
>>> I've got a question about quite a strange configuration.
>>> I was asked if we can have one storage, with one data directory where one postgresql
>>> instance writes data, and many other instances read those.
>>> Is that possible without any replication and copying data?
>>
>> Why do they think they need that?
>
> They've got some quite
> nice and huge storage and it would be nice to use it from many different machines running postgreses.

I'm surprised to hear that.
Normally storage is the bottleneck for a database, i.e. you would
not gain performance if more than one database ran against the same storage.

> Another option is Oracle which can do that.

I have not heard that RAC is a performance booster.
It's more like a protection against certain types of hardware failure.

Yours,
Laurenz Albe