Tablespaces

From: James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Tablespaces
Date: 2013-09-23 15:50:58
Message-ID: CAMu32AAZpq-dj601k+2LFcJ0YwigkmjQ4=C_AeCTkVz=p7sWLg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Devrim GÜNDÜZ 2013-09-23 20:49:37 Re: PostgreSQL 9.3 Error when trying to run Create Extension postgis
Previous Message Merlin Moncure 2013-09-23 13:49:46 Re: JSON row_to_json_array