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

一、PostgreSQL 对象介绍

对象指的是在 PostgreSQL 数据库实例中可以被创建、管理和操作的各类实体或结构。包含数据库、表、索引、视图、序列、函数、触发器等，这些不同的对象构成了完整的数据库系统。而在对数据库运维工作中最主要的工作内容也是对这些对象进行管理，比如对象权限控制、生命周期管理、性能调优及存储资源分配等操作，以保障系统的高可用性、稳定性与运行效率。

二、表空间管理（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 表空间

可以通过系统表或者快捷命令查询当前数据库实例中存在的表空间

#方法1
select oid,spcname from pg_tablespace;

#方法2
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中的database是一个逻辑概念，属于顶层对象。一个PostgreSQL实例可以创建多个Database，每个Database 下可有多个 Schema，Schema类似于Namespace的概念，每个Schema下了包含了表、视图、索引、函数等对象。当客户端连接到一个数据库以后所做的操作都是针对该数据库中的对象。

1、数据库模板

在PostgreSQL中，创建数据库都是通过复制数据库模板实现的。模板包含了数据库创建后的初始配置，如默认表、数据、索引、函数、权限等。PostgreSQL默认提供template0和template1两个数据库模板，template0 是一个纯净的数据库模板（该模板不 应该进行任何修改），该模板不包含任何用户定义的对象，适用于需要一个完全干净的数据库的场景。template1是默认模板，如果没有指定模板则会级自动使用该模板包含的相关信息。  

2、创建数据库

#语法
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;

3、修改数据库

#语法
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；

4、删除数据库

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

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

5、实际业务中建库流程

# 连接到 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）

模式用于将数据库中的各个对象（表、索引、函数等）逻辑上组织在一起，便于控制管理（表空间则是物理上组织在一起）。由于不同的模式之间是相互隔离的，所以通常建议根据业务进行分类，同个业务的对象放在该业务专属模式下。PostgreSQL中的模式和Oracle、MySQL都不太相同，也是容易混淆的点，它们区别如下：

· Oracle：通常用户=模式，创建用户的同时也会创建同名的Schema

· MySQL：没有Schema的概念，而是通过不同的database进行对象组织

· PostgreSQL：一个用户可以创建多个模式，而一个模式只能属于一个用户。如果没有手动创建Schema，那么所有对象默认属于public模式，该模式在初始化数据库后会自动创建

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)

五、扩展管理（Extension）

高扩展性是PostgreSQL的一大特性，在PostgreSQL安装成功以后就提供了大量扩展用以实现特殊需求，这些扩展存放在contrib/目录中，比如访问外部文件系统文件的file_fdw扩展、访问Oracle的oracle_fdw扩展等。大多数的扩展通过命令行可以直接安装，不用修改配置文件和重启数据库

1、安装扩展

一些主流扩展通常已经在安装数据库过程中包含，对于这类扩展可以在线安装，无需进行配置文件修改等操作

#查看已经包含在数据库中的所有扩展，这部分扩展都可以直接在线安装
SELECT * FROM pg_available_extensions;

#安装扩展，以pg_stat_statements为例
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

如果是第三方扩展，需要将扩展包上传到contrib目录进行手动编译后再执行CREATE EXTENSION操作

cd ./contrib/file_fdw/
make && make install

2、加载扩展

内置的扩展包不需要手动加载，而第三方扩展需要在 postgresql.conf 中设置并重启数据库。如果通过语句安装扩展出现类似 ERROR: could not access file "pg_stat_statements": No such file or directory这样的报错，表示需要先通过配置文件预加载

#修改postgresql.conf声明需要的扩展名
shared_preload_libraries = 'pg_stat_statements,auto_explain' 

#重启数据库
systemctl restart postgresql  #或pg_ctl restart -D $PGDATA

六、其他对象管理

· 索引管理（Index）

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

· 视图管理（View）

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

· 约束管理（Constraint）

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

· 触发器管理（Trigger）

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

· 函数管理（Function）

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

· 存储过程管理（Procedure）

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

七、如何查询对象OID关系

通过创建库、表来演示如何查找每个对象与其对应的OID以及物理文件位置

#创建库
postgres=# create database ywpie;
CREATE DATABASE

#切换库，并创建表
postgres=# \c ywpie
You are now connected to database "ywpie" as user "postgres".

ywpie=# create table t1(id int,name text);
CREATE TABLE

#可以看到表已经创建
ywpie=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)


#查询指定表的OID，可以看到是在base目录下,base对应数据目录，通常是$PGDATA变量所指定的路径
ywpie=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/16384/16385


#进入base目录，可以看到确实存在16384这个OID
-bash-4.2$ pwd
/var/lib/pgsql/14/data/base


-bash-4.2$ ll
drwx------ 2 postgres postgres 8192 Aug 16 11:07 1
drwx------ 2 postgres postgres 8192 Aug 16 11:07 14485
drwx------ 2 postgres postgres 8192 Aug 23 00:17 14486
drwx------ 2 postgres postgres 8192 Aug 23 00:18 16384


#查看指定OID 16384，可以查到是刚创建的ywpie这个库
ywpie=# select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
 14486 | postgres
 16384 | ywpie
     1 | template1
 14485 | template0

数据库postgresqlpgsql