Discussion:
Running Postgres Daemons with same data files
(too old to reply)
Bhartendu Maheshwari
2003-12-09 11:17:09 UTC
Permalink
Dear All,

I am working in Linux 8.0 and running postgresql 7.2. I am trying to
access same data files from the two postgres daemons. I mean there are
two PC's running with postgres and one NAS server where data files are
kept. To run postgres on the machines I first mount the NAS file system
and then run it like

shell>postmaster -D $(PATH_TO_DATA_FILE)

Daemons are running well but there is problems with synchronization of
the data files i.e when i insert some tuples in the tables its not
immediately writing then to file its kept in the cache only and when I
try to see from the other machine its displaying the old tuples.

I want after every transaction or query the database update the data
files, how can I do this? And always read from the data files for select
operations. My main aim is no cache operation to use the postgres as
file system(for every operation file operations only), I know it will
degrade the database performance but its the requirement OR if there is
any other way by which I can achieve this, please tell me.

regards
bhartendu







---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Sam Barnett-Cormack
2003-12-09 11:43:32 UTC
Permalink
Don't do that!!!! This absolutely cannot be done in any sane way.

Instead, consider locating the database itself on one server, and then
having multiple other servers running the client application.
Post by Bhartendu Maheshwari
Dear All,
I am working in Linux 8.0 and running postgresql 7.2. I am trying to
access same data files from the two postgres daemons. I mean there are
two PC's running with postgres and one NAS server where data files are
kept. To run postgres on the machines I first mount the NAS file system
and then run it like
shell>postmaster -D $(PATH_TO_DATA_FILE)
Daemons are running well but there is problems with synchronization of
the data files i.e when i insert some tuples in the tables its not
immediately writing then to file its kept in the cache only and when I
try to see from the other machine its displaying the old tuples.
I want after every transaction or query the database update the data
files, how can I do this? And always read from the data files for select
operations. My main aim is no cache operation to use the postgres as
file system(for every operation file operations only), I know it will
degrade the database performance but its the requirement OR if there is
any other way by which I can achieve this, please tell me.
regards
bhartendu
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Bhartendu Maheshwari
2003-12-09 12:33:31 UTC
Permalink
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
Post by Sam Barnett-Cormack
Don't do that!!!! This absolutely cannot be done in any sane way.
Instead, consider locating the database itself on one server, and then
having multiple other servers running the client application.
Post by Bhartendu Maheshwari
Dear All,
I am working in Linux 8.0 and running postgresql 7.2. I am trying to
access same data files from the two postgres daemons. I mean there are
two PC's running with postgres and one NAS server where data files are
kept. To run postgres on the machines I first mount the NAS file system
and then run it like
shell>postmaster -D $(PATH_TO_DATA_FILE)
Daemons are running well but there is problems with synchronization of
the data files i.e when i insert some tuples in the tables its not
immediately writing then to file its kept in the cache only and when I
try to see from the other machine its displaying the old tuples.
I want after every transaction or query the database update the data
files, how can I do this? And always read from the data files for select
operations. My main aim is no cache operation to use the postgres as
file system(for every operation file operations only), I know it will
degrade the database performance but its the requirement OR if there is
any other way by which I can achieve this, please tell me.
regards
bhartendu
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html
Frank Finner
2003-12-09 12:47:05 UTC
Permalink
On Tue, 9 Dec 2003 18:03:31 +0530
Post by Bhartendu Maheshwari
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
It seems MySQL tries every attempt to crash a database.

Seriously: What do you think will happen to the database files, if you
try to insert/update on the same files at the same time from two
different engines? Exactly: This would not only crash a database, this
would shatter it to thousands of pieces, not recoverable.

Even if you do only SELECTS from one engine, this cannot be synchronized
in any way. And by the way: What reason do you have for another engine
just doing selects? Why not use a client connecting to the one and only
engine, which can do everything (INSERTs, UPDATEs, SELECTs) at (nearly)
the same time? This is not XBase, where you have to look after locking
yourself - let the engine do the dirty work like file locking and
determine, who´s next to do an operation. Just connect to it with
several clients and work with these.

