PostgreSQL数据库逻辑结构详解：实例、数据库、schema、表之间的关系

PostgreSQL数据库逻辑结构详解：实例、数据库、schema、表之间的关系

引用

CSDN

1.

https://blog.csdn.net/penriver/article/details/119680114

PostgreSQL（简称PG）是一种功能强大的开源关系型数据库管理系统，广泛应用于各种规模的应用程序中。要有效地使用PG，理解其逻辑结构是非常重要的。本文将详细介绍PG的实例、数据库、schema、表之间的关系，并通过系统表的查询帮助读者深入理解这些概念。

1 PG的逻辑结构

PG的逻辑结构如下图所示：

• 第一层是实例(Instance)。
• 第二层是数据库(Database)；一个实例下有多个数据库；每个数据库之间是完全独立的。
• 第三层是Schema；数据库下面有多个Schema；其中“public” 是数据库创建时产生的。
• 每个Schema下面可以创建表，视图，索引，函数，序列，物化视图，外部表等等。

pg_class记录表和几乎所有具有列或者像表的东西。这包括索引（但还要参见pg_index）、序列（但还要参见pg_sequence）、视图、物化视图、组合类型和TOAST表，参见relkind。

此表记录了表对应的表空间、命名空间、但未记录此表属于哪一个数据库

注意：

• 在PostgreSQL每个数据库是完全独立的；所以访问同一个实例下其他数据库需要借用dblink或者fdw接口）。
• 在PostgreSQL同一实例下，不能直接访问其它数据库【MySQL可以，因为MYSQL跟PostgreSQL逻辑结构不一样，MYSQL实例中的数据库相当于PostgreSQL的第三层的Schema】

2 PG数据库中的Schema

通过系统表pg_database可以查到数据库，通过系统表pg_class可以查到数据库object。

而pg_database、pg_class存放在一个名为pg_catalog的Schema下。其中pg_catalog是系统级的schema，用于存储系统函数和系统元数据、而每个 database 创建好后，默认会有3个Schema。

• pg_catalog: 用于存储Postgresql系统自带的函数、表、系统视图、数据类型转换器、数据类型定义等元数据
• information_schema: 用于存储所需求提供的元数据查询视图, 目的是以符合ANSI SQL规范,可单独删除
• public: 用于存储用户创建的数据表。不建议项目的表存放在public下，原因如下：
• 是数据安全；
• 表存放混乱
• 不利于后期维护等等

查看隐藏的schema

select table_schema from information_schema.tables group by table_schema;

3 PG数据库中的catalog

新建一个数据库会在表空间对应的目录下创建一个 oid（数据库oid）的目录；用于存放这个数据库对应的数据库文件。

PG同实例下数据库之间不仅物理结构完全独立，逻辑结构也是完全独立

• 查询数据库的oid
• 在数据库存储目录查看数据，21338就是数据库的oid /home/postgres_data/base/21338
• 查询table catalog, 名称就是数据库名称

--数据库id
select oid, datname from pg_database where datname='test';
-- 查询table catalog
select table_catalog from information_schema.tables group by table_catalog; 

每个数据库的pg_class是同一个吗？

通过下面的验证，结果是：

• 在逻辑结构上；同实例下每个数据库是完全独立；表是在存放在同库名的catalog下；例如nacos数据库的表存放在nacos的catalog下，可以把catalog理解为database。
• 在物理结构上；同实例下每个数据库的pg_class对应的数据文件也是独立存放的。
• 每个 database 创建时；会默认为库创建pg_catalog/information_schema

参考

• PostgreSQL Catalogs
• PostgreSQL Information Schema