PostgreSQL查看表空间和表占用空间大小select sum(t.size) from (
SELECT table_schema || '.' || table_name AS table_full_name, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
) t;
2.查看每个表所占用磁盘空间大小
SELECT table_schema || '.' || table_name AS table_full_name, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;
3.查看数据库大小
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
-- 查看db数据库的大小
playboy=> select pg_database_size('db');
pg_database_size
------------------
3637896
(1 row)
-- 查看所有数据库的大小
playboy=> select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;
datname | size
-----------+---------
postgres | 3621512
playboy | 3637896
template1 | 3563524
template0 | 3563524
(4 rows)
-- 以KB，MB，GB的方式来查看数据库大小
playboy=> select pg_size_pretty(pg_database_size('db'));
pg_size_pretty
----------------
3553 kB
(1 row)
4.查看表大小
-- 相当于mysql的，mysql> desc test;
playboy=> \d test;
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
name | character varying(32) |
Indexes: "playboy_id_pk" PRIMARY KEY, btree (id)
-- 查看表大小
playboy=> select pg_relation_size('test_big1');
pg_relation_size
------------------
0
(1 row)
-- 以KB，MB，GB的方式来查看表大小
playboy=> select pg_size_pretty(pg_relation_size('test_big1'));
pg_size_pretty
----------------
0 bytes
(1 row)
-- 查看表的总大小，包括索引大小
playboy=> select pg_size_pretty(pg_total_relation_size('test_big1'));
pg_size_pretty
----------------
8192 bytes
(1 row)
5.查看所有所占磁盘空间大小
-- 相当于mysql的，mysql> show index from test;
playboy=> \di
List of relations
Schema | Name | Type | Owner | Table
--------+---------------+-------+---------+-------
public | playboy_id_pk | index | playboy | test
(1 row)
-- 查看索引大小
playboy=> select pg_size_pretty(pg_relation_size('test_big1'));
pg_size_pretty
----------------
8192 bytes
(1 row)
6.查看表空间大小
-- 查看所有表空间
playboy=> select spcname from pg_tablespace;
spcname
------------
pg_default
pg_global
(2 rows)
-- 查看表空间大小
playboy=> select pg_size_pretty(pg_tablespace_size('pg_default'));
pg_size_pretty
----------------
14 MB
(1 row)
