03：PostgreSQL逻辑结构（表空间、数据库、模式、表、索引）

环境规划：

• 操作系统：CentOS 7.9 64bit
• PostgreSQL 版本：16.x 或 15.x
• 安装用户：postgres
• 软件安装目标路径：/usr/pgsql-<version>
• 数据库数据目录：/pgdata

目录

表空间Tablespace

  默认表空间

  手动创建的表空间

实例/Database/Schema/对象关系

数据库Database

  默认数据库

  手动创建的数据库

模式Schema

  默认Schema 

  模式搜索路径search_path

  删除public模式

  建议对template1中删除public模式

表Table 

  表创建、插入、删除

  表结构复刻

  临时表

  UNLOGGED表

索引Index 

关闭psql自动提交AUTOCOMMIT 

---

本文假设已经掌握SQL基本语法和数据库基础概念。因此只简明扼要介绍PostgreSQL的逻辑结构。

表空间Tablespace

  默认表空间

数据库初始化之后自动会创建pg_default和pg_global两个表空间（pg_tablespace视图查看，也可以通过在psql中使用“\db+”查看）。

• pg_default表空间：是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。对应存储目录$PADATA/base/  （template0、template1和postgres数据库存放在pg_default表空间下）
• pg_global表空间：用来存放系统字典表，共享系统目录；对应存储目录$PADATA/global/。相当于oracle数据库的system表空间

  手动创建的表空间

1、为表空间创建存放目录($PGDATA在本环境为/pgdata)，在数据库服务器操作系统中以postgres用户执行：

mkdir -p $PGDATA/pg_tblspc/tbs_user1

 2、创建表空间。在psql中执行语句：

CREATE TABLESPACE tbs_user1 LOCATION  '/pgdata/pg_tblspc/tbs_user1';

创建tablespace的其他option选型，请参考官方文档。

实例/Database/Schema/对象关系

在PostgreSQL中，这几者之间关系为：

PG数据库服务(实例)

      └ database1

            └ schemaA

                   └ table_T1

                   └ table_T2

                   └ view_V1

            └ schemaB

      └ database2

            └ schemaA

            └ schemaB

      └ database3

            └ schemaA

            └ schemaB

PG数据库服务(实例)中可以有多个数据库(database)，在psql工具中可以通过\c切换所连接的库。每个数据库下有不同的shema、每个schema下有不同的对象。

• MySQL没有上图中数据库概念，只有上图的schema（在MySQL中schema也称为database，实际应为schema）
• 在Oracle中，用户与Schema是绑定对应的，一个用户就是一个Schema。

数据库Database

  默认数据库

数据库初始化之后自动会创建postgres、template0、template1三个数据库。

• postgres数据库：这是默认的管理数据库，用于管理 PostgreSQL 服务器和用户角色。它包含了用于管理服务器和用户的系统表，例如 pg_roles、pg_database 等。通常不建议用于存储实际数据，而仅用于管理目的。
• template0数据库：这是一个空模板数据库，改模版非常“干净”，且不可修改。可用于创建其他数据库的模板。。
• template1数据库：与 template0 类似，也是一个模板数据库，可用于创建其他数据库的模板。但与 template0 不同的是，template1 是可以被修改的，比如修改模版字符集、或者可以在其中创建用户自定义的对象，例如函数、表等。默认情况，我们新创建database会以template1库为模板进行创建。

  手动创建的数据库

在psql中执行语句：

--(不推荐)最简单创建
--其中：以执行语句的当前用户作为该库的owner，默认使用template1模板
CREATE DATABASE userdb;

--(推荐)指定其中该库的owner、模板、字符编码、表空间。
CREATE DATABASE userdb OWNER user_zyp TEMPLATE template0 ENCODING 'UTF8' TABLESPACE tbs_user1;

更多option选型，请参考官方文档。 

模式Schema

  默认Schema 

创建数据库时会自动会创建information_schema、pg_catalog、pg_toast、public四个模式。

手动创建的模式schema，在psql中(先连接至某个数据库)执行语句：

--(不推荐)最简单创建
--其中：以执行语句的当前用户作为该模式的owner
CREATE SCHEMA 模式名;

--(推荐)指定模式owner
CREATE SCHEMA 模式名 AUTHORIZATION 角色名/用户名;

--修改已有模式名
ALTER SCHEMA 模式名 RENAME TO 新模式名;

--修改已有模式的owner
ALTER SCHEMA 模式名 OWNER TO 角色名/用户名;

更多option选型，请参考官方文档。 

查看对象所属模式：

查看表所属模式
SELECT table_schema, table_name FROM information_schema.tables;

查看视图所属模式
SELECT table_schema, table_name FROM information_schema.views;

