MogDB基础系列–创建和管理数据库
原作者:杨有田
1.数据库逻辑结构介绍
上一篇文章，我们介绍了表空间，现在学习如何管理数据库这个“对象”。大家都知道，实例（内存和进程）和数据库有一定的对应关系，oracle（非RAC）是一对一，一个实例对应一个数据库；而MogDB是一对多，每个实例管理一个或多个数据库。下面是简要介绍：
1）数据库是模式的集合， 而模式包含表、函数等等。
2）每个实例管理一个或多个数据库。
3）每个数据库的数据可以存放到多个表空间。
4）每个表空间可以存储多个数据库的数据。
5）表空间可以简单理解为操作系统的目录。
6）Datafile Segment，即数据文件，通常每张表只对应一个数据文件。如果某张表的数据大于1GB，则会分为多个数据文件存储。
7）数据库系统中会有多个数据库，但是客户端程序一次只能连接一个数据库。也不能在不同的数据库之间相互查询
8）block，默认8k
2.创建数据库有哪些注意点
MogDB初始化默认包含3个数据库，分别是两个模板数据库template0和template1，以及一个默认用户数据库postgres。对于模板数据库，避免使用客户端或其他手段连接及操作。
我们先来看看创建数据库命令create database有哪些选项和系统表PG_DATABASE有哪些字段，从这里来了解创建数据库需要关注的地方。
–命令create database
MogDB=# \h create database
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE database_name
[ [ WITH ] {[ OWNER [=] user_name ]|
[ TEMPLATE [=] template ]|
[ ENCODING [=] encoding ]|
[ LC_COLLATE [=] lc_collate ]|
[ LC_CTYPE [=] lc_ctype ]|
[ DBCOMPATIBILITY [=] compatibility_type ]|
[ TABLESPACE [=] tablespace_name ]|
[ CONNECTION LIMIT [=] connlimit ]}[...] ];
MogDB=#
--系统表pg_database
从这两个地方可以看到，需要重点关注的点有，数据库owner、字符集编码(ENCODING)和兼容性(DBCOMPATIBILITY)等配置项。默认字符集编码为UTF-8；兼容性，A、B、PG，分别表示兼容Oracle、MySQL和PostgreSQL的语法，不同兼容模式下的语法行为存在一定差异，默认的兼容数据库类型为O（即DBCOMPATIBILITY = A ）。
MogDB=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility | Size | Tablespace | Description
-----------+-------+----------+-------------+-------------+-------------------+---------------+-------+------------+--------------------------------------------
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | A | 13 MB | pg_default | default administrative connection database
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A | 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | | |
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A | 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | | |
(3 rows)
MogDB=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Description
------------+-------+---------------------------+-------------------+-------------
pg_default | omm | | |
pg_global | omm | | |
tsd_leo01 | omm | /opt/mogdb/data/tsd_leo01 | |
tsd_leo02 | omm | /opt/mogdb/data/tsd_leo02 | |
(4 rows)
CREATE DATABASE实际上通过拷贝模板数据库来创建新数据库。支持拷贝template0，如果使用template1提示报错不支持。
MogDB=# create database leo02 template template1;
ERROR: template1 is not supported for using here, just support template0
MogDB=# create database leo02 template template0 tablespace tsd_leo02;
CREATE DATABASE
MogDB=# create database leo01 tablespace tsd_leo01 encoding='UTF-8' dbcompatibility='B';
CREATE DATABASE
MogDB=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility | Size | Tablespace | Description
-----------+-------+----------+-------------+-------------+-------------------+---------------+-------+------------+--------------------------------------------
leo01 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | B | 13 MB | tsd_leo01 |
leo02 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | A | 13 MB | tsd_leo02 |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | A | 13 MB | pg_default | default administrative connection database
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A | 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | | |
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A | 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | | |
(5 rows)
MogDB=# select * from pg_database where datname like 'leo%';
datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility | datac
l | datfrozenxid64 | datminmxid
---------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+------
--+----------------+------------
leo02 | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 15935 | 13017 | 16403 | A |
| 13017 | 2
leo01 | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 15935 | 13017 | 16389 | B |
| 13017 | 2
(2 rows)
MogDB=# select datname,encoding,pg_encoding_to_char(encoding) from pg_database;
datname | encoding | pg_encoding_to_char
-----------+----------+---------------------
template1 | 7 | UTF8
template0 | 7 | UTF8
leo02 | 7 | UTF8
postgres | 7 | UTF8
leo01 | 7 | UTF8
(5 rows)
进入表空间tsd_leo01目录，可以看到create database的时候，自动创建了大量系统对象。
[omm@mogdb01 tsd_leo01]$ pwd
/opt/mogdb/data/tsd_leo01
[omm@mogdb01 tsd_leo01]$ cd PG_9.2_201611171_dn_6001_6002/pgsql_tmp/
[omm@mogdb01 pgsql_tmp]$ cd …
[omm@mogdb01 PG_9.2_201611171_dn_6001_6002]$ ls
pgsql_tmp
[omm@mogdb01 PG_9.2_201611171_dn_6001_6002]$ cd 16435/
[omm@mogdb01 16435]$ ls
15354 15392 15429 15465 15494 15519 15543 15571 15624_fsm 15648 15709 15758 15796 15833 15886 15913_vm
15354_fsm 15394 15430 15466 15495 15519_fsm 15543_fsm 15572 15624_vm 15649 15710 15759 15798 15834 15887 15915
15354_vm 15395 15432 15467 15496 15519_vm 15543_vm 15573 15626 15650 15712 15760 15799 15835 15888 15917
15356 15396 15434 15468 15497 15521 15545 15573_fsm 15627 15652 15713 15761 15801 15840 15889 15918
15357 15398 15435 15468_fsm 15498 15522 15545_fsm 15573_vm 15628 15654 15714 15763 15803 15842 15891 15918_fsm
15358 15400 15437 15468_vm 15499 15523 15545_vm 15575 15628_fsm 15655 15716 15765 15804 15843 15892 15918_vm
15360 15401 15439 15470 15501 15523_fsm 15547 15576 15628_vm 15680 15717 15766 15805 15844 15893 15920
15365 15403 15440 15471 15502 15523_vm 15548 15577 15630 15682 15718 15769 15806 15846 15893_fsm 15922
15365_fsm 15405 15445 15472 15503 15525 15549 15577_fsm 15631 15683 15719 15771 15808 15847 15893_vm 15923
15365_vm 15406 15447 15473 15503_fsm 15526 15550 15577_vm 15632 15685 15721 15772 15809 15860 15895 15925
15367 15408 15448 15475 15503_vm 15527 15552 15579 15632_fsm 15687 15722 15773 15810 15862 15897 15927
15367_fsm 15410 15448_fsm 15477 15505 15528 15554 15580 15632_vm 15688 15724 15775 15811 15863 15898 15928
15367_vm 15411 15448_vm 15478 15506 15528_fsm 15555 15581 15634 15689 15725 15776 15812 15864 15898_fsm 15930
15369 15411_init 15450 15479 15507 15528_vm 15556 15582 15635 15690 15726 15777 15813 15866 15898_vm 15932
15370 15413 15451 15480 15507_fsm 15530 15557 15582_fsm 15636 15692 15728 15778 15814 15867 15900 15933
15371 15413_init 15452 15481 15507_vm 15531 15558 15582_vm 15636_fsm 15693 15729 15779 15816 15868 15902 15934
15373 15415 15452_fsm 15482 15509 15532 15558_fsm 15584 15636_vm 15694 15734 15780 15817 15870 15903 pg_filenode.map
15375 15415_init 15452_vm 15483 15510 15532_fsm 15558_vm 15585 15638 15696 15736 15781 15818 15871 15903_fsm pg_filenode.map.backup
15376 15416 15454 15483_fsm 15511 15532_vm 15560 15617 15639 15698 15741 15782 15819 15872 15903_vm pg_internal.init.92605
15377 15416_init 15454_fsm 15483_vm 15511_fsm 15534 15562 15617_fsm 15640 15699 15743 15784 15821 15874 15905 PG_VERSION
15379 15417 15454_vm 15485 15511_vm 15535 15563 15617_vm 15642 15703 15744 15785 15822 15875 15907
15381 15419 15456 15487 15513 15536 15564 15619 15642_fsm 15703_fsm 15746 15786 15823 15876 15908
15382 15421 15457 15488 15514 15538 15564_fsm 15620 15642_vm 15703_vm 15747 15788 15824 15878 15908_fsm
15384 15422 15458 15489 15515 15539 15564_vm 15621 15644 15705 15750 15789 15826 15879 15908_vm
15386 15424 15459 15490 15515_fsm 15539_fsm 15566 15621_fsm 15645 15706 15752 15791 15827 15880 15910
15387 15426 15460 15490_fsm 15515_vm 15539_vm 15567 15621_vm 15646 15707 15753 15792 15828 15882 15912
15389 15427 15461 15490_vm 15517 15541 15568 15623 15646_fsm 15707_fsm 15754 15794 15830 15883 15913
15391 15428 15463 15492 15518 15542 15570 15624 15646_vm 15707_vm 15756 15795 15831 15884 15913_fsm
[omm@mogdb01 16435]$
MogDB=# select oid,relname,relowner,relfilenode,relkind,reltablespace,relnamespace from pg_class where relfilenode=15932;
oid | relname | relowner | relfilenode | relkind | reltablespace | relnamespace
-------±---------------------±---------±------------±--------±--------------±-------------
15317 | pg_toast_15310_index | 10 | 15932 | i | 0 | 99
(1 row)
进一步连接数据库leo01，创建表tab1和tab2，分别指定不同表空间tsd_leo01和tsd_leo02，其中tsd_leo01为数据库leo01的默认表空间，可以看到pg_class.reltablespace显示默认表空间为0而不是16389。
MogDB=# \c leo01
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database “leo01” as user “omm”.
leo01=# create table tab1(i number);
CREATE TABLE
leo01=# select oid,relname,relowner,relfilenode,relkind,reltablespace,relnamespace from pg_class where relname=‘tab1’;
oid | relname | relowner | relfilenode | relkind | reltablespace | relnamespace
-------±--------±---------±------------±--------±--------------±-------------
16436 | tab1 | 10 | 16436 | r | 0 | 2200
(1 row)
leo01=# create table tab2(i number) tablespace tsd_leo02;
CREATE TABLE
leo01=# select oid,spcname from pg_tablespace;
oid | spcname
-------±-----------
1663 | pg_default
1664 | pg_global
16389 | tsd_leo01
16403 | tsd_leo02
(4 rows)
leo01=# select oid,relname,relowner,relfilenode,relkind,reltablespace,relnamespace from pg_class where relname=‘tab2’;
oid | relname | relowner | relfilenode | relkind | reltablespace | relnamespace
-------±--------±---------±------------±--------±--------------±-------------
16442 | tab2 | 10 | 16442 | r | 16403 | 2200
(1 row)
leo01=#
对比兼容性MySQL和Oracle，‘’和null处理逻辑不一样：
MogDB=# \c leo01
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "leo01" as user "omm".
leo01=# create table tab_leo01(id int,name char(10)) tablespace tsd_leo01 distribute by hash(id);
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
leo01=# \q
[omm@mogdb01 ~]$ g
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# \c leo01
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "leo01" as user "omm".
leo01=# create table tab_leo01(id int,name char(10)) tablespace tsd_leo01;
CREATE TABLE
leo01=# insert into tab_leo01 values(1,'enmo'),(2,''),(3,'pgdatabase'),(4,''),(5,'');
INSERT 0 5
leo01=# insert into tab_leo01 values(1,'enmo'),(2,''),(3,'pgdatabase'),(4,''),(5,'');
INSERT 0 5
leo01=# select * from tab_leo01 where name is null;
id | name
----+------
(0 rows)
leo01=# select * from tab_leo01 where name='';
id | name
----+------------
2 |
4 |
5 |
2 |
4 |
5 |
(6 rows)
leo01=# \c leo02
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "leo02" as user "omm".
leo02=# insert into tab_leo02 values(1,'enmo'),(2,''),(3,'pgdatabase'),(4,''),(5,'');
ERROR: relation "tab_leo02" does not exist on dn_6001_6002
LINE 1: insert into tab_leo02 values(1,'enmo'),(2,''),(3,'pgdatabase...
^
leo02=# create table tab_leo02(id int,name char(10)) tablespace tsd_leo02;
CREATE TABLE
leo02=# insert into tab_leo02 values(1,'enmo'),(2,''),(3,'pgdatabase'),(4,''),(5,'');
INSERT 0 5
leo02=# insert into tab_leo02 values(1,'enmo'),(2,''),(3,'pgdatabase'),(4,''),(5,'');
INSERT 0 5
leo02=# select * from tab_leo02 where name is null;
id | name
----+------
2 |
4 |
5 |
2 |
4 |
5 |
(6 rows)
leo02=# select * from tab_leo02 where name='';
id | name
----+------
(0 rows)
leo02=#
3.其他管理操作
3.1 查看数据库
查看数据库的属性，比如：owner、名称、字符集编码、兼容性等等，可以通过两种方式查看，元命令\l和视图pg_database。
3.2 修改数据库
可以修改数据库的名字、owner、默认表空间、配置参数等等，下面测试下修改数据库的owner：
MogDB=# \h alter database
Command: ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE database_name
[ [ WITH ] CONNECTION LIMIT connlimit ];
ALTER DATABASE database_name
RENAME TO new_name;
ALTER DATABASE database_name
OWNER TO new_owner;
ALTER DATABASE database_name
SET TABLESPACE new_tablespace;
ALTER DATABASE database_name
SET configuration_parameter { { TO | = } { value | DEFAULT } | FROM CURRENT };
ALTER DATABASE database_name
RESET { configuration_parameter | ALL };
ALTER DATABASE database_name
[ WITH ] { ENABLE | DISABLE } PRIVATE OBJECT;
MogDB=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility
-----------+-------+----------+-------------+-------------+-------------------+---------------
leo01 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | B
leo02 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | A
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | A
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A
| | | | | omm=CTc/omm |
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A
| | | | | omm=CTc/omm |
(5 rows)
MogDB=# alter database leo01 owner to leo;
ALTER DATABASE
MogDB=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility
-----------+-------+----------+-------------+-------------+-------------------+---------------
leo01 | leo | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | B
leo02 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | A
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | A
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A
| | | | | omm=CTc/omm |
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A
| | | | | omm=CTc/omm |
(5 rows)
3.3 删除数据库
使用DROP DATABASE命令删除数据库。这个命令删除了数据库中的系统目录，并且删除了带有数据的磁盘上的数据库目录。用户必须是数据库的owner或者系统管理员才能删除数据库。
MogDB=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility
-----------+-------+----------+-------------+-------------+-------------------+---------------
leo01_u | leo | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | B
leo02 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | A
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | A
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A
| | | | | omm=CTc/omm |
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| A
| | | | | omm=CTc/omm |
(5 rows)
MogDB=# drop database leo02;
DROP DATABASE
