Creating a tablespace in postgresql

I'm trying to create a tablespace in postgres, but I'm getting ownership problems. The command I'm using is:

CREATE TABLESPACE magdat OWNER maggie LOCATION '/home/john/BSTablespace'

I get the error:

ERROR:  could not开发者_开发技巧 set permissions on directory "/home/john/BSTablespace": Operation not permitted

The folder belongs to postgres:postgres, I've tried changing it to maggie, but if I go :

chown maggie:postgres /home/john/BSTablespace

I get:

chown: invalid user: `maggie:postgres'

How come the user does not exist? If I list the users inside of postgres it does come up. Any ideas what I could be doing wrong?

I would hazard a guess that the problem lies in the permissions of the parent directory "/home/john". Your home directory is probably setup so that only your user has access (i.e chmod 700) to it (it's a good thing for your home directory to be chmod 700, don't change it).

Doing something like:

mkdir /BSTablespace
chown postgres:postgres /BSTablespace

and then

CREATE TABLESPACE magdat OWNER maggie LOCATION '/BSTablespace';

should work fine.

Regarding the user maggie: database users are not the same as OS users. That isn't to say that you couldn't have a user in both places named maggie-- but you would need to create the user in both the database and the OS for that to happen.

When you install Postgres on a Mac, and are trying to use PgAdmin to create your databases, tablespaces, etc. You need to know that the PgAdmin Utility is running under the postgres account that it created when you installed the postgres database and the utilities.

The postgres account is part of the _postgres group

( dscacheutil -q group|grep -i postgres command will list the group associated with the postgres account)

The best practice would be to create a new directory under root(/) for housing the tablespaces,(let us call it /postgresdata then make postgres:_postgres the owners of that directory, using the command below)

sudo chown postgres:_postgres /postgresdata

This should do it for you. You could then create a subdirectory under /postgresdata for each unique table space

There is a problem with this solution. Think about it. Why do you want to create a new tablespace? Most people do it for either space limitations or performance. In both cases, that means placing each tablespace on a different drive. So, archive data goes on the slower hard-drive, while actively used data does on the SSD.

Assume your OS is on the SSD and you have mounted your slower spin up hard drive as /media/slowdrive. The same dilemma would occur if the reverse, where the spinup is the OS and SSD is the mounted.

Your solution would place the new tablespace at /newtablespace.

Do you see the problem? ... /newtablespace is on the SSD, which does not have the capacity to hold both the archival and active data. If it did, we would not be creating a new tablespace in the first place.

So, how do we solve this issue when our newtablespace is mounted at /media/slowdrive/newtablespace? In my case, the slowdrive (spinup HD) is mounted as root:root for security purposes, although I am not entirely sure about why. What you are suggesting is that I have to chage the mount of my secondary drive to postgres:postgres in addition to having the newtablespace directory as postgres:postgres. That makes no sense, especially since I use this drive for many other reasons than just a postgres tablesapce.

Joe