Re: Running Postgres Daemons with same data files

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tuesday 09 December 2003 08:21 pm, Bhartendu Maheshwari wrote:
> Dear Hal, Frank, Oli and all,
>
> I understand what you all trying to say, I know this is not good way of
> designing, but we are planning for using the database for the keeping
> mobile transactions and at the same time we need to provided the HA
> solutions. The one solution i derive from the discussion that uses one
> server and multiple clients but the issue in this if the system in which
> database server was running get down the its all the way no use of HA
> and load balancing, since without data the other one can't do anything.

Is the NAS server redundant ? If not it's not HA anyways.
If there is a problem with the NAS or the network itself (say someone
accidentially cuts a bunch of network wires) - what do you do ?
I don't see a big difference between the one server or the other server or the
network going down. Unless ALL components in your network are redundant and
have failover capabilities (for example one NAS automatically replacing the
other one if it fails) you don't have high availibility.

What exactly do you mean by "mobile transactions" ?
The easiest way that probably comes close to what you intend to do is to have
a second server stand by and take over (i.e. mount the NAS storage and start
a postmaster as well as taking over the IP address of the original machine)
the moment the primary server fails. That will still disrupt all queries
currently in progress, but at least things can be used immediately after the
failure.
Still, the NAS storage is a huge point of failure. What you failed to realize
in the list below is that via the network to a remote storage a lot of caches
and buffers are involved. I bet you won't be able to tell exactly when what
piece of data has been physically written to the disk. Even if you close the
files some information could still hang around in some buffer until the
storage array feels it's time to actually write that stuff.

What you are trying to achive is the classic "replication" approach. Replicate
the database to a second server and have that one take over if the first one
fails. Look into the replication projects on gborg - that's more likely to
give you a workable solution.



>
> What I have in mind is the following implementation:-
>
> Step 1 :- Mount the data files from NAS server.
> Step 2 :- start the postgres with the mounted data.
> Step 3 :- Lock the data files by one server.
> Step 4 :- do the database operation.
> Step 5 :- Commit in the database files.
> Step 6 :- Unlock the database files
> Step 7 :- Now the other one can do the same way.
>
> Or if anybody have other solution for this, please suggest. How can I
> commit the data into database files and flushing cache with the latest
> data files contents? Is there any command to refresh both.
>
> Thank you Hal for
> *************************************************************************
> 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.
> *************************************************************************
>
> If you really have someone to help me in this regards, please I need his
> help in this issue and want to derive a common techniques so that
> everyone can use this.
>
> regards
> bhartendu
>
> On Tue, 2003-12-09 at 18:20, Halford Dace wrote:
> > 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
> >
> > ---------------------------(end of
>
> broadcast)---------------------------
>
> > TIP 7: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

- --
    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/1qo8jqGXBvRToM4RAil5AJ9SbQ3oCdH7WhVMzsJSEGNFgyGO/gCgwdGj
3+Tp56/pgQz4gLxlGTO0M4k=
=fyS0
-----END PGP SIGNATURE-----