Regards, Frank.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Halford Dace
2003-12-09 12:50:05 UTC
Permalink
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
Post by Bhartendu Maheshwari
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
Chris Travers
2003-12-09 13:14:00 UTC
Permalink
Case in point:

MS Access was designed to have multiple database managers manipulating
the files themselves directly and uses another file for locking
information. However-- as anyone who has ever worked with the process
will tell you: Don't do it. Data corruption (often unrecoverable) will
result.

The lessons we have learned from MS Access are:
1) Don't have 2 unrelated backends trying to access the same data and
2) Don't do it across a network.

IMO, this shows a fundamental design flaw in MS Access at least given
how it is marketed.

Don't try to do the same with PostgreSQL.

Best Wishes,
Chris Travers


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Sam Barnett-Cormack
2003-12-09 14:26:52 UTC
Permalink
Post by Chris Travers
MS Access was designed to have multiple database managers manipulating
the files themselves directly and uses another file for locking
information. However-- as anyone who has ever worked with the process
will tell you: Don't do it. Data corruption (often unrecoverable) will
result.
1) Don't have 2 unrelated backends trying to access the same data and
2) Don't do it across a network.
IMO, this shows a fundamental design flaw in MS Access at least given
how it is marketed.
That all said, anyone who tells you MS Access is really an RDBMS should
lose their job...
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Bhartendu Maheshwari
2003-12-10 04:21:22 UTC
Permalink
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.

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
Post by Halford Dace
Hello Bhartendu,
It happens that I was just talking to Sam on irc, and he's gone to
lunch,
Post by Halford Dace
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
Post by Halford Dace
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
Post by Halford Dace
maintain independent transaction state information, and end up
mangling
Post by Halford Dace
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
Post by Halford Dace
with write synchronisation with NAS. It's strongly recommended that
DBMS
Post by Halford Dace
servers run databases only on physically local storage, otherwise
there
Post by Halford Dace
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
Post by Halford Dace
requirements are. What are the potential costs of downtime? What
will
Post by Halford Dace
you do if the NAS switch fails for instance, in the case you're trying
to
Post by Halford Dace
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
Post by Halford Dace
storage, not NAS. You get a lot more bang for the buck in the
availability stakes by using good-quality, well maintained hardware
and
Post by Halford Dace
software than by trying to do exotic things with replication (more
about
Post by Halford Dace
this below). You can consider using disk mirroring (RAID 1) or RAID 5
in
Post by Halford Dace
order to reduce the probability of having to do time-consuming
restores.
Post by Halford Dace
Why do you need sophisticated HA? IMVHO the only people who _really_
need
Post by Halford Dace
it are people like nuclear power stations, air traffic control (if
only!),
Post by Halford Dace
hospitals and the like. It's nice for global businesses too, which
have
Post by Halford Dace
to provide global business services 24/7. How were you planning to do
the
Post by Halford Dace
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
Post by Halford Dace
copy of the data, with something erserv sitting between them
synchronising
Post by Halford Dace
transactions. You might want to look at that.
But seriously -- most applications don't need HA solutions.
PostgreSQL
Post by Halford Dace
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
Post by Halford Dace
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
Post by Halford Dace
thing, since it's all too easy otherwise to unwittingly leave in lots
of
Post by Halford Dace
single points of failure. (Have you considered multiple independent
UPSes? Communications lines? NAS switches like I said (and you
shouldn't
Post by Halford Dace
be using NAS for PG data!), application servers (whatever your
application
Post by Halford Dace
may be) etc.?)
Good luck!
Hal
Post by Bhartendu Maheshwari
Dear Sam,
Thank you for the quick response.
Can I you tell me why its not possible, it is possible with mysql
then
Post by Halford Dace
Post by Bhartendu Maheshwari
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
Post by Halford Dace
Post by Bhartendu Maheshwari
want to read/write and then close the file, very simple isn't? So
how
Post by Halford Dace
Post by Bhartendu Maheshwari
can I achieve in postgres? Please help me.
regards
bhartendu
---------------------------(end of
broadcast)---------------------------
Post by Halford Dace
TIP 7: don't forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Uwe C. Schroeder
2003-12-10 05:08:12 UTC
Permalink
Bhartendu Maheshwari
2003-12-10 09:51:20 UTC
Permalink
Dear UC,

