RE: Can we have multiple tablespaces with in a database.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

RE: Can we have multiple tablespaces with in a database.

pafiti

Hi,

 

You can create more than one tablespace and assign different objects on different tablespaces.

For example :

CREATE TABLESPACE test_data OWNER test LOCATION '/tmp/test_data';

CREATE TABLESPACE test_idx OWNER test LOCATION '/tmp/test_idx';

 

CREATE DATABASE test WITH TABLESPACE = test_data;

 

Then, for example, when create table and index, you can specify

CREATE TABLE test1

 

(

   id               int               NOT NULL GENERATED ALWAYS AS IDENTITY,

   comment          text,

   CONSTRAINT pk_test PRIMARY KEY

   (

      id

   ) USING INDEX TABLESPACE TEST_IDX

) TABLESPACE TEST_DATA;

 

CREATE INDEX sk_comment ON test1( comment ) TABLESPACE TEST_IDX;

 

Patrick Fiche

Database Engineer, Aqsacom Sas.

c. 33 6 82 80 69 96

 

01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg

 

From: Daulat Ram <[hidden email]>
Sent: Friday, February 21, 2020 7:23 AM
To: amul sul <[hidden email]>
Cc: [hidden email]; [hidden email]
Subject: RE: Can we have multiple tablespaces with in a database.

 

That will be great if you  share any doc where it’s mentioned that we can’t use multiple tablespace for a single database. I have to assist my Dev team regarding tablespaces.

 

Also , what are the differences between Oracle and Postgres Tablespacs?

 

Thanks,

 

 

From: amul sul <[hidden email]>
Sent: Friday, February 21, 2020 11:48 AM
To: Daulat Ram <[hidden email]>
Cc: [hidden email]; [hidden email]
Subject: Re: Can we have multiple tablespaces with in a database.

 

 

 

On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram <[hidden email]> wrote:

Hi Amul ,

Please share the examples how we can create no. of tablespaces for a single database and how we can use them.

As I know we can create database on tablespace

  1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs';
  2. Create database test tablespace ‘conn_tbs';

Maybe I have misunderstood your question; there is no option to specify more
than one tablespace for the database, but you can place the objects of that
database to different tablespaces (if options available for that object).
E.g. you can place a table in than conn_tbs tablespace.

If option is not specified then by default that object will be created
in conn_tbs.

 

Regards,

Amul

 

 

 

Reply | Threaded
Open this post in threaded view
|

RE: Can we have multiple tablespaces with in a database.

Daulat Ram-2

Thanks Patrick ,

 

From: Patrick FICHE <[hidden email]>
Sent: Friday, February 21, 2020 9:54 PM
To: Daulat Ram <[hidden email]>; amul sul <[hidden email]>
Cc: [hidden email]
Subject: RE: Can we have multiple tablespaces with in a database.

 

Hi,

 

You can create more than one tablespace and assign different objects on different tablespaces.

For example :

CREATE TABLESPACE test_data OWNER test LOCATION '/tmp/test_data';

CREATE TABLESPACE test_idx OWNER test LOCATION '/tmp/test_idx';

 

CREATE DATABASE test WITH TABLESPACE = test_data;

 

Then, for example, when create table and index, you can specify

CREATE TABLE test1

 

(

   id               int               NOT NULL GENERATED ALWAYS AS IDENTITY,

   comment          text,

   CONSTRAINT pk_test PRIMARY KEY

   (

      id

   ) USING INDEX TABLESPACE TEST_IDX

) TABLESPACE TEST_DATA;

 

CREATE INDEX sk_comment ON test1( comment ) TABLESPACE TEST_IDX;

 

Patrick Fiche

Database Engineer, Aqsacom Sas.

c. 33 6 82 80 69 96

 

01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg

 

From: Daulat Ram <[hidden email]>
Sent: Friday, February 21, 2020 7:23 AM
To: amul sul <[hidden email]>
Cc: [hidden email]; [hidden email]
Subject: RE: Can we have multiple tablespaces with in a database.

 

That will be great if you  share any doc where it’s mentioned that we can’t use multiple tablespace for a single database. I have to assist my Dev team regarding tablespaces.

 

Also , what are the differences between Oracle and Postgres Tablespacs?

 

Thanks,

 

 

From: amul sul <[hidden email]>
Sent: Friday, February 21, 2020 11:48 AM
To: Daulat Ram <[hidden email]>
Cc: [hidden email]; [hidden email]
Subject: Re: Can we have multiple tablespaces with in a database.

 

 

 

On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram <[hidden email]> wrote:

Hi Amul ,

Please share the examples how we can create no. of tablespaces for a single database and how we can use them.

As I know we can create database on tablespace

  1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs';
  2. Create database test tablespace ‘conn_tbs';

Maybe I have misunderstood your question; there is no option to specify more
than one tablespace for the database, but you can place the objects of that
database to different tablespaces (if options available for that object).
E.g. you can place a table in than conn_tbs tablespace.

If option is not specified then by default that object will be created
in conn_tbs.

 

Regards,

Amul