PostgreSQL的逻辑结构
每一种数据库都有它自己的特点，了解了Oracle与MySQL，再来学习PG，总想着把PG的概念与另外两种数据库做一个等价对比。PG数据库也有实例、数据库、模式、表空间这些逻辑概念，下面总结一下这些概念与Oracle和MySQL的异同，画个简图方便自己理解。
目前是学习的PG的版本为12.20，以这个版本为例来说，等后面了解了新版本特性再来修正。
Service
PG在一台服务器上可以装多个数据库服务(实例)，通过不同端口来区分每个实例，这点和MySQL差不多，而Oracle除了端口还有服务名(service name)来标识每个数据库实例。PG，Oracle，MySQL三种数据库的默认端口分别是(5432,1521,3306) 。
Database
PG的一个数据库服务可以管理database，当应用连接到一个database时，一般只能访问这个database中的数据，而不能访问其他database中的内容（除非使用Dblink等其他手段）。
Oracle的一个数据库服务，只有一个database。（12C之后pluggable database除外）。
MySQL的一个数据库服务，也可以管理多个database，但是通过授权，应用通过一个用户脸上数据库之后，可以访问多个多个database的内容。
Schema
模式(Schema)的概念，可以理解为一个命名空间，或一个目录，不同模式下可以有相同名称的表、函数等对象而不会产生冲突，只要有权限，各个模式中的对象可以互相调用。PG的一个database，可以包含一个或多个模式，一个用户连接到一个database后，只要有权限就可以同时访问这个database中多个schema的对象，这点跟Oracle的schema相同，跟MySQL的database概念相同。只不过Oracle的每个Schema跟用户又是一一对应的，不会使用语句来显示创建schema，创建一个用户即相当于创建了一个schema。
PG每创建一个database，都会自动创建一个public的schema，登录到该数据库，如果不指定模式，则访问的就是public模式的对象。而Oracle和MySQL都没有public模式这个概念。
创建模式的语法格式：
CREATE SCHEMA schemaname [ AUTHORIZATION username ] [
schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username [ schema_element [
... ] ]
创建schema时，可以指定schema的owner，也可以同时创建表或视图等对象。
还可以更改schema的名称和属主。
ALTER SCHEMA name RENAME TO newname ;
ALTER SCHEMA name OWNER TO newowner;
语法使用举例：
为用户osdba创建模式，创建模式时顺便创建一个表T1
postgres=# create schema authorization osdba
postgres-# create table t1(id int,c1 int);
CREATE SCHEMA
postgres=# \dn
List of schemas
Name | Owner
----------+----------
osdba | osdba
postgres | postgres
public | postgres
(3 rows)
默认的模式名与用户名相同。
删除模式时，模式中有对象，不允许删除，除非需加cascade 级联删除模式中的对象。
postgres=# drop schema osdba;
ERROR: cannot drop schema osdba because other objects depend on it
DETAIL: table osdba.t1 depends on schema osdba
HINT: Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop schema osdba cascade;
NOTICE: drop cascades to table osdba.t1
DROP SCHEMA
显示创建schema oa, 指定其owner 为osdba
postgres=# create schema oa authorization osdba;
CREATE SCHEMA
postgres=# \dn
List of schemas
Name | Owner
----------+----------
oa | osdba
postgres | postgres
public | postgres
(3 rows)
更改schema oa的名称为osdba。
postgres=# alter schema oa rename to osdba;
ALTER SCHEMA
postgres=# \dn
List of schemas
Name | Owner
----------+----------
osdba | osdba
postgres | postgres
public | postgres
(3 rows)
psql 通过set search_path来切换模式。相当于MySQL的use 命令。
postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
postgres=# set search_path to osdba;
SET
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
osdba | aa | table | postgres
osdba | t1 | table | postgres
osdba | t2 | table | postgres
osdba | v_t12 | view | postgres
(4 rows)
postgres=# show search_path;
search_path
-------------
osdba
(1 row)
postgres=# set search_path to public;
SET
postgres=# show search_path;
search_path
-------------
public
(1 row)
模式的权限设置，与Oracle和MySQL相同。
用户操作或访问其他用户模式中的对象，需要授予相应的权限，访问是授予USAGE权限，在其他模式中创建对象，需要授予CREATE 权限。
但是PG中一个用户可以拥有多个模式，在自己模式中操作无需授权。
请注意一点：默认情况下，每个用户在public 模式下都有create和usage权限，也就是允许所有连接到该数据库的用户在public模式下创建对象。当然也可以撤销这个权限。
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
第一个“public”是模式的名称，第二个“PUBLIC”的意思是“所有用户”。第一个是标识符，第二个是关键字，关键字与大小写无关。
回收权限后，不指定模式，默认在与用户同名的模式下创建对象，如果同名的模式不存在，则会报错。
postgres=> \c
You are now connected to database "postgres" as user "osdba".
postgres=> create table public.c1(id int) ;
ERROR: permission denied for schema public
LINE 1: create table public.c1(id int) ;
^
postgres=> create table oa.c1(id int) ;
CREATE TABLE
表空间（Tablespace)
PG的tablespace是一个存储目录（磁盘目录），创建数据库时可以为其指定默认的表空间。
Oracle的tablespace是一个逻辑概念，一个数据库包含多个tablespace，每个tablespace包含1个或多个物理数据文件。
MySQL8.0的表空间，一个表就是一个表空间，对应一个ibd数据文件。
PG的表空间使用：
创建一个表空间
Create tablespace ts1 owner=osdba location ‘/var/lib/pgsql/12/data/pg_tblspc’
可以创建数据库时指定默认表空间
Create database db1 tablespace ts1;
修改数据库的默认表空间。修改时不能有用户连到这个数据库上。
Alter database osdba set tablespace ts1;
建表建索引时指定表的表空间
Create table t1(id int,c1 int) tablespace ts1;
create index idx_c1 on t1(c1) tablespace ts1;
添加主键或唯一约束时也可以指定表空间。
Alter table t1 add constraint pk_t1 primary key(id) tablespace ts1;
更改表的表空间。相当于表从一个表空间移到另一个表空间。移动表的时候表会被锁定。
alter table aa set tablespace ts1;
