Re: Postgres 10 temp tablespace question

Am 09.03.19 um 02:05 schrieb Joseph Dunleavy:
>
> I am building a multi-tenant deployment with multiple database - 1 
> tenant per database.
>
> I would like to be able to dedicate specific temp tablespace to 
> a specific database or user/schemas.
>
> I understand how to define temp_tablespace in postgresql.conf.
>
>
> I can't find anything in the documentation on how to dedicate a 
> specific temp_tablespaces to a specific database, user or schema.
>
> I also thought maybe I could create a trigger on logon to set a 
> specific temp tablespace per user, but I can't do that either.
>
>
> Is it possible in Postgresql to dedicate a specific temp tablespace to 
> a specific database or user/schema?
>
>

yes:

test=*# create tablespace tmp_tbsp1 location '/tmp/tbsp1';
FEHLER:  CREATE TABLESPACE kann nicht in einem Transaktionsblock laufen
test=*# commit;
COMMIT
test=# create tablespace tmp_tbsp1 location '/tmp/tbsp1';
CREATE TABLESPACE
test=# create tablespace tmp_tbsp2 location '/tmp/tbsp2';
CREATE TABLESPACE
test=# create tablespace tmp_tbsp3 location '/tmp/tbsp3';
CREATE TABLESPACE
test=# create user usr1;
CREATE ROLE
test=*# create user usr2;
CREATE ROLE
test=*# create user usr3;
CREATE ROLE
test=*# alter user usr1 set temp_tablespaces = 'tmp_tbsp1';
ALTER ROLE
test=*# alter user usr2 set temp_tablespaces = 'tmp_tbsp2';
ALTER ROLE
test=*# alter user usr3 set temp_tablespaces = 'tmp_tbsp3';
ALTER ROLE
test=*#


test=*# show temp_tablespaces;
  temp_tablespaces
------------------

(1 row)

test=*# commit;
COMMIT
test=# \c - usr2;
psql (11.1 (Ubuntu 11.1-3.pgdg16.04+1), server 9.5.15)
You are now connected to database "test" as user "usr2".
test=> show temp_tablespaces;
  temp_tablespaces
------------------
  tmp_tbsp2
(1 row)

test=*>



Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com