PostgreSQL教程（5）对象的基本管理

PostgreSQL 对象介绍

在 PostgreSQL 中，对象指的是整个数据库实例中可以创建、管理和操作的各类实体或结构。包含数据库、表、索引、视图、序列、函数、触发器等，这些不同的对象构成了整个数据库系统。DBA通常需要管理对象的权限、生命周期、性能优化及其存储空间分配，以保证数据库系统高效、稳定运行。

一、表空间（Tablespace）

表空间是PostgreSQL数据库中各个对象的物理存储位置，通过表空间可以将数据库、表、索引以及其他对象存放在指定的磁盘或者存储设备上，提高性能或灵活利用存储资源

1、创建 PostgreSQL 表空间

# 创建表空间语法（需要超级用户权限）
CREATE TABLESPACE tablespace_name    #表空间名字，不能pg_打头
[ OWNER user_name ]    #表空间属主，不指定的话默认为执行该命令的用户
LOCATION 'directory'    #表空间存放绝对路径，该目录需要提前创建且为空
[ WITH (tablespace_option = value [, ... ] ) ]    #表空间的参数设置，通常可忽略
    
#示例    
create tablespace tanglu_space location '/data/postgres/tanglu';

#创建表空间tanglu_space，存储路径为/data/postgres/tanglu（数据目录下会生成一个oid并通过软连接指向该目录），并将所属权赋予 tanglu 用户 
create user tanglu password '123456';
create tablespace tanglu_space owner tanglu location '/data/postgres/tanglu';

#改变数据库默认表空间(已有表的表空间不会改变),在执行该操作时不能有用户连接到这个数据库上,否则会报错
alter database testdb set tablespace tanglu_space;

2、使用PostgreSQL表空间

#创建数据库时指定表空间，在此数据库中创建的表、 索引会自动存储到该表空间
create database tanglu tablespace tanglu_space;

#创建表时指定表空间
create table t1(id int, name text,age int) tablespace tanglu_space;

#创建索引时指定表空间
create index on t1(id) tablespace tanglu_space;
 
#创建唯一约束时指定约束索引的表空间
alter table t1 add unique(id) using index tablespace tanglu_space;
 
#增加主键时指定主键索引的表空间
alter table t1 add primary key(id) using index tablespace tanglu_space;
 
#把表从一个表空间移到另一个表空间，该操作会锁表，包括SELECT操作，慎重移表
alter table t1 set tablespace pg_default;

3、查看PostgreSQL表空间：\db

tanglu_database=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres |

4、修改PostgreSQL表空间

#RENAME TO：更改表空间名称 
ALTER TABLESPACE name RENAME TO new_name

#OWNER TO：更改表空间所属用户  
ALTER TABLESPACE name OWNER TO { new_owner |CURRENT_USER | SESSION_USER }

#SET|RESET：设置表空间参数 
ALTER TABLESPACE name SET ( tablespace_option = value [,... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ])

#将表空间tbs_data重命名为tbs_data2：
alter tablespace tbs_data rename to tbs_data2;

#更改表空间tbs_data的拥有者：
alter tablespace tbs_data owner to user2;
 
#更改表空间tbs_data的random_page_cost参数
alter tablespace tbs_data set (random_page_cost=1.1)；
 
#恢复表空间tbs_data的random_page_cost参数
alter tablespace tbs_data reset (random_page_cost)；

5、删除PostgreSQL表空间

只有表空间的拥有者或超级用户才能删除表空间。在删除表空间之前，必须保证其上所有的数据库对象已经被清空。如果有任何数据文件存在此表空间，则drop命令执行失败。

# 语法
DROP TABLESPACE [ IF EXISTS ] name

# 删除表空间tanglu_space
drop tablespace tanglu_space;

二、数据库（Database）

数据库是一个逻辑概念，在 PostgreSQL 中属于顶层对象。一个PostgreSQL实例可以创建多个数据库，在每个数据库里可以包含相关的表、视图、索引、函数等对象。当客户端连接到一个数据库时，只能访问该数据库中的数据。在PostgreSQL中，创建数据库时有一个模板概念。模板是创建数据库的基础，包含新数据库应该具备的所有初始内容，比如模板数据库中的所有表、数据、索引、函数、权限等都会被复制到新创建的数据库中。PostgreSQL提供了两个默认的模板，其中，template0是一个干净的数据库模板，几乎不包含任何用户定义的对象，适用于需要一个完全干净的数据库的场景。template1是默认模板，如果没有指定模板则会级自动使用该模板包含的相关信息。

1、创建数据库

#语法
CREATE DATABASE name
    [ [ WITH ] [OWNER [=] user_name ]
           [TEMPLATE [=] template ]                 #使用指定模板
           [ENCODING [=] encoding ]
           [LOCALE [=] locale ]
           [LC_COLLATE [=] lc_collate ]
           [LC_CTYPE [=] lc_ctype ]
           [TABLESPACE [=] tablespace_name ] 
           [ALLOW_CONNECTIONS [=] allowconn ]        #是否允许连接
           [CONNECTION LIMIT [=] connlimit ]       #最大连接数
           [IS_TEMPLATE [=] istemplate ] ]          #是否为模板数据库


