【APM】监控PostgreSQL数据获取查询语句
APM 监控 PostgreSQL 所使用的查询语句如下：
1.索引检索详情：
select sum(c.relpages) as indexSpace from pg_class c, pg_index i where c.oid = i.indrelid
2.缓冲器统计和事务详情：
select sum(numbackends) as backends,sum(xact_commit) as commits,sum(xact_rollback) as rollbacks,sum(blks_hit) as bufHits,sum(blks_read) as bufReads from pg_stat_database
3.用户详情：
select count(*) as users from pg_user
4.锁定统计：
select granted as isHeld,count(locktype) as lockCount from pg_locks group by granted
5.查询统计：
select sum(pg_total_relation_size(relid))as diskSpace,sum(idx_scan)/(sum(idx_scan) + sum(seq_scan)) as idx_scan_ratio,sum(n_tup_ins) as rowsInserted,sum(n_tup_upd) as rowsUpdated,sum(n_tup_del) as rowsDeleted,sum(seq_scan) as seqscans,sum(idx_scan) as tindexscans,sum(seq_tup_read) as seqscansRowsRead,sum(idx_tup_fetch) as indscanRowReads from pg_stat_all_tables
6.索引扫描详情：
select sum(idx_scan) as indexScan,sum(idx_tup_read) as indexRead,sum(idx_tup_fetch) as indexFetches from pg_stat_all_indexes
7.数据库详情：
（1）select count(*) as totTables from pg_tables
（2）select count(*) as totTriggers from pg_trigger
（3）select count(distinct(proname)) as totProc from pg_proc
（4）select relname as tableName,pg_total_relation_size(relid)as tableSize from pg_stat_all_tables order by pg_total_relation_size(relid) desc limit 1
8.配置信息：
（1）show config_file
（2）show data_directory
（3）show external_pid_file
（4）show hba_file
（5）show ident_file
9.版本详情：
SHOW server_version
10.检查备机状态：
select pg_is_in_recovery()
11.检查复制状态：
select client_addr from pg_stat_replication
12.表空间详情：
（1）PGSQL版本<9.2：
select spcname, pg_get_userbyid(spcowner) as owner, spclocation, pg_tablespace_size (spcname)/ 1024 /1024 as size from pg_tablespace
（2）PGSQL版本=9.2：
select spcname, pg_get_userbyid(spcowner) as owner, pg_tablespace_location(oid) as spclocation, pg_tablespace_size (spcname)/ 1024 /1024 as size from pg_tablespace
（3）PGSQL版本>9.2：
select oid, spcname, pg_get_userbyid(spcowner) as owner, pg_tablespace_location(oid) as spclocation, pg_tablespace_size (spcname)/ 1024 /1024 as size from pg_tablespace
13.CPU排行前 10 的查询：
（1）PGSQL版本<=9.3：
select userid, dbid, pg_database.datname as databaseName, total_time as totalCPUTime, total_time/calls as avgCPUTime, query from pg_stat_statements inner join pg_database on pg_database.oid = pg_stat_statements.dbid order by avgCPUTime desc limit 10
（2）PGSQL版本=9.4：
select userid, dbid, queryid, pg_database.datname as databaseName, total_time as totalCPUTime, total_time/calls as avgCPUTime, regexp_replace(query, E'[\\n\\r\\f\\u000B\\u0085\\u2028\\u2029]+', ' ', 'g' ) as query from pg_stat_statements inner join pg_database on pg_database.oid = pg_stat_statements.dbid order by avgCPUTime desc limit 10
（3）PGSQL版本>9.4且<13.3
select userid, dbid, queryid, pg_database.datname as databaseName, total_time as totalCPUTime, max_time as maxCPUtime, mean_time as avgCPUTime, regexp_replace(query, E'[\\n\\r\\f\\u000B\\u0085\\u2028\\u2029]+', ' ', 'g' ) as query from pg_stat_statements inner join pg_database on pg_database.oid = pg_stat_statements.dbid order by avgCPUTime desc limit 10
（4）其他PGSQL版本：
select userid, dbid, queryid, pg_database.datname as databaseName, total_exec_time as totalCPUTime, max_exec_time as maxCPUtime, mean_exec_time as avgCPUTime, regexp_replace(query, E'[\\n\\r\\f\\u000B\\u0085\\u2028\\u2029]+', ' ', 'g' ) as query from pg_stat_statements inner join pg_database on pg_database.oid = pg_stat_statements.dbid order by avgCPUTime desc limit 10
14.复制详情：
（1）PGSQL 版本>=10.0：
SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}'
备机：
a）select pg_last_wal_replay_lsn()
b）SELECT CASE WHEN pg_last_wal_receive_lsn()=pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())/1000 END AS replication_lag
非备机：
select pg_current_wal_lsn()
（2）其他PGSQL版本：
SELECT COUNT(*) FROM pg_ls_dir('pg_xlog') WHERE pg_ls_dir ~ '^[0-9A-F]{24}'
备机：
a）select pg_last_xlog_replay_location()
b）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())/1000 END AS replication_lag
非备机：
select pg_current_xlog_location()
select checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, buffers_backend from pg_stat_bgwriter
（3）PGSQL版本>9.6：
a）select conninfo from pg_stat_wal_receiver
b）show wal_level
c）show wal_sync_method
15.从复制详情：
（1）PGSQL 版本>=9.2：
非备机：
SELECT pid, application_name, client_addr, client_port, state, sync_state, (pg_xlog_location_diff(pg_current_xlog_location(),sent_location) / 1024)::bigint as sending_lag, (pg_xlog_location_diff(sent_location,flush_location) / 1024)::bigint as receiving_lag, (pg_xlog_location_diff(flush_location,replay_location) / 1024)::bigint as replaying_lag, (pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/ 1024)::bigint as total_lag, (pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location) / 1024)::bigint as replication_byte_lag FROM pg_stat_replication
备机：
SELECT pid, application_name, client_addr, client_port, state, sync_state, (pg_xlog_location_diff(sent_location,flush_location) / 1024)::bigint as receiving_lag, (pg_xlog_location_diff(flush_location,replay_location) / 1024)::bigint as replaying_lag, (pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location) / 1024)::bigint as replication_byte_lag FROM
（2）其他 PGSQL 版本：
非备机：
SELECT pid, application_name, client_addr, client_port, state, sync_state, (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::bigint as sending_lag, (pg_wal_lsn_diff(sent_lsn,flush_lsn) / 1024)::bigint as receiving_lag, (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::bigint as replaying_lag, (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) / 1024 )::bigint as total_lag, (pg_wal_lsn_diff(pg_stat_replication.sent_lsn, pg_stat_replication.replay_lsn) / 1024)::bigint as replication_byte_lag FROM pg_stat_replication
备机：
SELECT pid, application_name, client_addr, client_port, state, sync_state, (pg_wal_lsn_diff(sent_lsn,flush_lsn) / 1024)::bigint as receiving_lag, (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::bigint as replaying_lag, (pg_wal_lsn_diff(pg_stat_replication.sent_lsn, pg_stat_replication.replay_lsn) / 1024)::bigint as replication_byte_lag FROM pg_stat_replication
16.复制插槽详情（PGSQL版本>=9.6）：
select slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn from pg_replication_slots
17.长时间运行的查询：
SELECT now() - pg_stat_activity.query_start as runtime,pid,datname, usename, query FROM pg_stat_activity WHERE now() - pg_stat_activity.query_start > '1 minutes'::interval and query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND state='active' ORDER BY runtime DESC
18.行数排名前 50 的表：
SELECT relname as tablename, reltuples as rows,pg_total_relation_size(cast(nspname ||'.'|| relname as varchar)) as tableSize FROM pg_database,pg_class JOIN pg_namespace ON relnamespace = pg_namespace.oid WHERE relkind = 'r' AND pg_namespace.nspname not in ('information_schema') AND pg_namespace.nspname not like 'pg_%' AND pg_database.datname='postgres' order by reltuples desc limit 50
19.数据库表详情：
select pg_database.dattablespace as tableSpace, pg_database.datname as DBName,pg_database_size(pg_database.datname)AS size, pg_stat_database.numbackends as connections from pg_database, pg_stat_database where pg_stat_database.datname = pg_database.datname
20.会话详情：
（1）PGSQL 版本<9.0
select procpid as pid, usename as username, client_addr as host, datname as db, waiting as blocked, EXTRACT(EPOCH FROM query_start-xact_start)::float*1000 as cpu_time, current_query as query from pg_stat_activity
（2）PGSQL 版本<9.2且>9.0
select procpid as pid, usename as username, client_addr as host, datname as db, application_name as program, waiting as blocked, EXTRACT(EPOCH FROM query_start-xact_start)::float*1000 as cpu_time, current_query as query from pg_stat_activity
（3）PGSQL 版本<9.6且>9.2
select pid, state as status, usename as username, client_addr as host, datname as db, application_name as program, waiting as blocked, EXTRACT(EPOCH FROM query_start-xact_start)::float*1000 as cpu_time, query from pg_stat_activity
（4）PGSQL 版本>9.6
select pid, state as status, usename as username, client_addr as host, datname as db, application_name as program, EXTRACT(EPOCH FROM query_start-xact_start)::float*1000 as cpu_time, regexp_replace(query, E'[\\n\\r\\f\\u000B\\u0085\\u2028\\u2029]+', ' ', 'g' ) as query, concat(wait_event_type, ' : ', wait_event) as blocked from pg_stat_activity
Related Articles
如何连接到ServiceDesk Plus的Pgsql和MSSQL数据库?
请参考以下说明连接到数据库： MSSQL数据库：请连接到SQL服务器的查询分析器并执行查询语句。 PGSQL数据库： 适用于9.4版本： (要了解使用的数据库配置信息，请查看[SDP-主目录]/conf文件夹下名为databaseinfo的文件) 适用于9.3及以下版本： (要了解使用的数据库的配置信息，请查看[SDP-主目录]/server/default/conf文件夹下名为databaseinfo的文件) ...【APM】中PGSQL数据库监视器中各指标查询
问题： PGSQL监视器中个别指标显示没有数据，可以先通过附件中的查询语句在目标数据库中执行，看是否有数据输出【APM】自定义数据库查询监视器常见问题
在创建数据库查询监视器的过程中，查询语句中需注意： 1.需要有主键：查询的标识符，并选择该标识符作为主键。 2.不需要在查询的末尾提供分号。 3.只有在提供多个查询的情况下，才需要换行充当分隔符。在单个查询中不需要换行符。 更多更详细的关于自定义数据库查询监视器信息可参考： https://www.manageengine.com/products/applications_manager/help/database-query-monitors.html【APM】人大金仓数据库监控
满足 PGSQL 监控前提条件，即可通过 APM 中添加 PGSQL 监控的步骤和所需信息，监控人大金仓数据库。 监控人大金仓数据库的前提条件和配置办法参考： https://www.manageengine.com/products/applications_manager/help/prerequisites-for-applications-manager.html#postgres 添加人大金仓数据库的步骤和监控项指南： ...将Azure PostgreSQL数据库配置为Applications Manager后端的步骤
Applications Manager与PostgreSQL捆绑使用。如果您想改用自己的PostgreSQL，请按照以下步骤操作： 先决条件： 应该可以从Applications Manager安装访问兼容的PostgreSQL数据库。要了解支持的版本和数据库用户权限，请单击此处。 PostgreSQL的用户具有登录、创建数据库并完全控制该数据库中表的权限。 步骤： 1. 安装具有捆绑的PGSQL后端的Applications ...