阅读PostgreSQL中的系统目录

瀚高PG实验室

于 2018-11-15 15:12:05 发布

阅读量1.5k

点赞数

分类专栏： PostgreSQL之日常维护文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/pg_hgdb/article/details/84104063

版权

PostgreSQL之日常维护专栏收录该内容

197 篇文章 41 订阅

订阅专栏

作者：瀚高PG实验室（Highgo PG Lab）- Kalath

PostgreSQL系统目录是一个包含表和视图的模式，其中包含有关数据库内所有其他对象的元数据等。这对于数据库管理员对数据库进行管理有很大的帮助。

PostgreSQL将有关数据库和集群的元数据信息存储在“pg_catalog”模式中。但是请注意，除非遇到绝对紧急情况，否则不应该修改。

接下来主要概述一下系统目录，并选择常用的几个系统目录简单介绍如何阅读然后提取出对我们有用的信息。其他系统表的相关信息请参考PostgreSQL的官方文档。

pg_database

通常，数据库信息存储在pg_database中。例如查询pg_database中数据库名为postgres的数据库信息，可能会得到类似如下的结果：

postgres=# SELECT oid,* FROM pg_database WHERE datname = 'postgres';
-[ RECORD 1 ]-+------------
oid           | 12330
datname       | postgres
datdba        | 10
encoding      | 6
datcollate    | en_US.UTF-8
datctype      | en_US.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12329
datfrozenxid  | 548
datminmxid    | 1
dattablespace | 1663
datacl

查询结果中，需要注意以下几列的信息：

1) oid - 对象标识符。

2) datname - 数据库的名称。

3) datdba - 数据库的所有者。

4) encoding - 数据库的字符编码。

5) datconnlimit - 数据库上允许的最大并发连接数。

6) dattablespace - 此数据库的默认表空间。

pg_stat_database

通常，统计信息存储在pg_stat_database中。例如查询pg_stat_database中数据库名为postgres的数据库信息，可能会得到类似如下的结果：

postgres=# SELECT * FROM pg_stat_database WHERE datname = 'postgres';
-[ RECORD 1 ]--+------------------------------
datid          | 12330
datname        | postgres
numbackends    | 1
xact_commit    | 277
xact_rollback  | 1
blks_read      | 262
blks_hit       | 11251
tup_returned   | 155045
tup_fetched    | 3408
tup_inserted   | 0
tup_updated    | 0
tup_deleted    | 0
conflicts      | 0
temp_files     | 0
temp_bytes     | 0
deadlocks      | 0
blk_read_time  | 0
blk_write_time | 0
stats_reset    | 2018-11-01 14:34:56.462662+08

这个统计表可以帮助我们获得有用的数据。此表中的每一行都包含每个数据库的实时数据，如果希望监视更改，可以定期导出以便随时进行跟踪。

查询结果中，需要注意以下信息：

1）事务信息可以在xact_commit列和xact_rollback列中找到，它们分别包含数据库已提交和回滚的事务数。

2）有关是否从磁盘或内存中检索数据的信息存储在blks_read列和blks_hit列中。blks_read列显示此数据库从磁盘读取的块数，而blks_hit列显示在PostgreSQL的缓冲区缓存中找到的块数（由shared_buffers参数表示）。

3）有几列涉及元组的信息。tup_returned列表示数据库中返回的行数。tup_fetched列表示数据库中提取的行数。tup_inserted、tup_updated和tup_deleted这几列，分别代表在这个数据库中插入、更新和删除的元组数。

4）如果备用服务器出了问题，则conflicts列可以作为一种跟踪由于与备用数据库处于“恢复模式”而冲突被取消的查询数量的方法。如果不是的话，则可以忽略此列。

5）temp_files列跟踪创建的文件的数量，temp_bytes列跟踪所有使用的临时文件的总大小。

6）deadlocks列跟踪死锁发生的次数。由于死锁可能会导致查询错误，因此最好跟踪此情况并确保应用程序不会因此出现运行错误。

