Thread: Tablespaces
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) Would appreciate any thoughts and guidance please. The aim is to keep everyone's work in their own folders. Thanks James
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
On Mon, Sep 23, 2013 at 5:50 PM, James David Smith <james.david.smith@gmail.com> wrote: > SELECT relname FROM pg_tablespace; > Uh? There is no relname in pg_tablespace: http://www.postgresql.org/docs/current/static/catalog-pg-tablespace.html > 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) > > Would appreciate any thoughts and guidance please. The aim is to keep > everyone's work in their own folders. According to the documentation (http://www.postgresql.org/docs/9.2/static/runtime-config-client.html) if the tablespace name does not match any existing table space PostgreSQL will default to default (i.e., will create objects in the default tablespace). Moreover: "This variable is also not used when creating databases. By default, a new database inherits its tablespace setting from the template database it is copied from." Luca
Thanks for the advice guys. I've seemed to get it working now. It'd like to provide an explanation as to how, but I've tried so many things that I'm a bit lost.... but all is working fine now! Thanks again. On 24 September 2013 12:40, Luca Ferrari <fluca1978@infinito.it> wrote: > On Mon, Sep 23, 2013 at 5:50 PM, James David Smith > <james.david.smith@gmail.com> wrote: > >> SELECT relname FROM pg_tablespace; >> > > Uh? There is no relname in pg_tablespace: > http://www.postgresql.org/docs/current/static/catalog-pg-tablespace.html > >> 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) >> >> Would appreciate any thoughts and guidance please. The aim is to keep >> everyone's work in their own folders. > > According to the documentation > (http://www.postgresql.org/docs/9.2/static/runtime-config-client.html) > if the tablespace name does not match any existing table space > PostgreSQL will default to default (i.e., will create objects in the > default tablespace). Moreover: "This variable is also not used when > creating databases. By default, a new database inherits its tablespace > setting from the template database it is copied from." > > > Luca