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

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.​​