postgresql查看数据库、表、表空间（位置大小）、索引的方法

postgresql查看数据库、表、表空间（位置大小）、索引的方法	

1.数据库

postgres=# \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 | postgres=CTc/postgres+
           |          |          |             |             | =c/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)
postgres=# select pg_database_size('test');    -----查看数据库的大小
 pg_database_size 
------------------
         43819524
(1 row)

postgres=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;   ----查看所有数据库的大小
  datname  |   size    
-----------+-----------
 template0 |   7430660
 postgres  | 224188952
 template1 |   7537156
 test      |  43819524
(4 rows)

postgres=# select pg_size_pretty(pg_database_size('test'));   ----以kb、mb、gb的形式显示数据库的大小
 pg_size_pretty 
----------------
 42 MB
(1 row)

2、表
postgres=# \d         ---查看数据库的所有表
                    List of relations
 Schema |      Name       |       Type        |  Owner   
--------+-----------------+-------------------+----------
 osdba  | tb11            | table             | postgres
 public | a               | table             | user1
 public | b               | table             | postgres
 public | emp             | table             | postgres
 public | events          | table             | postgres
 public | events_id_seq   | sequence          | postgres
 public | mv_events       | materialized view | postgres

(7 rows)

postgres=# \d a              -----查看表的信息（如果表中有索引会在下面显示索引的内容）
       Table "public.a"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
Indexes:
    "a_index" btree (a)

postgres=#  select pg_relation_size('a');    ----查看表的大小
 pg_relation_size 
------------------
             8192
(1 row)

postgres=# select pg_size_pretty(pg_relation_size('a'));    ------以kb、mb、gb的形式显示表的大小
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

postgres=# select pg_size_pretty(pg_total_relation_size('a'));   -----表的总大小，包括索引的大小
 pg_size_pretty 
----------------
 24 kB
(1 row)

3、索引
postgres=# \di                                           -------查看数据库的所有索引
                     List of relations
 Schema |      Name       | Type  |  Owner   |    Table     
--------+-----------------+-------+----------+--------------
 public | a_index         | index | user1    | a
 public | events_id       | index | postgres | events
 public | pk_user_info    | index | postgres | user_info
 public | pk_user_session | index | postgres | user_session
 public | t11_pkey        | index | postgres | t11
 public | tran_pkey       | index | postgres | tran
 public | zz_pkey         | index | postgres | zz
(7 rows)

 postgres=#  select pg_size_pretty(pg_relation_size('a_index'));   -----查看索引大小
 pg_size_pretty 
----------------
 16 kB
(1 row)

4、表空间
postgres=# \db             ------查看所有的表空间以及表空间对应的目录（pg_default、 pg_global为默认的表空间在data目录下）
                    List of tablespaces
    Name    |  Owner   |              Location              
------------+----------+------------------------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 txt        | postgres | /home/postgres/pgsql96/tablespace1
(3 rows)	

postgres=# select pg_size_pretty(pg_tablespace_size('pg_default'));       ----查看表空间的大小
 pg_size_pretty 
----------------
 270 MB
(1 row)