Greenplum+hadoop Learning Notes -14-defining database objects Creating and managing databases

6. Defining database Objects 6.1. Creating and Managing Databases

Use the \h command to view the syntax for creating a database, as follows:

testdw-# \h Create database command:     Create database Description:create A new database Syntax: Create database name     [[with] [OWN ER [=] Dbowner]            [Template [=] template] & nbsp;          [ENCODING [=] ENCODING]             [Lc_collate [=] lc_collate]             [LC_CTYPE [=] lc_ctype]             [tablespace [=] tablespace]            [CONNECTION LIMIT [=] Connlimit]]                  Connection Restrictions

Description

• A gpdb system can have multiple databases;
• About the database template: You can create a database based on a template, the default database templates for the TEMPLATE1,GP system internal use of: Template0 and Postgres;
• Create database should have Createdb permission or superuser identity;
• Created by CreateDatabase command;

template1=# CREATE DATABASE Devdw; CREATE DATABASE

• Cloning a Database

Template1=#\c devdw using \c to connect to the database Connected to Database "DEVDW" as User "Gpadmin". devdw=# CREATE TABLE tab_01 (id int); Create a table Notice:table doesn ' t has ' distributed by ' clause--Using column named ' ID ' 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 is the optimal data distribution key to minimize skew. CREATE TABLE devdw=# \d using \d to view table information List of relations Schema | Name |  Type | Owner | Storage --------+--------+-------+---------+--------- Public | tab_01 | Table | Gpadmin | Heap (1 row) devdw=# INSERT into TAB_01 values (101); inserting data into a table INSERT 0 1 devdw=# select * from tab_01; Querying the information in a table Id ----- 101 (1 row) template1=# CREATE database col_devdw template DEVDW; To create a clone database using devdw as a template CREATE DATABASE template1=# \l using the \l command to view all current databases List of databases Name | Owner |  Encoding | Access Privileges -----------+---------+----------+--------------------- COL_DEVDW | Gpadmin | UTF8 | DEVDW | Gpadmin | UTF8 | Postgres | Gpadmin | UTF8 | Template0 | Gpadmin | UTF8 | =c/gpadmin : Gpadmin=ctc/gpadmin template1 | Gpadmin | UTF8 | =c/gpadmin : Gpadmin=ctc/gpadmin TESTDW | Gpadmin | UTF8 | (6 rows) template1=# \c COL_DEVDW Connected to Database "COL_DEVDW" as User "Gpadmin". col_devdw=# \l List of databases Name | Owner |  Encoding | Access Privileges -----------+---------+----------+--------------------- COL_DEVDW | gpadmin | UTF8 | DEVDW | Gpadmin | UTF8 | Postgres | Gpadmin | UTF8 | Template0 | Gpadmin | UTF8 | =c/gpadmin : Gpadmin=ctc/gpadmin template1 | Gpadmin | UTF8 | =c/gpadmin : Gpadmin=ctc/gpadmin TESTDW | Gpadmin | UTF8 | (6 rows) col_devdw=# \d List of relations Schema | Name |  Type | Owner | Storage --------+--------+-------+---------+--------- Public | tab_01 | Table | Gpadmin | Heap (1 row)

• View a list of databases: You can query the Pg_database System log table

template1=# \l List of databases Name | Owner |  Encoding | Access Privileges -----------+---------+----------+--------------------- DEVDW | Gpadmin | UTF8 | Gpadmin | Gpadmin | UTF8 | Postgres | Gpadmin | UTF8 | Template0 | Gpadmin | UTF8 | =c/gpadmin : Gpadmin=ctc/gpadmin template1 | Gpadmin | UTF8 | =c/gpadmin : Gpadmin=ctc/gpadmin TESTDW | Gpadmin | UTF8 | (6 rows) template1=# SELECT * from Pg_database; Datname | DATDBA | encoding | Datistemplate | Datallowconn | Datconnlimit | datlastsysoid | Datfrozenxid | Dattablespace |              Datconfig | Datacl -----------+--------+----------+---------------+--------------+--------------+---------------+--------------+-- -------------+-----------+----------------------------------  testdw    |     |        6 | f              | t            |           -1 |         10899 |           803 |          1663 |            |  postgres  |     |        6 | t              | t            |           -1 |         10899 |           803 |          1663 |            |  devdw     |     |        6 | f             | t            |           -1 |         10899 |           803 |          1663 |            | template1 |        10 | 6 | T |           T |         -1 |          10899 |          803 |           1663 | | {=c/gpadmin,gpadmin=ctc/g Padmin} Template0 |        10 | 6 | T |           f |         -1 |          10899 |          803 |           1663 | | {=c/gpadmin,gpadmin=ctc/g Padmin} (5 rows)

• Change database: You must have owner or Superuser permissions and use the Alterdatabase command to change the properties of the DB

template1=# \h ALTER DATABASE Command:alter DATABASE Description:change a Database Syntax: ALTER DATABASE name [[with] option [...]] Where option can be: CONNECTION LIMIT Connlimit ALTER DATABASE name SET parameter {to | =} {value | DEFAULT} ALTER DATABASE name RESET parameter ALTER DATABASE name RENAME to NewName ALTER DATABASE name OWNER to New_owner

template1=# ALTER DATABASE DEVDW set Search_path to Public,pg_catalog; ALTER DATABASE template1=# select * from Pg_database; Datname | DATDBA | encoding | Datistemplate | Datallowconn | Datconnlimit | datlastsysoid | Datfrozenxid |             Dattablespace |              Datconfig | Datacl -----------+--------+----------+---------------+--------------+--------------+---------------+--------------+-- -------------+------------------------------------+----------------------------------  testdw    |     |        6 | f              | t            |           -1 |         10899 |           803 |          1663 |                                      |  postgres  |     |        6 | t              | t            |           -1 |         10899 |           803 |          1663 |                                      |  col_devdw |     |        6 | f              | t            |            -1 |         10899 |           803 |          1663 |                                      |  template1 |     |        6 | t              | t            |           -1 |         10899 |           803 |          1663 |                                      | {=c/gpadmin,gpadmin=ctc/gpadmin}  template0 |     |        6 | t              | f            |           -1 |         10899 |           803 |          1663 |                                      | {=c/gpadmin,gpadmin=ctc/gpadmin} DEVDW |        10 | 6 | f |           T |         -1 |          10899 |          803 | 1663 | {"Search_path=public, Pg_catalog"} | (6 rows)

• Delete database: Must have owner or Superuser permission, delete by CreateDatabase command

testdw-# \h Drop Database Command:drop DATABASE Description:remove a Database Syntax: DROP DATABASE [IF EXISTS] Name

template1=# drop Database Col_devdw; DROP DATABASE

Greenplum+hadoop Learning Notes -14-defining database objects Creating and managing databases