BXW

博哥的blog

博哥的技术博客

0%

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

发表于 更新于 分类于
postgresql查看数据库、表、表空间

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
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)