[转帖]七. PostgreSQL逻辑结构（1）—数据库和模式

postgresql,逻辑,结构,数据库,模式 · 浏览次数 : 0

小编点评

**数据库模式的相关操作** **创建模式** ```sql CREATE SCHEMA test_schema AUTHORIZATION osdba; ``` **删除模式** ```sql DROP SCHEMA test_schema; ``` **修改模式** ```sql ALTER SCHEMA schema2 RENAME TO sma2; ALTER SCHEMApostgres=# \\dn List of schemas Name | Owner --------+---------- osdba | osdba public | postgres sma2 | postgres(3 rows) ``` **其他** * 默认情况下，会自动创建一个public模式，连接的时候也是连接到public模式。 * 但是因为默认情况下，所有的用户在public模式下都有USAGE和CREATE权限，而具有CREATE权限，就可以在public模式下创建各种对象。 *这种情况下，容易导致数据库管理混乱。 *所以为了方便管理，同时为了数据的安全性，最后不要使用public模式，同时也不要随便对外开放最高权限的postgres用户。 *而应该为每个应用单独建立一个模式，使用和模式对应的用户名，实现专号和专户对应。

正文

https://www.jianshu.com/p/ee8b1bdfdb19

在PostgreSQL里，逻辑结构从高到底依次是：数据库、模式（又叫架构）、表、行。当我们打开PostgreSQL官方自带的客户端软件pgadmin的时候，点开一个实例的连接，如下所示：

image.png

从图中我们可以看到，最外层的就是我们的在244服务器上的数据库服务实例，点开以后，第一层是数据库，我们点开postgres数据库。下层有

目录
事件触发器
拓展
架构

对于目录、时间触发器和拓展这里先不讲。但是要知道他们都是和架构是同一个层级的结构。在PostgreSQL里，架构又被称为模式（Schema），在一个数据库中会有多个模式。点开模式以后，如下所示：

image.png

模式下我们需要关注的对象是：

函数
序列
数据表
触发器函数
视图

后面会依次介绍这些内容，今天要学习的是表的结构。我们点开数据表，里面就可以看到各种表名了，我们随便点开一个表，查看一下表的结构，如下所示：

image.png

可以看到表里有

字段
约束
索引
规则
触发器
这些也是我们要关注的重点内容。后面几章我们来依次学习这些逻辑结构。在没有学习PostgreSQL的权限管理之前，我们默认使用具有最高权限的postgres账户来进行所有操作，同时也建议看这篇文章的所有人在操作之前一定要确认：不是在生产数据库上进行操作。建议最好是在自己的虚拟机上操作。

1. 数据库

对于数据库的操作包括数据库的创建、删除和修改，下面分别讲解：

创建数据库

数据库的创建命令格式是：

CREATE DATABASE dbname [with] 
                [owner [=] role_name] 
                [ TEMPLATE [=] template_name] 
                [ ENCODING [=] encoding] 
                [LC_COLLATE = lc_collate] 
                [LC_CTYPE [=] lc_ctype] 
                [ TABLESPACE [=] tablespace] 
                [CONNECTION LIMIT [=] connlimit ]

参数含义介绍：

with 表示后面会跟哪些参数，可带可不带
owner 表示数据库的所有者，可以指定数据库中存在的角色名，如果不指定，则默认是当前用户。
TEMPLATE 表示这个数据库以哪个模板来创建，前面的章节里讲过PostgreSQL自带两个模板template0和template1，可以在这两个模板的基础上继承生成新的数据库。
ENCODING 表示数据库的字符编码格式，默认是UTF8，开发人员可以根据自己的需要设置成不同的格式。
TABLESPACE 表示和数据库关联的表空间名字，在后面学习表空间的内容的时候介绍。
CONNECTION LIMIT 表示这个数据库允许的连接数，默认值是-1，表示没有限制。连接数的内容在之前数据库管理的章节里讲过。
LC_COLLATE 和 LC_CTYPE 是用于影响数据库字段排序的参数，根据数据库字符编码的格式来设置对应的值。

