Re: Tablespaces

2013/9/23 James David Smith <james(dot)david(dot)smith(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice