Creating a tablespace in postgresql

Question

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?

Solution 1

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.

Solution 2

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

Comments

[The_Denominater]

Thanks, that was the problem. Put it in root and there was no problem.

[Giri]

@gsiems update your answer, chown postgres:postgres /Directory.

[Max Candocia]

How do you do this for files on a separate drive/partition? I've been trying to reference a '/media/username/drive_name/pg_data' directory, whose permissions are set with a similar chown command and parent directory whose permissions are set to 700 (with a separate user). I am getting the same permissions error as the original post, though.