On Thu, Feb 15, 2018 at 9:22 PM, Tim Cross <theophilusx@xxxxxxxxx> wrote:
Hi All,
I was wondering if anyone has some pointers to
sites/repositories/resources for scripts to perform basic database
audits and health checks.
situation: I have just commenced a DBA and developer role for an
organisation with a number of Postgres databases (9.4 and 9.6
versions). There has been no dedicated DBA and a number of the databases
were setup by people with little to know Postgres or database
experience. I need to get an overview on what I'm dealing with and start
prioritising what to address first.
It has been some years since I've done any real work with Postgres. Most
of my technical work over the last 10 years has been with Oracle. I
prefer to use scripts over GUI tools like pgAdmin and suspect that there
is probably some good resources out there with existing scripts I can
use as a starting point.
Any pointers greatly appreciated.
thanks,
Tim
--
Tim Cross
Tim,
Good luck to you. I have been in your situation a few times.
I have attached the following scripts which I use as a starter
to get a general idea of database status. All of the should
run on both 9.4 & 9.4, but sometimes catalog changes may
fudge things up, so you may have to tweak a bit. I have many
more general queries, so if you have any specific need, let
me know and I'll be glad to send if I have one that fits the
need.
The names should be self descriptive as to what the do
but except for get_trans_min_cnt.sql (Transaction per minute) none have any DDL.
database_sizes.sql
bad_idx.sql
cache_hit_ratio.sql
get_trans_min_cnt.sql
get_version_num.sql
pg_runtime.sql
pg_stat_all_indexes.sql
pg_stat_all_tables.sql
table_sizes.sql
table_stats.sql
useless_indexes2.sql
Please also note I have bash script versions of the same, but
as you did not state the O/S, I felt the sql was best.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
SELECT oid,
datname,
pg_size_pretty(pg_database_size(datname))as size_pretty,
pg_database_size(datname) as size,
(SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
FROM pg_database) AS total,
((pg_database_size(datname) / (SELECT SUM( pg_database_size(datname))
FROM pg_database) ) * 100)::numeric(6,3) AS pct
FROM pg_database
ORDER BY datname;SELECT n.nspname,
i.relname,
i.indexrelname,
CASE WHEN idx.indisprimary
THEN 'pkey'
WHEN idx.indisunique
THEN 'uidx'
ELSE 'idx'
END AS type,
'INVALID'
FROM pg_stat_all_indexes i
JOIN pg_class c ON (c.oid = i.relid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_index idx ON (idx.indexrelid = i.indexrelid )
WHERE idx.indisvalid = FALSE
ORDER BY 1, 2;SELECT pg_stat_database.datname,
pg_stat_database.blks_read,
pg_stat_database.blks_hit,
round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+1)::double precision * 100::double precision)::numeric, 2) AS cachehitratio
FROM pg_stat_database
WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
ORDER BY round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+ 1)::double precision * 100::double precision)::numeric, 2) DESC;
BEGIN;
DROP TABLE IF EXISTS tmp_trans_stats;
CREATE TEMP TABLE tmp_trans_stats AS
SELECT 'start_cnt'::varchar(10) AS taken,
SUM(xact_commit + xact_rollback) AS cnt
FROM pg_stat_database;
COMMIT;
SELECT pg_sleep(60);
INSERT INTO tmp_trans_stats
SELECT 'end_cnt'::varchar(10) AS taken,
SUM(xact_commit + xact_rollback) AS cnt
FROM pg_stat_database;
SELECT (
(SELECT cnt
FROM tmp_trans_stats
WHERE taken = 'end_cnt')
- (SELECT cnt
FROM tmp_trans_stats
WHERE taken = 'start_cnt')
) as tot_trans;
SELECT current_setting('server_version_num');SELECT pg_postmaster_start_time() as pg_start,
current_timestamp - pg_postmaster_start_time() as runtime;SELECT n.nspname as schema,
i.relname as table,
i.indexrelname as index,
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
CASE WHEN idx.indisprimary
THEN 'pkey'
WHEN idx.indisunique
THEN 'uidx'
ELSE 'idx'
END AS type,
idx.indisexclusion,
pg_get_indexdef(idx.indexrelid),
CASE WHEN idx.indisvalid
THEN 'valid'
ELSE 'INVALID'
END as statusi,
pg_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(i.relname)) as size_in_bytes,
pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(i.relname))) as size
FROM pg_stat_all_indexes i
JOIN pg_class c ON (c.oid = i.relid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_index idx ON (idx.indexrelid = i.indexrelid )
WHERE i.relname LIKE '%%'
AND n.nspname NOT LIKE 'pg_%'
AND NOT idx.indisunique = TRUE
AND NOT idx.indisprimary
-- AND i.indexrelname LIKE 'tmp%'
-- AND idx.indisvalid IS false
/* AND NOT idx.indisprimary
AND NOT idx.indisunique
AND idx_scan = 0
*/ ORDER BY 1, 2, 3;
SELECT n.nspname,
s.relname,
c.reltuples::bigint,
-- c.relfrozenxid,
age(c.relfrozenxid) AS age_frozenxid,
-- n_live_tup,
n_tup_ins,
n_tup_upd,
n_tup_del,
date_trunc('second', last_vacuum) as last_vacuum,
date_trunc('second', last_autovacuum) as last_autovacuum,
date_trunc('second', last_analyze) as last_analyze,
date_trunc('second', last_autoanalyze) as last_autoanalyze
,
round( current_setting('autovacuum_vacuum_threshold')::integer + current_setting('autovacuum_vacuum_scale_factor')::numeric * C.reltuples) AS av_threshold
/* ,CASE WHEN reltuples > 0
THEN round(100.0 * n_dead_tup / (reltuples))
ELSE 0
END AS pct_dead,
CASE WHEN n_dead_tup > round( current_setting('autovacuum_vacuum_threshold')::integer + current_setting('autovacuum_vacuum_scale_factor')::numeric * C.reltuples)
THEN 'VACUUM'
ELSE 'ok'
END AS "av_needed"
*/
FROM pg_stat_all_tables s
JOIN pg_class c ON c.oid = s.relid
JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE s.relname NOT LIKE 'pg_%'
AND s.relname NOT LIKE 'sql_%'
-- AND s.relname LIKE '%TBL%'
ORDER by 1, 2;
SELECT n.nspname as schema,
c.relname as table,
a.rolname as owner,
c.relfilenode as filename,
c.reltuples::bigint,
pg_size_pretty(pg_relation_size(n.nspname|| '.' || c.relname)) as size,
pg_size_pretty(pg_total_relation_size(n.nspname|| '.' || c.relname)) as total_size,
pg_relation_size(n.nspname|| '.' || c.relname) as size_bytes,
pg_total_relation_size(n.nspname|| '.' || c.relname) as total_size_bytes,
CASE WHEN c.reltablespace = 0
THEN 'pg_default'
ELSE (SELECT t.spcname
FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )
END as tablespace
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE n.nspname NOT LIKE 'pg_%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;
--LIMIT 10;
SELECT c.oid,
n.nspname as schema,
c.relname as table,
pg_stat_get_last_vacuum_time(c.oid) as last_vacuum,
pg_stat_get_tuples_inserted(c.oid) as inserted,
pg_stat_get_tuples_updated(c.oid) as updated,
pg_stat_get_tuples_deleted(c.oid) as deleted
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE nspname NOT IN ('information_schema', 'pg_toast', 'pg_catalog')
and relkind = 'r'
ORDER BY 2, 3;
SELECT n.nspname as schema,
i.relname as table,
i.indexrelname as index,
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.relname))) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))) AS index_size,
pg_get_indexdef(idx.indexrelid) as idx_definition
FROM pg_stat_all_indexes i
JOIN pg_class c ON (c.oid = i.relid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_index idx ON (idx.indexrelid = i.indexrelid )
WHERE i.idx_scan = 0
AND n.nspname <> 'pg_catalog'
AND NOT idx.indisprimary
AND NOT idx.indisunique
ORDER BY 1, 2, 3;
