[toc]

连接信息

查看当前连接信息

1
2
3
4
5
6
7
8
\c 和 \conninfo  
db01-# \c [database name] [] -- 查看当前连接信息
db01-# \c
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "db01" as user "omm".
db01-# \conninfo
You are connected to database "db01" as user "omm" via socket in "/opt/mogdb/tmp" at port "26000".

数据库（database）

查看当前集群中的数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
\l -- 查看当前集群中的数据库
MogDB=# \l
                                 List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility
-----------+-------+----------+---------+-------+-------------------+---------------
 db01      | omm   | UTF8     | C       | C     |                   | A
 postgres  | omm   | UTF8     | C       | C     |                   | A
 template0 | omm   | UTF8     | C       | C     | =c/omm           +| A
           |       |          |         |       | omm=CTc/omm       |
 template1 | omm   | UTF8     | C       | C     | =c/omm           +| A
           |       |          |         |       | omm=CTc/omm       |
(4 rows)

SELECT datname FROM pg_database; -- 使用SQL 语句查看已有的数据库：

MogDB=# SELECT datname FROM pg_database;
  datname
-----------
 template1
 db01
 template0
 postgres
(4 rows)

创建数据库

1
create database db01;

系统默认创建了 3 个数据，其中 template0 和template1 是模板数据库，创建新的数据库时默认基于template1 进行复制；postgres 数据库是为 postgres 用户创建的默认数据库。

创建数据库时还可以指定许多选项，例如字符集编码、拥有者、默认表空间、最大连接数等等。具体参考官方文档中完整的 CREATE DATABASE 语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE DATABASE name
    [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ STRATEGY [=] strategy ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ ICU_LOCALE [=] icu_locale ]
           [ ICU_RULES [=] icu_rules ]
           [ LOCALE_PROVIDER [=] locale_provider ]
           [ COLLATION_VERSION = collation_version ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ]
           [ OID [=] oid ]

默认创建的 database 是当前用户的 owner；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
db01=> \conninfo
You are connected to database "db01" as user "admin" via socket in "/opt/mogdb/tmp" at port "26000".
liups=>
db01=> \l
                                 List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility
-----------+-------+----------+---------+-------+-------------------+---------------
 db01      | omm   | UTF8     | C       | C     |                   | A
 postgres  | omm   | UTF8     | C       | C     |                   | A
 template0 | omm   | UTF8     | C       | C     | =c/omm           +| A
           |       |          |         |       | omm=CTc/omm       |
 template1 | omm   | UTF8     | C       | C     | =c/omm           +| A
           |       |          |         |       | omm=CTc/omm       |

liups=> create database admindb;
CREATE DATABASE
liups=> \l
                                 List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility
-----------+-------+----------+---------+-------+-------------------+---------------
 admindb   | admin | UTF8     | C       | C     |                   | A
 db01      | omm   | UTF8     | C       | C     |                   | A
 postgres  | omm   | UTF8     | C       | C     |                   | A
 template0 | omm   | UTF8     | C       | C     | =c/omm           +| A
           |       |          |         |       | omm=CTc/omm       |
 template1 | omm   | UTF8     | C       | C     | =c/omm           +| A
           |       |          |         |       | omm=CTc/omm       |

可以看到 当前以 admin 用户登录到 db01 database；执行 create database admindb; 可以看到，数据库 admindb 的 owner 是 admin .

查看当前数据库

1
2
3
4
5
6
7
select current_Database;
db01=> select current_database();
 current_database
------------------
 db01
(1 row)

模式（schema）

用于查看当前数据库中的模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
\dn  -- 用于查看当前数据库中的模式
db01-# \dn
     List of schemas
      Name       | Owner
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 dbe_sql_util    | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm

创建模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 admindb 中创建一个新的模式：
admindb=> \dn
     List of schemas
      Name       | Owner
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 dbe_sql_util    | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(11 rows)

admindb=> CREATE SCHEMA hr;
CREATE SCHEMA
admindb=> \dn
     List of schemas
      Name       | Owner
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 dbe_sql_util    | omm
 hr              | admin
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(12 rows)

创建模式时还可以指定它的拥有者：
testdb=# CREATE SCHEMA app AUTHORIZATION tony;
CREATE SCHEMA
testdb=# \dn
List of schemas
Name | Owner
--------+----------
app | tony
hr | postgres
public | pg_database_owner
(3 rows)
以pg_开头的名称是系统保留的模式名称，用户无法创建这样的模式。
创建了模式之后，我们就可以在模式中创建各种数据库对象，

