Thread: Multiple backends on a single physical database
I am wondering if it is sane to mount PGDATA over NFS on many
machines and run posmaster backends on them, so that all the
postmasters are using the same database.
Obviously performance is going to suffer because network traffic
is going to be high, but later we may use a distributed filesystem
with replications instead of NFS.
Thanks in advance.
Anuradha
--
Debian GNU/Linux (kernel 2.4.18-xfs-1.1)
There is more simplicity in the man who eats caviar on impulse than in the
man who eats Grape-Nuts on principle.
-- G.K. Chesterton
No. Only one postmaster can access the PGDATA dir at one time. Attempting to run multiple copies will result in data corruption. http://gborg.postgresql.org/project/pgreplication/projdisplay.php contains the gborg project for replication. Anuradha Ratnaweera wrote: >I am wondering if it is sane to mount PGDATA over NFS on many >machines and run posmaster backends on them, so that all the >postmasters are using the same database. > >Obviously performance is going to suffer because network traffic >is going to be high, but later we may use a distributed filesystem >with replications instead of NFS. > >Thanks in advance. > > Anuradha > > >
Vincent Janelle wrote: > No. Only one postmaster can access the PGDATA dir at one time. > Attempting to run multiple copies will result in data corruption. We also don't recommend NFS directories because the NFS file behavior is not 100% the same as a local file system. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
I have just done this. There are three things I have found necessary: 1. Only one backend can use the postmaster.pid file. You must edit the sourcecode for the other backends before you compile it. 2. If you have backends A,B,C and A makes a modification to the data, B and C need to be restarted before they can see the changes. 3. Startup scripts for backends B and C that do not use the postmaster.pid file need to be changed, specifically the method of stopping them. I do a `ps` and awk out the pid to kill it. This is necessary to prevent removing the lockfile from backend A. I plan to use a high speed network storage device to accomplish this setup effectively. ----- Original Message ----- From: "Anuradha Ratnaweera" <anuradha@gnu.org> To: <pgsql-admin@postgresql.org> Sent: Wednesday, October 09, 2002 11:09 PM Subject: [ADMIN] Multiple backends on a single physical database > > I am wondering if it is sane to mount PGDATA over NFS on many > machines and run posmaster backends on them, so that all the > postmasters are using the same database. > > Obviously performance is going to suffer because network traffic > is going to be high, but later we may use a distributed filesystem > with replications instead of NFS. > > Thanks in advance. > > Anuradha > > -- > > Debian GNU/Linux (kernel 2.4.18-xfs-1.1) > > There is more simplicity in the man who eats caviar on impulse than in the > man who eats Grape-Nuts on principle. > -- G.K. Chesterton > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Thu, Oct 10, 2002 at 10:09:55AM -0400, Bruce Momjian wrote: > We also don't recommend NFS directories because the NFS file behavior is > not 100% the same as a local file system. Can you comment on what the incompatabities are? (related to PostgreSQL only). We currently run PostgreSQL on Linux servers with the data on dedicated Netapp filers over NFS. We have had no problems so far and performance is very acceptable. If there is something I need to watch out for, I would like to know. Cheers, Chris.
Chris Miles wrote: > On Thu, Oct 10, 2002 at 10:09:55AM -0400, Bruce Momjian wrote: > > We also don't recommend NFS directories because the NFS file behavior is > > not 100% the same as a local file system. > > Can you comment on what the incompatabities are? (related to PostgreSQL > only). > > We currently run PostgreSQL on Linux servers with the data on dedicated > Netapp filers over NFS. We have had no problems so far and performance > is very acceptable. > > If there is something I need to watch out for, I would like to know. Good question. It is my understanding that fsync, locking, and the order of writes is not guaranteed in NFS like it is for local file systems. I question how well it would handle any of the failure modes that local file systems can withstand. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Sat, Oct 12, 2002 at 11:11:53AM -0400, Bruce Momjian wrote: > Good question. It is my understanding that fsync, locking, and the > order of writes is not guaranteed in NFS like it is for local file > systems. I question how well it would handle any of the failure modes > that local file systems can withstand. Good point. Perhaps that depends on the NFS client and server combination also? I know that Netapp worked with Oracle and both companies guarantee and recommend a Sun/Netapp Oracle over NFS solution (which I have setup in a previous life) and such a solution works very well. Nobody is going to make such a guarantee with postgresql (are they?) so consider us a case study for such a setup. So far, so good, even under heavy load testing. If anybody has any horror stories with postgresql over NFS, please share them. Cheers, Chris.
Chris Miles <chris_pg002@psychofx.com> writes:
> On Sat, Oct 12, 2002 at 11:11:53AM -0400, Bruce Momjian wrote:
>> Good question. It is my understanding that fsync, locking, and the
>> order of writes is not guaranteed in NFS like it is for local file
>> systems. I question how well it would handle any of the failure modes
>> that local file systems can withstand.
AFAIR, we do not use file locking, so the unreliability of NFS locks
doesn't hurt us. What I'd be concerned about is fsync() and sync()
semantics, namely whether data is down to disk when we think it is.
> Nobody is going to make such a guarantee with postgresql (are they?)
> so consider us a case study for such a setup. So far, so good, even
> under heavy load testing.
Load testing is not the issue here; crash testing is. Try pulling the
power plug on your NFS box while the database is under load. If you
can do that repeatedly and not suffer database corruption, then maybe
you have a safe setup. Be sure to try the case of NFS crash immediately
after a CHECKPOINT.
regards, tom lane
On Sat, Oct 12, 2002 at 11:11:53AM -0400, Bruce Momjian wrote: > Chris Miles wrote: > > On Thu, Oct 10, 2002 at 10:09:55AM -0400, Bruce Momjian wrote: > > > We also don't recommend NFS directories because the NFS file behavior is > > > not 100% the same as a local file system. > > > > Can you comment on what the incompatabities are? (related to PostgreSQL > > only). > > > > We currently run PostgreSQL on Linux servers with the data on dedicated > > Netapp filers over NFS. We have had no problems so far and performance > > is very acceptable. > > > > If there is something I need to watch out for, I would like to know. > > Good question. It is my understanding that fsync, locking, and the > order of writes is not guaranteed in NFS like it is for local file > systems. I question how well it would handle any of the failure modes > that local file systems can withstand. 1. fsync will work over nfs if it's configured properly. Use nfs version 3. Don't use "async" mount option; be awere that async used to be default at least on linux. I'm not sure about fdatasync or O_SYNC methods. 2. I don't know what locking, if any, postgresql uses. Flock does not work over NFS at all. Fcntl works, if both the server and the client runs the lock-deamon. 3. order of writes is not guaranteed either locally or over NFS; that's why fsync is used to enforce it when postgresql requires a specific write-order. So; postgresql over NFS should be safe if you are aware of the pitfalls, either with NetApp or other NFS-servers (but make sure the nfs-server as well as the client follows the "rules"). That's not to say it's a good idea - running postgresql over NFS is slower and more expensive than running it on local storage. -- Ragnar Kjørstad Big Storage
On Sat, Oct 12, 2002 at 12:26:51PM -0400, Tom Lane wrote: > Load testing is not the issue here; crash testing is. Try pulling the > power plug on your NFS box while the database is under load. If you > can do that repeatedly and not suffer database corruption, then maybe > you have a safe setup. Be sure to try the case of NFS crash immediately > after a CHECKPOINT. If pulling the plug causes database-corruption that proves that there is something wrong with NFS-setup, but if it _doesn't_ cause database-corruption that doesn't prove that it's safe - it could be that you're just lucky. This should be obvious, but I've seen too many people argument like "I've tested it twice and it didn't break so it must be safe". -- Ragnar Kjørstad Big Storage
We use V3 over TCP for our NFS mounts from Netapp. Mount options are actually: hard,intr,vers=3,proto=tcp,suid,rsize=32768,wsize=32768 Cheers, CM On Sun, Oct 13, 2002 at 11:15:15PM -0700, Chad R. Larson wrote: > I think the version of the NFS protocol you can use is =very= > important. For example, regarding file locking: V1 didn't do it at all > (every transaction was supposed to be stateless, which means persistent > requests were just an error. In V2, support for locking was defined, but > many implementations always returned successful and didn't actually do > anything. I believe that V3 gets locking right, but I don't know about fsync. -- Chris Miles chris@psychofx.com http://www.psychofx.com/chris/
On Sat, 12 Oct 2002, [iso-8859-1] Ragnar Kj�rstad wrote: > > If pulling the plug causes database-corruption that proves that there is > something wrong with NFS-setup, but if it _doesn't_ cause > database-corruption that doesn't prove that it's safe - it could be that > you're just lucky. > > This should be obvious, but I've seen too many people argument like > "I've tested it twice and it didn't break so it must be safe". > Also keep in mind if the Postgres box is running on linux the joys that is what linux does when an nfs mount "goes away" (unpingable). On our machines linux simply blocks in the kernel so you cannot even kill -9 the applications (or unmount the mount!) granted things will resume once nfs comes back.. but by that time your machine may be in Big Trouble(tm) ------------------------------------------------------------------------------ Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ Ronald McDonald, with the help of cheese soup, controls America from a secret volkswagon hidden in the past -------------------------------------------------------------------------------
At 04:19 PM 10/12/02 +0100, Chris Miles wrote:
>If anybody has any horror stories with postgresql over NFS, please share
>them.
I have no horror stories, as we aren't doing such a thing. But it might be
in our future.
I think the version of the NFS protocol you can use is =very=
important. For example, regarding file locking: V1 didn't do it at all
(every transaction was supposed to be stateless, which means persistent
requests were just an error. In V2, support for locking was defined, but
many implementations always returned successful and didn't actually do
anything. I believe that V3 gets locking right, but I don't know about fsync.
You're gonna have to test your environment...
-crl
--
Chad R. Larson (CRL22) chad@eldocomp.com
Eldorado Computing, Inc. 602-604-3100
5353 North 16th Street, Suite 400
Phoenix, Arizona 85016-3228