Re: The case of PostgreSQL on NFS Server (II)

Hi David, John et al.,

as far as I understand eSATA is not an option for me. First because I
>> already have bought the DS2415+ NAS, which does not has eSATA I/O :(
>> And second, because I need the database to be accessible from two
>> computers in the same LAN.
>>
>
> ​This is new - and while the desire for using your multi-terrabyte device
> may ​make the risk of running over NFS potentially worthwhile there is no
> reasonable way to have two running databases share data files. You can
> share the NAS device and have each PostgreSQL instance consume an unshared
> portion of its resources.
>
> ​You appear to either be "mis-speaking" or omitting potentially critically
> important details here...
>

Oops, sorry yes I think I may "miss-spoke" when explaining my second reason
why not choosing eSATA.
My situation is the following:

-Two computers (C1 & C2) and NAS (with no eSATA I/O) on the same LAN.
-C1 acquires images from a telescope and periodically stores them via NFS
in the NAS (no database involved here, just in the ext4 filesystem).
-C2 is a 12 xeon core-class server designed to analyze the stored images in
the NAS, and compute astrometry & photometry measurements (catalogs & light
curves) for every star & image. These measurements are inserted in the
catalogs database inside the NAS.

Therefore there's only *one* computer (C2) which will run postgresql server
with the tablespace onNAS.

So does this approach sound like feasible if the NFS parameters are set
properly?

I have a small NAS, but my main 2Tb disk is eSATA. My fstab entry for
> the NAS just looks like:
> smartstor1:/VOLUME1/DV7T /SmartStor1/Volume1/DV7T nfs
> user,rw,noauto,noexec,nosuid 0 0
> which is basically what you have, performance wise, as best as I can tell.
>

I see this message
<http://www.postgresql.org/message-id/4C24352D.7060104@postnewspapers.com.au>
in this list any ideas suggesting to use the NFS parameters 'sync' (for
synchronizing changes to a file) and 'noac' (for no caching).

Could you confirm that
nas_ip:/volume1/data /home/ofors/Documents/nas nfs noac,sync
would be good options for /etc/fstab?

Any additional NFS parameter?

Note, the most important aspect of that fact is that your WAL gets written
> to your data directory and not to the tablespace on which the database
> tables reside. (i.e. WAL does not make it to NAS - unless you setup wal
> shipping).
>

first time I hear about the importance of WAL and NFS.
I googled some and found this discussion
<http://stackoverflow.com/questions/19047954/where-is-the-postgresql-wal-located-how-can-i-specify-a-different-path>
about the topic.
Any ideas on how to include the options they mention (archinve_mode?) into
NAS or /etc/fstab?

Thanks a lot in advance,
--
Octavi Fors

On Thu, Apr 2, 2015 at 8:47 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thu, Apr 2, 2015 at 5:09 PM, Octavi Fors <octavi(at)live(dot)unc(dot)edu> wrote:
>
>> Thanks John for your extensive and helpful response.
>>
>> I have a NAS box. But I would worry about responsiveness. What is
>>> better, IMO, is an external SATA connected DAS box. DAS is "Direct
>>> Attached Storage". Many PCs have a eSATA port on the back side.
>>>
>>
>> as far as I understand eSATA is not an option for me. First because I
>> already have bought the DS2415+ NAS, which does not has eSATA I/O :(
>> And second, because I need the database to be accessible from two
>> computers in the same LAN.
>>
>>
> ​This is new - and while the desire for using your multi-terrabyte device
> may ​make the risk of running over NFS potentially worthwhile there is no
> reasonable way to have two running databases share data files. You can
> share the NAS device and have each PostgreSQL instance consume an unshared
> portion of its resources.
>
> ​You appear to either be "mis-speaking" or omitting potentially critically
> important details here...
>
> 1) could you confirm that I don't have to mount --bind
>> /var/lib/postgresql/9.2/main ?
>>
>>
> ​/var/lib/... is not on the NAS but, likely, on whatever your primary
> internal hard drive is. Note, the most important aspect of that fact is
> that your WAL gets written to your data directory and not to the tablespace
> on which the database tables reside. (i.e. WAL does not make it to NAS -
> unless you setup wal shipping).
>
> 2) on my my /etc/fstab here is the current line for my NAS:
>> nas_ip:/volume1/data /home/ofors/Documents/nas nfs
>>
>> Which NFS client and server (on NAS side) options/arguments do you
>> suggest for optimizing performance? Or in other words, for minimizing
>> database corruption in case of NAS (note that NAS drives are in RAID6) or
>> computer failure?
>>
>>
> ​I am a little out of my league here but the main risk is that incomplete
> data is sent/received by/from the NAS. Once the data is in the NAS it is
> really no different than any other storage medium in terms of durability
> concerns. I do not really know how checkpoints and transient failed reads
> interact with PostgreSQL and what circumstances would prevent properly
> recorded WAL from being used to restore should a read/write failure occur.
>
> ​David J.​​
>
>