Thread: Shared disk storage
Hi List Does anyone have any comments, HOWTOs and experience running multiple Postgres servers with a shared disk (SAN) in a Hot standby configuration? Can someone please point me in the direction of any docs on this subject? Thanks in Advance -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
>>[Peter Nixon wrote] : >>Hi List >> Does anyone have any comments, HOWTOs and experience running multiple >> Postgres servers with a shared disk (SAN) in a Hot standby configuration? >> Can someone please point me in the direction of any docs on this subject? >> Thanks in Advance For server availability, then take a look at these http://www.linux-ha.org/ http://www.douzhe.com/article/data/28/83.html http://www.taygeta.com/ha-postgresql.html Shinga
On Mon, Sep 05, 2005 at 12:20:24 +0300, Peter Nixon <listuser@peternixon.net> wrote: > Hi List > > Does anyone have any comments, HOWTOs and experience running multiple > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > Can someone please point me in the direction of any docs on this subject? Be sure to have some failsafe to prevent two servers from running at the same time on the same data. If that ever happens your database will be hosed.
On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > On Mon, Sep 05, 2005 at 12:20:24 +0300, > Peter Nixon <listuser@peternixon.net> wrote: > > Hi List > > > > Does anyone have any comments, HOWTOs and experience running multiple > > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > > > Can someone please point me in the direction of any docs on this subject? > > Be sure to have some failsafe to prevent two servers from running at the > same time on the same data. If that ever happens your database will be hosed. I thought PostgreSQL already had such a safeguard? Or is it only against starting two backends against the same PGDATA on the same machine? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, Sep 06, 2005 at 01:47:42PM -0500, Jim C. Nasby wrote: > On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > > On Mon, Sep 05, 2005 at 12:20:24 +0300, > > Peter Nixon <listuser@peternixon.net> wrote: > > > Hi List > > > > > > Does anyone have any comments, HOWTOs and experience running multiple > > > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > > > > > Can someone please point me in the direction of any docs on this subject? > > > > Be sure to have some failsafe to prevent two servers from running at the > > same time on the same data. If that ever happens your database will be hosed. > > I thought PostgreSQL already had such a safeguard? Or is it only against > starting two backends against the same PGDATA on the same machine? Only in the same machine. I've seen people report corruption because the failover kicked in when it wasn't actually needed. He had to restore from backup AFAIR ... -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "Porque francamente, si para saber manejarse a uno mismo hubiera que rendir examen... ¿Quién es el machito que tendría carnet?" (Mafalda)
On Tue, Sep 06, 2005 at 13:47:42 -0500, "Jim C. Nasby" <jnasby@pervasive.com> wrote: > On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > > On Mon, Sep 05, 2005 at 12:20:24 +0300, > > Peter Nixon <listuser@peternixon.net> wrote: > > > Hi List > > > > > > Does anyone have any comments, HOWTOs and experience running multiple > > > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > > > > > Can someone please point me in the direction of any docs on this subject? > > > > Be sure to have some failsafe to prevent two servers from running at the > > same time on the same data. If that ever happens your database will be hosed. > > I thought PostgreSQL already had such a safeguard? Or is it only against > starting two backends against the same PGDATA on the same machine? Yes, but it is more likely to have problems when there are two machines involved. One is that the file may not be on the cross mounted file system (on FC4 it is in /var/run) and even if it is on the cross mounted file system, there is a good chance the lock file will appear to be stale because the process id is for the other machine. I am not sure if there are other gotchas, but you definitely want to be careful, since a mistake is going to defeat the purpose of having the hot spares.
On Tue, Sep 06, 2005 at 02:58:52PM -0500, Bruno Wolff III wrote: > On Tue, Sep 06, 2005 at 13:47:42 -0500, > "Jim C. Nasby" <jnasby@pervasive.com> wrote: > > On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > > > On Mon, Sep 05, 2005 at 12:20:24 +0300, > > > Peter Nixon <listuser@peternixon.net> wrote: > > > > Hi List > > > > > > > > Does anyone have any comments, HOWTOs and experience running multiple > > > > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > > > > > > > Can someone please point me in the direction of any docs on this subject? > > > > > > Be sure to have some failsafe to prevent two servers from running at the > > > same time on the same data. If that ever happens your database will be hosed. > > > > I thought PostgreSQL already had such a safeguard? Or is it only against > > starting two backends against the same PGDATA on the same machine? > > Yes, but it is more likely to have problems when there are two machines > involved. One is that the file may not be on the cross mounted file > system (on FC4 it is in /var/run) and even if it is on the cross mounted > file system, there is a good chance the lock file will appear to be stale > because the process id is for the other machine. I am not sure if there are > other gotchas, but you definitely want to be careful, since a mistake is > going to defeat the purpose of having the hot spares. Maybe it would be better to keep this in PGDATA (or even a duplicate copy). Holding a write lock on the file should also help ensure that you can tell if it's stale or not. I realize this probably still isn't perfect, but it's probably better than forcing users to find an external means of locking out the other backend. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, Sep 06, 2005 at 17:01:41 -0500, "Jim C. Nasby" <jnasby@pervasive.com> wrote: > > Maybe it would be better to keep this in PGDATA (or even a duplicate > copy). Holding a write lock on the file should also help ensure that you > can tell if it's stale or not. And the end user can do that if they know to look for it. What you get by default is going to come from the distro vender and they may decide to put it in /var/run no matter what the default is when you build from source.
Bruno Wolff III <bruno@wolff.to> writes:
> "Jim C. Nasby" <jnasby@pervasive.com> wrote:
>> Maybe it would be better to keep this in PGDATA (or even a duplicate
>> copy). Holding a write lock on the file should also help ensure that you
>> can tell if it's stale or not.
> And the end user can do that if they know to look for it. What you get by
> default is going to come from the distro vender and they may decide to
> put it in /var/run no matter what the default is when you build from source.
Well, if you know any vendors who move postmaster.pid out of the PGDATA
directory, let us know so we can knock some sense into their heads.
postmaster.pid is specifically a lock on the directory, and moving it
seriously weakens the strength of the lock.
Having said that, I'm not sure I believe in filesystem locks as doing
much to improve security in the case of multiple hosts attached to a SAN
filesystem. Does the locking work at all across hosts, and if it does,
does the lock get released reasonably promptly if the owning host
crashes? This seems like a there's-no-free-lunch situation.
regards, tom lane
On Wed, Sep 07, 2005 at 00:19:19 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Well, if you know any vendors who move postmaster.pid out of the PGDATA > directory, let us know so we can knock some sense into their heads. > postmaster.pid is specifically a lock on the directory, and moving it > seriously weakens the strength of the lock. I thought Fedora did, but there are actually two files, one in /var/run and another in /var/lib/pgsql/data . I had vaguely remembered some discussions about there being two files a while back, but looked in the wrong place for the second one and figured I had misremembered things.
On Wed, Sep 07, 2005 at 12:19:19AM -0400, Tom Lane wrote: > Having said that, I'm not sure I believe in filesystem locks as doing > much to improve security in the case of multiple hosts attached to a SAN > filesystem. Does the locking work at all across hosts, and if it does, > does the lock get released reasonably promptly if the owning host > crashes? This seems like a there's-no-free-lunch situation. The way I see it, it will work fine for some setups, and not work for others. That means it won't help everyone, but it will help some. ISTM like it would be pretty easy to do, so why not help those who could make use of it? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Wed, Sep 07, 2005 at 12:19:19AM -0400, Tom Lane wrote: >> Having said that, I'm not sure I believe in filesystem locks as doing >> much to improve security in the case of multiple hosts attached to a SAN >> filesystem. Does the locking work at all across hosts, and if it does, >> does the lock get released reasonably promptly if the owning host >> crashes? This seems like a there's-no-free-lunch situation. > > The way I see it, it will work fine for some setups, and not work for > others. That means it won't help everyone, but it will help some. ISTM > like it would be pretty easy to do, so why not help those who could make > use of it? On SUSE Linux (Tested on 10.0beta but I am fairly sure my servers running SLES9 are the same) I have: # ls -l /var/lib/pgsql/data/postmaster.* -rw------- 1 postgres postgres 41 Sep 7 09:09 /var/lib/pgsql/data/postmaster.opts -rw------- 1 postgres postgres 45 Sep 7 09:09 /var/lib/pgsql/data/postmaster.pid Both of these files are updated when Postgres starts, with postmaster.pid being removed when it stops, but postmaster.opts stays. If I have a shared disk between 2 machines with Postgres running on them is this enough to protect my data or not? Has anyone ever done anything like this? TIA -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Bruno Wolff III <bruno@wolff.to> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, if you know any vendors who move postmaster.pid out of the PGDATA
>> directory, let us know so we can knock some sense into their heads.
> I thought Fedora did, but there are actually two files, one in /var/run
> and another in /var/lib/pgsql/data .
Right, we put in the "external_pid_file" option to satisfy people who
wanted something under /var/run ... but Postgres itself makes no use
of that file, we just write it out during postmaster start. The
postmaster.pid file, on the other hand, is a critical safety interlock
to keep two postmasters from starting in the same data directory.
regards, tom lane