Re: Running Postgres Daemons with same data files - Mailing list pgsql-admin

Hello Bhartendu,

It happens that I was just talking to Sam on irc, and he's gone to lunch,
so I'll have a shot at this.

This should never work for any respectable DBMS.  The DBMS is what
manages access to the data files.  The DBMS does the locking and
concurrency control, and state information about transactions in progress
is held within the DBMS.  Since PostgreSQL uses far more sophisticated
transaction mechanisms than table level locking, it's not as simple as
locking files.  You're pretty much guaranteeding yourself serious data
corruption problems if you try this, since two DBMS instances will try to
maintain independent transaction state information, and end up mangling
each other's data.  Seriously.

Further, since you're relying on a single storage point, you're not
actually implementing HA at all.  You're also going to have nasty issues
with write synchronisation with NAS.  It's strongly recommended that DBMS
servers run databases only on physically local storage, otherwise there
are too many layers of data shuffling between the DBMS server and the
physical disk.  Data will get lost and corrupted sooner or later.

I'd suggest that you take a serious look at what your actual availability
requirements are.  What are the potential costs of downtime?  What will
you do if the NAS switch fails for instance, in the case you're trying to
construct?  It happens.  And most organisations don't carry spare ones
lying around, because they're expensive things to have sitting idle.

General rules with almost any proper RDBMS you care to name:  Use local
storage, not NAS.  You get a lot more bang for the buck in the
availability stakes by using good-quality, well maintained hardware and
software than by trying to do exotic things with replication (more about
this below).  You can consider using disk mirroring (RAID 1) or RAID 5 in
order to reduce the probability of having to do time-consuming restores.

Why do you need sophisticated HA?  IMVHO the only people who _really_ need
it are people like nuclear power stations, air traffic control (if only!),
hospitals and the like.  It's nice for global businesses too, which have
to provide global business services 24/7.  How were you planning to do the
failover switching?

In terms of replication, this can be done (with difficulty still) but
always (always!) between two database servers each of which keeps a local
copy of the data, with something erserv sitting between them synchronising
transactions.  You might want to look at that.

But seriously -- most applications don't need HA solutions.  PostgreSQL
running on decent, well-maintained hardware and software is perfectly
capable of achieving 99%+ uptime, which is more than most applicaitons
need.  (And I don't say that idly, we're running it on antique, creaky SGI
Challenges and achieving that kind of uptime.  If we were to put it on
really good new boxes we'd exceed that easily).

If you really, really do need an HA solution, then I'd hunt around for
someone to add to your team who has extensive experience in this kind of
thing, since it's all too easy otherwise to unwittingly leave in lots of
single points of failure.  (Have you considered multiple independent
UPSes?  Communications lines? NAS switches like I said (and you shouldn't
be using NAS for PG data!), application servers (whatever your application
may be) etc.?)

Good luck!

Hal


On Tue, 9 Dec 2003, Bhartendu Maheshwari wrote:

> Dear Sam,
>
> Thank you for the quick response.
>
> Can I you tell me why its not possible, it is possible with mysql then
> why not with postgres. Actually I am working on High Avaibility
> framework, and its our need, we can't make a separate database server. I
> want to read/write and then close the file, very simple isn't? So how
> can I achieve in postgres? Please help me.
>
> regards
> bhartendu
>