The case of PostgreSQL on NFS Server (II)

On Thu, Apr 2, 2015 at 1:01 PM, Octavi Fors <octavi@live.unc.edu> wrote:

I don't see how to migrate the databases from my desktop directory
determined in a) to my NAS. Could someone please provide the steps to
accomplish that?

ALTER DATABASE name SET TABLESPACE new_tablespace

​You are solely responsible for any future frustration or harm you
experience for choosing to access your data over NFS.​

David J.

On Thu, Apr 2, 2015 at 3:01 PM, Octavi Fors <octavi@live.unc.edu> wrote:

Hi,

this is somehow overlapping one thread which was already posted in this list
here.

However, I'm newbie in PostgreSQL and would need some help from experts on
two aspects. I apologize if these were already implicitely mentioned in the
thread above.

My situation is the following:
1-running postgresql-9.2 server under my Ubuntu 12.04 LTS desktop,

I'm running release 9.4 on Fedora 21, but hopefully what I can test
will be of some use to you.

2-user postgres created on the system,

Good.

3-two databases with several tables created, populated and owned by
postgres,
4-these databases can be queried (SELECT, INSERT, etc.) by any system user
of my desktop.

Now here are my two questions:

a) how can I know where my two databases are stored (I presume in a
directory somewhere in my desktop path)?

You might want to read: https://help.ubuntu.com/community/PostgreSQL

They will be stored in the default location. On Fedora, this is
/var/lib/pgsql/data. I don't know what it is on Ubuntu, but you can
find out by using psql. Perhaps something like:

sudo su - postgres -c 'SHOW data_directory;'

Just using my normal id on Fedora, which is a PostgreSQL (not UNIX)
super user, I can see:

tsh009=# SHOW data_directory;
data_directory
---------------------
/var/lib/pgsql/data
(1 row)

tsh009=#

b) this is the though one (at least for me ;) For data size reasons, I want
to store my two databases in a NAS which I have mounted via NFS, but still
keeping the postgresql server on my desktop.

Well, if you insist. But I would not move the default data there. I'd
keep it local. See below for an alternative.

I've seen in this message of the thread above, that NFS noac mounting option
is highly recommended.
Apart from this, I don't see how to migrate the databases from my desktop
directory determined in a) to my NAS. Could someone please provide the steps
to accomplish that?

Well, there are a couple of approaches. Both will require UNIX root to
accomplish. If you are running SELinux enabled & enforcing, it is even
more complicated. But basically create an new, empty subdirectory on
your NAS box. Look at the owner & group on the directory shown above
(/var/lib/pgsql/data) and make it the new directory have the same
information. Something like:

sudo mkdir -p /NAS-box/pgsql-data
sudo chown --reference /var/lib/pgsql/data /NAS-box/pgsql-data

This sets up the new NAS-box/pgsql-data directory and assigns it the
proper owner:group for use by the server.

=== Moving the data - BAD IDEA, IMO ==

#stop the postgresql server on your system, however you do that. I
don't know Ubuntu.

