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