Re: Postgres on shared network drive - Mailing list pgsql-general

J Ottery wrote:
> Using windows XP and TCP/IP network.
>
> I install PostgreSQL on a client PC and put the data files on a
> networked drive (instead of the local drive).  Postgres as user and
> localport. This works well.

I wouldn't personally trust this setup.

> Now I install postgresSQL on another client machine and point it to
> the same data directory on the network drive.

That's an *amazingly* bad idea, and shows that you've misunderstood how
PostgreSQL works.

PostgreSQL expects exclusive access to its data directory. No other
programs, be they other copies of postgresql or anything else, should
ever be allowed any kind of access to the postgresql data directory
except for the very few special cases mentioned in the documentation
(like archive_wal).

If you want multiple users, you need to have them connect over the
network to the same postgresql server. Say you have three computers and
you want them to have access to a database. Here's how it should work:

computer 1
(has postgresql server installed and enabled for tcp/ip connections)
(clients connect to "computer1")

computer 2
(clients connect to "computer1")

computer 3
(clients connect to "computer1")

... and so on. There is *no* way for computer 2 or computer 3 to access
the postgresql data files directly, only via the postgresql server
process. In fact, other programs on computer 1 should also be denied
access to the postgresql data directory.

PostgreSQL is a relational database management system. It's intended as
the sole way to access its data. If you need a simpler shared database
system where all programs open the database files directly over a
network share, there are options out there. SQLite might work, for example.

It's also not a great idea to put it on a network share. You should
explicitly verify that the database system you use is safe to use on the
particular type of network share you are using, because they often cause
problems.

Ideally the postgresql database should be on a server machine that's on
all the time. The postgresql data directory *must* not be shared. All
the client computers should connect to the postgresql server over the
network using a postgresql client like pgODBC, pgJDBC, the psql command
line, etc when they need database access. If you don't have one, maybe
it's time to get a small workgroup server to handle all your file
sharing, database requirements, etc. Even a spare PC will do for a
server in a pinch. Remember to keep good backups!

> Will this setup work OK for multiple / concurrent users and accessing
> the same tables on either of the client machines or is there something
> else I need to be aware of?

No, as far a I know it'll break horribly and eat all your data.

--
Craig Ringer