探索PostgreSQL：数据库与模式的逻辑结构（一）

在逻辑上一个实例或集群由多个database组成，一个数据库包含多个schema，一个schema包含很多数据对象，如表、索引、视图等。database之间是隔离的，即一个数据库对象不能访问另外一个数据库中的对象。要访问其他数据库中的对象需要使用插件如DBLINK。逻辑结构如下图所示：

1. 可以使用命令或sql查询视图获取数据库信息。

mydb=# \l+

mydb=# select d.oid,d.datname,u.usename,d.encoding from pg_database d,pg_user u where d.datdba=u.usesysid;

2.查询schema信息

mydb=# \dn+

SELECT n.nspname AS "Name",

pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",

pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",

pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"

FROM pg_catalog.pg_namespace n

WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'

ORDER BY 1;

3.查询schema中表的信息

mydb=# \dt+

SELECT n.nspname as "Schema",

c.relname as "Name",

CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",

pg_catalog.pg_get_userbyid(c.relowner) as "Owner",

CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",

am.amname as "Access method",

pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",

pg_catalog.obj_description(c.oid, 'pg_class') as "Description"

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam

WHERE c.relkind IN ('r','p','')

AND n.nspname <> 'pg_catalog'

AND n.nspname !~ '^pg_toast'

AND n.nspname <> 'information_schema'

AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

4.快捷命令和sql

pg中的快捷命令内部也是通过sql语句查询相应的表和视图获取的信息。如上面所示的\l+，\dn+等。如何获取快捷命令的sql语句呢？可通过设置环境变量打开输出，然后执行快捷命令就会显示命令所执行的sql。

mydb=# \set ECHO_HIDDEN on

关闭sql显示为mydb=# \set ECHO_HIDDEN off