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+> . 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> 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.mck...@gmail.com> wrote: > On Thu, Apr 2, 2015 at 3:01 PM, Octavi Fors <oct...@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 >