关于模式：

  • 默认创建一个 public 模式
  • 每个 database 可以有多个模式(schema)
  • 不同模式(schema)下的对象可以重名
  • 每个database下的模式可以重名
  • 一个schema只属于一个database，不同database下的schema可以重名，互不影响。
  • 会自动创建一个跟用户名同名的schema（MogDB）

查看当前模式

1
2
3
4
5
db01=> select current_schema();
 current_schema
----------------
 admin
(1 row)

查看模式属于哪个库

1
2
3
4
5
6
7
SELECT 
    catalog_name as database,
    schema_name as schema
FROM 
    information_schema.schemata
WHERE 
    schema_name = 'yourSchemaName';

切换模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
db01=> show search_path;
  search_path
----------------
 "$user",public
(1 row)

SET search_path TO myschema;
f002=> SET search_path TO myschema;
SET
f002=> show search_path;
 search_path
-------------
 myschema
(1 row)

f002=> select current_schema();
 current_schema
----------------

(1 row)

f002=> SET search_path TO public;
SET
f002=> select current_schema();
 current_schema
----------------
 public
(1 row)

f002=> create schema f02;
CREATE SCHEMA
f002=> SET search_path TO f02;
SET
f002=> select current_schema();
 current_schema
----------------
 f02
(1 row)

切换 schema 的时候，如果schema不存在，通过 select current_schema(); 显示为空

用户 （role）

查看数据库中的用户

1
2
3
4
5
6
7
8
9
10
11
12
13
\du -- 查看数据库的用户（可以登录的role，用户和角色都是角色，只是用户是具有登录权限的角色。）
db01=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

db01=# select rolname  from pg_catalog.pg_roles where rolcanlogin='t';--查询可以登录的角色也就是用户
db01=# select rolname  from pg_catalog.pg_roles where rolcanlogin='t';
 rolname
---------
 omm
(1 row)

创建用户

1
2
3
4
5
6
7
8
9
-- 只有具有 LOGIN 属性的角色才能连接数据库。
-- 具有 LOGIN 角色的用户可以被看作一个“数据库用户”。
-- 使用以下语句创建具有登录特权的角色：
CREATE ROLE user01 LOGIN ;
CREATE USER user02  ;
admindb=> create user tomy;
ERROR:  The password could not be NULL.
-- 创建用户需要制定密码，
-- CREATE USER 与CREATE ROLE 都可以用于创建角色，只不过CREATE USER 默认包含了 LOGIN 选项，而CREATE ROLE 没有。

relations 信息

查看表结构信息

1
2
3
(options: S = show system objects, + = additional detail)
\d[S+]                 list tables, views, and sequences
\d[S+]  NAME           describe table, view, sequence, or index

\d 列出 tables, views, and sequences，选项 S 显示 系统对象，选项 + 显示附加信息比如多了 Size 和 Description

\d name 显示表结构、视图、序列或者索引信息。

查看数据库中所有对象的数量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
select
	nsp.nspname as SchemaName ,
	case
		cls.relkind when 'r' then 'TABLE'
		when 'm' then 'MATERIALIZED_VIEW'
		when 'i' then 'INDEX'
		when 'S' then 'SEQUENCE'
		when 'v' then 'VIEW'
		when 'c' then 'composite type'
		when 't' then 'TOAST'
		when 'f' then 'foreign table'
		when 'p' then 'partitioned_table'
		when 'I' then 'partitioned_index'
		else cls.relkind::text
	end as ObjectType,
	COUNT(*) cnt
from
	pg_class cls
join pg_namespace nsp on
	nsp.oid = cls.relnamespace
where
	nsp.nspname not in ('information_schema', 'pg_catalog')
	and nsp.nspname not like 'pg_toast%'
group by
	nsp.nspname,
	cls.relkind
union all
select
	n.nspname as "Schema",
	case
		p.prokind when 'a' then 'agg'
		when 'w' then 'window'
		when 'p' then 'proc'
		else 'func'
	end as "Type",
	COUNT(*) cnt
from
	pg_catalog.pg_proc p
left join pg_catalog.pg_namespace n on
	n.oid = p.pronamespace
where
	pg_catalog.pg_function_is_visible(p.oid)
	and n.nspname not in ('information_schema', 'pg_catalog')
group by
	n.nspname ,
	p.prokind;

原文作者: Hi.MogDB

原文链接: https://hi.mogdb.org/posts/59d0ed5e/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议