| From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
|---|---|
| To: | Tim Cross <theophilusx(at)gmail(dot)com> |
| Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Database health check/auditing |
| Date: | 2018-02-16 03:11:38 |
| Message-ID: | CANu8Fiy3m68s3T8Yr4-Z435q3r7DcdJYG0E4Hm-wZbovPGq8+Q@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Thu, Feb 15, 2018 at 9:22 PM, Tim Cross <theophilusx(at)gmail(dot)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.
| Attachment | Content-Type | Size |
|---|---|---|
| database_sizes.sql | text/plain | 458 bytes |
| bad_idx.sql | text/plain | 481 bytes |
| cache_hit_ratio.sql | text/plain | 694 bytes |
| get_trans_min_cnt.sql | text/plain | 582 bytes |
| get_version_num.sql | text/plain | 48 bytes |
| pg_runtime.sql | text/plain | 117 bytes |
| pg_stat_all_indexes.sql | text/plain | 1.2 KB |
| pg_stat_all_tables.sql | text/plain | 1.3 KB |
| table_sizes.sql | text/plain | 1020 bytes |
| table_stats.sql | text/plain | 469 bytes |
| useless_indexes2.sql | text/plain | 749 bytes |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tim Cross | 2018-02-16 04:03:32 | Re: Database health check/auditing |
| Previous Message | Tim Cross | 2018-02-16 02:22:28 | Database health check/auditing |