Specifying different database and related files location for a single PostgreSQL instance

Question

We are reviewing how to segregate different PostgreSQL databases files to different drives on a single server with a single PostgreSQL instance. Does PostgreSQL allow this setup?

We can perform this function in both DB2 and MSSQL.

Does installing multiple PostgreSQL on the same server the only option to archive this setup? Installing multiple SQL instances on the same server method would add complexity and additional breakpoints for future maintenances and upgrades. I.e. we want to avoid.

Thanks in advance for any comments. Sincerely, Sunny

A bit unrelated: you don't need to install Postgres multiple times. you can create multiple instances using different data directory (and TCP ports) using the same binaries. Just run initdb with a different data directory. But assigning a default tablespace to each database is probably a better (and easier) way. — user330315
– user330315, Commented May 30, 2018 at 8:39

Vao Tsun · Accepted Answer · 2018-05-30 08:34:18Z

1

the right way to do it would be:

t=# \! mkdir /pg/so
t=# create tablespace so location '/pg/so';
CREATE TABLESPACE
t=# create database so;
CREATE DATABASE
t=# alter database so set default_tablespace to so;
ALTER DATABASE

of course you can overcome default_tablespace if you have permissions to use other tablespace (which is seasily restricted)

answered May 30, 2018 at 8:34

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sunny Over a year ago

Thank you! Will look into. Sunny

Collectives™ on Stack Overflow

Specifying different database and related files location for a single PostgreSQL instance

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related