#创建数据库testdb
create database testdb;
 
#创建数据库db01，指定所属用户为user1，关联的表空间为tbs_data
create database db01 owner user1 tablespace tbs_data;

#创建数据库db02，指定语言环境为en_US.UTF8，由于这里指定了语言环境并且与template1中的语言环境不同， 所以声明使用template0模板 
create database db02 
    locale 'en_US.UTF8'
    template template0;

2、修改数据库

#语法
ALTER DATABASE name [ [ WITH ] option [ ... ] ]

#更改数据库名称
ALTER DATABASE name RENAME TO new_name
alter database testdb rename to testdb2;

#更改数据库所属用户
ALTER DATABASE name OWNER user_name

#更改数据库表空间 
ALTER DATABASE name SET TABLESPACE new_tablespace

#设置数据库参数
ALTER DATABASE name SET configuration_parameter { TO | =} { value | DEFAULT }： 
alter database db02 set enable_indexscan to off;

ALTER DATABASE name SET configuration_parameter FROMCURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

#将数据库db01中的最大连接数改为 200，该操作针对的是数据库的owner用户，管理员不受影响
alter database db01 connection limit 200；

3、删除数据库

#语法
DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [,...] ) ]
 
#删除db01数据库，如果该数据库有用户已经连接则无法删除 
drop database db01;

#从 PostgreSQL 13 开始，支持通过 WITH (FORCE)来删除存在连接的数据库
drop database db02 (force);

4、实际业务中建库流程

# 连接到 PostgreSQL 服务器
psql -U postgres -h localhost

# 创建表空间（可选）
CREATE TABLESPACE tanglu_space LOCATION '/path/to/tanglu_space';

# 创建数据库并指定表空间
CREATE DATABASE tanglu TABLESPACE tanglu_space;

# 连接到业务数据库
\c tanglu

# 创建 schema
CREATE SCHEMA business;

# 在 schema 中创建表
CREATE TABLE business.customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE business.orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES business.customers(customer_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    amount DECIMAL(10, 2) NOT NULL
);

# 创建用户并分配权限
CREATE USER tanglu_user WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE tanglu TO tanglu_user;
GRANT USAGE ON SCHEMA business TO tanglu_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA business TO tanglu_user;

#  插入数据
INSERT INTO business.customers (name, email) VALUES ('John Doe', 'john.doe@example.com');
INSERT INTO business.orders (customer_id, amount) VALUES (1, 100.50);

# 查询数据
SELECT * FROM business.customers;
SELECT * FROM business.orders;

三、模式（Schema）

模式用于将数据库中的各个对象（表、索引、函数等）逻辑上组织在一起，便于控制管理（表空间则是物理上组织在一起）。如果管理员没有手动创建Schema，那么所有对象默认属于一个公用模式public下，该模式是初始化数据库后自动创建的。

通常建议根据业务进行分类，同业务的对象放在该业务的模式下。模式之间的数据是互相隔离的。一个用户可以创建多个模式，而一个模式只能属于一个用户。这里容易和MySQL中的database混淆。在PostgreSQL中Database是最高级别的存储单位，每个数据库都是相互独立的，不能直接跨数据库操作。当连接到某个数据库以后所做的操作都是针对该数据库的对象，而通过Schema是数据库中的逻辑命名空间，用于组织和管理数据库对象

1、创建模式

create schema test_schema;

2、模式授权

GRANT USAGE ON SCHEMA schema_name TO user_name;
GRANT SELECT ON schema_name.table_name TO user_name;

四、表（Table）

表是数据库中用于存储数据的结构化对象，它由一组列组成，每列都有对应的数据类型。

#在指定模式下创建表
create table test_schema.t1(id int)

五、索引（Index）

同MySQL索引，用于加速数据库中的数据检索操作

六、视图（View）

视图是基于一个或多个表的查询结果，它类似于虚拟表，可以像表一样进行查询操作，但是视图本身不存储数据，而是根据定义时的查询实时生成结果。

· Constraint（约束）

约束是用于确保数据库中数据完整性的规则，包括主键约束、唯一约束、外键约束、检查约束等。

· Trigger（触发器）

在特定事件发生时自动执行预定义的操作，比如插入、更新、删除等。

· Event Trigger（事件触发器）

事件触发器是 PostgreSQL 特有的一种机制，它允许用户在数据库中定义自定义事件，并在这些事件发生时执行相应的操作。

· Function（函数）

函数是一种可在数据库中执行的命名代码块，它可以接受参数并返回结果，常用于实现业务逻辑、数据转换等功能。

· Procedure（存储过程）

存储过程是一种特殊的函数，与普通函数不同的是，存储过程可以在数据库中存储和调用，并且可以包含更复杂的逻辑和控制结构。