作者:瀚高PG实验室 (Highgo PG Lab)
1. 查看数据库
mydb=> \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
mydb | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
mydb=> select pg_database_size('mydb'); --查看mydb数据库的大小
pg_database_size
------------------
3637896
(1 row)
mydb=> select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; --查看所有数据库的大小
datname | size
-----------+---------
postgres | 3621512
mydb | 3637896
template1 | 3563524
template0 | 3563524
(4 rows)
mydb=> select pg_size_pretty(pg_database_size('mydb')); --以KB,MB,GB的方式来查看数据库大小
pg_size_pretty
----------------
3553 kB
(1 row)
2. 查看多表
mydb=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | test | table | mydb
(1 row)
3. 查看单表
mydb=> \d test;
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
name | character varying(32) |
Indexes: "mydb_id_pk" PRIMARY KEY, btree (id)
mydb=> select pg_relation_size('test'); --查看表大小
pg_relation_size
------------------
0
(1 row)
mydb=> select pg_size_pretty(pg_relation_size('test')); --以KB,MB,GB的方式来查看表大小
pg_size_pretty
----------------
0 bytes
(1 row)
mydb=> select pg_size_pretty(pg_total_relation_size('test')); --查看表的总大小,包括索引大小
pg_size_pretty
----------------
8192 bytes
(1 row)
4. 查看索引
mydb=> \di
List of relations
Schema | Name | Type | Owner | Table
--------+---------------+-------+---------+-------
public | mydb_id_pk | index | mydb | test
(1 row)
mydb=> select pg_size_pretty(pg_relation_size('mydb_id_pk')); --查看索大小
pg_size_pretty
----------------
8192 bytes
(1 row)
5. 查看表空间,以及大小
mydb=> select spcname from pg_tablespace; --查看所有表空间
spcname
------------
pg_default
pg_global
(2 rows)
mydb=> select pg_size_pretty(pg_tablespace_size('pg_default')); --查看表空间大小
pg_size_pretty
----------------
14 MB
(1 row)