Greenplum 数据库和schema管理

Greenplum 数据库和schema管理文章目录Greenplum 数据库和schema管理关于模板和默认数据创建一个数据库克隆一个数据库创建一个不同Owner的数据库查看数据库的列表修改一个数据库删除一个数据库Scheme管理系统SCHEMA一个Greenplum数据库是Greenplum数据库的一个单一实例。 可能会安装有多个单独的Greenplum数据库系统，但是通常只会用环境变量设置选择其中一个。 详情请咨询用户的Greenplum管理员。

一个Greenplum数据库系统中可以有多个数据库。 这与某些数据库管理系统（如Oracle）不同，那些系统中数据库实例 就是数据库。 尽管用户可以在一个Greenplum系统中创建很多数据库，但是客户端程序一次只能连接上并且访问一个数据库，因此用户无法跨数据库进行查询。

关于模板和默认数据Greenplum数据库提供了一些模板数据库和一个默认数据库 template1，template0和postgres。

默认情况下，每个新创建的数据库都是基于一个模板数据库。 Greenplum数据库默认使用template1作为模板，除非手动指定另一个模板。 不推荐在在模板数据库template1里创建对象。否则，对象将会出现在所有使用默认模板创建的数据库里。

Greenplum数据库内部使用另外一个模板数据库template0。不要删除和更改template0。你可以使用template0创建一个完全干净的，仅包含Greenplum数据库初始化时预定义好的标准对象的数据库。

你可以使用postgres作为次连接Greenplum数据库时使用的数据库。Greenplum数据库使用postgres作为管理连接的默认数据库。 例如，postgres被启动进程，全局死锁检测进程和FTS（故障恢复服务）进程在访问catalog表时使用。

创建一个数据库create database 具体语法

SynopsisCREATE DATABASE name [ [WITH] [OWNER [=] dbowner][TEMPLATE [=] template][ENCODING [=] encoding][TABLESPACE [=] tablespace][CONNECTION LIMIT [=] connlimit ] ]

CREATE DATABASE命令会创建一个新的数据库。例如：

=> CREATE DATABASE new_dbname;

create database yanwei;

要创建一个数据库，用户必须具有创建一个数据库的特权或者是一个Greenplum数据库超级用户。 如果用户没有正确的特权，用户就不能创建数据库。可以联系用户的Greenplum数据库管理员为用户授予必要的特权或者替用户创建一个数据库。

用户还可以使用客户端程序createdb来创建一个数据库。 例如，在一个使用提供的主机名和端口连接到Greenplum数据库命令行终端中运行下列命令，它将会创建一个名为mydatabase的数据库：

$ createdb -h masterhost -p 5432 mydatabase

[gpadmin@mdw ~]$ createdb -h 127.0.0.1 -p 5432 yanwei1[gpadmin@mdw ~]$ psqlpsql (8.3.23)Type "help" for help.

archdata=# \d List of relations Schema | Name | Type | Owner | Storage --------+------+-------+---------+--------- public | tb1 | table | gpadmin | heap public | tb2 | table | gpadmin | heap public | test | table | gpadmin | heap

上述主机名和端口必须匹配所安装的Greenplum数据库系统的主机名和端口。

一些对象（如角色）会在一个Greenplum数据库系统的所有数据库之间共享 其他对象（例如用户创建的表）则只出现在创建它们的数据库中。

Warning: CREATE DATABASE命令不是事务性的。

克隆一个数据库默认情况下，一个新数据库通过克隆标准系统数据库模板template1而创建。其实在创建新数据库时，任何一个数据库都可以被用作模板，这样就提供了“克隆”或者复制一个现有数据库及其所包含的所有对象和数据的能力。例如：

=> CREATE DATABASE new_dbname TEMPLATE old_dbname;

创建一个不同Owner的数据库在创建数据库时可以指定另一个数据库Owner：

=> CREATE DATABASE new_dbname WITH owner=new_user;

查看数据库的列表如果用户在使用psql客户端程序，用户可以使用 \l元命令来显示用户的Greenplum数据库系统中的数据库和模板的列表。 如果使用的是另一种客户端程序并且用户是超级用户，用户可以从 pg_database系统目录表查询数据库的列表。例如：

=> SELECT datname from pg_database;

