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

字段	说明
datid	数据库的oid
datname	数据库名
numbackends	访问当前数据库的连接数量
xact_commit	该数据库事务提交总量：和下面的rollback和作为TPS统计
xact_rollback	该数据库事务rollback总量，如果特别多，需要看业务是否有问题了
blks_read	总磁盘物理读的块数，这里的read可能是从 cache中读取，如果很高需要结合blk_read_time看是否真的存在从磁盘读取的情况
blks_hit	从shared buffer命中块数
tup_returned	对于表来说，是全表扫描的行数；对于索引是通过索引返回的索引行数，如果这个值明显大于tup_fetched，说明当前数据库存在大量的全表扫描。可结合pg_stat_statments查找慢SQL，也可结合pg_stat_user_table找全表扫描次数和行数最多的表
tup_fetched	通过索引返回的行数
tup_inserted	插入的行数
tup_updated	更新的行数
tup_deleted	删除的行数
conflicts	与恢复冲突取消的查询次数，只会在备机上发生
temp_files	产生临时文件的数量，如果这个值很高，说明存在很多排序，hash,或者聚合这种操作，需要调大work_mem
temp_bytes	临时文件的大小
deadlocks	死锁的数量，如果这个值很大说明业务逻辑有问题
blk_read_time	数据库中花费在读取文件的时间，这个值很高说明内存较小，需要频繁从磁盘读入数据文件
blk_write_time	数据库中花费在写数据文件的时间，pg中脏页一般写入page cache，如果这个值较高，则说明cache较小，操作系统的cache需要更积极的写入
stats_reset	统计信息重置的时间

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

字段	说明
relid	表oid
schemaname	模式名
relname	表名
seq_scan	这个表进行全表扫描的次数
seq_tup_read	全表扫描的数据行数，如果这个值很大说明操作这个表的SQL语句很可能是全表扫描，需要结合执行计划分析
idx_scan	索引扫描的次数
idx_tup_fetch	通过索引扫描返回的行数
n_tup_ins	插入的数据行数
n_tup_upd	更新的数据行数
n_tup_del	删除的数据行数
n_tup_hot_upd	hot update的数据行数，这个值与n_tup_upd接近说明更新性能较好，不需要更新索引
n_live_tup	活的行数量
n_dead_tup	死记录个数
n_mod_since_analyze	上次analyze的实际
last_vacuum	上次手动vacuum的实际
last_autovacuum	上次autovacuum的实际
last_analyze	上次analyze时间
last_autoanalyze	上次自动analyze时间
vacuum_count	acuum次数
autovacuum_count	自动vacuum次数
analyze_count	analyze次数
autoanalyze_count	自动analyze次数

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

字段	说明
relid	相关表的oid
indexrelid	索引的oid
schemaname	模式名
relname	表名
indexrelname	索引名
idx_scan	通过索引扫描的次数，如果该值很小，说明该索引很少被用到，可以考虑删除
idx_tup_read	通过任意索引方法返回的索引行数
idx_tup_fetch	通过索引方法返回的数据行数

三、数据库运行状态

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);

字段	说明
datid	这个后端连接到的数据库的OID
datname	这个后端连接到的数据库的名称
pid	这个后端的进程 ID
usesysid	登录到这个后端的用户的 OID
usename	登录到这个后端的用户的名称
application_name	连接到这个后端的应用的名称
client_addr	连接到这个后端的客户端的 IP 地址。如果这个域为空，它表示客户端通过服务器机器上的一个 Unix 套接字连接或者这是一个内部进程（如自动清理）。
client_hostname	已连接的客户端的主机名，由client_addr的反向 DNS 查找报告。这个域将只对 IP 连接非空，并且只有log_hostname被启用时才会非空。
client_port	客户端用以和这个后端通信的 TCP 端口号，如果使用 Unix 套接字则为-1
backend_start	这个进程被启动的时间，即客户端是什么时候连接到服务器的
xact_start	这个进程的当前事务被启动的时间，如果没有活动事务则为空。如果当前查询是它的第一个事务，这一列等于query_start。
query_start	当前活动查询被开始的时间，如果state不是active，这个域为上一个查询被开始的时间
state_change	state上一次被改变的时间
wait_event_type	后端正在等待的事件类型，如果不存在则为 NULL。可能的值有： LWLockNamed：后端正在等待一个特定命名的轻量级锁。每一个这样的锁保护共享内存中的一个特定数据结构。wait_event将包含该轻量级锁的名称 LWLockTranche：后端正在等待一组相关轻量级锁中的一个。该组中的所有锁都执行一种相似的功能。wait_event将标识这个组中锁的大体目的。 Lock：后端正在等待一个重量级锁。重量级锁，也称为锁管理器锁或者简单锁，主要保护 SQL 可见的对象，例如表。不过，它们也被用于确保特定内部操作的互斥，例如关系扩展。wait_event将标识等待的锁的类型。 BufferPin：服务器进程正在等待访问一个数据缓冲区，而此时没有其他进程正在检查该缓冲区。如果另一个进程持有一个最终从要访问的缓冲区中读取数据的打开的游标，缓冲区 pin 等待可能会被拖延。
wait_event	如果后端当前正在等待，则是等待事件的名称，否则为 NULL。
state	这个后端的当前总体状态。可能的值是： active：后端正在执行一个查询。 idle：后端正在等待一个新的客户端命令。 idle in transaction：后端在一个事务中，但是当前没有正在执行一个查询。 idle in transaction (aborted)：这个状态与idle in transaction相似，不过在该事务中的一个语句导致了一个错误。 fastpath function call：后端正在执行一个 fast-path 函数。 disabled：如果在这个后端中track_activities被禁用，则报告这个状态。
backend_xid	这个后端的顶层事务标识符（如果存在）。
backend_xmin	当前后端的xmin范围。
query	这个后端最近查询的文本。如果state为active，这个域显示当前正在执行的查询。在所有其他状态下，它显示上一个被执行的查询。