sudo cp -a /var/lib/pgsql/data/* /NAS-box/pgsql-data
sudo cp -a /var/lib/pgsql/data/.* /NAS-box/pgsql-data
sudo mount --bind /var/lib/pgsql/data /NAS-box/pgsql-data
sudo restorecon -R /var/lib/pgsql/data # restore SELinux attributes

Commands #1 and #2 copy all the data to the new directory.
Command #3 does a "bind" mount so that any reference to
/var/lib/pgsql/data is redirected to /NAS-box/pgsql
Command #4 is if you run with SELinux.

Update your fstab to maintain the bind mount when you reboot. The
entry will look something like:

/NAS-box/pgsql-data /var/lib/pgsql/data none bind

=== THE ABOVE IS A BAD IDEA ===

=== Decent idea: ===
Create the NAS directory as above. Do _NOT_ copy anything into it! In
psql, as a postgres super user, create a new TABLESPACE
ref: http://www.postgresql.org/docs/9.2/interactive/sql-createtablespace.html

CREATE TABLESPACE onNAS LOCATION '/NAS-box/pgsql-data';
CREATE DATABASE somedb TABLESPACE onNAS;
\c somedb --connect to new database stored on the NAS
CREATE TABLE sometable ... ;

Now all the stuff in the "somedb" data base will reside on your NAS
box. Now all you need worry about is that it is reliable and fast
enough. Hopefully on at least a 1Gb ethernet.
In this case, I don't know the SELinux commands to set the SELinux
attributes. It is complicated.

=== Opinion.

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. You
connect your DAS box to that will an eSATA cable. Now you have an
external disk expansion which runs at full SATA speed, just like an
internal disk drive. It's just an alternative. But you'd do the same
thing as with the NAS to get data onto it. Well, except that you could
dedicate an entire filesystem on the DAS to the /var/lib/pgsql/data
with something like:

# /dev/sd?n is the value for the DAS box.
sudo mkfs.ext4 /dev/sd?n #create new filesystem on DAS
sudo mount /dev/sd?n /tmp #mount it
sudo cp -a /var/lib/pgsql/data/* /tmp
sudo cp -a /var/lib/pgsql/data/.* /tmp
sudo umount /tmp
sudo mount /dev/sd?n /var/lib/pgsql/data #mount it
sudo restorecon -R /var/lib/pgsql/data # update SELinux attributes

The restorecon is only needed if you run SELinux

Thanks so much in advance,

--
Octavi Fors

--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks John for your extensive and helpful response.

A few quick answers which may clarify my desktop-NAS system details:

If you are running SELinux enabled & enforcing, it is even

more complicated.

-no, I'm not running SELinux.

-My NAS is a Synology DS2415+
<https://www.synology.com/en-us/products/DS2415+&gt; . I created a volume of
~72.44Tb with 12 8Tb Seagate ST8000AS0002
<http://www.seagate.com/products/enterprise-servers-storage/nearline-storage/archive-hdd/?sku=ST8000AS0002&gt;
drives, with no encryption and Synology Hybrid RAID-2 (SHR) protection.

-Yes, the NAS is accessible only from two computers in the same Gigabit
LAN. Actually, I have defined trunks on the router for speeding up the
computers<->NAS access.

-The rest of the LAN has ~4 tcp/ip devices with very low traffic.

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.

See below the results to your comments/commands:

Just using my normal id on Fedora, which is a PostgreSQL (not UNIX)

super user, I can see:

tsh009=# SHOW data_directory;
data_directory
---------------------
/var/lib/pgsql/data

postgres@ev1:~$ psql -c 'SHOW data_directory;'
data_directory
------------------------------
/var/lib/postgresql/9.2/main

Note that inside /var/lib/postgresql/9.2/main directory there are the
following files and subdirs:

postgres@ev1:~/9.2/main$ cd /var/lib/postgresql/9.2/main
postgres@ev1:~/9.2/main$ ls -ltr
drwx------ 2 postgres postgres 4096 Aug 27 2014 pg_snapshots
drwx------ 2 postgres postgres 4096 Aug 27 2014 pg_serial
-rw------- 1 postgres postgres 4 Aug 27 2014 PG_VERSION
drwx------ 2 postgres postgres 4096 Aug 27 2014 pg_twophase
drwx------ 2 postgres postgres 4096 Aug 27 2014 pg_tblspc
drwx------ 4 postgres postgres 4096 Aug 27 2014 pg_multixact
drwx------ 2 postgres postgres 4096 Sep 3 2014 pg_clog
drwx------ 2 postgres postgres 4096 Sep 3 2014 pg_subtrans
drwx------ 7 postgres postgres 4096 Sep 3 2014 base
-rw------- 1 postgres postgres 93 Mar 9 20:10 postmaster.pid
-rw------- 1 postgres postgres 133 Mar 9 20:10 postmaster.opts
drwx------ 2 postgres postgres 4096 Mar 9 20:10 pg_notify
drwx------ 2 postgres postgres 4096 Mar 9 20:11 global
drwx------ 3 postgres postgres 4096 Mar 27 13:57 pg_xlog
drwx------ 2 postgres postgres 4096 Apr 2 17:35 pg_stat_tmp

postgres@ev1:~/9.2/main$ du -sh ./*
485M ./base
448K ./global
624K ./pg_clog
28K ./pg_multixact
12K ./pg_notify
4.0K ./pg_serial
4.0K ./pg_snapshots
32K ./pg_stat_tmp
176K ./pg_subtrans
4.0K ./pg_tblspc
4.0K ./pg_twophase
4.0K ./PG_VERSION
129M ./pg_xlog
4.0K ./postmaster.opts
4.0K ./postmaster.pid

ofors@ev1:~$ sudo /etc/init.d/postgresql stop
* Stopping PostgreSQL 9.2 database server [ OK ]

ofors@ev1:~$ sudo mkdir -p /home/ofors/Documents/nas/
ofors@ev1:~$ sudo chown --reference /var/lib/postgresql/9.2/main
/home/ofors/Documents/nas/pgsql-data

ofors@ev1:~$ sudo /etc/init.d/postgresql start
* Starting PostgreSQL 9.2 database server [ OK ]

postgres@ev1:~/9.2/main$ psql
psql (9.3.6, server 9.2.10)
Type "help" for help.

postgres=# CREATE TABLESPACE onNAS LOCATION
'/home/ofors/Documents/nas/pgsql-data';
CREATE TABLESPACE
postgres=# ALTER DATABASE catalogs SET TABLESPACE onNAS;
ALTER DATABASE

You see that I used the ALTER from David in last message, instead your
suggestion of creating the whole database again.

Anyway, it seems the data from catalogs database is in the NAS.
See the following:

ofors@ev1:~$ sudo ls -l /home/ofors/Documents/nas/pgsql-data/
drwx------ 3 postgres postgres 4096 Apr 2 18:02 PG_9.2_201204301

ofors@ev1:~$ sudo du -sh
/home/ofors/Documents/nas/pgsql-data/PG_9.2_201204301
467M /home/ofors/Documents/nas/pgsql-data/PG_9.2_201204301

postgres@ev1:~/9.2/main$ cd /var/lib/postgresql/9.2/main
postgres@ev1:~/9.2/main$ ls -ltr
drwx------ 2 postgres postgres 4096 Aug 27 2014 pg_snapshots
drwx------ 2 postgres postgres 4096 Aug 27 2014 pg_serial
-rw------- 1 postgres postgres 4 Aug 27 2014 PG_VERSION
drwx------ 2 postgres postgres 4096 Aug 27 2014 pg_twophase
drwx------ 4 postgres postgres 4096 Aug 27 2014 pg_multixact
drwx------ 2 postgres postgres 4096 Sep 3 2014 pg_clog
drwx------ 2 postgres postgres 4096 Sep 3 2014 pg_subtrans
drwx------ 3 postgres postgres 4096 Mar 27 13:57 pg_xlog
drwx------ 2 postgres postgres 4096 Apr 2 17:56 pg_tblspc
drwx------ 6 postgres postgres 4096 Apr 2 18:01 base
-rw------- 1 postgres postgres 91 Apr 2 18:19 postmaster.pid
drwx------ 2 postgres postgres 4096 Apr 2 18:19 pg_notify
-rw------- 1 postgres postgres 133 Apr 2 18:19 postmaster.opts
drwx------ 2 postgres postgres 4096 Apr 2 18:19 global
drwx------ 2 postgres postgres 4096 Apr 2 19:17 pg_stat_tmp

postgres@ev1:~/9.2/main$ du -sh ./*
19M ./base
448K ./global
624K ./pg_clog
28K ./pg_multixact
12K ./pg_notify
4.0K ./pg_serial
4.0K ./pg_snapshots
36K ./pg_stat_tmp
176K ./pg_subtrans
4.0K ./pg_tblspc
4.0K ./pg_twophase
4.0K ./PG_VERSION
129M ./pg_xlog
4.0K ./postmaster.opts
4.0K ./postmaster.pid

./base has pased from 485M to 19M, and PG_9.2_201204301 is now 467M, which
seems to be consistent.
Also, I have double-checked with pgadmin3, and the database catalogs is now
assigned to the tablespace onnas, while the other database I have
(postgres) is still with pg_default.

Two only questions remain for me:

1) could you confirm that I don't have to mount --bind
/var/lib/postgresql/9.2/main ?

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?

Thanks so much in advance,

--
Octavi Fors

On Thu, Apr 2, 2015 at 4:54 PM, John McKown <john.archie.mckown@gmail.com>
wrote:

On Thu, Apr 2, 2015 at 7:09 PM, Octavi Fors <octavi@live.unc.edu> wrote:

Thanks John for your extensive and helpful response.

<snip>

You see that I used the ALTER from David in last message, instead your
suggestion of creating the whole database again.

Looks good!

<snip>

Two only questions remain for me:

1) could you confirm that I don't have to mount --bind
/var/lib/postgresql/9.2/main ?

If it is working for you now, then you don't need to mess with the
fstab or the mount --bind at all.

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?

Hum, performance is a bit over my head on disk drives, especially NAS.
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.

Oh, are you going to try to share a single data base file with
different PostgreSQL servers on different machines __AT THE SAME
TIME__??? I think you're going to "Corruption City". There are locks
which work fairly well on local disks (to share something like a file
or directory) which don't work very well on some NAS boxes. At least,
that is what Dr. Richard Hipp, who wrote and maintains SQLite,
maintains in his support forum. And he is much brighter than I!

Thanks so much in advance,

--
Octavi Fors

On Thu, Apr 2, 2015 at 4:54 PM, John McKown <john.archie.mckown@gmail.com>
wrote:

On Thu, Apr 2, 2015 at 3:01 PM, Octavi Fors <octavi@live.unc.edu> wrote:

Hi,

this is somehow overlapping one thread which was already posted in this
list
here.

However, I'm newbie in PostgreSQL and would need some help from experts
on
two aspects. I apologize if these were already implicitely mentioned in
the
thread above.

My situation is the following:
1-running postgresql-9.2 server under my Ubuntu 12.04 LTS desktop,

I'm running release 9.4 on Fedora 21, but hopefully what I can test
will be of some use to you.

2-user postgres created on the system,

Good.

3-two databases with several tables created, populated and owned by
postgres,
4-these databases can be queried (SELECT, INSERT, etc.) by any system
user
of my desktop.

Now here are my two questions:

a) how can I know where my two databases are stored (I presume in a
directory somewhere in my desktop path)?

You might want to read: https://help.ubuntu.com/community/PostgreSQL

They will be stored in the default location. On Fedora, this is
/var/lib/pgsql/data. I don't know what it is on Ubuntu, but you can
find out by using psql. Perhaps something like:

sudo su - postgres -c 'SHOW data_directory;'

Just using my normal id on Fedora, which is a PostgreSQL (not UNIX)
super user, I can see:

tsh009=# SHOW data_directory;
data_directory
---------------------
/var/lib/pgsql/data
(1 row)

tsh009=#

b) this is the though one (at least for me ;) For data size reasons, I
want
to store my two databases in a NAS which I have mounted via NFS, but
still
keeping the postgresql server on my desktop.

Well, if you insist. But I would not move the default data there. I'd
keep it local. See below for an alternative.

I've seen in this message of the thread above, that NFS noac mounting
option
is highly recommended.
Apart from this, I don't see how to migrate the databases from my
desktop
directory determined in a) to my NAS. Could someone please provide the
steps
to accomplish that?

Well, there are a couple of approaches. Both will require UNIX root to
accomplish. If you are running SELinux enabled & enforcing, it is even
more complicated. But basically create an new, empty subdirectory on
your NAS box. Look at the owner & group on the directory shown above
(/var/lib/pgsql/data) and make it the new directory have the same
information. Something like:

sudo mkdir -p /NAS-box/pgsql-data
sudo chown --reference /var/lib/pgsql/data /NAS-box/pgsql-data

This sets up the new NAS-box/pgsql-data directory and assigns it the
proper owner:group for use by the server.

=== Moving the data - BAD IDEA, IMO ==

#stop the postgresql server on your system, however you do that. I
don't know Ubuntu.

sudo cp -a /var/lib/pgsql/data/* /NAS-box/pgsql-data
sudo cp -a /var/lib/pgsql/data/.* /NAS-box/pgsql-data
sudo mount --bind /var/lib/pgsql/data /NAS-box/pgsql-data
sudo restorecon -R /var/lib/pgsql/data # restore SELinux attributes

Commands #1 and #2 copy all the data to the new directory.
Command #3 does a "bind" mount so that any reference to
/var/lib/pgsql/data is redirected to /NAS-box/pgsql
Command #4 is if you run with SELinux.

Update your fstab to maintain the bind mount when you reboot. The
entry will look something like:

/NAS-box/pgsql-data /var/lib/pgsql/data none bind

=== THE ABOVE IS A BAD IDEA ===

=== Decent idea: ===
Create the NAS directory as above. Do _NOT_ copy anything into it! In
psql, as a postgres super user, create a new TABLESPACE
ref:
http://www.postgresql.org/docs/9.2/interactive/sql-createtablespace.html

CREATE TABLESPACE onNAS LOCATION '/NAS-box/pgsql-data';
CREATE DATABASE somedb TABLESPACE onNAS;
\c somedb --connect to new database stored on the NAS
CREATE TABLE sometable ... ;

Now all the stuff in the "somedb" data base will reside on your NAS
box. Now all you need worry about is that it is reliable and fast
enough. Hopefully on at least a 1Gb ethernet.
In this case, I don't know the SELinux commands to set the SELinux
attributes. It is complicated.

=== Opinion.

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. You
connect your DAS box to that will an eSATA cable. Now you have an
external disk expansion which runs at full SATA speed, just like an
internal disk drive. It's just an alternative. But you'd do the same
thing as with the NAS to get data onto it. Well, except that you could
dedicate an entire filesystem on the DAS to the /var/lib/pgsql/data
with something like:

# /dev/sd?n is the value for the DAS box.
sudo mkfs.ext4 /dev/sd?n #create new filesystem on DAS
sudo mount /dev/sd?n /tmp #mount it
sudo cp -a /var/lib/pgsql/data/* /tmp
sudo cp -a /var/lib/pgsql/data/.* /tmp
sudo umount /tmp
sudo mount /dev/sd?n /var/lib/pgsql/data #mount it
sudo restorecon -R /var/lib/pgsql/data # update SELinux attributes

The restorecon is only needed if you run SELinux

Thanks so much in advance,

--
Octavi Fors

--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Apr 2, 2015, at 5:09 PM, Octavi Fors <octavi@live.unc.edu> wrote:

And second, because I need the database to be accessible from two computers in the same LAN.

If you do this, you will destroy your database[1]Almost certainly, unless you make absolutely sure postgresql is only started on one machine at a time, amongst other things..

Why not have the database running on one machine, all the time, potentially with a real disk subsystem then just access it from wherever on the LAN you need to? Postgresql is a client-server database, and you can access it over the network as easily as you can from the machine it's running on.

Cheers,
Steve

[1]: Almost certainly, unless you make absolutely sure postgresql is only started on one machine at a time, amongst other things.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Apr 2, 2015 at 5:09 PM, Octavi Fors <octavi@live.unc.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.​​

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
</messages/by-id/4C24352D.7060104@postnewspapers.com.au&gt;
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&gt;
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.g.johnston@gmail.com> wrote:

On Fri, Apr 3, 2015 at 1:40 AM, Octavi Fors <octavi@live.unc.edu> wrote:

Hi David, John et al.,

<snip>

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?

OK, it is very understandable why the images are on the NAS. It is the
easiest way to share them. I guess you want the DB on the NAS simply
because you don't have sufficient disk space on the disks connected to
C2.

<snip>

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?

Have you done a web search on "NFS Performance"? I got some good hits
with Google.
http://www.tldp.org/HOWTO/NFS-HOWTO/performance.html (a bit old, I've
been told)
http://www.cyberciti.biz/faq/linux-unix-tuning-nfs-server-client-performance/

Mentions "noatime" and "nodiratime" to not update the last access
date/time on files & directories, saving bandwidth. A good explanation
of the NFS mount options, IMO, are on this site:
http://www.dbaexpert.com/blog/nfs-options-for-performance/

Some interesting "speed test" code:
https://github.com/sabujp/nfsSpeedTest

<snip>

Thanks a lot in advance,
--
Octavi Fors

--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general