命令格式和参数含义介绍完以后，我们来看几个示例：

# 1. 创建一个数据库，不带任何参数
postgres=# create database testdb; 
CREATE DATABASE

#查看数据库，可以看到数据库所有者是postgres，就是当前用户，默认编码格式是UTF8
postgres=# \l testdb; 
                              List of databases
  Name  |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
--------+----------+----------+-------------+-------------+-------------------
 testdb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

# 2. 创建的时候指定数据库的所有者
postgres=# CREATE DATABASE testdb1 owner osdba;
CREATE DATABASE

#查看数据库信息，可以看到owner变成了osdba
postgres=# \l testdb1; 
                             List of databases
  Name   | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
---------+-------+----------+-------------+-------------+-------------------
 testdb1 | osdba | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)

删除数据库

删除数据库就一个命令：

DROP DATABASE [ IF EXISTS ] dbname

IF EXISTS 参数是用于避免删除不存在的数据库，不带这个参数删除不存在的数据库时会报错，而带这个参数，则数据库存在时直接删除，不存在时直接跳过。一般在事务处理或脚本中会带上这个参数
几个测试用例如下：

#删除一个不存在的数据库testdb2，会报错。
postgres=# DROP DATABASE testdb2;
ERROR:  database "testdb2" does not exist

#使用IF EXISTS 参数删除不存在的数据库testdb2，会提示跳过，不会报错。
postgres=# DROP DATABASE IF EXISTS testdb2;
NOTICE:  database "testdb2" does not exist, skipping
DROP DATABASE

#删除前面创建的数据库testdb，提示删除成功
postgres=# DROP DATABASE testdb;
DROP DATABASE

#带IF EXISTS关键字删除存在的数据库testdb1，提示和不带关键字时相同
postgres=# DROP DATABASE IF EXISTS testdb1;
DROP DATABASE

修改数据库

因为数据库关联的属性和逻辑结构较多，因此修改数据库的命令比较复杂，命令的基本格式是：

ALTER DATABASE dbname [WITH] [option]

带WITH关键字时，option可以是下面的各种样式：

CONNECTION LIMIT connlimit：connlimit表示连接限制值
ALLOW_CONNECTIONS allowconn ：是否允许连接，allowconn为true时表示允许连接，为false表示不允许任何人连接到数据库
IS_TEMPLATE istemplate：istemplate的值是true或者false，true时表示这个数据库可以作为模板被其他数据库继承，false表示不可继承。

不带with关键字时，option有以下几种样式：

RENAME TO newname 修改数据库的名称
OWNER TO newowner 将数据库的所有者改为另外一个用户，这个命令只能是数据库所有者或者超级用户才能执行。
SET TABLESPACE new_tablespace 将数据库关联的表空间修改为新的表空间
SET config_param {TO | =} {value|DEFAULT} 将某个配置的值设置为某个值或者默认值（DEFAULT)
SET config_param FROM CURRENT 将某个配置的值设置为当前值？
RESET config_param 将某个配置的值恢复为默认值？
RESET ALL 将数据库恢复为默认配置？

示例1：带with关键字

# 查看当前数据库的连接数限制
SELECT datconnlimit FROM pg_database WHERE datname = 'testdb';
 datconnlimit 
--------------
           -1
(1 row)
#可以看到默认的设置是-1，即不限制连接，将它的值修改为1，然后再看
postgres=# alter database testdb with connection limit 1;
ALTER DATABASE
postgres=# select datconnlimit from pg_database where datname = 'testdb'; 
 datconnlimit 
--------------
            1
(1 row)
#这个时候要保证testdb数据库上没有任何连接，我们通过下面这个语句来查看
postgres=# select datname, usename  from pg_stat_activity where datname = 'testdb'; 
 datname | usename 
---------+---------
(0 rows)
# 如果像上面那样显示0行，说明该数据库上没有任何连接。我们打开两个终端，使用普通的osdba用户来连接
#第一次连接
-bash-4.2$ psql -U osdba -d testdb
Password for user osdba: 
psql (9.6.6)
Type "help" for help.

