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

Source: Internet
Author: User
Tags system log superuser permission

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.