PostgreSQL中查询对象大小

 

1. 数据库大小（pg_database_size）
postgres=# select datname from pg_database;
   datname  
------------
  postgres
  osdbadb
  template1
  template0
  mytestdb01
  db03
(6 rows)

postgres=# select pg_database_size ('db03'),pg_size_pretty(pg_database_size('db03'));
  pg_database_size | pg_size_pretty
------------------+----------------
           8072191 | 7883 kB
(1 row)

2. 表空间大小(pg_tablespace_size)
postgres=# select pg_size_pretty(pg_tablespace_size('pg_default'));
  pg_size_pretty
----------------
  641 MB
(1 row)

postgres=# select pg_size_pretty(pg_tablespace_size('pg_global'));
  pg_size_pretty
----------------
  590 kB
(1 row)

3. 表空间下有哪些文件(查看表对应的数据文件）
postgres=# select pg_relation_filepath('t');
  pg_relation_filepath
----------------------
  base/13523/16396
(1 row)
[postgres@ogg2 13523]$ ls -l 16396
-rw------- 1 postgres postgres 8192 Nov  6  2021 16396
[postgres@ogg2 13523]$ pwd
/data/base/13523

4. 表大小(pg_relation_seize--仅计算表的大小,pg_total_relation_size--包含索引的大小)
postgres=# select pg_size_pretty(pg_relation_size('t'));
  pg_size_pretty
----------------
  8192 bytes
(1 row)

postgres=# select pg_size_pretty(pg_total_relation_size('t'));
  pg_size_pretty
----------------
  8192 bytes
(1 row)

5. 索引大小（pg_indexes_size）
postgres=# select pg_size_pretty(pg_indexes_size('t'));
  pg_size_pretty
----------------
  0 bytes
(1 row)

6. 当前用户
postgres=# select current_user;
  current_user
--------------
  postgres
(1 row)

postgres=# select user;
    user  
----------
  postgres
(1 row)

postgres=# select session_user;
  session_user
--------------
  postgres
(1 row)

7. 当前会话pid
第一个会话
postgres=# select pg_backend_pid();
  pg_backend_pid
----------------
            2646
(1 row)
第一个会话中执行
[root@ogg2 ~]# ps -ef | grep 2646
postgres   2646   2590  0 20:28 ?        00:00:00 postgres: postgres postgres [local] idle
root       2742   2726  0 20:37 pts/2    00:00:00 grep 2646
第二个会话中执行
postgres=# select pg_cancel_backend(2646);
  pg_cancel_backend
-------------------
  t
(1 row)
第一个会话中执行：
postgres=# select pg_backend_pid();
  pg_backend_pid
----------------
            2646
(1 row)

第二个会话中执行：
postgres=# select pg_terminate_backend(2646);
  pg_terminate_backend
----------------------
  t
(1 row)

第一个会话中：
postgres=# select pg_backend_pid();
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
     This probably means the server terminated abnormally
     before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

可以通过pg_stat_activity找出长时间运行的sql命令

8. 分区表
pg_class的rekind，p表示分区表
r = 普通表， i = 索引， S = 序列， t = TOAST表， v = 视图， m = 物化视图， c = 组合类型， f = 外部表， p = 分区表， I = 分区索引
postgres=# \d pg_class
                      Table "pg_catalog.pg_class"
        Column        |     Type     | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
  relname             | name         |           | not null |
  relnamespace        | oid          |           | not null |
  reltype             | oid          |           | not null |
  reloftype           | oid          |           | not null |
  relowner            | oid          |           | not null |
  relam               | oid          |           | not null |
  relfilenode         | oid          |           | not null |
  reltablespace       | oid          |           | not null |
  relpages            | integer      |           | not null |
  reltuples           | real         |           | not null |
  relallvisible       | integer      |           | not null |
  reltoastrelid       | oid          |           | not null |
  relhasindex         | boolean      |           | not null |
  relisshared         | boolean      |           | not null |
  relpersistence      | "char"       |           | not null |
  relkind             | "char"       |           | not null |
  relnatts            | smallint     |           | not null |
  relchecks           | smallint     |           | not null |
  relhasoids          | boolean      |           | not null |
  relhasrules         | boolean      |           | not null |
  relhastriggers      | boolean      |           | not null |
  relhassubclass      | boolean      |           | not null |
  relrowsecurity      | boolean      |           | not null |
  relforcerowsecurity | boolean      |           | not null |
  relispopulated      | boolean      |           | not null |
  relreplident        | "char"       |           | not null |
  relispartition      | boolean      |           | not null |
  relrewrite          | oid          |           | not null |
  relfrozenxid        | xid          |           | not null |
  relminmxid          | xid          |           | not null |
  relacl              | aclitem[]    |           |          |
  reloptions          | text[]       |           |          |
  relpartbound        | pg_node_tree |           |          |
Indexes:
     "pg_class_oid_index" UNIQUE, btree (oid)
     "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
     "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
    
    
pg_partitioned_table存放有关表如何被分区的信息，partstrat表示分区类型
h = 哈希分区表，l = 列表分区表，r = 范围分区表
postgres=# \d pg_partitioned_table
             Table "pg_catalog.pg_partitioned_table"
     Column     |     Type     | Collation | Nullable | Default
---------------+--------------+-----------+----------+---------
  partrelid     | oid          |           | not null |
  partstrat     | "char"       |           | not null |
  partnatts     | smallint     |           | not null |
  partdefid     | oid          |           | not null |
  partattrs     | int2vector   |           | not null |
  partclass     | oidvector    |           | not null |
  partcollation | oidvector    |           | not null |
  partexprs     | pg_node_tree |           |          |
Indexes:
     "pg_partitioned_table_partrelid_index" UNIQUE, btree (partrelid)

9. 分区索引
pg_class的rekind，I表示分区索引
r = 普通表， i = 索引， S = 序列， t = TOAST表， v = 视图， m = 物化视图， c = 组合类型， f = 外部表， p = 分区表， I = 分区索引

10. 数据库列表
postgres=# select datname,datdba,enconding,datlastsysoid,datfrozenxid,datminmxid,dattablespace from pg_database;
ERROR:  column "enconding" does not exist
LINE 1: select datname,datdba,enconding,datlastsysoid,datfrozenxid,d...
                               ^
HINT:  Perhaps you meant to reference the column "pg_database.encoding".    --提示，还挺智能
postgres=# select datname,datdba,encoding,datlastsysoid,datfrozenxid,datminmxid,dattablespace from pg_database;
   datname   | datdba | encoding | datlastsysoid | datfrozenxid | datminmxid | dattablespace
------------+--------+----------+---------------+--------------+------------+---------------
  postgres   |     10 |        6 |         13522 |          561 |          1 |          1663
  osdbadb    |     10 |        6 |         13522 |          561 |          1 |          1663
  template1  |     10 |        6 |         13522 |          561 |          1 |          1663
  template0  |     10 |        6 |         13522 |          561 |          1 |          1663
  mytestdb01 |     10 |        6 |         13522 |          561 |          1 |          1663
  db03       |     10 |        6 |         13522 |          561 |          1 |          1663
(6 rows)

11. 查询对象大小，在文档9.26系统管理函数中有介绍

12. 索引无效，PostgreSQL里没找到类似Oracle的alter index的语法设置索引，也没搜到类似Oracle的dba_indexes的status状态查看。PostgreSQL关于索引重建部分有索引INVALID查看方法；这个上篇中通过psql -E显示具体查询命令的，但太多，现在还分不清哪个是查询索引无效的。

如果在扫描表示出现问题，例如死锁或者唯一索引中的唯一性被违背， CREATE INDEX将会失败，但留下一个“不可用” 的索引。这个索引会被查询所忽略，因为它可能不完整。不过它仍将消耗更新 开销。psql的\d命令将把这类索引报告为 INVALID：

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID