Postgresql数据常用监控命令.txt

查看主机ip：
SELECT inet_server_addr(); 

查询当前xlog：
select pg_current_xlog_location();

查询表大小：
select oid,relname,reltoastrelid from pg_class where relname = 'task_instance';
select pg_size_pretty(pg_relation_size(84309)); 
select pg_size_pretty(pg_total_relation_size('task_instance'));
select repeat(md5(random()::text), 10000)

流复制延时：
select now()- pg_last_xact_replay_timestamp();
select pg_last_xlog_receive_location() ,pg_last_xlog_replay_location();
select pg_xlog_location_diff(pg_current_xlog_insert_location(),sent_location)  from pg_stat_replication;

SELECT CASE
         WHEN pg_last_xlog_receive_location() =
              pg_last_xlog_replay_location() THEN
          0
         ELSE
          EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
       END AS replication_lag;

top sql：
SELECT query,
       calls,
       total_time,
       rows,