Table of contents:' as info; select '
|
|
Statistics generated on: '|| now();
select 'on database: '||current_database()||'' as info;
select 'by user: '||user as info;
select 'using: pg2html.sql v.1.0.23d' as info;
select '
Software by ' as info;
select 'Meo
' as info; select '
| Summary | |
| Item', ' | Value' as info; select ' |
| '||' Database :', '', ' | '||current_database() union select ' |
| '||' Version :', '', ' | '||substring(version() for position('on' in version())-1) union select ' |
| '||' DB Size (MB):', '', ' | '||trunc(sum(pg_database_size(datname))/(1024*1024)) from pg_database; select ' |
| '||' Created :', '', ' | '|| (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification from pg_database where datname=current_database(); select ' |
| '||' Started :', '', ' | '||pg_postmaster_start_time() union select ' |
| '||' Memory buffers (MB) :', '', ' | '||trunc(sum(setting::int*8)/1024) from pg_settings where name like '%buffers' union select ' |
| '||' Work area (MB) :', '', ' | '||trunc(sum(setting::int)/1024) from pg_settings where name like '%mem' union select ' |
| '||' Wal Archiving :', '', ' | '||setting from pg_settings where name like 'archive_mode'; select ' |
| '||' Databases :', '', ' | '||count(*) from pg_database where not datistemplate union select ' |
| '||' Defined Users :', '', ' | '||count(*) from pg_roles union select ' |
| '||' Defined Schemata :', '', ' | '||count(distinct relowner) from pg_class union select ' |
| '||' Defined Tables :', '', ' | '||count(*) from pg_class where relkind='r'; select ' |
| '||' Sessions :', '', ' | '||count(*) from pg_stat_activity union select ' |
| '||' Sessions (active) :', '', ' | '||count(*) from pg_stat_activity where state = 'active'; select ' |
| '||' Host IP :', '', ' | '||inet_server_addr() union select ' |
| '||' Port :', '', ' | '||inet_server_port() order by 2; select ' |
| Version |
| '||version()||' |
' as info; select '
| Version check | ||||
| Version', ' | Supported', ' | Last major release (N or N-1)', ' | Last minor release (N or N-1)', ' | Notes'; SELECT ' |
| '||substring(version() for position('on' in version())-1); SELECT ' | ', CASE WHEN trunc(cast(current_setting('server_version_num') as integer)/100) in (1000, 1100, 1200, 1300, 1400) THEN 'YES' ELSE 'NO' END; -- supported SELECT ' | ', CASE WHEN trunc(cast(current_setting('server_version_num')as integer)/100) in (1300, 1400) THEN 'YES' ELSE 'NO' END; -- last2 release SELECT ' | ', CASE WHEN cast(current_setting('server_version_num')as integer) in (90623,90624, 100020,100018,100019, 110015,110013,110014, 120010,120008,120009, 130006,130004,130005, 140000,140001,140002) THEN 'YES' ELSE 'NO' END; -- last2 update select ' | Latest Releases: 14.5, 13.8, 12.12, 11.17, 10.22';
select ' Latest Unsupported: 9.6.24, 9.5.25, 9.4.26, 9.3.25, 9.2.24, 9.1.24, 9.0.23, 8.4.21, 8.3.23, 8.2.23, 8.1.23, 8.0.26, 7.4.30; 6.5.3, 1.0.9'; select ' |
| Databases | |||
| Name', ' | OID', ' | Size', ' | UR Size' as info; select ' |
| '||datname,' | ', oid, ' | '||pg_database_size(datname), ' | '||pg_size_pretty(pg_database_size(datname)) from pg_database where not datistemplate; select ' |
| TOTAL (MB)',' | ', ' | '||trunc(sum(pg_database_size(datname))/(1024*1024)), ' | '||pg_size_pretty(sum(pg_database_size(datname))::int8) from pg_database; select ' |
| Tablespaces |
| Name' as info; select ' |
| '||spcname from pg_tablespace; select ' |
| Schema/Object Matrix | |||||||||||||
| Schema | Owner', ' | Table', ' | Index', ' | View', ' | Sequence', ' | Composite type', ' | Foreign table', ' | TOAST table', ' | Materialized view', ' | Partitioned table', ' | Unlogged', ' | Temporary', ' | TOTAL' as info; select ' |
| '||nspname, ' | '||rolname, ' | '||sum(case when relkind='r' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='i' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='v' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='S' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='c' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='f' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='t' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='m' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='p' THEN 1 ELSE 0 end), ' | '||sum(case when relpersistence='u' THEN 1 ELSE 0 end), ' | '||sum(case when relpersistence='t' THEN 1 ELSE 0 end), ' | '||count(*) from pg_class, pg_roles, pg_namespace where relowner=pg_roles.oid and relnamespace=pg_namespace.oid group by rolname, nspname order by nspname, rolname; select ' |
| TOTAL | TOTAL', ' | '||sum(case when relkind='r' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='i' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='v' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='S' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='c' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='f' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='t' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='m' THEN 1 ELSE 0 end), ' | '||sum(case when relkind='p' THEN 1 ELSE 0 end), ' | '||sum(case when relpersistence='u' THEN 1 ELSE 0 end), ' | '||sum(case when relpersistence='t' THEN 1 ELSE 0 end), ' | '||count(*) from pg_class; select ' |
' as info; select '
| Owner/Function Matrix | ||||||
| Owner', ' | Internal', ' | C', ' | SQL', ' | plpgSQL', ' | TOTAL', ' | Source size' as info; select ' |
| '||rolname, ' | '||sum(case when lanname='internal' THEN 1 ELSE 0 end), ' | '||sum(case when lanname='c' THEN 1 ELSE 0 end), ' | '||sum(case when lanname='sql' THEN 1 ELSE 0 end), ' | '||sum(case when lanname='plpgsql' THEN 1 ELSE 0 end), ' | '||count(*), ' | '||sum(char_length(prosrc)) from pg_proc, pg_roles, pg_language where proowner=pg_roles.oid and prolang=pg_language.oid group by rolname order by rolname; select ' |
| TOTAL', ' | '||sum(case when lanname='internal' THEN 1 ELSE 0 end), ' | '||sum(case when lanname='c' THEN 1 ELSE 0 end), ' | '||sum(case when lanname='sql' THEN 1 ELSE 0 end), ' | '||sum(case when lanname='plpgsql' THEN 1 ELSE 0 end), ' | '||count(*), ' | '||sum(char_length(prosrc)) from pg_proc, pg_language where prolang=pg_language.oid; select ' |
| Space Usage | ||||||
| Owner', ' | Table#', ' | Tables rows', ' | Tables KBytes', ' | Indexes KBytes', ' | TOAST KBytes', ' | Total KBytes' as info; select ' |
| '||rolname, ' | '||to_char(sum(case when relkind='r' THEN 1 ELSE 0 end),'999G999G999G999G999'), ' | '||to_char(sum(case when relkind='r' THEN reltuples ELSE 0 end),'999G999G999G999G999'), ' | '||to_char(trunc(sum(case when relkind='r' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999'), ' | '||to_char(trunc(sum(case when relkind='i' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999'), ' | '||to_char(trunc(sum(case when relkind='t' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999'), ' | '||to_char(trunc(sum(cast(1 as bigint)* relpages *8)),'999G999G999G999G999') from pg_class, pg_roles where relowner=pg_roles.oid group by rolname order by rolname; select ' |
| TOTAL', ' | '||to_char(sum(case when relkind='r' THEN 1 ELSE 0 end),'999G999G999G999G999'), ' | '||to_char(sum(case when relkind='r' THEN reltuples ELSE 0 end),'999G999G999G999G999'), ' | '||to_char(trunc(sum(case when relkind='r' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999'), ' | '||to_char(trunc(sum(case when relkind='i' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999'), ' | '||to_char(trunc(sum(case when relkind='t' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999'), ' | '||to_char(trunc(sum(cast(1 as bigint)* relpages *8)),'999G999G999G999G999') from pg_class, pg_roles where relowner=pg_roles.oid; select ' |
' as info; select '
| Internals | |||||||
| Tables#', ' | Rows', ' | Relpages*8', ' | Total Size', ' | Main Fork', ' | Free Space Map', ' | Visibility Map', ' | Initialization Fork' as info; select ' |
| '||to_char(count(*),'999G999G999G999G999') obj, ' | '||to_char(sum(reltuples),'999G999G999G999G999') rowcount, ' | '||to_char(trunc(sum(relpages *8)),'999G999G999G999G999') relpages, ' | '||to_char(trunc(sum(pg_total_relation_size(oid))/1024),'999G999G999G999G999') total, ' | '||to_char(trunc(sum(pg_relation_size(oid, 'main'))/1024),'999G999G999G999G999') main, ' | '||to_char(trunc(sum(pg_relation_size(oid, 'fsm'))/1024),'999G999G999G999G999') fsm, ' | '||to_char(trunc(sum(pg_relation_size(oid, 'vm'))/1024),'999G999G999G999G999') vm, ' | '||to_char(trunc(sum(pg_relation_size(oid, 'init'))/1024),'999G999G999G999G999') init from pg_class where relkind='r'; select ' |
' as info; select '
| Vacuum and Analyze | ||||
| # Tables', ' | Last autoVACUUM', ' | Last VACUUM', ' | Last autoANALYZE', ' | Last ANALYZE' as info; select ' |
| '||count(*), ' | '||max(last_autovacuum), ' | '||max(last_vacuum), ' | '||max(last_autoanalyze), ' | '||max(last_analyze) from pg_stat_user_tables; select ' |
' as info; select '
| High dead tuples | ||||||
| Table', ' | Tuples', ' | Dead tuples', ' | Last autoVACUUM', ' | Last VACUUM', ' | Last autoANALYZE', ' | Last ANALYZE' as info; select ' |
| '||schemaname||'.'||relname, ' | '||n_live_tup, ' | '||n_dead_tup, ' | '||last_autovacuum, ' | '||last_vacuum, ' | '||last_autoanalyze, ' | '||last_analyze from pg_stat_all_tables where n_dead_tup>1000 and n_dead_tup>n_live_tup*0.05 order by n_dead_tup desc limit 20; select ' |
' as info; select '
| Database max age | |
| Database', ' | Max XID age' as info; SELECT ' |
| '||datname||' | ', age(datfrozenxid) FROM pg_database ORDER BY 2 DESC LIMIT 20; select ' |
' as info; select '
| Relations too aged | ||
| Schema', | Relation, ' | XID age' as info; SELECT ' |
| '|| nspname ||' | '|| relname ||' | ', age(relfrozenxid) FROM pg_class JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid WHERE relkind = 'r' AND age(relfrozenxid)> 2^30 ORDER by 2 DESC; select ' |
| Users/Roles | |||||
| Role', ' | Login', ' | Inherit', ' | Superuser', ' | Expiry time', ' | Config' as info; select ' |
| '||rolname, ' | '||rolcanlogin, ' | '||rolinherit, ' | '||rolsuper, ' | ',rolvaliduntil, ' | '||rolconfig::text from pg_roles order by rolname; select ' |
| TOTAL', ' | '||count(*) from pg_roles; select ' |
' as info; select '
| Users with poor password | ||
| Username',' | Password', ' | Note' ; select ' |
| ',usename, ' | ', passwd, ' | Weak password' from pg_shadow where substr(passwd,4) in ( md5('postgres'||usename), md5('mypass'|| usename), md5('admin'|| usename), md5('secret'|| usename), md5('root'|| usename), md5('password'|| usename), md5('public'|| usename), md5('private'|| usename), md5('1234'|| usename), md5('secure'|| usename), md5('pass'|| usename), md5('qwerty'|| usename), md5('pippo'|| usename), md5('manager'|| usename), md5('changeme'|| usename), md5('pwd'|| usename), md5('changeme'|| usename), md5('xxx'|| usename), md5('toor'|| usename), md5('supervisor'|| usename)) order by usename; select ' |
| ',usename, ' | ', passwd, ' | Same as user' from pg_shadow where substr(passwd,4) = md5(usename||usename) order by usename; select ' |
| ',usename, ' | ', passwd, ' | Empty password' from pg_shadow where passwd is null order by usename; select ' |
| ',usename, ' | ', passwd, ' | Unencrypted password' from pg_shadow where passwd not like 'md5%' and passwd not like 'SCRAM%' order by usename; select ' |
'; select '
| HBA Rules | |||||||
| Type',' | Database', ' | User', ' | Address', ' | Netmask', ' | Auth', ' | Options', ' | Error'; select ' |
| ',type, ' | ',database, ' | ',user_name, ' | ',address, ' | ',netmask, ' | ',auth_method, ' | ',options, ' | ',error from pg_hba_file_rules order by line_number; select ' |
'; select '
| PGAudit logged Objects | ||
| Schema', ' | Table', ' | Privilege'; SELECT ' |
| ',table_schema, ' | ',table_name, ' | ',privilege_type FROM information_schema.role_table_grants WHERE grantee in ('rds_pgaudit', 'auditor', 'pgaudit') ORDER BY table_schema, table_name; select ' |
|
|
| Sessions | |||||||
| Pid', ' | Database', ' | User', ' | Address', ' | State', ' | Query start', ' | Backend', ' | SQL' ; select ' |
| ',pid, ' | ',datname, ' | ',usename, ' | ',client_addr, ' | ',state, ' | ',query_start, ' | ',backend_type, ' | ',query from pg_stat_activity where pid<>pg_backend_pid() order by state, pid; select ' |
| Locks | |||||
| Pid', ' | Type', ' | Database', ' | Relation', ' | Mode', ' | Granted' as info; select ' |
| ',pid, ' | ',locktype, ' | ', datname, ' | ', relname, ' | ',mode, ' | ',granted from pg_locks l left join pg_catalog.pg_database d on d.oid = l.database left join pg_catalog.pg_class r on r.oid = l.relation order by granted, pid limit 50; select ' |
| ...'; select ' |
' as info; select '
| Blocking Locks | |||||
| Blocked Pid', ' | Blocked User', ' | Blocking Pid', ' | Blocking User', ' | Blocked Statement', ' | Blocking Statement' as info; SELECT ' |
| ',blocked_locks.pid AS blocked_pid, ' | ',blocked_activity.usename AS blocked_user, ' | ',blocking_locks.pid AS blocking_pid, ' | ',blocking_activity.usename AS blocking_user, ' | ',blocked_activity.query AS blocked_statement, ' | ',blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED; select ' |
| Memory | ||
| Element', ' | KB', ' | Description' as info; select ' |
| '||name, ' | '||setting::int*8, ' | '||short_desc from pg_settings where name like '%buffers'; select ' |
| '||name, ' | '||setting::int, ' | '||short_desc from pg_settings where name like '%mem'; select ' |
' as info; select '
| Buffer Cache Contents | ||||||
| Class',' | Kind', ' | Buffers', ' | Buffered', ' | Buffers%', ' | Relation%', ' | Avg. usage' as info; SELECT ' |
| ', c.relname, ' | ', c.relkind, ' | ', count(*), ' | ', pg_size_pretty(count(*) * 8192), ' | ', round(100.0 * count(*)/(SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1), ' | ', round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1), ' | ', round(avg(usagecount),2) FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) WHERE pg_relation_size(c.oid) > 0 GROUP BY c.oid, c.relname, c.relkind ORDER BY 6 DESC LIMIT 20; select ' |
' as info;
SELECT 'Buffer Cache size: ', pg_size_pretty(setting::bigint*8192::bigint)
FROM pg_settings
WHERE name='shared_buffers';
SELECT '
Estimated Minimal Buffer Cache size: ', pg_size_pretty(count(*) * 8192)
FROM pg_buffercache
WHERE usagecount >= 3;
select '
' as info; select '
| Cluster Database Statistics' as info; select ' | ||||||||||||
| Database', ' | Backends', ' | Commit', ' | TPS', ' | Rollback', ' | Read', ' | Hit', ' | Hit Ratio%', ' | Return', ' | Fetch', ' | Insert', ' | Update', ' | Delete' as info; select ' |
| '||datname, ' | '||numbackends, ' | '||xact_commit, ' | '||round(xact_commit/EXTRACT( EPOCH FROM (now()-stats_reset))::decimal,2), ' | '||xact_rollback, ' | '||blks_read, ' | '||blks_hit, ' | '||round((blks_hit)*100.0/nullif(blks_read+blks_hit, 0),2) hit_ratio, ' | '||tup_returned, ' | '||tup_fetched, ' | '||tup_inserted, ' | '||tup_updated, ' | '||tup_deleted from pg_stat_database where datname not like 'template%'; select ' |
' as info; select '
| BG Writer statistics' as info; select ' | |||||||
| checkpoints_timed', ' | checkpoints_req ', ' | buffers_checkpoint ', ' | buffers_clean ', ' | maxwritten_clean ', ' | buffers_backend ', ' | buffers_alloc ', ' | Minutes between checkpoints '; select ' |
| '||checkpoints_timed, ' | '|| checkpoints_req, ' | '|| buffers_checkpoint, ' | '|| buffers_clean, ' | '|| maxwritten_clean, ' | '|| buffers_backend, ' | '|| buffers_alloc from pg_stat_bgwriter; select ' | '|| seconds_since_start / total_checkpoints / 60 AS mbc from (SELECT EXTRACT(EPOCH FROM (now() - pg_postmaster_start_time())) AS seconds_since_start, (checkpoints_timed+checkpoints_req) AS total_checkpoints FROM pg_stat_bgwriter ) AS sub; select ' |
' as info; select '
| Cache statistics' as info; select ' | |||
| Object Type | #Read', ' | #Hit ', ' | Hit Ratio% '; SELECT ' |
| Table', ' | '||sum(heap_blks_read) as heap_read, ' | '||sum(heap_blks_hit) as heap_hit, ' | '||trunc(100*sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)),2) as ratio FROM pg_statio_user_tables; SELECT ' |
| Index', ' | '||sum(idx_blks_read) as idx_read, ' | '||sum(idx_blks_hit) as idx_hit, ' | '||trunc(100*(sum(idx_blks_hit) - sum(idx_blks_read)) / nullif(sum(idx_blks_hit),0),2) as ratio FROM pg_statio_user_indexes; select ' |
' as info; SELECT cast(current_setting('server_version_num')as integer)>= 130000 as version_13p \gset var_ select '
' as info; select '
| Statement statistics' as info; select ' | |||||||
| Query', ' | User', ' | Calls', ' | Average (sec.)', ' | Total Time', ' | I/O Time', ' | Rows', ' | Hit Ratio%' as info; \if :var_version_13p SELECT ' |
| '||replace(query,',',', '), ' | '||pg_get_userbyid(userid), ' | '||calls, ' | '||round((total_exec_time::numeric / calls::numeric)/1000,3), ' | '||round(total_exec_time), ' | '||round(blk_read_time+blk_write_time), ' | '||rows, ' | '||round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)),2) AS hit_percent FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; \else SELECT ' |
| '||replace(query,',',', '), ' | '||pg_get_userbyid(userid), ' | '||calls, ' | '||round((total_time::numeric / calls::numeric)/1000,3), ' | '||round(total_time), ' | '||round(blk_read_time+blk_write_time), ' | '||rows, ' | '||round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)),2) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20; \endif select ' |
' as info; select '
' as info; select '
| Slowest Statements' as info; select ' | |||||||
| Query', ' | User', ' | Calls', ' | Average (sec.)', ' | Total Time', ' | I/O Time', ' | Rows', ' | Hit Ratio%' as info; \if :var_version_13p SELECT ' |
| '||replace(query,',',', '), ' | '||pg_get_userbyid(userid), ' | '||calls, ' | '||round((total_exec_time::numeric / calls::numeric)/1000,3), ' | '||round(total_exec_time), ' | '||round(blk_read_time+blk_write_time), ' | '||rows, ' | '||round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)),2) AS hit_percent FROM pg_stat_statements ORDER BY (total_exec_time::numeric/calls::numeric) DESC LIMIT 10; \else SELECT ' |
| '||replace(query,',',', '), ' | '||pg_get_userbyid(userid), ' | '||calls, ' | '||round((total_time::numeric / calls::numeric)/1000,3), ' | '||round(total_time), ' | '||round(blk_read_time+blk_write_time), ' | '||rows, ' | '||round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)),2) AS hit_percent FROM pg_stat_statements ORDER BY (total_time::numeric/calls::numeric) DESC LIMIT 5; \endif select ' |
' as info; select '
| Database', ' | DBcpu', ' | IOcpu' as info; \if :var_version_13p select ' |
| ', datname, ' | ', round(sum( (total_exec_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) DBcpu, ' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) IOcpu from pg_stat_statements, pg_database where pg_stat_statements.dbid=pg_database.oid group by datname; select ' |
| TOTAL | ', round(sum( (total_exec_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) DBcpu, ' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) IOcpu from pg_stat_statements; \else select ' |
| ', datname, ' | ', round(sum( (total_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) DBcpu, ' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) IOcpu from pg_stat_statements, pg_database where pg_stat_statements.dbid=pg_database.oid group by datname; select ' |
| TOTAL | ', round(sum( (total_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) DBcpu, ' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) IOcpu from pg_stat_statements; \endif select ' |
' as info; select '
| Most accessed tables' as info; select ' | ||||
| Schema | Table', ' | Heap Reads', ' | Table Hit Ratio%', ' | Index Hit Ratio%'; select ' |
| '||schemaname, ' | '||relname, ' | '||heap_blks_read, ' | '||heap_blks_hit*100/nullif(heap_blks_read+heap_blks_hit,0) as tb_hit_ratio, ' | '||idx_blks_hit*100/nullif(idx_blks_read+idx_blks_hit,0) as idx_hit_ratio from pg_statio_all_tables where heap_blks_read>0 order by heap_blks_read desc limit 20; select ' |
' as info; select '
| Index Usage - Details |
| Per-table index usage' as info; select ' | |||
| Relation | Index Usage%', ' | #Rows ', ' | #Scan '; SELECT ' |
| ', relname, ' | ', 100 * idx_scan / (seq_scan + idx_scan) index_used_pct, ' | ', n_live_tup, ' | ', seq_scan FROM pg_stat_user_tables WHERE seq_scan + idx_scan > 1 AND 100 * idx_scan / (seq_scan + idx_scan) < 95 ORDER BY n_live_tup DESC LIMIT 32; select ' |
' as info; select '
| Missing indexes' as info; select ' | ||||||
| Schema | Relation', ' | Contraint | Issue', ' | #Table Scan | Parent', ' | Columns'; WITH fk_actions ( code, action ) AS ( VALUES ( 'a', 'error' ), ( 'r', 'restrict' ), ( 'c', 'cascade' ), ( 'n', 'set null' ), ( 'd', 'set default' ) ), fk_list AS ( SELECT pg_constraint.oid as fkoid, conrelid, confrelid as parentid, conname, relname, nspname, fk_actions_update.action as update_action, fk_actions_delete.action as delete_action, conkey as key_cols FROM pg_constraint JOIN pg_class ON conrelid = pg_class.oid JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code WHERE contype = 'f' ), fk_attributes AS ( SELECT fkoid, conrelid, attname, attnum FROM fk_list JOIN pg_attribute ON conrelid = attrelid AND attnum = ANY( key_cols ) ORDER BY fkoid, attnum ), fk_cols_list AS ( SELECT fkoid, array_agg(attname) as cols_list FROM fk_attributes GROUP BY fkoid ), index_list AS ( SELECT indexrelid as indexid, pg_class.relname as indexname, indrelid, indkey, indpred is not null as has_predicate, pg_get_indexdef(indexrelid) as indexdef FROM pg_index JOIN pg_class ON indexrelid = pg_class.oid WHERE indisvalid ), fk_index_match AS ( SELECT fk_list.*, indexid, indexname, indkey::int[] as indexatts, has_predicate, indexdef, array_length(key_cols, 1) as fk_colcount, array_length(indkey,1) as index_colcount, round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb, cols_list FROM fk_list JOIN fk_cols_list USING (fkoid) LEFT OUTER JOIN index_list ON conrelid = indrelid AND (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols ), fk_perfect_match AS ( SELECT fkoid FROM fk_index_match WHERE (index_colcount - 1) <= fk_colcount AND NOT has_predicate AND indexdef LIKE '%USING btree%' ), fk_index_check AS ( SELECT 'no index' as issue, *, 1 as issue_sort FROM fk_index_match WHERE indexid IS NULL UNION ALL SELECT 'questionable index' as issue, *, 2 FROM fk_index_match WHERE indexid IS NOT NULL AND fkoid NOT IN ( SELECT fkoid FROM fk_perfect_match) ), parent_table_stats AS ( SELECT fkoid, tabstats.relname as parent_name, (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes, round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb FROM pg_stat_user_tables AS tabstats JOIN fk_list ON relid = parentid ), fk_table_stats AS ( SELECT fkoid, (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes, seq_scan as table_scans FROM pg_stat_user_tables AS tabstats JOIN fk_list ON relid = conrelid ) SELECT ' |
| ', nspname as schema_name, ' | ', relname as table_name, ' | ', conname as fk_name, ' | ', issue, ' | ', table_scans, ' | ', parent_name, ' | ', cols_list FROM fk_index_check JOIN parent_table_stats USING (fkoid) JOIN fk_table_stats USING (fkoid) WHERE table_mb > 5 AND ( writes > 1000 OR parent_writes > 1000 OR parent_mb > 10 ) ORDER BY table_scans DESC, table_mb DESC, table_name, fk_name LIMIT 64; select ' |
| Unused indexes' as info; select ' | |||
| Schema | Table', ' | Index', ' | Size'; SELECT ' |
| ',s.schemaname, ' | ',s.relname, ' | ',s.indexrelname, ' | ',pg_relation_size(s.indexrelid) FROM pg_catalog.pg_stat_user_indexes s JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid WHERE s.idx_scan = 0 AND 0 <>ALL (i.indkey) AND NOT i.indisunique AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conindid = s.indexrelid) ORDER BY pg_relation_size(s.indexrelid) DESC LIMIT 64; select ' |
' as info; select '
| All indexes (up to 1000)' as info; select ' | |||
| Schema | Relation', ' | Index | DDL'; SELECT ' |
| ',schemaname, ' | ',tablename, ' | ',indexname, ' | ',indexdef FROM pg_indexes WHERE schemaname not in ('pg_catalog') AND tablename not like 'pgstatspack%' ORDER BY schemaname, tablename, indexname LIMIT 1000; select ' |
| PostgreSQL Tuning Parameters | |||||
| Parameter', ' | Value', ' | Min', ' | Max', ' | Unit', ' | Description' as info; select ' |
| ',name,' | ',replace(replace(setting,'<','<'),'>','>'), ' | ',min_val,' | ',max_val, ' | ',unit, ' | ',short_desc from pg_settings where name in ('max_connections','shared_buffers','effective_cache_size','work_mem', 'wal_buffers', 'checkpoint_completion_target', 'checkpoint_segments', 'synchronous_commit', 'wal_writer_delay', 'max_fsm_pages','fsync','commit_delay','commit_siblings','random_page_cost', 'checkpoint_timeout', 'max_wal_size') order by name; select ' |
| Biggest Objects | |||||
| Object', ' | Type', ' | Owner', ' | Schema', ' | Bytes', ' | Rows' as info; select ' |
| '||relname, ' | '||case WHEN relkind='r' THEN 'Table' WHEN relkind='i' THEN 'Index' WHEN relkind='t' THEN 'TOAST Table' ELSE relkind||'' end, ' | '||rolname, ' | '||n.nspname, ' | '||to_char(relpages::INT8*8*1024,'999G999G999G999G999'), ' | '||to_char(reltuples,'999G999G999G999G999') from pg_class, pg_roles, pg_catalog.pg_namespace n where relowner=pg_roles.oid and n.oid=pg_class.relnamespace order by relpages desc, reltuples desc limit 32; select ' |
| Procedural Languages |
| Available languages' as info; select ' |
| '||lanname from pg_language; select ' |
| PL Objects | ||
| Owner', ' | Language', ' | Count' as info; select ' |
| '||o.rolname, ' | '||l.lanname, ' | '||count(*) from pg_proc f, pg_roles o, pg_language l where f.proowner=o.oid and f.prolang=l.oid group by o.rolname, l.lanname order by o.rolname, l.lanname; select ' |
' as info; -- regexp_split_to_table(prosrc, E'\n') select '
| Data Types - Details |
| Data Types | ||
| Owner', ' | Data type', ' | Count' as info; select ' |
| '||o.rolname, ' | '||t.typname, ' | '||count(*) from pg_attribute a, pg_class r, pg_roles o, pg_type t where a.attrelid=r.oid and a.atttypid=t.oid and r.relowner=o.oid group by o.rolname, t.typname order by o.rolname, t.typname; select ' |
| Physical Backup | |
| Parameter', ' | Value' as info; select ' |
| ',name,' | ',setting from pg_settings where name in ('archive_mode', 'archive_timeout') order by name; select ' |
| Write xlog location', ' | '||pg_current_wal_lsn(); select ' |
| Insert xlog location', ' | '||pg_current_wal_insert_lsn(); select ' |
' as info; select '
| Archiver Statistics | ||||||||
| Archived Count ', ' | Last Archived WAL ', ' | Last Archived Time ', ' | Failed Count ', ' | Last Failed WAL ', ' | Last Failed Time ', ' | Statistics Reset ', ' | Archiving ', ' | WALS ps ' as info; select ' |
| ',archived_count, ' | ',last_archived_wal, ' | ',last_archived_time, ' | ',failed_count, ' | ',last_failed_wal, ' | ',last_failed_time, ' | ',stats_reset, ' | ', current_setting('archive_mode')::BOOLEAN AND (last_failed_wal IS NULL OR last_failed_wal <= last_archived_wal), ' | ', CAST (archived_count AS NUMERIC) / EXTRACT (EPOCH FROM age(now(), stats_reset)) from pg_stat_archiver; select ' |
| Replication | |
| Parameter', ' | Value' as info; select ' |
| In Recovery Mode', ' | '||pg_is_in_recovery()||''; select ' |
| ',name,' | ',replace(replace(setting,'<','<'),'>','>') from pg_settings where name in ('wal_level', 'archive_command', 'hot_standby', 'max_wal_senders', 'checkpoint_segments', 'max_wal_size', 'archive_mode', 'max_standby_archive_delay', 'max_standby_streaming_delay', 'hot_standby_feedback', 'wal_keep_segments', 'synchronous_standby_names', 'recovery_target_timeline', 'wal_receiver_create_temp_slot', 'max_slot_wal_keep_size', 'ignore_invalid_pages') order by name; select ' |
' as info; select '
| Master Statistics | ||||||||
| Client', ' | State', ' | Sync', ' | Current Snapshot', ' | Sent loc.', ' | Write loc.', ' | Flush loc.', ' | Replay loc.', ' | Backend Start'; select ' |
| ',client_addr, ' | ', state, ' | ', sync_state, ' | ', txid_current_snapshot(), ' | ', sent_lsn, ' | ', write_lsn, ' | ', flush_lsn, ' | ', replay_lsn, ' | ', backend_start from pg_stat_replication; select ' |
| Slave Statistics | ||||
| Last Replication',' | Replication Delay',' | Current Snapshot', ' | Receive loc.',' | Replay loc.'; select ' |
| ', now() - pg_last_xact_replay_timestamp(), ' | ', CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END, ' | ', case when pg_is_in_recovery() then txid_current_snapshot() else null end, ' | ', pg_last_wal_receive_lsn(), ' | ', pg_last_wal_replay_lsn(); select ' |
' as info; select '
| Logical Replication - Subscriptions | |||||||
| Subscription Name',' | Pid',' | Relation OID', ' | Received',' | Last Message Send',' | Last Message Receipt', ' | Latest location',' | Latest time'; select ' |
| ',subname, ' | ',pid, ' | ',relid, ' | ',received_lsn, ' | ',last_msg_send_time, ' | ',last_msg_receipt_time, ' | ',latest_end_lsn, ' | ',latest_end_time from pg_stat_subscription order by subname; select ' |
' as info; select '
| Logical Replication - Details |
| Publications | |||||
| Publication Name',' | Owner', ' | All tables', ' | Insert', ' | Update', ' | Delete'; select ' |
| ',pubname, ' | ',rolname, ' | ',puballtables, ' | ', pubinsert, ' | ', pubupdate, ' | ', pubdelete from pg_publication p, pg_roles a where a.oid=p.pubowner order by pubname; select ' |
| Publication Name',' | Schema',' | Table'; select ' | |||
| ',pubname, ' | ',schemaname, ' | ',tablename from pg_publication_tables order by pubname, tablename; select ' |
'; select '
| Subscriptions | ||||||
| Subscription Name',' | Database',' | Owner', ' | Enabled', ' | Sync. Commit', ' | Slot ', ' | Connection '; select ' |
| ',subname, ' | ',datname, ' | ',rolname, ' | ',subenabled, ' | ', subsynccommit, ' | ', subslotname, ' | ', subconninfo from pg_subscription s, pg_database d, pg_roles a where d.oid=s.subdbid and a.oid=s.subowner order by subname; select ' |
| Subscription Name',' | Schema',' | Table', ' | State', ' | LSN'; select ' | ||
| ',subname, ' | ', ' | ',relname, ' | ',srsubstate, ' | ', srsublsn from pg_subscription_rel r, pg_subscription s, pg_class c where s.oid=r.srsubid and c.oid=r.srrelid order by subname, relname; select ' |
'; select '
| Extensions | |||
| Name', ' | Default Version', ' | Installed Version', ' | Description' as info; select ' |
| ',name,' | ',default_version,' | ',installed_version,' | ',comment from pg_available_extensions order by case when installed_version is null then 1 else 0 end, name; select ' |
| postgis | PostGIS installed (pre-extensions check)' pg from pg_proc where proname='postgis_version'; select ' |
| NLS Settings | ||
| Parameter', ' | Value', ' | Description' as info; select ' |
| ',name,' | ',setting, ' | ',short_desc from pg_settings where name like 'lc%' order by name; select ' |
| PostgreSQL Parameters | |||||||
| Parameter', ' | Value', ' | Min', ' | Max', ' | Unit', ' | Description', ' | Category', ' | Context' as info; select ' |
| ',name,' | ',replace(replace(setting,'<','<'),'>','>'), ' | ',min_val,' | ',max_val, ' | ',unit, ' | ',short_desc, ' | ',category, ' | ',context from pg_settings order by name; select ' |
| HBA file |
' as info;
select pg_read_file('pg_hba.conf',1,10240);
select ' |
| WAL files |
' as info; select pg_ls_waldir(); select ' |
Optional informations
' as info; SELECT EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_stat_statements' and installed_version is not null) as pg_stat_statements, EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_buffercache' and installed_version is not null) as pg_buffercache, EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pgstattuple' and installed_version is not null) as pgstattuple, EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_freespacemap' and installed_version is not null) as pg_freespacemap, EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='sslinfo' and installed_version is not null) as sslinfo, EXISTS (SELECT 1 FROM pg_stat_ssl WHERE ssl limit 1) as ssl_active, EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pgaudit' and installed_version is not null) as pgaudit, EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pgrowlocks' and installed_version is not null) as pgrowlocks \gset opt_ \if :opt_pgstattuple select '
| Bloat detailed informations for biggest tables (time expensive, uncomment if needed) |
' as info; \pset tuples_only \a \if 1 select relname Relation, (pgstattuple(pg_class.oid)).* from pg_class, pg_roles, pg_catalog.pg_namespace n where relowner=pg_roles.oid and n.oid=pg_class.relnamespace and relkind = 'r' order by relpages desc, reltuples desc limit 20; \endif \pset tuples_only \a select ' |
| Buffer Cache Contents | ||||||
| Class',' | Kind', ' | Buffers', ' | Buffered', ' | Buffers%', ' | Relation%', ' | Avg. usage' as info; SELECT ' |
| ', c.relname, ' | ', c.relkind, ' | ', count(*), ' | ', pg_size_pretty(count(*) * 8192), ' | ', round(100.0 * count(*)/(SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1), ' | ', round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1), ' | ', round(avg(usagecount),2) FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) WHERE pg_relation_size(c.oid) > 0 GROUP BY c.oid, c.relname, c.relkind ORDER BY 6 DESC LIMIT 20; select ' |
' as info; \endif \if :opt_sslinfo select '
| SSL Informations on current connection | |||
| SSL Usage',' | SSL Version', ' | SSL Cipher', ' | Client Certificate' as info; SELECT ' |
| ', ssl_is_used(), ' | ', ssl_version(), ' | ', ssl_cipher(), ' | ', ssl_client_cert_present(); select ' |
| SSL Informations on all connections |
' as info; select * from pg_stat_ssl; select ' |
Statistics generated on: '|| current_date || ' ' ||localtime as info;
select '
More info on' as info;
select 'this site' as info;
select 'or contact' as info;
select 'Meo.
' as info;