You are right for the HA solution but at the same time we are also
implementing the load balancing solution so we can't have for one Node 2
different processing entity and database as well. We try to provide
solution for HA, load balancing both and in that there are 2 different
processing machine but sharing the common database so that both get the
latest and synchronized data files.

You are right if the NAS is down then everything get down but the
probability for the NAS is down is very less and by this we are able to
provide service for 99% cases and if you are 99% handle cases then you
are providing good service, isn't?

About the cache to file write :- If the database is writting all the
stuff to the files after each transaction then both have one
synchronized set of data file whoever want can acquire the lock and use
and then unlock it. The MySQL have command "flush tables" to enforce the
database to write all the cache contents to the files, Is there anything
similar in postgres? This will definitely degrade the performance of my
system but its much more fast since I have 2 processing unit.

Anyway if somebody have some other solution for the same please help me.
One I got have one common postmaster running on one PC and the two nodes
connect to that server to get the data. Any other please let me know.

regards
bhartendu
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Post by Bhartendu Maheshwari
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.
Post by Bhartendu Maheshwari
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
Post by Halford Dace
Hello Bhartendu,
It happens that I was just talking to Sam on irc, and he's gone to
lunch,
Post by Halford Dace
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
Post by Halford Dace
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
Post by Halford Dace
maintain independent transaction state information, and end up
mangling
Post by Halford Dace
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
Post by Halford Dace
with write synchronisation with NAS. It's strongly recommended that
DBMS
Post by Halford Dace
servers run databases only on physically local storage, otherwise
there
Post by Halford Dace
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
Post by Halford Dace
requirements are. What are the potential costs of downtime? What
will
Post by Halford Dace
you do if the NAS switch fails for instance, in the case you're trying
to
Post by Halford Dace
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
Post by Halford Dace
storage, not NAS. You get a lot more bang for the buck in the
availability stakes by using good-quality, well maintained hardware
and
Post by Halford Dace
software than by trying to do exotic things with replication (more
about
Post by Halford Dace
this below). You can consider using disk mirroring (RAID 1) or RAID 5
in
Post by Halford Dace
order to reduce the probability of having to do time-consuming
restores.
Post by Halford Dace
Why do you need sophisticated HA? IMVHO the only people who _really_
need
Post by Halford Dace
it are people like nuclear power stations, air traffic control (if
only!),
Post by Halford Dace
hospitals and the like. It's nice for global businesses too, which
have
Post by Halford Dace
to provide global business services 24/7. How were you planning to do
the
Post by Halford Dace
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
Post by Halford Dace
copy of the data, with something erserv sitting between them
synchronising
Post by Halford Dace
transactions. You might want to look at that.
But seriously -- most applications don't need HA solutions.
PostgreSQL
Post by Halford Dace
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
Post by Halford Dace
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
Post by Halford Dace
thing, since it's all too easy otherwise to unwittingly leave in lots
of
Post by Halford Dace
single points of failure. (Have you considered multiple independent
UPSes? Communications lines? NAS switches like I said (and you
shouldn't
Post by Halford Dace
be using NAS for PG data!), application servers (whatever your
application
Post by Halford Dace
may be) etc.?)
Good luck!
Hal
Post by Bhartendu Maheshwari
Dear Sam,
Thank you for the quick response.
Can I you tell me why its not possible, it is possible with mysql
then
Post by Halford Dace
Post by Bhartendu Maheshwari
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
Post by Halford Dace
Post by Bhartendu Maheshwari
want to read/write and then close the file, very simple isn't? So
how
Post by Halford Dace
Post by Bhartendu Maheshwari
can I achieve in postgres? Please help me.
regards
bhartendu
---------------------------(end of
broadcast)---------------------------
Post by Halford Dace
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-----
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
John Gibson
2003-12-10 19:16:55 UTC
Permalink
Bhartendu,

