Re: The case of PostgreSQL on NFS Server (II) - Mailing list pgsql-general

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