查看存储过程、函数所属模式
SELECT specific_schema, specific_name FROM information_schema.routines;

查看序列所属模式
SELECT sequence_schema, sequence_name FROM information_schema.sequences;

查看触发器所属模式
SELECT trigger_schema, trigger_name FROM information_schema.triggers;

  模式搜索路径search_path

由于存在多个模式，PostgreSQL提供了模式搜索路径（类似Linux中的PATH环境变量）来查找数据库中各对象。若语句中没有显示指定schema，则从search_path中存的模式列表中搜索：

• 查找某个对象（表、视图、函数、索引等）：则从search_path中左至右搜索，若在某模式下可找到对象，则以该模式下的对象作为选中对象。若遍历所有模式均未找到，则报不存在该对象Did not find any.......。
• 若需要创建对象，则从search_path中左至右搜索，若某个模式存在且当前用户对该模式具有CREATE权限(Owner永远有创建权限)，则以该模式作为新创建对象的所属模式。

查看和修改模式搜索路径search_path：

--查看
SHOW search_path;

--修改（当前会话级别）
SET search_path TO "$user", public;

--修改（数据库级别，重新连接生效）
ALTER DATABASE 数据库名 SET search_path TO "$user", public;

--修改（用户级别，重新登录后生效）
ALTER ROLE 角色名/用户名 SET search_path TO "$user", public;

search_path中：$user 表示模式名等于当前用户名，既仅在当前用户名与schema同名才能匹配上。

  删除public模式

PostgreSQL中每个database都默认存在public模式，而public不是SQL标准概念，在其他数据库软件中也不存在public模式。

因此，为了最大程度兼容，并考虑可一致性，强烈建议删除public模式不使用。

若经评估不适合删除public模式，为避免不小心误将对象创建public模式下，至少应撤销在public模式下创建对象权限，命令参考如下。

--默认：所有用户对public模式具有CREATE和USAGE权限。
--撤销在public模式下创建对象权限。第一个public指模式名，第二个PUBLIC表示所有用户
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

--以超级用户，修改所有用户的search_path，去除public（重新登录后生效）
ALTER ROLE ALL SET search_path TO "$user";

--修改数据库，去除public（重新连接生效）
ALTER DATABASE 数据库名 SET search_path TO "$user";

--删除模式
DROP SCHEMA public;

  建议对template1中删除public模式

建议将template1模板中的public模式删除，这样使用template1模板在新创建数据库时，新库就不再有public模式

--连接
\c template1;

--修改数据库，去除public
ALTER DATABASE template1 SET search_path TO "$user";

--删除模式
DROP SCHEMA public;

表Table 

  表创建、插入、删除

略。

注意：在PostgreSQL更新一条数据，源数据并不会覆盖，而是会插入一条新的数据。

  表结构复刻

CREATE TABLE 新表名 (LIKE 母表 like_option)

like_option常见的:
   INCLUDING COMMENTS
   INCLUDING COMPRESSION
   INCLUDING CONSTRAINTS
   INCLUDING DEFAULTS
   INCLUDING GENERATED
   INCLUDING IDENTITY
   INCLUDING INDEXES --新的索引名为：新表名_涉及字段名_idx
   INCLUDING STATISTICS
   INCLUDING STORAGE
   INCLUDING ALL

CREATE TABLE LIKE：以母表为源复刻表结构，不涉及数据。

CREATE TABLE AS： 以select结果集创建表并fill填充数据

  临时表

CREATE TEMPORARY TABLE tb_tmp_t1 (name varchar(32));

\d+ tb_tmp_t1;

• 临时表分为两种：会话级别、事务级别。在创建临时表语句增加ON COMMIT选型来区分。
• 临时表在会话结束后，表即会消失（表数据和表结构均会消失）
• 临时表所属的schema为特殊schema，名字为pg_temp_N （不同的会话N不同）

  UNLOGGED表

CREATE UNLOGED TABLE tb_unlogged_t01 (name varchar(32));

 UNLOGGED表不会写WAL日志，无法实现主备库之间同步（在主库机器上会存储该表），使用上与普通表没有区别。UNLOGGED在插入/删除/更新性能会略高。

索引Index 

略。

PostgreSQL支持在创建索引是通过选项CONCURRENTLY进行并发创建索引，避免在创建过程中阻塞对表的插入/删除/更新操作。

关闭psql自动提交AUTOCOMMIT 

psql客户端默认开启了自动提交，若需关闭自动提交，则可在psql执行：

--注意AUTOCOMMIT必须为大写
\set AUTOCOMMIT off

注意以上AUTOCOMMIT必须为大写（若为小写不会报错，但不会生效）。