In my humble opinion, you would be well served if you listened to all
the nice people on this list.

Use a local disk subsystems with RAID-type storage, use replication to
have a second "standby" system available if the first one fails.

The path you seem anxious to trod will get very muddy and slippery.

Good Luck.

...john
Post by Bhartendu Maheshwari
Dear UC,
You are right for the HA solution but at the same time we are also
implementing the load balancing solution so we can't have for one Node 2
different processing entity and database as well. We try to provide
solution for HA, load balancing both and in that there are 2 different
processing machine but sharing the common database so that both get the
latest and synchronized data files.
You are right if the NAS is down then everything get down but the
probability for the NAS is down is very less and by this we are able to
provide service for 99% cases and if you are 99% handle cases then you
are providing good service, isn't?
About the cache to file write :- If the database is writting all the
stuff to the files after each transaction then both have one
synchronized set of data file whoever want can acquire the lock and use
and then unlock it. The MySQL have command "flush tables" to enforce the
database to write all the cache contents to the files, Is there anything
similar in postgres? This will definitely degrade the performance of my
system but its much more fast since I have 2 processing unit.
Anyway if somebody have some other solution for the same please help me.
One I got have one common postmaster running on one PC and the two nodes
connect to that server to get the data. Any other please let me know.
regards
bhartendu
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org
Bhartendu Maheshwari
2003-12-11 05:03:55 UTC
Permalink
Dear All,

I got all your points, thanks for such a great discussion, Now the last
thing I want is how can I close the data files and flush the cache into
the data files. How can I do this in postgresql????

I will also try with RAID and other suggested way you all suggested.

regards
bhartendu
Post by Halford Dace
Bhartendu,
In my humble opinion, you would be well served if you listened to all
the nice people on this list.
Use a local disk subsystems with RAID-type storage, use replication to
have a second "standby" system available if the first one fails.
The path you seem anxious to trod will get very muddy and slippery.
Good Luck.
...john
Post by Bhartendu Maheshwari
Dear UC,
You are right for the HA solution but at the same time we are also
implementing the load balancing solution so we can't have for one Node 2
different processing entity and database as well. We try to provide
solution for HA, load balancing both and in that there are 2 different
processing machine but sharing the common database so that both get the
latest and synchronized data files.
You are right if the NAS is down then everything get down but the
probability for the NAS is down is very less and by this we are able to
provide service for 99% cases and if you are 99% handle cases then you
are providing good service, isn't?
About the cache to file write :- If the database is writting all the
stuff to the files after each transaction then both have one
synchronized set of data file whoever want can acquire the lock and use
and then unlock it. The MySQL have command "flush tables" to enforce the
database to write all the cache contents to the files, Is there anything
similar in postgres? This will definitely degrade the performance of my
system but its much more fast since I have 2 processing unit.
Anyway if somebody have some other solution for the same please help me.
One I got have one common postmaster running on one PC and the two nodes
connect to that server to get the data. Any other please let me know.
regards
bhartendu
---------------------------(end of broadcast)---------------------------
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Matt Clark
2003-12-11 11:26:22 UTC
Permalink
Post by Bhartendu Maheshwari
I got all your points, thanks for such a great discussion, Now the last
thing I want is how can I close the data files and flush the cache into
the data files. How can I do this in postgresql????
pg_ctl stop






---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Bhartendu Maheshwari
2003-12-11 12:43:07 UTC
Permalink
Dear matt

Don't joke, this for stopping the Postgres Server. I want close the
files in between the database operations. I am looking for some SQL
command like in Mysql command is 'flush tables'. what's in postgres?

