GreenPlum查询数据库大小
👉 本文共约81个字，系统预计阅读时间或需1分钟。
方法1：和PG查询SQL一样
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 | SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges", CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END as "Size", t.spcname as "Tablespace", pg_catalog.shobj_description(d.oid, 'pg_database') as "Description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid UNION ALL SELECT '总计' as "Name", '' as "Owner", '' as "Encoding", '' as "Collate", '' as "Ctype", '' AS "Access privileges", pg_catalog.pg_size_pretty(SUM((pg_catalog.pg_database_size(d.datname)))) as "Size", '' as "Tablespace", '' as "Description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid ORDER BY 1; |
方法2：使用gp_toolkit模式
1 2 3 | select sodddatname,sodddatsize/1024/1024 as db_size_MB from gp_toolkit.gp_size_of_database d order by d.sodddatname ; |
方法3：
1 2 3 4 5 6 7 8 9 10 11 | postgres=# \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+---------+----------+------------+------------+---------------------+-------+------------+-------------------------------------------- postgres | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | | 82 MB | pg_default | default administrative connection database template0 | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/gpadmin +| 81 MB | pg_default | unmodifiable empty database | | | | | gpadmin=CTc/gpadmin | | | template1 | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/gpadmin +| 82 MB | pg_default | default template for new databases | | | | | gpadmin=CTc/gpadmin | | | test_db | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | | 82 MB | pg_default | (4 rows) |
