PostgreSQL中的逻辑结构

Posted 2021-07-19 Updated 2024-09- 28

By Administrator

8~10 min read

在postgresql中，数据的组织结构可以分为：数据库、表与索引（relation）、数据行(tuple)。本问主要介绍了相关的：claster、database、schema、tablespace的概念。

cluster

当启动数据库服务时，不同的文件夹参数即代表不同的cluster：

一个PG Cluster是在同一个os上的一个或者多个PG database的集合
在一个操作系统（OS）中，可以有一个或者多个PG Cluster
不同的PG Cluster是以port （端口号）作为区分的

数据库

创建数据库

CREATE DATABASE [数据库名称]  [参数=值]

常用的参数：

OWNER :用于指定数据库的所属用户，如果不指定，则新建的数据库即为当前执行命令的用户所有
TEMPLATE:用于指定创建出来的数据库的模板，如果不指定则默认使用template1。template1中包含了一些编码的详细，如果要使用特定的编码则需要使用 template0,template0中不包含任何会受编码影响的数据或者索引。
ENCODING:指定创建数据库要使用的编码
TABLESPACE:指定数据库要使用的表空间
CONNECTION lIMIT: 用于指定数据库可以接受多少的并发连接。默认值为-1，代表不限制。

修改数据库信息

在修改时，参数主要由以下三种写法，例如：

修改数据库的最大连接数

ALTER DATABASE [数据库名称] CONNECTION LIMIT [连接限制数]

修改数据库名称

ALTER DATABASE [数据库名称] RENAME TO [新的数据库名]

修改数据库的参数

ALTER DATABASE [数据库名] SET [ [参数名] TO [值] ]

删除数据库

DROP DATABASE [IF EXISTS] 数据库名称

Schema(模式)

在postgresql中schema类似于编程语言中的命名空间，不同schema中可以有相同名称的表。

在postgresql中，使用不同的数据库时，需要重新连接到新的数据库，而模式没有此限制。

使用schema的原因：

多个用户使用同一个数据库又不会相互干扰
把数据库对象放在不同的模式下组织成逻辑，可以使数据库更加便于管理
可以防止不同应用的名字冲突

schema的使用

# 创建
CREATE SCHEMA [名称]

#　查看shema
\dn

# 删除模式
DROP SCHEMA [名称]

# 修改
ALTER SCHEMA [ [模式名] [配置] to [新值] ]

公共模式与模式的搜索路径

当需要在特定模式的表中查询数据时，只需要指定表名为：模式名.表名。数据库创建好后，会默认存在一个public的schema,当没有指定特定的schema时，默认会从search_path中搜索表。search_path 类似于linux中的PATH，PATH中的schema使用逗号隔开。默认的是 “$user”,public

默认状态下所有的用户都拥有对PUBLIC模式的访问权限，可以使用以下命令撤销这个权限：

REVOKE CREATE ON SCHEMA public FROM public

# 第一个public指的是 schema
# 第二个public指的是所有的用户

表空间

表空间用来存储表、索引等信息的存储目录。在创建表或者索引时，可以指定其表空间。

表空间相关的命令

# 创建表空间
CREATE TABLESPACE [表空间名称] LOCATION ["文件夹名称"]

# 改变数据库的默认表空间
ALTER DATABASE [数据库名称] SET TABLESPACE [新的表空间名称]

# 创建索引时，指定表空间
CREATE INDEX  [索引名称] on  [[表名称]([字段名])] TABLESPACE [表空间名称]

# 修改表的表空间（会移动原表空间的数据）
ALTER TABLE [表名称] SET TABLESPACE [表空间名称]

tip:
表空间创建时，文件夹必须已经存在。
修改数据库的表空间时，原来的表所在的表空间不会被修改，且不允许有用户连接此数据库。
修改表的表空间时表会被锁定，此时对表的所有操作都无法执行

数据库

postgresql

License: CC BY 4.0