Обсуждение: PostgreSQL and a clustered file system

Hello,

Is anyone running PostgreSQL on a clustered file system on Linux? By
"clustered" I actually mean "shared", such that the same storage is
mounted by different servers at the same time (of course, only one
instance of PostgreSQL on only one server can be running on such a
setup, and there are a lot of other precautions that need to be satisfied).

Hello Ivan,

this sounds so "mainframe-ish", i recall, in IBM MVS (circa 1990+) we used to attach two systems to
the same "DASDie" storage, and then employ disk serialization provided by the OS to achieve some
integrity to the data. (do not get me wrong i had adequate Unix/SUNOS/Ultrix experience
before i had to go through all that MVS stuff, which made it even more painful!).

On Δευ 12 Νοε 2012 11:03:14 Ivan Voras wrote:
> Hello,
>
> Is anyone running PostgreSQL on a clustered file system on Linux? By
> "clustered" I actually mean "shared", such that the same storage is
> mounted by different servers at the same time (of course, only one
> instance of PostgreSQL on only one server can be running on such a
> setup, and there are a lot of other precautions that need to be satisfied).
>
>
-
Achilleas Mantzios
IT DEPT

Am 12.11.2012 11:03, schrieb Ivan Voras:
> Hello,
>
> Is anyone running PostgreSQL on a clustered file system on Linux? By
> "clustered" I actually mean "shared", such that the same storage is
> mounted by different servers at the same time (of course, only one
> instance of PostgreSQL on only one server can be running on such a
> setup, and there are a lot of other precautions that need to be satisfied).
>
>
TBTH, I don't see the potential benefit. Clustered filesystems have
benefits for special use cases (e.g. redundant fileservers or
applications that actually can work in parallel, relying on file
locking, DB clusters that coordinate writes themselves, ...), but PG
certainly is not one of these...

I'm not even sure if PG will lock any files at all (I'd guess not, as
SHM is used for process coordination), so you'd actually have to roll
your own safety measures. OTOH, you pay a huge price for the clustering
part (google for DLM), which will slow your I/O down significantly.

If you're "just" looking for shared storage (e.g. because SAN space is
fricking expensive and you don't want to allocate it twice to your
primary and secondary server ;-), it is probably safer & performing
better to implement the  volumes and filesystems as underlying cluster
resources. That's something your cluster software should be able to
handle... ;-)

A newly imported filesystem will also not carry any leftover file locks
around, should your primary server fail. The journal replay should not
hurt you too much, as the relevant I/O PG does is sync anyway.

Just my 2p,

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

On 11/12/2012 1:52 PM, Gunnar "Nick" Bluth wrote:
> Am 12.11.2012 11:03, schrieb Ivan Voras:
>>
>>
>> Is anyone running PostgreSQL on a clustered file system on Linux? By
>> "clustered" I actually mean "shared", such that the same storage is
>> mounted by different servers at the same time (of course, only one
>> instance of PostgreSQL on only one server can be running on such a
>> setup, and there are a lot of other precautions that need to be
>> satisfied).
>>
>>
> TBTH, I don't see the potential benefit. Clustered filesystems have
> benefits for special use cases (e.g. redundant fileservers or
> applications that actually can work in parallel, relying on file
> locking, DB clusters that coordinate writes themselves, ...), but PG
> certainly is not one of these...

Although I'm also a non-fan of database over clustered filesystems, I
wanted to speak up here since I have a hunch the OP wasn't looking for
the solution you're thinking of. I think he's asking if folk have run an
"HA" setup with PG where the database files are stored in a
dual-ported/clustered filesystem, the idea being that you can have a
failure in the hardware for one DB server, and make the other one take
over using the same underlying files. I've seen this done. You need some
plumbing to ensure that only one PG instance can run at the same time.
 From memory, there are HA monitor tools available that do this in a
generic way. As far as PG is concerned it has no idea there is a shared
access filesystem, and so it has no need to perform file-level locking.

Ivan,

* Ivan Voras (ivoras@freebsd.org) wrote:
> Is anyone running PostgreSQL on a clustered file system on Linux? By
> "clustered" I actually mean "shared", such that the same storage is
> mounted by different servers at the same time (of course, only one
> instance of PostgreSQL on only one server can be running on such a
> setup, and there are a lot of other precautions that need to be satisfied).

Yes.  I've seen PG used across both DRBD and OCFS2.  It can be made to
work quite well as an HA setup, provided you do appropriate fencing to
make sure that two postmasters don't end up trying to access the data
directory at the same time, etc, etc.

    Thanks,

        Stephen