Archive

Posts Tagged ‘PostgreSQL’

PostgreSQL查询表和index占用空间大小

November 12th, 2018 No comments

PostgreSQL查询表和index占用空间大小

PostgreSQL表和index占用空间大小信息存储在
information_schema.tables中
通过SQL可以查询到相应的统计数据

1
2
--查出单个表的大小
select pg_size_pretty(pg_relation_size('TABLENAME'));

查出表大小按大小含Index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查出表大小按大小含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,
SUBSTRING("table_name",1,10) as short_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
where all_tables.table_name like '%TABLENAME%'
ORDER BY total_size DESC
) AS pretty_sizes

Read more…

PostgreSql查询正在执行的SQL和查询执行耗时的SQL

November 9th, 2018 No comments

运行在AWS RDS上的PostgreSql今天TransactionID耗尽,原因是有一个SQL执行占用CPU超过12小时

–查询正在执行的SQL

1
select * from pg_stat_activity where datname='schema名称';

–结束正在进行的R查询

1
select pg_cancel_backend(pid);

–结束正在执行CUD操作

1
select  pg_terminate_backend(pid)

Read more…

Categories: 系统管理, 语言编程 Tags: , ,