FAQ
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?


regards
Szymon

Search Discussions

  • Vivek Khera at May 18, 2011 at 7:21 pm

    On Wed, May 18, 2011 at 7:21 AM, 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?
    no.
  • Ireneusz Pluta at May 18, 2011 at 8:29 pm

    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?
  • Szymon Guz at May 18, 2011 at 8:33 pm

    On 18 May 2011 22:22, Ireneusz Pluta 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. Replicating data to another
    directory is not an option, not for this amount of data and the way of
    loading/using data they need.
    I've always done that using replication to different machines and running
    there Postgres on each, I've never heard of this kind of using Postgres.
    That's why I think this is "strange".

    regards
    Szymon
  • Terry Schmitt at May 18, 2011 at 10:39 pm
    I have no idea what type of storage that you are using, but we utilize
    NetApp storage and use Flexclones to create multiple read-only copies of a
    "master" database. The flexclone takes seconds to configure and essentially
    only consume delta space. Works great so far.

    Terry
    On Wed, May 18, 2011 at 1:33 PM, Szymon Guz wrote:


    On 18 May 2011 22:22, Ireneusz Pluta 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. Replicating data to another
    directory is not an option, not for this amount of data and the way of
    loading/using data they need.
    I've always done that using replication to different machines and running
    there Postgres on each, I've never heard of this kind of using Postgres.
    That's why I think this is "strange".

    regards
    Szymon
  • Craig Ringer at May 19, 2011 at 2:08 am

    On 05/19/2011 04:33 AM, Szymon Guz wrote:

    On 18 May 2011 22:22, Ireneusz Pluta 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
  • Szymon Guz at May 19, 2011 at 6:39 am

    On 19 May 2011 04:08, Craig Ringer wrote:
    On 05/19/2011 04:33 AM, Szymon Guz wrote:



    On 18 May 2011 22:22, Ireneusz Pluta <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

    Hi,
    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 :)

    regards
    Szymon
  • John R Pierce at May 19, 2011 at 7:05 am

    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.
  • Szymon Guz at May 19, 2011 at 7:17 am

    On 19 May 2011 09:05, John R Pierce wrote:
    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.

    Well,
    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?

    regards
    Szymon
  • John R Pierce at May 19, 2011 at 7:25 am

    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.
  • Craig Ringer at May 19, 2011 at 8:14 am

    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
  • Albe Laurenz at May 19, 2011 at 8:30 am

    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
  • David Johnston at May 18, 2011 at 8:47 pm
    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 user credentials.

    With the way PostgreSQL works the "data directory" is the limiting factor so any multiple-server/single-data configuration is 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMay 18, '11 at 11:21a
activeMay 19, '11 at 8:30a
posts13
users8
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase