Thread: Multiple backends on a single physical database

Multiple backends on a single physical database

From
Anuradha Ratnaweera
Date:
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


Re: Multiple backends on a single physical database

From
Vincent Janelle
Date:
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
>
>
>




Re: Multiple backends on a single physical database

From
Bruce Momjian
Date:
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

Re: Multiple backends on a single physical database

From
"Josh Goldberg"
Date:
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
>


Re: Multiple backends on a single physical database

From
Chris Miles
Date:
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.


Re: Multiple backends on a single physical database

From
Bruce Momjian
Date:
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

Re: Multiple backends on a single physical database

From
Chris Miles
Date:
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.


Re: Multiple backends on a single physical database

From
Tom Lane
Date:
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

Re: Multiple backends on a single physical database

From
Ragnar Kjørstad
Date:
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

Re: Multiple backends on a single physical database

From
Ragnar Kjørstad
Date:
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

Re: Multiple backends on a single physical database

From
Chris Miles
Date:
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/

Re: Multiple backends on a single physical database

From
Jeff
Date:
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
-------------------------------------------------------------------------------



Re: Multiple backends on a single physical database

From
"Chad R. Larson"
Date:
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