regards
bhartendu
Post by Matt Clark
Post by Bhartendu Maheshwari
I got all your points, thanks for such a great discussion, Now the last
thing I want is how can I close the data files and flush the cache into
the data files. How can I do this in postgresql????
pg_ctl stop
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Matt Clark
2003-12-11 12:58:11 UTC
Permalink
Well I wasn't joking.

If you want to get the files on disk into a state where they can be modified by a different postmaster, then you need to shut the
current postmaster down. In theory, maybe, if you only want the second postmaster to have read access, then you could disconnect
all users from the current postmaster and stop accepting any more connections, but I wouldn't do it if I were you.

Maybe I'm missing the point. The only other answer is:

commit;

Because for postgres commit *means* commit the transaction to disk. Of course if you're using a NAS then who knows whether the
files have _really_ been written to disk after an fsync. Postgres just has to believe what the operating system tells it...

Matt
Post by Bhartendu Maheshwari
Don't joke, this for stopping the Postgres Server. I want close the
files in between the database operations. I am looking for some SQL
command like in Mysql command is 'flush tables'. what's in postgres?
regards
bhartendu
Post by Matt Clark
Post by Bhartendu Maheshwari
I got all your points, thanks for such a great discussion, Now the last
thing I want is how can I close the data files and flush the cache into
the data files. How can I do this in postgresql????
pg_ctl stop
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html
Sam Barnett-Cormack
2003-12-11 13:37:01 UTC
Permalink
Post by Matt Clark
Well I wasn't joking.
If you want to get the files on disk into a state where they can be modified by a different postmaster, then you need to shut the
current postmaster down. In theory, maybe, if you only want the second postmaster to have read access, then you could disconnect
all users from the current postmaster and stop accepting any more connections, but I wouldn't do it if I were you.
commit;
Because for postgres commit *means* commit the transaction to disk. Of course if you're using a NAS then who knows whether the
files have _really_ been written to disk after an fsync. Postgres just has to believe what the operating system tells it...
Of course, they might just get written to the WAL... the *only* way to
get the files into a consistent, safe state is to stop the server.
Sorry.
Post by Matt Clark
Post by Bhartendu Maheshwari
Don't joke, this for stopping the Postgres Server. I want close the
files in between the database operations. I am looking for some SQL
command like in Mysql command is 'flush tables'. what's in postgres?
regards
bhartendu
Post by Matt Clark
Post by Bhartendu Maheshwari
I got all your points, thanks for such a great discussion, Now the last
thing I want is how can I close the data files and flush the cache into
the data files. How can I do this in postgresql????
pg_ctl stop
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly
Jeff
2003-12-11 13:39:36 UTC
Permalink
On 11 Dec 2003 18:13:07 +0530
Post by Bhartendu Maheshwari
Dear matt
Don't joke, this for stopping the Postgres Server. I want close the
files in between the database operations. I am looking for some SQL
command like in Mysql command is 'flush tables'. what's in postgres?
regards
bhartendu
forcing a checkpoint or using commit may get those tables flushed out.
(or at least the wal). but the problem is other postmasters on other
machines may not know that data has changed - it could be sitting in a
shared buffer.

Machine A reads block 123, stored in a shared buffer.
Machine B reads block 123, modifies it, writes it back out
Machine A needs block 123, finds it in its shared buffer and uses that
data [which is no longer valid]

Thus you can see the problems.

or another one

Machine A locks row 123 for update
Machine B also wants to lock row 123. How does it know A already has
the lock? (locks are stored in shared memory)

What you really want is multi-master replication. Currently this
doesn't exist on PG (that I know of). There's a JDBC app that simulates
it. (And you could too, to varying degrees of success).

If mysql works, then use it, but I'd be suprised if it didn't suffer a
lot of these same problems as well.

