Thread: Sharing data directories between machines

You can't just make them share the data dir (for example: what about
caches in memory?)

Probably what you want is streaming replication:

http://wiki.postgresql.org/wiki/Streaming_Replication

Regards.

On Tue, Aug 6, 2013 at 7:45 PM, JD Wong <jdmswong@gmail.com> wrote:
> Hi all!
>
> I have two servers, which share a large mounted drive.  I would like to
> share postgres databases between them dynamically so that when one makes
> changes, they are immediately available in the other.
>
> I tried moving the data directory over to the mounted drive, and pointing
> both postgresql.confs to that one.  I was able to have both access the same
> databases, but they can't share changes.  It's like they're running on two
> separate data directories, even though show data_directory reports the same
> path for each.
>
> How can I make them play nicely?
>
> Thanks!
> -JD



--

Davide Setti
code: http://github.com/vad

On Aug 6, 2013, at 10:45 AM, JD Wong <jdmswong@gmail.com> wrote:

> Hi all!
>
> I have two servers, which share a large mounted drive.  I would like to share postgres databases between them
dynamicallyso that when one makes changes, they are immediately available in the other.   
>
> I tried moving the data directory over to the mounted drive, and pointing both postgresql.confs to that one.  I was
ableto have both access the same databases, but they can't share changes.  It's like they're running on two separate
datadirectories, even though show data_directory reports the same path for each. 

That'll likely damage your database, probably irrecoverably.

> How can I make them play nicely?

You can't do it by sharing the disk files, at all. The two instances will trash each others data.

If you want multiple database servers for redundancy, or you want to be able to offload read access to a second server,
takea look at hot standby servers. http://www.postgresql.org/docs/9.2/static/high-availability.html 

If you really want to be able to write to either database and have it replicated to the other one immediately, you
shouldprobably rethink what you need. It can be done (with multimaster replication) but it's almost never the right
approach.

Cheers,
  Steve

On 8/6/2013 10:45 AM, JD Wong wrote:
>
> I tried moving the data directory over to the mounted drive, and
> pointing both postgresql.confs to that one.  I was able to have both
> access the same databases, but they can't share changes.  It's like
> they're running on two separate data directories, even though show
> data_directory reports the same path for each.


wow, if you actually did that, I'll be really really surprised if you
don't have massive data corruption already.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 2013-08-06 15:28, John McKown wrote:
> Me again. Perhaps what is needed, in this case, is for a "distributor"
> which "looks like" a PostgreSQL server running on a given system (i.e.
> it is listening on the default TCPIP ports and UNIX sockets and
> <insert whatever for Windows>) but would simply act like a pipe to and
> from the real server running somewhere else.
That sounds like a job for pgpool.

On Tue, Aug 06, 2013 at 12:08:57PM -0700, Steve Atkins wrote:
>
> You can't do it by sharing the disk files, at all. The two instances will trash each others data.
>

Right.  This is why products that do this sort of hardware fail-over
have something akin to the "STONITH" (Shoot The Other Node In The
Head) program, to make sure the failed box is actually turned off
before the failover box starts up.[1]

Given Postgres's hot standby features today, you don't need this sort
of solution, I'll wager.

Best,

A


[1] Or anyway, they have those kinds of program unless they are
designed and sold by IBM, whose product once made my afternoon more
amusing than I wanted by blowing up the data area on fail over not
once, or even twice, but three times.  (This was attributed to
operator error, because the operator had dared to run the failover
sequence.)


--
Andrew Sullivan
ajs@crankycanuck.ca

On 8/6/2013 12:24 PM, John McKown wrote:
> Too bad the PostgreSQL server cannot be in a "Federated" configuration
> so that it "knows" which databases are controlled by which server and
> automatically passes the requests around. Or does it and I just can't
> find it?
> http://en.wikipedia.org/wiki/Federated_database_system
> IBM's DB2 can do this.

run pgbouncer on each system connected to the real database.

its a lot cheaper than DB2, too!!



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast