postgresql相关的重命名和数据库大小相关查询

66 阅读 0 评论 44 点赞

我是靠谱客的博主优美人生，最近开发中收集的这篇文章主要介绍postgresql相关的重命名和数据库大小相关查询，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、postgresql有关的重名

-- 重命名数据库
-- 使用alter database修改数据库名的时候，要保证当前数据库没有活动的session连接
alter database test rename to test2;
SQL 错误 [55006]: ERROR: database "test" is being accessed by other users
Detail: There is 1 other session using the database.
-- 关闭所有活动session
select pg_terminate_backend(pg_stat_activity.pid)
from pg_stat_activity
where datname='test' and pid<>pg_backend_pid();
-- 使用update方式修改，但是即便可以运行成功，但是实际上没有修改，证明实际不可取
UPDATE pg_database SET datname = 'test' WHERE datname = 'test2';
postgres=# c test2
FATAL:
database "test2" does not exist
-- 重命名模式
alter schema test rename to test2;
-- 重命名表名
alter table schema_name.tb_name rename to new_table_name;
-- 重命名字段
alter table schema_name.tb_name rename column col_name to new_col_name;

二、查询数据库大小

-- 查询单个数据库大小
-- select pg_size_pretty(pg_database_size('db_name'));
postgres=# select pg_size_pretty(pg_database_size('postgres'));
pg_size_pretty
----------------
11 MB
(1 row)
-- 查询单个表大小
-- select pg_size_pretty(pg_relation_size('tb_name'));
postgres=# select pg_size_pretty(pg_relation_size('aa'));
pg_size_pretty
----------------
8192 bytes
(1 row)
-- 查询单个表的总大小，包括该表的索引大小
-- select pg_size_pretty(pg_total_relation_size('tb_name'));
postgres=# select pg_size_pretty(pg_total_relation_size('aa'));
pg_size_pretty
----------------
24 kB
(1 row)
-- 查询索引大小
-- select pg_size_pretty(pg_relation_size('index_name'));
postgres=# select pg_size_pretty(pg_relation_size('aa_key'));
pg_size_pretty
----------------
16 kB
(1 row)
-- 查询单个表空间大小
-- select pg_size_pretty(pg_tablespace_size('pg_default'));
postgres=# select pg_size_pretty(pg_tablespace_size('pg_default'));
pg_size_pretty
----------------
34 MB
(1 row)
-- 查询所有数据库大小
-- select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
postgres=# select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
datname
| pg_size_pretty
-----------+----------------
postgres
| 7885 kB
template1 | 7737 kB
template0 | 7737 kB
(3 rows)
-- 查询所有表大小
-- select relname,pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables;
postgres=# select relname,pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables;
relname | pg_size_pretty
---------+----------------
(0 rows)
-- 查询所有表的总大小，包括其索引大小
-- select relname,pg_size_pretty(pg_total_relation_size(relid)) from pg_stat_user_tables;
postgres=# select relname,pg_size_pretty(pg_total_relation_size(relid)) from pg_stat_user_tables;
relname | pg_size_pretty
---------+----------------
(0 rows)

三、数据库中的表按照大小排序

-- data+index的大小，并且排序
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes
-- 查询结果
table_name
| table_size | indexes_size | total_size
--------------------------------------------------------------+------------+--------------+------------
"pg_catalog"."pg_depend"
| 488 kB
| 616 kB
| 1104 kB
"pg_catalog"."pg_proc"
| 688 kB
| 336 kB
| 1024 kB
"pg_catalog"."pg_rewrite"
| 656 kB
| 32 kB
| 688 kB
"pg_catalog"."pg_attribute"
| 456 kB
| 200 kB
| 656 kB
"pg_catalog"."pg_description"
| 328 kB
| 168 kB
| 496 kB
"pg_catalog"."pg_collation"
| 272 kB
| 112 kB
| 384 kB
"pg_catalog"."pg_statistic"
| 248 kB
| 16 kB
| 264 kB
"pg_catalog"."pg_class"
| 136 kB
| 120 kB
| 256 kB
"pg_catalog"."pg_operator"
| 144 kB
| 88 kB
| 232 kB
"pg_catalog"."pg_amop"
| 80 kB
| 112 kB
| 192 kB
"pg_catalog"."pg_type"
| 120 kB
| 72 kB
| 192 kB

-- 查出所有表（包含索引）并排序
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(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 limit 20
-- 查询结果
table_full_name
|
size
---------------------------------+---------
pg_catalog.pg_depend
| 1104 kB
pg_catalog.pg_proc
| 1024 kB
pg_catalog.pg_rewrite
| 688 kB
pg_catalog.pg_attribute
| 656 kB
pg_catalog.pg_description
| 496 kB
pg_catalog.pg_collation
| 384 kB
pg_catalog.pg_statistic
| 264 kB