testdb=> 
# 第二次连接 
-bash-4.2$ psql -U osdba -d testdb 
Password for user osdba: 
psql: FATAL:  too many connections for database "testdb"
# 可以看到提示连接太多，说明限制生效。也说我们我们修改的配置生效。

示例2：不带with关键字

# 修改数据库testdb为testdb00
postgres=# ALTER DATABASE testdb RENAME TO testdb00; 
ALTER DATABASE
postgres=# \l testdb00; 
                               List of databases
   Name   |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
----------+----------+----------+-------------+-------------+-------------------
 testdb00 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)
# 修改后查看数据库，可以看到除了名称，数据库的其他信息都没有发生变化。查看数据库的连接限制，如下所示：
postgres=# select datconnlimit from pg_database where datname = 'testdb00'; 
 datconnlimit 
--------------
            1
(1 row)
# 可以看到除了名称变化了，连接限制也还是刚才我们设置的值。

上面就是数据库的相关内容。

2. 模式

在PostgreSQL中，连接到某个数据库上面以后，如果需要查看另外一个数据库的数据，则只能断开当前的数据连接，然后重新连接另外一个数据库。这样的情况下，当前端的应用同时连接多个数据库时，查看数据库来回切换就非常麻烦。而在PostgreSQL里存在模式这样一个概念，表、视图和函数等都是保存到模式里面，在同一个数据库里，可以同时查看多个模式里面的表，只需要通过点号(.)操作符来进行操作即可，格式是：
schema_name.table_name
这样操作的话，就可以实现一个模式对应一个应用，多个应用的数据存放到同一个项目数据库中，方便管理。

公共模式

但是我们在写SQL语句的时候，或者进行数据库连接的时候，却并没有使用这种访问方式，一般都是直接写的表名，而PostgreSQL也没有提示出错，这是为什么呢？这里面涉及到一个概念——公共模式，这个模式的意思是：当新建数据库以及连接数据库时，如果没有指定模式名称，都会自动创建一个叫做public的模式或者连接到public模式。
而PostgreSQL为什么会自动创建public模式或者连接到public模式呢？这是因为在我们的SQL语句中只输入了表名的时候，例如下面这样一个SQL语句：
SELECT * FROM table1 WHERE pub_date = '2017-01-01'
我们给出的表名就是table1，PostgreSQL在用户的搜索路径中查找table1这个表名。而每个用户的搜索路径可以通过search_path这个变量来查看：

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

上面就是postgres用户的搜索路径。搜索路径的规则是：

不同的路径名之间通过逗号隔开，PostgreSQL依次搜索，如果在哪个路径中找到表名，则是用这个路径下的表，如果没找到则查找下一个；
$user指的是连接到当前数据库的用户，如果存在一个和用户名相同的模式，且用户在这个模式中具有USAGE权限，则最先使用这个模式；
public默认在所有的用户路径下，默认所有用户都在public模式下具有USAGE权限，如果创建数据库时没有创建和用户名同名的模式，则登录时默认登录到public模式。

这就是为什么我们登录新建的数据库时，都会在public模式下。下面我们继续深入学习模式相关的操作：创建模式、删除模式和修改模式。

创建模式

创建模式的命令基本格式是：

