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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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.

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, take a 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 should probably rethink what you need. It can be done (with multimaster replication) but it's almost never the right approach.

Cheers,
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

OOPS - send to Davide directly. Sorry about that. Didn't look at the To:
I'm new here today.

---------- Forwarded message ----------
From: John McKown <john.archie.mckown@gmail.com>
Date: Tue, Aug 6, 2013 at 2:23 PM
Subject: Re: [GENERAL] Sharing data directories between machines
To: Davide Setti <davide.setti@gmail.com>

Given that the user seems to want a single copy of the data base, rather
than two replicated and synchronized copies, wouldn't that indicate that
the clients on all machines need to point to the PostgreSQL server on one
machine? Instead of trying to have a the data base server process running
on both machines and pointing at the same physical data base. The thought
makes me shudder.

If it were me (on Linux or UNIX, I don't know Windows), I'd have a
world-readable file somewhere on the "mounted" drive (shared via NFS or
CIFS I guess). This file would simply contain lines like:

export PGHOST=ip.or.name.of.machine.running.server
export PGPORT=5432 #stand PostgreSQL TCP/IP port

I would then "source" that file in the shell's start up script on all
machines, and also "source" it in the start up scripts for everything else
which uses PostgreSQL. This shared file could be modified whenever the
PostgreSQL server is moved from one machine to another.

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.

On Tue, Aug 6, 2013 at 2:00 PM, Davide Setti <davide.setti@gmail.com> wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
As of next week, passwords will be entered in Morse code.

Maranatha! <><
John McKown

--
As of next week, passwords will be entered in Morse code.

Maranatha! <><
John McKown

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.

--
As of next week, passwords will be entered in Morse code.

Maranatha! <><
John McKown

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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]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.)

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.)
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general