Postgresql 常用数据库管理SQL

一、数据库参数设置

1. 修改数据库时区

ALTER DATABASE mydb SET timezone TO 'Asia/Shanghai'

二、数据库统计分析

1. pg_stat_database

通过pg_stat_database可以大概了解数据库的历史情况。

select
 datname as 数据库名, 
 numbackends as 连接数,
 xact_commit as 事务提交总量,
 xact_rollback as 事务回滚总量,
 tup_returned as 全表扫描行数,
 tup_fetched as 索引返回行数,
 deadlocks as 死锁数量
from pg_stat_database
order by datname

2. pg_stat_user_tables

通过查询pg_stat_user_tables，可以基本清除哪些表的全表扫描次数较多，表中DML哪种操作多，也可以了解垃圾数据的数量。

select
 schemaname as 模式名, 
 relname  as 表名,
 seq_scan as 全表扫描次数,
 seq_tup_read as 全表返回行数,
 idx_scan as 索引扫描次数,
 idx_tup_fetch as 索引返回行数
from pg_stat_user_tables
order by schemaname, relname

3. pg_stat_user_indexes

可以知道当前哪些索引频繁使用，哪些是无效索引。无效索引可以删除掉，减少磁盘空间的使用和提升insert、delete、update的性能

select
 schemaname as 模式名, 
 relname  as 表名,
 indexrelname as 索引名,
 idx_scan as 索引扫描次数,
 idx_tup_read as 索引读取行数,
 idx_tup_fetch as 索引返回行数
from pg_stat_user_indexes
order by schemaname, relname, indexrelname

三、数据库运行状态

1. pg_stat_activity

查看各客户端的连接数

select
 client_addr as 客户端IP,
 count(*) as 连接数
from pg_stat_activity
group by client_addr

查看当前运行的SQL执行时长

select
 pid as 进程ID,
 CURRENT_TIMESTAMP - least(query_start,xact_start) AS 运行时长,
 query AS 执行语句
from pg_stat_activity
where not pid = pg_backend_pid()
      and state = 'active';

取消后台操作，回滚未提交事物

select pg_terminate_backend(进程ID);

这篇好文章是转载于：学新通技术网