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