greenplum schemaSchema是Database中逻辑组织object和data。
在同一Database中,不同schema的对象可以使用相同的名称。
例如:A schema 中表叫tab1, B schema中表也可以叫tab1. 但是在同一个schema中就会报错
=> SELECT * FROM myschema.mytable;1注意:如果sql中指定了schema名字的话,就查询指定schema,否则查询search path中配置参数。
------管理命令------
创建数据库 create database mydb;
切换数据库 \c dbname
[创建schema] => CREATE SCHEMA myschema;
[创建并设置owner] => CREATE SCHEMA myschema AUTHORIZATION username;
[查看当前schema] => SELECT current_schema();
[查看search path] => SHOW search_path;
[修改search path] => ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog;
[删除schema] => DROP SCHEMA myschema;
[删除schema及数据库中所有对象] => DROP SCHEMA myschema CASCADE;
------用户级schema------
public => GP默认安装,default schema
psql中切换schema set search_path to test_schema
查询一个库下面各Schema占用空间:
1. select pg_size_pretty(cast( sum(pg_relation_size( schemaname || '.' || tablename)) as bigint)), schemaname from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname group by schemaname;2. select schemaname, count(tablename), pg_size_pretty(cast(sum(pg_relation_size(schemaname||'.'||tablename)) as bigint)) as schema_size from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname group by schemaname;3. 查看schema磁盘使用量和配额 create schema s1; set search_path to s1; //必须要设置,要不然建表时候找不到schema create table a(i int); select * from diskquota.show_fast_schema_quota_view; 注:建表之后才能看到磁盘配额123456789greenplum元数据信息
#1.元数据重要的表
1. pg_database:所有的数据库信息2. pg_namespace:所有的schema信息3. pg_class :所有的表信息4. pg_attribute:所有的属性信息5. pg_proc : 函数信息,包括自定义函数
以上都可以以select * from xxxx;去查看
强制删除数据库在删除数据库的时候如果遇到一下报错:ERROR: database “mydb” is being accessed by other usersDETAIL: There are 3 other sessions using the database.需要执行以下命令:
SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activityWHERE datname='mydb' AND pidpg_backend_pid();
然后再删除
drop DATABASE mydb;
本文来源:https://blog.csdn.net/qq_33783361/article/details/113364396