Creating a tablespace in postgresql

postgresql tablespace chown
The_Denominater picture The_Denominater · Mar 6, 2011 · Viewed 31.3k times · Source

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?

Answer

gsiems picture gsiems · Mar 6, 2011

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.

Related questions

How can I tell what is in a Postgresql tablespace?

I've created a new tablespace called indexes, and I'm trying to remove the old tablespace indexes_old, which used to contain some tables and indexes. When I try to drop the tablespace, I get: => drop tablespace indexes_old; ERROR: …

Read More
Where in the DB is the Location of a postgres tablespace stored

I am using postgres 9.2 on redhat 6 this should be simple but I can't find it anywhere. I am looking for the database table and column which stores the Location for a postgres tablespace, I thought it would be in PG_…

Read More
could not write to hash-join temporary file: No space left on device

I am executing PostgreSQL functions to update a table which has a huge amount of data and the update happens for about 100000 records everyday. During the update I get an error saying: "could not write to hash-join temporary file: No …

Read More