postgres 生产问题定位sql

查询当前进程连接

select pid,query_start,query,client_addr from pg_stat_activity where pid in (
	select a.pid from pg_locks a 
        join pg_class b on a.relation=b.oid

)
-- and state ='active'
-- and datname like 'database%'
-- and query like '%project_subsys%'
order by query_start

postgresql死锁查询

 --查询是否锁表了
select oid from pg_class where relname='可能锁表了的表'
--pid 是进程
select pid from pg_locks where relation='上面查出的oid'
--如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid)

批量杀死查询进程

select pg_terminate_backend(t.pid) from (
select pid,query_start,query,client_addr from pg_stat_activity where pid in (
	select a.pid from pg_locks a 
        join pg_class b on a.relation=b.oid

)
and state in ('active','idle')
-- and datname like 'database%'
-- and query like '%project_subsys%'
)t where t.query_start<now() -interval '1 m';

查询空间碎片大小排序

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
;

查询单表空间碎片大小

--数据库中单个表的大小（不包含索引）
select pg_size_pretty(pg_relation_size('表名'));

查出表大小按大小排序并分离data与index

--查出表大小按大小排序并分离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

处理多余的空间碎片

#(堵塞）
vacuum full public.table；

#(非堵塞，重建索引 ） run_job_pkey 索引  databasename  数据库名字
pg_repack -i run_job_pkey --no-kill-backend -d databasename 

#(非堵塞，重建表） run_job 表  databasename  数据库名字
pg_repack -t run_job --no-kill-backend -d databasename 

连接数据库语句

 psql -h localhost 'dbname=postgres user=postgres password=123456'

导出postgres sql

pg_dump -h localhost -p 5432 -U postgres(用户) postgres(database) -a --column-inserts -t pg_proc(表名) -f pg_proc.sql