疑問
前幾天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
-- 查看隱藏的schema
lottu=# select table_schema
lottu-# from information_schema.tables
lottu-# group by table_schema;
table_schema
--------------------
information_schema
pg_catalog
(2 rows)
-- 系統表存放的schema是在pg_catalog下；
lottu=# \d pg_catalog.pg_class
lottu=# \d pg_catalog.pg_database
--這里我們先拋出一個問題；后面決議；每個資料庫的pg_class是同一個嗎？
3、探索PG資料庫中catalog
我們知道新建一個資料庫會在表空間對應的目錄下創建一個 oid（資料庫oid）的目錄；用于存放這個資料庫對應的資料庫檔案，
-- 例如資料庫lottu
lottu=# select oid, datname from pg_database where datname='lottu';
oid | datname
-------+---------
32771 | lottu
(1 row)
--對應的目錄就會有一個32771的檔案夾
[postgres@node3 ~]$ ll /data/postgres/data/base/
total 32
drwx------. 2 postgres postgres 4096 Sep 16 10:54 1
drwx------. 2 postgres postgres 4096 Sep 16 10:54 13542
drwx------. 2 postgres postgres 12288 Sep 21 11:44 13543
drwx------. 2 postgres postgres 12288 Sep 21 14:33 32771
-- 在 資料庫lottu中；其中的table_catalog
lottu=# select table_catalog
lottu-# from information_schema.tables
lottu-# group by table_catalog;
table_catalog
---------------
lottu
(1 row)
-- 在資料庫 postgres中；其中table_catalog是postgres，
lottu=# \c postgres
You 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對應的資料檔案也是獨立存放的，
結論
- 在PG中；同實體下每個資料庫是完全獨立；表是在存放在同庫名的catalog下；例如Postgres資料庫的表存放在Postgres的catalog下，可以把catalog理解為database，同時物理結構是也是獨立存放的
- 每個 database 創建時；會默認為庫創建pg_catalog/information_schema
最后，前面的疑問“PostgreSQL實體中資料庫之間的關系，以及資料庫和物件的關系”，應該很好解釋清楚，
參考文獻
https://www.postgresql.org/docs/current/catalogs.html
https://www.postgresql.org/docs/current/infoschema-schema.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/106728.html
標籤：其他
上一篇：資料庫 之 資料庫分類