CREATE SCHEMA schema_name [AUTHORIZATION role_specification] [schema_element [...] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

参数解释：

AUTHORIZATION role_specification：指的是将这个模式赋予给某个在数据库中的用户或角色
schema_element：指的是模式的其他参数，包括CREATE TABLE/VIEW/INDEX/SEQUENCE/TRIGGER和GRANT语句等。用于创建模式下的各种对象。

上面前两个语句分别是创建新的模式并赋权给某个用户，第二个语句是直接创建和某个用户同名的模式。后面两个语句则是在前面两个语句里添加了一个逻辑判断。
来看示例：

# 创建一个名为test_schema的模式
postgres=# CREATE SCHEMA test_schema AUTHORIZATION osdba;
CREATE SCHEMA
postgres=# \dn 
    List of schemas
    Name     |  Owner   
-------------+----------
 public      | postgres
 test_schema | osdba
(2 rows)

#创建一个和osdba用户同名的模式
postgres=# CREATE SCHEMA AUTHORIZATION osdba;
CREATE SCHEMA
postgres=# \dn 
    List of schemas
    Name     |  Owner   
-------------+----------
 osdba       | osdba
 public      | postgres
 test_schema | osdba
(3 rows)

#创建模式的同时创建一张表
postgres=# CREATE SCHEMA schema2 AUTHORIZATION osdba CREATE TABLE t1(id int primary key, comments varchar(50)); 
CREATE SCHEMA

# 查看创建的模式的信息
postgres=# \dn 
    List of schemas
    Name     |  Owner   
-------------+----------
 osdba       | osdba
 public      | postgres
 schema2     | osdba
 test_schema | osdba
(4 rows)

# 查看模式里的表t1的信息
postgres=# \d schema2.t1; 
              Table "schema2.t1"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 id       | integer               | not null
 comments | character varying(50) | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)

删除模式

删除模式的命令格式是：
DROP SCHEMA schema_name
看示例：

#删除刚才创建的一个模式test_schema；
postgres=# DROP SCHEMA test_schema; 
DROP SCHEMA
postgres=# \dn 
  List of schemas
  Name   |  Owner   
---------+----------
 osdba   | osdba
 public  | postgres
 schema2 | osdba
(3 rows)

直接删除，非常简单。

修改模式

对于模式，只允许修改其名称和它的属主这两种信息，命令格式如下：

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

示例如下：

#将当前的schema2模式修改未sma2
postgres=# ALTER SCHEMA schema2 RENAME TO sma2;
ALTER SCHEMA
postgres=# \dn 
  List of schemas
  Name  |  Owner   
--------+----------
 osdba  | osdba
 public | postgres
 sma2   | osdba
(3 rows)

# 将sma2的属主修改为postgres
postgres=# ALTER SCHEMA sma2 OWNER TO postgres; 
ALTER SCHEMA
postgres=# \dn 
  List of schemas
  Name  |  Owner   
--------+----------
 osdba  | osdba
 public | postgres
 sma2   | postgres
(3 rows)

模式的安全性

前面我们说到，默认情况下会自动创建一个public模式，连接的时候也是连接到public模式，但是因为默认情况下，所有的用户在public模式下都有USAGE和CREATE权限，而具有CREATE权限，就可以在public模式下创建各种对象。这种情况下，很容易导致数据库管理混乱。因此为了方便管理，同时为了数据的安全性，最后不要使用public模式，同时也不要随便对外开放最高权限的postgres用户。而应该为每个应用单独建立一个模式，使用和模式对应的用户名，实现专号和专户对应。这样不光便于管理，同时有利于排错。

在某个库里创建一个和角色同名的模式，那么这个用户切换到这个库的时候，默认模式就是它的同名模式，这个同名模式的默认拥有者也是该角色，而不是它的创建者。如下面示例中所示：

# 创建dbuser角色同名的模式dbuser，可以看到它的拥有者也是dbuser，而不是创建者postgres
test=# create schema authorization dbuser; 
CREATE SCHEMA
test=# \dn 
   List of schemas
   Name   |  Owner   
----------+----------
 dbuser   | dbuser
 public   | postgres
 topology | postgres
(3 rows)

# 使用dbuser用户登录，查看当前模式
-bash-4.2$ psql -U dbuser -d test  
psql (9.6.9, server 9.5.13)
Type "help" for help.

test=> select current_schema; 
 current_schema 
----------------
 dbuser
(1 row)

以上就是数据库逻辑结构中数据库和模式的内容，在下一篇文章中，会继续介绍数据库的结构内容，包括表、视图、索引、触发器、表空间等内容。