Pivotal Knowledge Base

Follow

How to change default tablespace.

Goal

Suppose you wish to change the default tablespace of the database and figuring it how this can be done, below article give you the step by step procedure on doing the same.

Solution

In this article, i would be discussing two steps ( both having different behavior).

Setup

Before starting to explain the steps , lets create some basic objects that we will used in explaining the procedure.

  • Create the filespace configuration
gpadmin:Fullrack@mdw $ gpfilespace -o gpfilespace_config
20140426:07:42:34:027340 gpfilespace:mdw:gpadmin-[INFO]:-
A tablespace requires a file system location to store its database
files. A filespace is a collection of file system locations for all components
in a Greenplum system (primary segment, mirror segment and master instances).
Once a filespace is created, it can be used by one or more tablespaces.


20140426:07:42:34:027340 gpfilespace:mdw:gpadmin-[INFO]:-getting config
Enter a name for this filespace
> fs10

Checking your configuration:
Your system has 4 hosts with 6 primary and 0 mirror segments per host.
Your system has 2 hosts with 0 primary and 0 mirror segments per host.

Configuring hosts: [sdw5, sdw1, sdw2, sdw3]

Please specify 6 locations for the primary segments, one per line:
primary location 1> /data1/primary
primary location 2> /data1/primary
/data1/primaryprimary location 3> 
primary location 4> /data1/primary
primary location 5> /data1/primary
primary location 6> /data1/primary

Configuring hosts: [smdw, mdw]

Enter a file system location for the master
master location> 
master location> /data/master
20140426:07:43:08:027340 gpfilespace:mdw:gpadmin-[INFO]:-Creating configuration file...
20140426:07:43:08:027340 gpfilespace:mdw:gpadmin-[INFO]:-[created]
20140426:07:43:08:027340 gpfilespace:mdw:gpadmin-[INFO]:-
To add this filespace to the database please run the command:
   gpfilespace --config /data/home/gpadmin/gpfilespace_config

gpadmin:Fullrack@mdw $ 
  • Create the filespace
gpadmin:Fullrack@mdw $  gpfilespace --config /data/home/gpadmin/gpfilespace_config
20140426:07:50:31:028264 gpfilespace:mdw:gpadmin-[INFO]:-
A tablespace requires a file system location to store its database
files. A filespace is a collection of file system locations for all components
in a Greenplum system (primary segment, mirror segment and master instances).
Once a filespace is created, it can be used by one or more tablespaces.


20140426:07:50:31:028264 gpfilespace:mdw:gpadmin-[INFO]:-getting config
Reading Configuration file: '/data/home/gpadmin/gpfilespace_config'
20140426:07:50:31:028264 gpfilespace:mdw:gpadmin-[INFO]:-Performing validation on paths
..............................................................................

20140426:07:50:32:028264 gpfilespace:mdw:gpadmin-[INFO]:-Connecting to database
20140426:07:50:33:028264 gpfilespace:mdw:gpadmin-[INFO]:-Filespace "fs10" successfully created
  • Create tablespace
gpadmin=# create TABLESPACE ts1 FILESPACE fs10;
CREATE TABLESPACE
Time: 262.837 ms
  • Create database ( which will be used for the explanation ) we will create the database on tablespace ts1 which we have created one step above.
gpadmin=# create database g4 tablespace ts1;
CREATE DATABASE
Time: 8506.713 ms
  • Create a dummy table with data.
g4=# create table a1 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 385
Time: 2449.628 ms

Scenario 1: The future table that gets created , should be on the new tablespace.

  • To do that , you can set the default_tablespace parameter, set it globally using the below command.
template1=# alter database g4 set default_tablespace to pg_default;
ALTER DATABASE
Time: 113.969 ms
  • So the below query shows that the database is by default in the tablespace 17429 ( which is ts1) and the new set of tables will be created on pg_default (datconfig column) tablespace.
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)
  • So attempting to create one new 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
  • Shows us that the new table is indeed created in pg_default tablespace.

The reason why the reltablespace = 0 has been described in the article

g4=# select relname,reltablespace from pg_class where relname in ('a1','a2');
 relname  | reltablespace
----------+--------------
 a1       |           0 
 a2       |        1663 

Time: 0.837 ms
g4=# 
  • If you wish to avoid setting it globally and only want to set it via the user level, you can use.
alter user <username> set default_tablespace to <new_tablespace_name>;

Scenario 2: Moving all the data from the database to a new tablespace.

Please NOTE: Pivotal Greenplum was inherited from postgres version 8.2 so the command below is available from postgres after 8.2.

alter database <database_name> set 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)
  • Which is for the tablespace ts1
g4=# select * from pg_tablespace where oid=17429;
 spcname | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid
---------+----------+-------------+--------+-----------------+-----------------+----------
 ts1     |       10 |             |        |                 |                 |    17428
(1 row)
  • So in order to change the default tablespace you will need to create a new database with the template as 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)
  • A quick look on the pg_database show the database has been created with pg_default has the database.
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)
  • As you can see 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)

The reason why the reltablespace = 0 has been described in the article

  • Now you can drop the original database and mark the new database has default.
template1=# drop database g4;
DROP DATABASE
Time: 1092.213 ms
template1=# alter database g5 rename to g4;
ALTER DATABASE
Time: 92.953 ms

Comments

Powered by Zendesk