On Thu, Feb 15, 2018 at 9:22 PM, Tim Cross <theophil...@gmail.com> 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 starterto 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 manymore general queries, so if you have any specific need,
letme know and I'll be glad to send if I have one that fits theneed.The
names should be self descriptive as to what the dobut except for
get_trans_min_cnt.sql (Transaction per minute) none have any DDL.
database_sizes.sqlbad_idx.sqlcache_hit_ratio.sqlget_trans_min_cnt.sqlget_version_num.sqlpg_runtime.sqlpg_stat_all_indexes.sqlpg_stat_all_tables.sqltable_sizes.sqltable_stats.sqluseless_indexes2.sqlPlease
also note I have bash script versions of the same, butas 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.
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;
