google Windows Android Ubuntu apache 微软 linux命令 nginx wordpress 程序员 Python Firefox HTML5 php linux shell java centos mysql 开源

postgresql 查看数据库,表,索引,表空间以及大小

客户要求用pgsql,所在服务器装了一下pgsql,我出了一个pgsql的分类,看这篇文章前,把这个分类下的文章都可以看一下,这是我熟悉pgsql的一套流程。以前搞过一次pgsql,很早了。

1,查看数据库

playboy=> l //加上字母l,相当于mysql的,mysql> show databases;
List of databases
Name | Owner | Encoding
-----------+----------+----------
playboy | postgres | UTF8
postgres | postgres | UTF8 
template0 | postgres | UTF8
template1 | postgres | UTF8

playboy=> select pg_database_size('playboy'); //查看playboy数据库的大小
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)

playboy=> select pg_size_pretty(pg_database_size('playboy')); //以KB,MB,GB的方式来查看数据库大小
pg_size_pretty
----------------
3553 kB
(1 row)

2,查看多表

playboy=> dt //相当于mysql的,mysql> show tables;
List of relations 
Schema | Name | type | Owner
--------+------+-------+---------
public | test | table | playboy
(1 row)

3,查看单表

playboy=> d test; //相当于mysql的,mysql> desc 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'); //查看表大小
pg_relation_size
------------------
0
(1 row)

playboy=> select pg_size_pretty(pg_relation_size('test')); //以KB,MB,GB的方式来查看表大小
pg_size_pretty
----------------
0 bytes
(1 row)

playboy=> select pg_size_pretty(pg_total_relation_size('test')); //查看表的总大小,包括索引大小
pg_size_pretty
----------------
8192 bytes
(1 row)

4,查看索引

playboy=> di //相当于mysql的,mysql> show index from test;
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('playboy_id_pk')); //查看索大小
pg_size_pretty
----------------
8192 bytes 
(1 row)

5,查看表空间,以及大小

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)

延伸阅读

评论