Geek Answers Handbook

Creating table space in postgresql

I am trying to create a tablespace in postgres, but I am having ownership problems. The command I use:

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

I get an error message:

 ERROR: could not set permissions on directory "/home/john/BSTablespace": Operation not permitted

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

 chown maggie:postgres /home/john/BSTablespace

I get:

 chown: invalid user: `maggie:postgres'

Why doesn't the user exist? If I have listed users in postgres, it will come up. Any ideas what I can do wrong?

+7
source share
2 answers

I would venture to suggest that the problem is the permissions of the parent directory "/ home / john". Your home directory is probably configured so that only your user can access it (i.e. chmod 700) (for your home directory it will be chmod 700, do not 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 maggie user: database users do not match OS users. This does not mean that you cannot have a user in both places named maggie - but you will need to create a user in both the database and the OS for this to happen.

+16
source

When you install Postgres on a Mac and try to use PgAdmin to create your databases, table spaces, etc. You need to know that the PgAdmin Utility runs under the postgres account that it created when you installed the postgres database and utilities.

postgres account is part of the _postgres group

Team

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

It would be best practice to create a new directory under root ( / ) to host table spaces (let's call it /postgresdata , then make postgres:_postgres owners of this directory using the command below)

sudo chown postgres:_postgres /postgresdata

That should do it for you. Then you can create a subdirectory under /postgresdata for each unique tablespace

0
source

More articles:


All Articles