7）blk_read_time列和blk_write_time列跟踪数据库中后端读取和写入数据所花费的总毫秒数。

8）stats_reset列仅显示上次重置此行中的统计信息时的时间戳（带时区）。

pg_stat_bgwriter

查询pg_stat_bgwriter中的信息，可以得到类似如下的结果：

postgres=# SELECT * FROM pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed     | 23
checkpoints_req       | 0
checkpoint_write_time | 0
checkpoint_sync_time  | 0
buffers_checkpoint    | 0
buffers_clean         | 0
maxwritten_clean      | 0
buffers_backend       | 0
buffers_backend_fsync | 0
buffers_alloc         | 291
stats_reset           | 2018-11-01 14:34:54.810804+08

查询结果中，需要注意以下信息：

1）有四列信息用于跟踪检查点：

checkpoints_timed列和checkpoints_req列显示已发生的计划检查点数（定时）和请求的检查点数（也称为强制检查点）。

checkpoint_write_time列和checkpoint_sync_time列记录检查点进程写入和同步到磁盘所花费的总时间（以毫秒为单位）。

2）buffers_checkpoint列中的信息是检查点写入磁盘的缓冲区总数。

3）buffers_clean列表示后台进程写入磁盘的缓冲区数。

4）maxwritten_clean列表示后台写入进程在每次运行时达到刷新的最大页数的次数（使用bgwriter_lru_maxpages参数控制）。

5）buffers_backend列表示后端必须自身，而不是后台写入进程或检查点写的缓冲区数量，buffers_backend_fsync列表示后端必须执行自身的fsync调用的次数，buffers_alloc列则显示通常已分配的缓冲区数量。

pg_stat_activity

查询时会显示有关数据库当前连接的信息，例如可能会得到类似如下的结果：

postgres=# SELECT * FROM pg_stat_activity;
-[ RECORD 3 ]----+---------------------------------
datid            | 12330
datname          | postgres
pid              | 2021
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2018-11-08 15:56:11.293063+08
xact_start       | 2018-11-08 16:07:54.817688+08
query_start      | 2018-11-08 16:07:54.817688+08
state_change     | 2018-11-08 16:07:54.817691+08
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 876
query            | SELECT * FROM pg_stat_activity ;
backend_type     | client backend

查询结果中，需要注意以下信息：

1）datname列表示实际连接到的数据库，pid列表示数据库主机自身连接的进程ID，usesysid列和usename列表示连接的数据库用户。

2）有四个时间戳列显示了特定事件何时开始：backend_start列是实际建立连接的时间，xact_start列是当前事务开始的时间（如果客户端没有打开事务，则为null），query_start列是当前或最近的查询启动的时间，state_change列是连接状态最后一次更改的时间。

3）如果一个查询正在等待另一个查询释放锁，则wait_event_type列会包含有关它的等待事件类型的一些信息，并且wait_event列将显示等待事件的名称。

4）state列显示当前连接所处的状态，例如活动，空闲，在事务中空闲，query列将显示正在运行或最近运行的实际查询。

pg_locks

查询时会显示有关数据库当前连接的信息，以及对表的锁的信息。例如查询时可能会得到类似如下结果：

postgres=# SELECT * FROM pg_locks;
-[ RECORD 1 ]------+----------------
locktype           | relation
database           | 12330
relation           | 11577
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 3/23
pid                | 2021
mode               | AccessShareLock
granted            | t
fastpath           | t
-[ RECORD 2 ]------+----------------
locktype           | virtualxid
database           | 
relation           | 
page               | 
tuple              | 
virtualxid         | 3/23
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 3/23
pid                | 2021
mode               | ExclusiveLock
granted            | t
fastpath           | t

查询结果中，需要注意以下信息：

1）最重要的列是pid列，它匹配来自pg_stat_activity的pid。

2）relation列匹配来自pg_class的OID。

3）mode列显示持有的锁模式的名称。

4）granted列来说明是否已经对锁进行授权。