Re: Tablespaces - Mailing list pgsql-novice
From | Mael Rimbault |
---|---|
Subject | Re: Tablespaces |
Date | |
Msg-id | CAEKp92x6rfA1+vX25MJuitGZyWG-BeyRFTy58MMZJZHz8YsBXg@mail.gmail.com Whole thread Raw |
In response to | Tablespaces (James David Smith <james.david.smith@gmail.com>) |
List | pgsql-novice |
2013/9/23 James David Smith <james.david.smith@gmail.com>: > Dear all, > > I'm having some problems with setting default tablespaces for people. > I have some directory's on our server as follows (names changes): > > /home/userdb/persona > /home/userdb/personb > > And each person has their own login for PostgreSQL. However when they > create tables and databases etc, they aren't being created in these > folders as I would like (to do with storage issues). > > If I go to here: > > /var/lib/pgsql/9.0/data/pg_tblspc/ > > Then I have two files. > > lrwxrwxrwx 1 postgres postgres 21 Jul 9 2012 17282 -> /home/userdb/persona > lrwxrwxrwx 1 postgres postgres 22 Jul 9 2012 17285 -> /home/userdb/personb > > If I go back into psql, and run this: > > SELECT relname FROM pg_tablespace; > > I get this: > > personatabs | 17828 | /home/userdb/persona > personbtabs | 17285 | /home/userdb/personb > > So it looks to me like any databases or tables made by each user > should go into their default folder on the server no? > > I have even got persona to login and run the following command: > > SET default_tablespace = 'personatabs' > > But when they run a command such as this, the new database is still > made in the default directory, rather than the persona folder: > > CREATE DATABASE testing(i int) > Hi James, First, I think you have a typo : CREATE DATABASE testing(i int) This is not working, seems to me you mixed database and table creation syntaxes. About default_tablespace parameter, from the fantastic manual : "When default_tablespace is set to anything but an empty string, it supplies an implicit TABLESPACE clause for CREATE TABLE and CREATE INDEX commands that do not have an explicit one." (http://www.postgresql.org/docs/9.3/static/manage-ag-tablespaces.html) Thus it does not apply to the CREATE DATABASE command. If I read you correctly, you want to create a distinct database for each user. I don't know about the specifics, but personnaly I'd rather create separate schemas/tablespaces in a single database, and then : ALTER ROLE myuser SET default_tablespace = 'myuser_tbs' SET search_path = 'myuser_schema' ; But if having one database for each user is what you need, you can do it this way : CREATE TABLESPACE personatbs LOCATION '/path/to/personatbs/' ; CREATE DATABASE personabd TABLESPACE='personatbs' ; ALTER ROLE persona SET default_tablespace = 'personatabs' ; Obviously you will also have to deal with databases / relations ownership, to grant privileges, and so on ... Hope this helps. -- Mael > Would appreciate any thoughts and guidance please. The aim is to keep > everyone's work in their own folders. > > Thanks > > James > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice
pgsql-novice by date: