The PostgreSQL shared/global catalog

中年如酒

已于 2022-08-27 10:52:54 修改

阅读量255

点赞数

分类专栏： Postgresql 文章标签： postgresql 数据库 mysql

于 2022-08-12 15:59:54 首次发布

原文链接：https://www.dbi-services.com/blog/the-postgresql-shared-global-catalog/

版权

Postgresql 专栏收录该内容

34 篇文章 4 订阅

订阅专栏

一个 PostgreSQL 实例（或集群）可以包含许多数据库，其中三个（template0、template1 和 postgres）是默认存在的。在目录(catalog)和 postgres 默认数据库方面，主要有两点需要进一步澄清：

1.默认数据库postgres 是否定义了catalog,并且它是master数据库吗？
2.global catalog(全局目录中)到底有什么？
在这篇文章中，我们将研究这两点，我希望更清楚地说明共享/全局目录包含什么，并且 postgres 是默认的master数据库吗？它有没有定义 postgres catalog。

对于第一点（默认的 postgres 数据库是master数据库吗？它是否定义了catalog？）答案很容易给出。默认的 postgres 数据库存在只有一个原因：因为大多数客户端实用程序都假定它存在，并且默认连接到该数据库。但这并不意味着默认的 postgres 有什么特别之处，您可以删除它：

postgres=# \l
     Name     |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges
--------------+--------------+----------+-------------+-------------+-----------------------
 postgres     | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 template1    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres  
(3 rows)
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# drop database postgres;
DROP DATABASE
template1=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(2 rows)

我们甚至有默认情况下这样做的客户。默认的 postgres 数据库没有什么特别之处，最初它与 template1 完全相同。如果需要，您可以轻松地重新创建它：

template1=# create database postgres;
CREATE DATABASE
template1=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

这回答了第一个问题：默认的 postgres 数据库不是主数据库，它没有定义 PostgreSQL 目录。
第二个问题也很容易回答：全局/共享目录中到底有什么？大多数 PostgreSQL 目录表是for每个数据库的，例如 pg_tables：

postgres=# \d pg_tables
              View "pg_catalog.pg_tables"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 schemaname  | name    |           |          | 
 tablename   | name    |           |          | 
 tableowner  | name    |           |          | 
 tablespace  | name    |           |          | 
 hasindexes  | boolean |           |          | 
 hasrules    | boolean |           |          | 
 hastriggers | boolean |           |          | 
 rowsecurity | boolean |           |          |

所有这些目录表和视图都在一个名为“pg_catalog”的系统模式中。当您在 psql 中使用“dn”快捷方式时，默认情况下不会列出此架构：

postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

您需要添加“S”选项，列出系统模式：

postgres=# \dnS
        List of schemas
        Name        |  Owner   
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | postgres
(4 rows)

一些目录表/视图对于集群/实例是全局的，而不是每个数据库。显而易见的是用户/角色和表空间。它们都不是每个数据库的，因为用户/角色可以访问各种数据库，并且各种数据库可以将表存储在同一个表空间中。现在的问题是：我如何知道目录表/视图是全局的还是每个数据库的？甚至全局目录表/视图也列在local catalog schema：

postgres=# \d pg_catalog.pg_roles
                         View "pg_catalog.pg_roles"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           |          | 
 rolsuper       | boolean                  |           |          | 
 rolinherit     | boolean                  |           |          | 
 rolcreaterole  | boolean                  |           |          | 
 rolcreatedb    | boolean                  |           |          | 
 rolcanlogin    | boolean                  |           |          | 
 rolreplication | boolean                  |           |          | 
 rolconnlimit   | integer                  |           |          | 
 rolpassword    | text                     |           |          | 
 rolvaliduntil  | timestamp with time zone |           |          | 
 rolbypassrls   | boolean                  |           |          | 
 rolconfig      | text[]                   | C         |          | 
 oid            | oid                      |           |          |

通过仅查看目录模式，我们无法回答该问题。然而，我们可以做的是查看数据目录（$PGDATA）。数据库位于“base”中，全局/共享目录位于“global”中：

postgres@centos8pg:/home/postgres/ [pgdev] cd $PGDATA
postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] ls -l | egrep "base|global"
drwx------. 6 postgres postgres    58 Nov 21 09:50 base
drwx------. 2 postgres postgres  4096 Nov 21 09:48 global

当我们查看“global”目录时，我们会看到许多 OID（对象标识符），这就是 PostgreSQL 内部引用表的方式：

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] ls -l global/
total 564
-rw-------. 1 postgres postgres  8192 Nov 21 03:52 1213
-rw-------. 1 postgres postgres 24576 Nov 20 22:52 1213_fsm
-rw-------. 1 postgres postgres  8192 Nov 21 03:53 1213_vm
-rw-------. 1 postgres postgres  8192 Nov 20 22:52 1214
-rw-------. 1 postgres postgres 24576 Nov 20 22:52 1214_fsm
-rw-------. 1 postgres postgres  8192 Nov 20 22:52 1214_vm
-rw-------. 1 postgres postgres 16384 Nov 20 22:52 1232
-rw-------. 1 postgres postgres 16384 Nov 20 22:52 1233
-rw-------. 1 postgres postgres  8192 Nov 20 22:57 1260
-rw-------. 1 postgres postgres 24576 Nov 20 22:52 1260_fsm
-rw-------. 1 postgres postgres  8192 Nov 20 22:52 1260_vm

这些 OID 中的每一个都是全局/共享目录的一个表。由于我们对.vm和.fsm不感兴趣，因此我们将它们排除在外，只列出唯一的 OID：

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] ls -l global/ | awk -F " " '{print $9}' | egrep "^[0-9]" | egrep -v "fsm|vm"
1213
1214
1232
1233
1260
1261
1262
2396
2397
2671
2672
2676
2677
2694
2695
2697
2698
2846
2847
2964
2965
2966
2967
3592
3593
4060
4061
4175
4176
4177
4178
4181
4182
4183
4184
4185
4186
6000
6001
6002
6100
6114
6115

这些是全局/共享目录中的表。可以使用 oid2name将这些 OID 翻译成可读的名称。如果没有任何参数，oid2name 将为您提供“base”目录中列出的数据库的名称：

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  24616       postgres  pg_default
  12905      template0  pg_default
      1      template1  pg_default

我们还可以将共享/全局目录的 OID 传递给 oid2name，结果将回答第二个问题：全局/共享目录中到底有什么？

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] for i in `ls -l global/ | awk -F " " '{print $9}' | egrep "^[0-9]" | egrep -v "fsm|vm"`; do oid2name -x -S -q -o $i; done | grep -v "index"
      1213  pg_tablespace  1213  pg_catalog   pg_global
      1214  pg_shdepend  1214  pg_catalog   pg_global
      1260   pg_authid  1260  pg_catalog   pg_global
      1261  pg_auth_members  1261  pg_catalog   pg_global
      1262  pg_database  1262  pg_catalog   pg_global
      2396  pg_shdescription  2396  pg_catalog   pg_global
      2846  pg_toast_2396  2846  pg_toast   pg_global
      2964  pg_db_role_setting  2964  pg_catalog   pg_global
      2966  pg_toast_2964  2966  pg_toast   pg_global
      3592  pg_shseclabel  3592  pg_catalog   pg_global
      4060  pg_toast_3592  4060  pg_toast   pg_global
      4175  pg_toast_1260  4175  pg_toast   pg_global
      4177  pg_toast_1262  4177  pg_toast   pg_global
      4181  pg_toast_6000  4181  pg_toast   pg_global
      4183  pg_toast_6100  4183  pg_toast   pg_global
      4185  pg_toast_1213  4185  pg_toast   pg_global
      6000  pg_replication_origin  6000  pg_catalog   pg_global
      6100  pg_subscription  6100  pg_catalog   pg_global

这是答案（不包括索引）。如果我们也排除 toast 表，会注意到全局/共享目录中的目录表/视图并不多：

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] for i in `ls -l global/ | awk -F " " '{print $9}' | egrep "^[0-9]" | egrep -v "fsm|vm"`; do oid2name -x -S -q -o $i; done | egrep -v "index|toast"
      1213  pg_tablespace  1213  pg_catalog   pg_global
      1214  pg_shdepend  1214  pg_catalog   pg_global
      1260   pg_authid  1260  pg_catalog   pg_global
      1261  pg_auth_members  1261  pg_catalog   pg_global
      1262  pg_database  1262  pg_catalog   pg_global
      2396  pg_shdescription  2396  pg_catalog   pg_global
      2964  pg_db_role_setting  2964  pg_catalog   pg_global
      3592  pg_shseclabel  3592  pg_catalog   pg_global
      6000  pg_replication_origin  6000  pg_catalog   pg_global
      6100  pg_subscription  6100  pg_catalog   pg_global