archdata=# select * from pg_database;-[ RECORD 1 ]-+---------------------------------datname | template1datdba | 10encoding | 6datistemplate | tdatallowconn | tdatconnlimit | -1datlastsysoid | 12096datfrozenxid | 681dattablespace | 1663datconfig | datacl | {=c/gpadmin,gpadmin=CTc/gpadmin}-[ RECORD 2 ]-+---------------------------------datname | template0datdba | 10encoding | 6datistemplate | tdatallowconn | fdatconnlimit | -1datlastsysoid | 12096datfrozenxid | 681dattablespace | 1663datconfig | datacl | {=c/gpadmin,gpadmin=CTc/gpadmin}-[ RECORD 3 ]-+---------------------------------datname | postgresdatdba | 10encoding | 6datistemplate | tdatallowconn | tdatconnlimit | -1datlastsysoid | 12096datfrozenxid | 681dattablespace | 1663datconfig | datacl | -[ RECORD 4 ]-+---------------------------------datname | archdatadatdba | 10encoding | 6datistemplate | fdatallowconn | tdatconnlimit | -1datlastsysoid | 12096datfrozenxid | 681dattablespace | 1663datconfig | datacl | -[ RECORD 5 ]-+---------------------------------datname | gpperfmondatdba | 10encoding | 6datistemplate | fdatallowconn | tdatconnlimit | -1datlastsysoid | 12096datfrozenxid | 681dattablespace | 1663datconfig | datacl | {gpadmin=CTc/gpadmin,=c/gpadmin}-[ RECORD 6 ]-+---------------------------------datname | yanweidatdba | 10encoding | 6datistemplate | fdatallowconn | tdatconnlimit | -1datlastsysoid | 12096datfrozenxid | 681dattablespace | 1663datconfig | datacl | -[ RECORD 7 ]-+---------------------------------datname | yanwei1datdba | 10encoding | 6datistemplate | fdatallowconn | tdatconnlimit | -1datlastsysoid | 12096datfrozenxid | 681dattablespace | 1663datconfig | datacl |

archdata=# select pg_encoding_to_char(6) from pg_database;-[ RECORD 1 ]-------+-----pg_encoding_to_char | UTF8-[ RECORD 2 ]-------+-----pg_encoding_to_char | UTF8-[ RECORD 3 ]-------+-----pg_encoding_to_char | UTF8-[ RECORD 4 ]-------+-----pg_encoding_to_char | UTF8-[ RECORD 5 ]-------+-----pg_encoding_to_char | UTF8-[ RECORD 6 ]-------+-----pg_encoding_to_char | UTF8-[ RECORD 7 ]-------+-----pg_encoding_to_char | UTF8

column type references descriptiondatname name Database name. datdba oid pg_authid.oid Owner of the database, usually the user who created it.encoding int4 Character encoding for this database. pg_ encoding_to_char() can translate this number to the encoding name. datistemplate boolean If true then this database can be used in the TEMPLATE clause of CREATE DATABASE to create a new database as a clone of this one. datallowconn boolean If false then no one can connect to this database. This is used to protect the template0 database from being altered. datconnlimit int4 Sets the maximum number of concurrent connections that can be made to this database. -1 means no limit. datlastsysoid oid Last system OID in the database 修改一个数据库ALTER DATABASE命令可以修改数据库的属性，例如拥有者、名称或者默认配置属性。 例如，下面的命令会修改一个数据库的默认方案搜索路径（ search_path配置参数）：

=> ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog;

show search_path-[ RECORD 1 ]---------------search_path | "$user",publicPostgreSQL会首先在和当前用户的用户名相同的模式中查询，如果相关数据不在此模式中，则会继续在路径中的下一个模式中查询（上图中为public），以此类推。

archdata=# select catalog_name,schema_name,schema_owner from information_schema.schemata; catalog_name | schema_name | schema_owner --------------+--------------------+-------------- archdata | pg_catalog | gpadmin archdata | pg_toast | gpadmin archdata | pg_bitmapindex | gpadmin archdata | public | gpadmin archdata | pg_aoseg | gpadmin archdata | information_schema | gpadmin archdata | gp_toolkit | gpadmin

要修改一个数据库，用户必须是该数据库的拥有者或者超级用户。

alter database yanwei with connection limit 200 ;

-[ RECORD 7 ]-+---------------------------------datname | yanweidatdba | 10encoding | 6datistemplate | fdatallowconn | tdatconnlimit | 200datlastsysoid | 12096datfrozenxid | 681dattablespace | 1663datconfig | datacl |

删除一个数据库DROP DATABASE命令删除一个数据库。 它会移除该数据库的系统目录项并且删除该数据库在磁盘上的目录及其中包含的数据。 要删除一个数据库，用户必须是该数据库的拥有者或者超级用户，并且当用户或者其他人正连接到该数据库时不能删除它。 在删除一个数据库时，可以连接到postgres（或者另一个数据库）。例如：

=> \c postgres => DROP DATABASE mydatabase;

用户也可以使用客户端程序dropdb来删除一个数据库。 例如，下面的命令会用给出的主机名和端口连接到Greenplum数据库并且删除数据库mydatabase：

$ dropdb -h masterhost -p 5432 mydatabase

