2020年9月21日星期一

探讨PostgreSQL实例中数据库之间的关系

疑问

前几天PG学习微信群有人在问"pg_class 存储了对象和命名空间，表空间的对应关系，为什么没有和数据库的关系？我想查数据库下面有多少对象，找不到数据库和对象的关系？"

简而言之；PostgreSQL数据库和对象的关系；提这个问题的同学；应该是想知道

PostgreSQL实例中数据库之间的关系，以及数据库和对象的关系。

至于能否有这样的SQL；容我一步一步探索

探索

1、探索PG的逻辑结构

逻辑结构图

上图解析：

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

由于在PostgreSQL每个数据库是完全独立的；这可以解释另外一个问题（为什么访问同一个实例下其他数据库为什么要借用 dblink或者fdw接口？）。

这时可能会有学MYSQL同学会问到；（为什么在MYSQL中可以访问同实例下其他数据库？）。其实MYSQL跟PostgreSQL逻辑结构不一样；其中MYSQL实例中的数据库相当于PostgreSQL上图的第三层的Schema。

2、探索PG数据库中schema

了解过PostgreSQL的同学都知道；我们通过系统表pg_database可以查到数据库，通过系统表pg_class可以查到数据库object。

postgres=# select datname, oid from pg_database where datname = 'postgres'; datname  |  oid  ----------+------- postgres | 13543(1 row)postgres=# select relname, oid from pg_class where oid = 't1' :: regclass; relname |  oid  ---------+------- t1      | 32768(1 row)

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

  • 一个名为pg_catalog，用于存储Postgresql系统自带的函数,表,系统视图,数据类型转换器以及数据类型定义等元数据

  • 一个名为information_schema，用于存储所需求提供的元数据查询视图,目的是以符合ANSI SQL规范,可单独删除

  • 一个名为public，用于存储用户创建的数据表。不建议项目的表存放在public下，1、是数据安全；2、表存放混乱；不利于后期维护等等

查看schema

-- 通过元命令查看lottu=# \dn  List of schemas  Name  |  Owner   --------+---------- public | postgres-- 查看隐藏的schemalottu=# select table_schemalottu-#   from information_schema.tableslottu-#  group by table_schema;    table_schema    -------------------- information_schema pg_catalog(2 rows)-- 系统表存放的schema是在pg_catalog下；lottu=# \d pg_catalog.pg_classlottu=# \d pg_catalog.pg_database--这里我们先抛出一个问题；后面解析；每个数据库的pg_class是同一个吗？

3、探索PG数据库中catalog

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

-- 例如数据库lottulottu=# select oid, datname from pg_database where datname='lottu';  oid  | datname -------+--------- 32771 | lottu(1 row)--对应的目录就会有一个32771的文件夹[postgres@node3 ~]$ ll /data/postgres/data/base/total 32drwx------. 2 postgres postgres  4096 Sep 16 10:54 1drwx------. 2 postgres postgres  4096 Sep 16 10:54 13542drwx------. 2 postgres postgres 12288 Sep 21 11:44 13543drwx------. 2 postgres postgres 12288 Sep 21 14:33 32771-- 在 数据库lottu中；其中的table_cataloglottu=# select table_cataloglottu-#   from information_schema.tableslottu-#  group by table_catalog;  table_catalog --------------- lottu(1 row)-- 在数据库 postgres中；其中table_catalog是postgres。lottu=# \c postgresYou are now connected to database "postgres" as user "postgres".postgres=# select table_catalog  from information_schema.tables group by table_catalog;  table_catalog --------------- postgres(1 row)

通过上述实验；我们可以了解到PG同实例下数据库之间不仅物理结构完全独立，逻辑结构也是完全独立。这个不仅作用于用户表，还作用于系统表。现在来解析上面抛出的问题"每个数据库的pg_class是同一个吗"？

-- 在postgres数据库中postgres=# select table_catalog, table_schema, table_name, table_name :: regclass ::oid from information_schema.tables where table_name = 'pg_class'; table_catalog | table_schema | table_name | table_name ---------------+--------------+------------+------------ postgres      | pg_catalog   | pg_class   |       1259(1 row)--在 lottu 数据库中lottu=# select table_catalog, table_schema, table_name, table_name :: regclass ::oid from information_schema.tables where table_name = 'pg_class'; table_catalog | table_schema | table_name | table_name ---------------+--------------+------------+------------ lottu         | pg_catalog   | pg_class   |       1259(1 row)--根据oid找到文件[postgres@node3 data]$ find . -name '1259*'./base/1/1259./base/1/1259_vm./base/1/1259_fsm./base/13543/1259./base/13543/1259_vm./base/13543/1259_fsm./base/13542/1259./base/13542/1259_vm./base/13542/1259_fsm./base/32771/1259./base/32771/1259_vm./base/32771/1259_fsm

结果：

  • 在逻辑结构上；同实例下每个数据库的表是在存放在同库名的catalog下。
  • 在物理结构上；同实例下每个数据库的pg_class对应的数据文件也是独立存放的。

结论

  1. 在PG中；同实例下每个数据库是完全独立；表是在存放在同库名的catalog下；例如Postgres数据库的表存放在Postgres的catalog下，可以把catalog理解为database。同时物理结构是也是独立存放的
  2. 每个 database 创建时；会默认为库创建pg_catalog/information_schema

最后，前面的疑问"PostgreSQL实例中数据库之间的关系，以及数据库和对象的关系"，应该很好解释清楚。

参考文献

https://www.postgresql.org/docs/current/catalogs.html

https://www.postgresql.org/docs/current/infoschema-schema.html


原文转载：http://www.shaoqun.com/a/477479.html

inkfrog：https://www.ikjzd.com/w/668
联动优势电子商务：https://www.ikjzd.com/w/1921
不好！美国加州要求在线卖家补缴税款：https://www.ikjzd.com/home/19161
三八画像|印度社交电商女性卖家崛起！头部卖家每月成交超千单：https://www.ikjzd.com/home/117721
亚马逊红人Reviewer email分享：https://www.ikjzd.com/tl/103129
疑问前几天PG学习微信群有人在问"pg_class存储了对象和命名空间，表空间的对应关系，为什么没有和数据库的关系？我想查数据库下面有多少对象，找不到数据库和对象的关系？"简而言之；PostgreSQL数据库和对象的关系；提这个问题的同学；应该是想知道PostgreSQL实例中数据库之间的关系，以及数据库和对象的关系。至于能否有这样的SQL；容我一步一步探索探索1、探索PG的逻辑结构逻辑结构图上图
巨鲸：https://www.ikjzd.com/w/1986
ebay易趣：https://www.ikjzd.com/w/210
盘点：2018年11月，亚马逊出台了哪些"惊心动魄"的平台政策？：https://www.ikjzd.com/home/10645
TikTok ads丨手把手教你设置TikTok广告投放后台：https://www.ikjzd.com/home/129305
这才是正确处理A-to-Z的方式！：https://www.ikjzd.com/home/123447
发帖者 时间：

没有评论:

发表评论

订阅： 博文评论 (Atom)