How to change the default tablespace
Article ID: 295632
Updated On:
Products
Issue/Introduction
This article explains how to change the default tablespace of a database in Greenplum Database.
Environment
Resolution
Create a tablespace
For details abd steps to create table spaces refer to the standard documentation
- 5.x Creating and Managing Tablespaces
- 6.x Creating and Managing Tablespaces
- 7.x Creating and Managing Tablespaces
Scenario 1: A table that gets created should be in the new tablespace
1. Set the default_tablespace parameter. Set it globally using the command below:
template1=# alter database g4 set default_tablespace to pg_default; ALTER DATABASE Time: 113.969 ms
2. The query below shows that the database is by default in the tablespace 17429 (which is ts1). The new set of tables will be created on the pg_default tablespace due to above command.
g4=# select datname,dattablespace from pg_database where datname='g4'; datname | dattablespace ---------+--------------- g4 | 17429 (1 row) g4=# select oid,* from pg_tablespace where oid in (17429,1663); oid | spcname | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid -------+------------+----------+-------------+--------+-----------------+-----------------+---------- 1663 | pg_default | 10 | | | | | 3052 17429 | ts1 | 10 | | | | | 17428 (2 rows)
Note: The "dattablespace" in pg_database shows the tablesspace used when creating the database.
3. Create a table:
g4=# create table a2 as select * from pg_class; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relname' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. SELECT 388 Time: 2036.285 ms
4. Confirm that the new table is created in the pg_default tablespace.
Note: The reason why the reltablespace = 0 is described in this article.
g4=# select relname,reltablespace from pg_class where relname in ('a1','a2');
relname | reltablespace
----------+--------------
a1 | 0
a2 | 1663
Time: 0.837 ms
g4=#
5. To avoid setting the parameter globally, use the following you command:
alter user <username> set default_tablespace to <new_tablespace_name>;
Scenario 2: Moving all the data from a database to a new tablespace
It is possible to set the default tablespace for a particular database with:
alter database <database_name> set default_tablespace to <new_tablespace_name>;
The current database default tablespace is 17429.
g4=# select datname,dattablespace from pg_database where datname='g4'; datname | dattablespace ---------+--------------- g4 | 17429 (1 row)
The tablespace 17429 is for 'ts1'.
g4=# select * from pg_tablespace where oid=17429; spcname | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid ---------+----------+-------------+--------+-----------------+-----------------+---------- ts1 | 10 | | | | | 17428 (1 row)
To change the default tablespace, create a new database with the template of the original database.
g4=# \c template1 You are now connected to database "template1" as user "gpadmin".
template1=# create database g5 template g4 tablespace pg_default; CREATE DATABASE Time: 7837.136 ms
g4=# select datname,dattablespace from pg_database where datname in ('g4','g5'); datname | dattablespace ---------+--------------- g5 | 1663 g4 | 17429 (2 rows)
Observe that in pg_database, it can be confirmed that the database has been created.
g4=# select oid,* from pg_tablespace where oid in (17429,1663); oid | spcname | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid -------+------------+----------+-------------+--------+-----------------+-----------------+---------- 1663 | pg_default | 10 | | | | | 3052 17429 | ts1 | 10 | | | | | 17428 (2 rows)
Observe that the new database has all the objects created from the g4 database.
template1=# \c g5
You are now connected to database "g5" as user "gpadmin".
g5=#
g5=#
g5=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+---------+---------
public | a1 | table | gpadmin | heap
public | a2 | table | gpadmin | heap
(2 row)
g5=# select count(*) from a1 ;
count
-------
385
(1 row)
Time: 174.436 ms
g5=#
g5=# select relname,reltablespace from pg_class where relname='a1';
relname | reltablespace
---------+---------------
a1 | 0
a2 | 0
(2 row)
Note: The reason why the reltablespace = 0 is described in this article.
Drop the original database and mark the new database as the default database.
template1=# drop database g4; DROP DATABASE Time: 1092.213 ms template1=# alter database g5 rename to g4; ALTER DATABASE Time: 92.953 ms
Additional Information
Feedback
