手把手教你管理PostgreSQL数据库及其对象

一、逻辑结构

（一）基本概念

集群服务（Cluster）

集群是指一组数据库，其使用同一配置文件并响应公共端口的请求，属于集群的数据库使用一个共同的文件系统位置，存在一套共同后台进程和内存结构。集群间从下述两个方面区分：

物理存储的绝对路径 ($PGDATA)

各自的端口号各自的端口号

二、表空间

表空间是数据库对象的容器，一个表空间可以供多个数据库使用；

pg_global 系统字典表都存这里;

pg_default 默认表空间;

创建数据库时可以指定一个默认表空间；

表空间实际上是为数据库对象指定一个存储的目录。

（一）创建表空间的完整语法

1. postgres=# \h create tablespace
   
2. Command:     CREATE TABLESPACE
   
3. Description: define a new tablespace
   
4. Syntax:
   
5. CREATE TABLESPACE tablespace_name
   
6.    [ OWNER { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
   
7.    LOCATION 'directory'
   
8.    [ WITH ( tablespace_option = value [, ... ] ) ]
   
9. 
   
10. URL: https://www.postgresql.org/docs/14/sql-createtablespace.html
    

示例：

1. postgres=# CREATE TABLESPACE test  LOCATION '/pgdb/data/test';
   

（二）表空间管理

1、查看表空间

1. \db                    #元命令方式查看
   

1. select * from pg_tablespace;          #获取表空间信息
   

1. select oid from pg_tablespace;          #获取表空间的oid信息
   

1. select pg_tablespace_location(oid);          #查看表空间的位置
   

1. select pg_size_pretty(pg_tablespace_size('pg_global'));    #查看表空间的大小
   

2、表空间使用

1. create database dbname tablespace tbsname;
   
2. alter database dbname tablespace tbsname;           #改变数据库默认表空间，需要断开连接
   
3. create table tt(id int) tablespace tbsname;
   
4. alter table tabname set tablespace tbsname;           #移动表到目标表空间，会锁表
   
5. grant create on tablespace tbsname to a;      #授权
   

3、删除表空间

1. DROP TABLESPACE [ IF EXISTS ] name
   

注意：删除表空间前必须要删除该表空间下的所有数据库对象，否则无法删除。

4、临时表空间

PG的临时表空间用来存储临时表或临时表的索引,以及执行SQL时可能产生的临时文件（例排序，聚合，哈希等）

PG的临时表空间，通过参数temp_tablespaces 进行配置

PG允许用户配置多个临时表空间，配置多个临时表空间时，使用逗号隔开

如果没有配置temp_tablespaces 参数，临时表空间对应的是默认的表空间

为了提高性能，一般建议将临时表空间放在SSD或者IOPS，以及吞吐量较高的分区中

示例：

1. mkdir /pgdata/tbls/tmptbs
   

1. create tablespace tmptbs1 location '/pgdata/tbls/tmptbs1';
   
2. alter role all set temp_tablespaces='tmptbs1';
   

三、数据库

数据库是一些SQL对象（”数据库对象”）的命名集合，提供数据存储功能

通常每个数据库对象（表、函数等） 属于并且只属于一个数据库（不过有几个系统表如pg_database属于整个集簇并且对集簇中的每个数据库都是可访问的）。

数据库是一个模式的集合， 而模式包含表、函数等数据库对象

完整的层次是这样的：服务器、数据库、模式、表（或者某些其他对象类型，如函数）。

1、数据库创建

主要有三种方式可以创建

1）通过Pgadmin客户端工具

2）系统命令提示符调用createdb命令

3）psql环境执行create database

1. postgres=# \h create database
   
2. Command:     CREATE DATABASE
   
3. Description: create a new database
   
4. Syntax:
   
5. CREATE DATABASE name
   
6.    [ WITH ] [ OWNER [=] user_name ]
   
7.           [ TEMPLATE [=] template ]
   
8.           [ ENCODING [=] encoding ]
   
9.           [ LOCALE [=] locale ]
   
10.           [ LC_COLLATE [=] lc_collate ]
    
11.           [ LC_CTYPE [=] lc_ctype ]
    
12.           [ TABLESPACE [=] tablespace_name ]
    
13.           [ ALLOW_CONNECTIONS [=] allowconn ]
    
14.           [ CONNECTION LIMIT [=] connlimit ]
    
15.           [ IS_TEMPLATE [=] istemplate ]
    
16. 
    
17. URL: https://www.postgresql.org/docs/14/sql-createdatabase.html
    

语法示例：

CREATE DATABASE testdb1；

不指明数据库模板，默认克隆template1数据库

数据库默认表空间pg_default，所有template1中的对象将被同步克隆到新的数据库

相对完整的语法示例：

create database testdb2 with owner dh encoding ‘UTF8’ tablespace tmptbs1;

2、修改数据库参数

类似“修改用户”，还支持下述命令：

alter database DbName set parameter to value;

alter database DbName reset parameter / all;

3、数据库（查看及删除）

查看数据库信息

查看数据库列表

select * from pg_database; 或者 \l

查看数据库大小

select pg_database_size(‘testdb1’),pg_size_pretty(pg_database_size(‘testdb1’)) ;

删除数据库

SQL命令删除： drop database DbName;

客户端命令删除：dropdb DbName；

四、数据库对象

数据库对象，是数据库的组成部分，用模式来分组管理

或者可以简单的认为数据库中的一切都是数据库对象

包括表、视图、约束、索引、物化视图、规则、函数、触发器等

1、模式

模式（schema）是对数据库（database）逻辑分割，可理解为数据库对象分组

一个数据库可以存在多个模式，创建的数据库对象都会隶属于某个模式

在数据库创建的同时，就已经默认为数据库创建了一个默认模式（public）

不指定模式的数据库对象（表、函数、视图、索引、序列等）都在public模式中

一个数据库中的模式名不能相同，不同模式名下的对象可以重名

2、模式（创建）

可以在数据库中通过SQL命令create schema来创建模式

基本语法如下：

create schema SchemaName；

还可以：

1、配置该schema的所有者

1. create schema SchemaName authorization User;
   

2、以所有者名创建新schema

1. create schema authorization user1;
   

3、模式（修改）

可以在数据库中通过SQL命令alter schema来修改模式，支持修改功能如下：

alter schema SchemaName rename to NewName; #重命名

1. alter schema SchemaName owner to NewUser;          #修改所有者
   

4、模式（删除）

可以在数据库中通过SQL命令drop schema来删除模式，基本语法如下：

drop schema SchemaName；

如果该模式下包含有其他对象，还可以：

1）同时强制删除这些对象，如：drop schema SchemaName cascade;

2）缺省方式，不删除这些对象，报错退出：drop schema SchemaName [restrict];

3）将模式下的对象批量转移

5、模式（搜索路径）

模式的搜索路径search_path

类似于linux中的path环境变量

连接数据库后执行指令可查看该值：show search_path;

默认值是$user,public，当以某个用户登录到数据库时,先查找和登录用户同名的schema,再查找public

修改设置search_path

会话级别修改方式如下，重连DB后失效：SET search_path [TO|=] s1;

数据库级别修改方式如下，需要重连接生效：

ALTER database “DBNAME” SET search_path [TO|=] s1;

五、表

表，table，是数据库中一个基本的数据对象；另外还有分区表的存在。

一个数据库中可以有若干个表，是用来存储数据的一种逻辑结构。

表由行/记录和列/字段组成，因此也称之为二维表。

在PG中，每个表最多可以创建250-1600个字段

每个表中的记录条数是无限制的

表文件缺省最大为1GB，当超过1GB后，会以下划线“_”+数字的形式作为文件后缀名创建新文件继续存储数据。

PG还有外部表的定义，通常可以通过外部表访问其他数据库中的数据。

1、创建表

此部分已演示过多次，这里不再演示。

可以在数据库中通过SQL命令create table来创建表

基本语法：create table TableName ([ColumnName DataType ]，….)；

详细语法：参见手册或在线帮助 \h create table

除此之外，常见的操作还有：

1）创建空表：create table TableName ();

2）指定表所属的模式：create table SchemaName.TableName ();

3）指定表所属的表空间：create table TableName () tablespace SpaceName;

2、查看表

可以在数据库中通过元命令或SQL查看表信息

\dt[+] tablename #查看表属性

\d[+] tablename #查看表定义

select pg_relation_filepath(‘company’); #查看表对应的数据文件

select pg_size_pretty(pg_relation_size(‘company’); #查看表的大小

select pg_size_pretty(pg_total_relation_size(‘company’); #查看表的大小，包含索引

select * from pg_stats where tablename=’company’; #查看表字段的统计信息

3、修改表

可以在数据库中通过SQL命令alter table来修改表，支持常见的修改包括：

alter table TableName rename to NewName; #修改表名

alter table TableName rename ColumnName to NewName; #修改字段名

alter table TableName set schema NewSchema; #修改所属模式：

alter table TableName add column ColumnName DataType; #增加字段：

alter table TableName drop column ColumnName; #删除字段：

4、删除表

可以在数据库中通过SQL命令drop table来删除表，基本语法如下：

drop table TableName；

如果该表上存在依赖对象如视图，还可以：

同时强制删除这些对象，如：drop table TableName cascade;

缺省方式，不删除这些对象，报错推出：drop table TableName [restrict];

六、约束

约束是数据表列执行的规则。这些是用来防止无效的数据被输入到数据库中，确保数据库中的数据的准确性和可靠性。

约束可以是列级或表级。仅适用于表级约束被应用到整个表的列级约束。为列定义的数据类型，本身是一种约束。例如，一列DATE类型的限制列为有效的日期格式。

以下是常用的限制，可用在PostgreSQL。

NOT NULL Constraint: 确保列不能有NULL值。

UNIQUE Constraint: 确保所有列中的值是不同的。

PRIMARY Key: 唯一标识数据库表中的各行/记录。

FOREIGN Key: 约束基于其他表列的数据。

CHECK Constraint: CHECK约束，确保一列中的所有值满足一定条件。

1、创建约束

—创建部门表

1. CREATE TABLE dept
   
2. (
   
3.  deptno numeric(4) NOT NULL PRIMARY KEY,
   
4.  deptname varchar(10),
   
5.  loc text
   
6. );
   

—创建雇员表

1. CREATE TABLE emp
   
2. (
   
3.  empno numeric(4) NOT NULL,ename varchar(10),job varchar(9),mgr numeric(4),hiredate date,sal numeric(7,2),comm numeric(7,2),deptno numeric(2),
   
4.  CONSTRAINT emp_pk PRIMARY KEY (empno),
   
5.  CONSTRAINT emp_ref_dept_fk FOREIGN KEY (deptno)
   
6.    REFERENCES dept (deptno) ON UPDATE NO ACTION
   
7.    ON DELETE NO ACTION,
   
8.  CONSTRAINT emp_sal_ck CHECK (sal > 0)
   

2、约束（修改、删除）

— 添加主键

1. alter table emp add primary key(empno);
   

—添加外键

1. alter table emp add foreign key(deptno) references dept(deptno)  ON UPDATE NO ACTION ON DELETE NO ACTION；
   
2. 
   
3. on update cascade: 被引用行更新时，引用行自动更新；
   
4. 
   
5. on update restrict: 被引用的行禁止更新；
   
6. 
   
7. on delete cascade: 被引用行删除时，引用行也一起删除；
   
8. 
   
9. on dellete restrict: 被引用的行禁止删除；
   

— 删除主键、外键

1. alter table emp drop constraint 约束名;
   

—添加唯一约束

1. alter table emp add constraint unique_goods_sid unique(empno);
   

七、索引

索引，index，是一种供服务器在表中快速查找数据的数据库结构

在正确使用索引的前提下，可以明显的提高数据检索速度

索引指定表的一列或多列，并指示索引是否在升序或降序排列

索引也可以是唯一的，类似UNIQUE约束，在列上有一个索引的列或组合索引防止重复项

在考虑使用索引时，应遵循的基本原则：

1）先插入数据后建索引

2）限制索引数量

3）合理安排索引列的顺序

1、创建索引

基本语法如下：

create index IndexName on TableName using IndexMethod (ColumnName)

tablespace SpaceName；

关于命令中的参数说明如下：

1）IndexMethod：定义索引使用的方法，如btree、hash、gist等

2）tablespace：该索引使用的表空间，缺省是表所在的表空间

B-Tree索引示例:

1. postgres=# CREATE TABLE test1 (id integer, content varchar);
   
2. CREATE TABLE
   
3. postgres=# CREATE INDEX test1_id_index ON test1 (id);
   
4. CREATE INDEX
   

2、索引的修改和删除

通过SQL命令alter index来修改索引，支持常见的修改包括：

修改索引名：alter index IndexName rename to NewName；

修改索引表空间：alter index IndexName set tablespace NewSpace；

通过SQL命令drop index来删除索引，基本语法如下：

drop index IndexName；

如果该索引上存在依赖对象，还可以：

1）同时强制删除这些对象，如：drop index IndexName cascade;

2）缺省方式，不删除这些对象，报错退出：drop index IndexName [restrict]

八、视图

视图，view，是根据用户观点所定义的从一个或多个表（或视图）导出的数据结构

视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义

对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基表

优化器(optimizer)把视图当做子查询(subquery)

PG视图默认是不可以更新的(not updateable)，除非创建了规则(rules)

关系数据库通常含有另外一种存放数据的视图，叫物化视图

视图的作用：

简单化—看到的就是需要的。视图可以简化用户查询数据时的操作，将经常使用的查询定义成视图，以后不必每次查询都带着一堆查询条件。

安全性—通过视图和权限控制，让用户只能看到和修改他们有权限操作的数据。

逻辑独立—视图可以屏蔽真实表结构变化带来的影响。当表的结构发生变化时，我们只需要修改视图的定义即可，避免了修改实际使用数据的程序。

1、创建视图

使用CREATE VIEW语句创建视图，从单一表、多个表或另一个视图中可以创建视图。

1. CREATE VIEW语法的基本情况如下：
   
2. CREATE [TEMP | TEMPORARY] VIEW view_name AS
   
3. SELECT column1, column2.....
   
4. FROM table_name
   
5. WHERE [condition];
   

备注：可以包含多个表在SELECT语句中，与在正常的SELECT查询中使用它们的方式非常相似。

如果可选的TEMP或TEMPORARY关键字，视图将被创建在临时空间。在当前会话结束临时视图会自动丢弃。

1. postgres=# create table users(id int,user_name varchar(40), password varchar(256), user_email text, user_mark text);
   
2. CREATE TABLE
   
3. postgres=# insert into  users values(1,'a','123455','test@163.com');
   
4. INSERT 0 1
   
5. postgres=# create view vw_users(no,name,email,mark)
   
6. postgres-# as select id,user_name,user_email,user_mark from users;
   
7. CREATE VIEW
   
8. postgres=# create temp view vw_users1 as select id,user_name,user_email,user_mark from users;
   
9. CREATE VIEW
   
10. postgres=# insert into  users values(2,'b','123466','test1@163.com');
    
11. INSERT 0 1
    
12. postgres=#
    

2、修改视图

通过SQL命令alter view来修改视图，支持常见的修改包括：

修改所有者：alter view ViewName owner to NewOwner;

重命名：alter view ViewName rename to NewName;

修改所属模式：alter view ViewName set schema NewSchema;

3、删除视图

通过SQL命令drop view来删除视图，基本语法如下：

1. drop view ViewName；
   

如果该视图上存在依赖对象如视图，还可以：

1）同时强制删除这些对象，如：drop view ViewName cascade;

2）缺省方式，不删除这些对象，报错退出：drop view ViewName [restrict];

九、序列(Sequences)

用来生成唯一的键(Unique Key)

语法

1. CREATE [TEMPORARY | TEMP] SEQUENCE name [INCREMENT [ BY ] increment]
   
2.    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
   
3.    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
   

示例

1. CREATE SEQUENCE dept_id_seq;  #创建序列
   
2. INSERT INTO dept(deptno, deptname,loc)
   
3. VALUES (nextval('dept_id_seq'), 'MARKETING','beijing');    #使用序列
   

十、函数(Function)

PostgreSQL函数也称为PostgreSQL存储过程。

PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明，分配，循环，控制流程等)。

它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。

您可以在许多语言(如SQL，PL/pgSQL，C，Python等)中创建PostgreSQL函数。

示例：

—创建函数

1. create function func_1(int,int) returns int as $$
   
2. declare
   
3. result int;
   
4. begin
   
5. result = $1 + $2;
   
6. return result;
   
7. end;
   
8. $$ language 'plpgsql';
   
9. --调用函数
   
10. select func_1(5,3);
    
11. --查看函数
    
12. \df
    
13. --修改函数
    
14. alter function func_1(int,int) owner to u1;
    
15. --删除函数
    
16. drop function func_1;
    

十一、触发器(Trigger)

PostgreSQL触发器是一组动作或数据库回调函数

它们在指定的表上执行指定的数据库事件(即，INSERT，UPDATE，DELETE或TRUNCATE语句)时自动运行。触发器用于验证输入数据，执行业务规则，保持审计跟踪等。

PostgreSQL在以下情况下执行/调用触发器：在尝试操作之前(在检查约束并尝试INSERT，UPDATE或DELETE之前)。或者在操作完成后(在检查约束并且INSERT，UPDATE或DELETE完成后)。或者不是操作(在视图中INSERT，UPDATE或DELETE的情况下)

对于操作修改的每一行，都会调用一个标记为FOR EACH ROWS的触发器。 另一方面，标记为FOR EACH STATEMENT的触发器只对任何给定的操作执行一次，而不管它修改多少行。

您可以为同一事件定义同一类型的多个触发器，但条件是按名称按字母顺序触发。

当与它们相关联的表被删除时，触发器被自动删除。

1、创建语法

1. CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name  
   
2. ON table_name  
   
3. [  
   
4. -- Trigger logic goes here....  
   
5. ];
   

示例：

1. --创建触发器函数
   
2. CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   
3.    BEGIN
   
4.        INSERT INTO EMP_AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
   
5.        RETURN NEW;
   
6.    END;
   
7. $example_table$ LANGUAGE plpgsql;
   
8. --创建触发器
   
9. CREATE TRIGGER example_trigger AFTER INSERT ON EMP
   
10. FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
    
11. --插入记录，验证触发器
    
12. INSERT INTO EMP(ID,NAME,AGE,ADDRESS,SALARY)
    
13. VALUES (1, 'Paul', 32, 'California', 20000.00 );
    
14. --查看触发的数据
    
15. Select * from EMP_AUDIT;
    

1. --查看触发器
   
2. select tgrelid::regclass as reltable,tgname as trigger_name from pg_trigger;  
   
3. --修改触发器
   
4. alter trigger example_trigger on emp rename to example_trigger1;
   
5. --删除触发器
   
6. drop trigger example_trigger on emp cascade;