Warning: 删除数据库不能被撤销。

DROP DATABASE命令不是事务性的。

Scheme管理1 查询当前search_path：

archdata=# show search_path; search_path ---------------- "$user",public(1 row)

2 创建一个测试表

create table test1 (int int);查询该表所属模式：\dtarchdata=# \dt List of relations Schema | Name | Type | Owner | Storage --------+-------+-------+---------+--------- public | one | table | gpadmin | heap public | tb1 | table | gpadmin | heap public | tb2 | table | gpadmin | heap public | test | table | gpadmin | heap public | test1 | table | gpadmin | heap

3 默认的test是创建到了public模式下，因为没有登录名相同的用户

4 创建登录名用户

select catalog_name,schema_name,schema_owner from information_schema.schemata;archdata=# select catalog_name,schema_name,schema_owner from information_schema.schemata; catalog_name | schema_name | schema_owner --------------+--------------------+-------------- archdata | pg_catalog | gpadmin archdata | pg_toast | gpadmin archdata | pg_bitmapindex | gpadmin archdata | public | gpadmin archdata | pg_aoseg | gpadmin archdata | information_schema | gpadmin archdata | gp_toolkit | gpadmin(7 rows)

archdata=# create schema gpadmin;

5 这样默认创建的表就在gpadmin模式下了

archdata=# create schema gpadmin;CREATE SCHEMAarchdata=# create table four (int int);NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'int' 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.CREATE TABLEarchdata=# \dt List of relations Schema | Name | Type | Owner | Storage ---------+-------+-------+---------+--------- gpadmin | four | table | gpadmin | heap public | one | table | gpadmin | heap public | tb1 | table | gpadmin | heap public | tb2 | table | gpadmin | heap public | test | table | gpadmin | heap public | test1 | table | gpadmin | heap(6 rows)

6 显性的创建表

create table public.seven(int int);

create table gpadmin.SIX(int int);

archdata=# \dt List of relations Schema | Name | Type | Owner | Storage ---------+-------+-------+---------+--------- gpadmin | four | table | gpadmin | heap gpadmin | six | table | gpadmin | heap public | one | table | gpadmin | heap public | seven | table | gpadmin | heap public | tb1 | table | gpadmin | heap public | tb2 | table | gpadmin | heap public | test | table | gpadmin | heap public | test1 | table | gpadmin | heap

6 查询schema和该schema下的表

SELECT pn.oid AS schema_oid, iss.catalog_name, iss.schema_owner, iss.schema_nameFROM information_schema.schemata iss INNER JOIN pg_namespace pn ON pn.nspname = iss.schema_name;

archdata=# SELECT pn.oid AS schema_oid, iss.catalog_name, iss.schema_owner, iss.schema_namearch; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers ------------+-----------+------------+------------+------------+----------+------------- gpadmin | four | gpadmin | | f | f | f gpadmin | six | gpadmin | | f | f | f(2 rows)

7 删除scheme

drop schema gpadmin; --空schema

drop schema gpadmin cascade;--一起删除

archdata=# SELECT pn.oid AS schema_oid, iss.catalog_name, iss.schema_owner, iss.schema_namearchdata-# FROM information_schema.schemata issarchdata-# INNER JOIN pg_namespace pn ON pn.nspname = iss.schema_name; schema_oid | catalog_name | schema_owner | schema_name ------------+--------------+--------------+-------------------- 11 | archdata | gpadmin | pg_catalog 99 | archdata | gpadmin | pg_toast 3012 | archdata | gpadmin | pg_bitmapindex 2200 | archdata | gpadmin | public 6104 | archdata | gpadmin | pg_aoseg 11724 | archdata | gpadmin | information_schema 11916 | archdata | gpadmin | gp_toolkit(7 rows)

系统SCHEMA下列系统级schema存在于每一个数据库中：

pg_catalog包含着系统目录表、内建数据类型、函数和操作符。即便在schema搜索路径中没有显式地提到它，它也总是schema搜索路径的一部分。information_schema有一个包含数据库中对象信息的视图集合组成。这些视图以一种标准化的方式从系统目录表中得到系统信息。pg_toast存储大型对象，如超过页面尺寸的记录。这个schema由Greenplum数据库系统内部使用。pg_bitmapindex存储位图索引对象，例如值的列表。 这个schema由Greenplum数据库系统内部使用。pg_aoseg存储追加优化表对象。 这个schema由Greenplum数据库系统内部使用。gp_toolkit是一个管理用途的schema，它包含用户可以用SQL命令访问的外部表、视图和函数。 所有的数据库用户都能访问gp_toolkit来查看和查询系统日志文件以及其他系统指标。

本文来源：https://blog.csdn.net/murkey/article/details/105925047