If you seriously need this feature, then you'll either have to A. buy
oracle/whatever or B. pay to fund development of it for PG
--
Jeff Trout <***@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)
Tom Lane
2003-12-11 15:01:08 UTC
Permalink
Post by Bhartendu Maheshwari
I got all your points, thanks for such a great discussion, Now the last
thing I want is how can I close the data files and flush the cache into
the data files. How can I do this in postgresql????
Shut down the postmaster.
Post by Bhartendu Maheshwari
I will also try with RAID and other suggested way you all suggested.
I don't think what people have been telling you has penetrated at all.
You CAN NOT run multiple postmasters concurrently on the same set of
database files. It will not work. It will corrupt your data. There
is no hack that will make it work.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html
William Yu
2003-12-10 17:25:42 UTC
Permalink
Post by Bhartendu Maheshwari
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.
Here's an important question. What exactly is the thinking behind your
load balancing and HA requirements? The reason I'm asking this question
is because there's nuances to what high available means.

As an example, you've got redundant servers but they're all in the same
server room. A fire breaks out and kills everything. Not really HA IMO.
Or you have redundant servers in different rooms/buildings hooked up to
a NAS unit someplace else. A mover knocks the head off the ceiling fire
extinguisher and floods the place (I've seen this happen) killing the
NAS device. Again, not very HA. On the otherhand, if all your users are
housed in same building as the servers where a fire kills the servers
and also stops your users from doing any work, then it's not a problem.

The situation my company is in is we have users all over the U.S.
connecting to our app so to do HA, we needed to put duplicate servers
thousands of miles away from each other. That way, an earthquake in SF
or a terrorist attack in D.C. doesn't bring down our app. And since
traffic was load balanced between both locations, we needed
master-master replication which we had to code in at the app level.
Andrew Rawnsley
2003-12-10 18:34:01 UTC
Permalink
Post by William Yu
Post by Bhartendu Maheshwari
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.
Here's an important question. What exactly is the thinking behind your
load balancing and HA requirements? The reason I'm asking this
question is because there's nuances to what high available means.
As an example, you've got redundant servers but they're all in the
same server room. A fire breaks out and kills everything. Not really
HA IMO. Or you have redundant servers in different rooms/buildings
hooked up to a NAS unit someplace else. A mover knocks the head off
the ceiling fire extinguisher and floods the place (I've seen this
happen) killing the NAS device. Again, not very HA. On the otherhand,
if all your users are housed in same building as the servers where a
fire kills the servers and also stops your users from doing any work,
then it's not a problem.
The situation my company is in is we have users all over the U.S.
connecting to our app so to do HA, we needed to put duplicate servers
thousands of miles away from each other. That way, an earthquake in SF
or a terrorist attack in D.C. doesn't bring down our app. And since
traffic was load balanced between both locations, we needed
master-master replication which we had to code in at the app level.
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
There are 2 basic things to remember about proper HA, by any definition:

1. It is hard.
2. It is expensive.

This is true even for systems which were built with HA/redundancy in
mind - Postgres wasn't. Depending on what NAS device you're talking
about, it may not
have been either (call EMC and see what the price tag is on a
redundant, HA SAN setup. If you can afford that, you can afford the
right tools).

This topic has floated around on the erserver mailing list - either you
have the budget to do HA right, or you don't. If you don't, you have to
be clear
in your specs about what is possible with the tools that you can afford
without trying to jigger inappropriate technology to do something it
really can't
do. Trying to do something that the developers of a product say is not
possible, and selling it as HA, is not going to make you many friends
when
problems arise.


--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org
Patrick Spinler
2003-12-14 05:19:20 UTC
Permalink
Just to add a tidbit on top of this, there are commercial RDBMS's that
do, or at least say they do, clustered database servers. (cough cough
oracle, cough cough ingres)

The products are expensives as all get out, a bastard to set up, and
require truely kickass SAN storage and clustered hardware to be
meaningful. Not, in general, something to be set up on a lark.

You can get an awful lot of what you seem to want with replication.
Have you considered doing so ? For instance, I expect there are
replication products for postgres that would allow a disconnected client
to push transactions up to a master server, then resyncronise itself
with the master.

In terms of HA, in addition to replication solutions, you can also do a
fair to middling job of failover by having a primary and hot backup
database server on a heartbeat/STONITH setup, using a shared, redundant
SAN as a data store. The hot backup would mount the SAN drives on
primary failure, takeover the IP, and start the postmaster.

Of course, just the SAN setup for such a beast is going to run to a
minimum of 5 figures, and any transaction that might be in progres will
be borked. Oh, and this complicates the mobile operations requirement
quite a bit too. :-(

If you feel really really really masocistic, uh, I mean ambitious, you
could start looking at what it would take to port and run a RDBMS server
in a truely clustered, high availablibility environment. It will not be
simple. You have issues of distributed cache coherency, distributed
locking, distributed filesystems/datastory, load balancing on a
by-transaction basis (wouldn't want to send the second part of the same
transaction to a different server ....), recovery from server node
failure, adding server nodes to a running instance, and lots of other
things I haven't thought of.

This is complicated by the fact that there really isn't any mature linux
cluster solution that gives decent support for everything you've asked
for yet. The closest project I'm aware of is SSIC-Linux. Look it up on
sourceforge if you're curious.

Good luck,
-- Pat

p.s. Yes, I was brought up and spoiled on VMS clusters. Still nothing
beats good 'ol RDB for a clustered database solution.
Post by Bhartendu Maheshwari
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.
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Glenn Wiorek
2003-12-15 19:00:55 UTC
Permalink
He might also want to take a look at SUN's grid - the developement kit and
most of the components are free. Is also supports Linux. I bet with two
Postgres servers in sync via replication and the GRID setup to do the switch
over should one fail it would be as low cost HA he could get.


----- Original Message -----
From: "Patrick Spinler" <***@yahoo.com>
To: "Postgres Mailing List" <pgsql-***@postgresql.org>
Cc: "Bhartendu Maheshwari" <***@jataayusoft.com>
Sent: Saturday, December 13, 2003 11:19 PM
Subject: Re: [ADMIN] Running Postgres Daemons with same data files
Post by Patrick Spinler
Just to add a tidbit on top of this, there are commercial RDBMS's that
do, or at least say they do, clustered database servers. (cough cough
oracle, cough cough ingres)
The products are expensives as all get out, a bastard to set up, and
require truely kickass SAN storage and clustered hardware to be
meaningful. Not, in general, something to be set up on a lark.
You can get an awful lot of what you seem to want with replication.
Have you considered doing so ? For instance, I expect there are
replication products for postgres that would allow a disconnected client
to push transactions up to a master server, then resyncronise itself
with the master.
In terms of HA, in addition to replication solutions, you can also do a
fair to middling job of failover by having a primary and hot backup
database server on a heartbeat/STONITH setup, using a shared, redundant
SAN as a data store. The hot backup would mount the SAN drives on
primary failure, takeover the IP, and start the postmaster.
Of course, just the SAN setup for such a beast is going to run to a
minimum of 5 figures, and any transaction that might be in progres will
be borked. Oh, and this complicates the mobile operations requirement
quite a bit too. :-(
If you feel really really really masocistic, uh, I mean ambitious, you
could start looking at what it would take to port and run a RDBMS server
in a truely clustered, high availablibility environment. It will not be
simple. You have issues of distributed cache coherency, distributed
locking, distributed filesystems/datastory, load balancing on a
by-transaction basis (wouldn't want to send the second part of the same
transaction to a different server ....), recovery from server node
failure, adding server nodes to a running instance, and lots of other
things I haven't thought of.
This is complicated by the fact that there really isn't any mature linux
cluster solution that gives decent support for everything you've asked
for yet. The closest project I'm aware of is SSIC-Linux. Look it up on
sourceforge if you're curious.
Good luck,
-- Pat
p.s. Yes, I was brought up and spoiled on VMS clusters. Still nothing
beats good 'ol RDB for a clustered database solution.
Post by Bhartendu Maheshwari
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.
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
*************************************************************************
Post by Patrick Spinler
Post by Bhartendu Maheshwari
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.
*************************************************************************
Post by Patrick Spinler
Post by Bhartendu Maheshwari
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Loading...