Search code examples
sqlpostgresqlpermissionspsqltablespace

How do I get a PostgreSQL tablespace on a second, installed hard drive?

I have PostgreSQL 9.5 installed on Ubuntu 16.04. Since it is on an SSD, and I don't want to write to it too frequently, I would prefer to keep most of my data on a hard disk (connected via SATA) that I recently installed and partitioned to an Ext4 filesystem. (Postgres is installed on the SSD)

I am trying to create a tablespace on the hard drive with the following ownership and location settings:

root@mycomp:/media/me/EXT4_LARGE# ls -la
total 36
drwx------  6 me      root      4096 Nov 30 23:17 .
drwxr-x---+ 5 me      root      4096 Nov 30 23:09 ..
drwx------  2 root     root     16384 Nov 30 21:59 lost+found
drwx------  2 postgres postgres  4096 Nov 30 22:25 pg_data

And from the psql command line:

postgres=# CREATE TABLESPACE hdd_ts LOCATION 
'/media/me/EXT4_LARGE/pg_data';
ERROR:  could not set permissions on 
directory "/media/me/EXT4_LARGE/pg_data": Permission denied

I've tried modifying ownership settings in various configurations, but none of them have worked.

How do I go about getting a tablespace on the hard disk?

Solution

  • In Linux, users need the 'x' permission on the parent directory to be able to enter the directory and access files and directories inside it.

    They need also need the 'w' permission to make any changes to entries in that directory.

    From your printout:

    drwx------  6 me      root      4096 Nov 30 23:17 .

    This tells me that only the user me has x and w permissions on the directory /media/me/EXT4_LARGE. So the user postgres will not be able to change permissions of /media/me/EXT4_LARGE/pgdata, nor even see inside it.

    A quick and dirty solution would be:

    chmod a+rwx /media/me/EXT4_LARGE

    The drawback of that solution is it would give ALL users read/write access to EXT4_LARGE. A more fine grained approach would be to create a group and assign group permissions.

    Also it might be unwise to give postgres access to the whole EXT4_LARGE filesystem, so as per some of the comments, you might want to create another directory level in between, eg:

    mkdir -p /media/me/EXT4_LARGE/db/pgdata
chown postgres:postgres /media/me/EXT4_LARGE/db
chown postgres:postgres /media/me/EXT4_LARGE/db/pgdata
chmod a+x /media/me/EXT4_LARGE/

    Note that the x permission is needed on all parent directories, so you might have to adjust /media/me and /media.