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
