Re: Database health check/auditing

On Thu, Feb 15, 2018 at 11:03 PM, Tim Cross <theophilusx(at)gmail(dot)com> wrote:

> Hi Mel,
>
> thanks a lot. The databases are all running on Red Hat (well OUL to be
> specific). The SQL is fine and I can always wrap them in a bash script if
> needed.
>
> Having these scripts is a real help. My biggest challenge at the moment is
> just turning off my Oracle habits and getting back Postgres ones! Reading
> these scripts really helps drag out old forgotten stuff. Luckily, there
> doesn't seem to be too much really nasty or weird. For the most part,
> doesn't look like anyone has made weird configuration changes and there are
> no obscure triggers doing hidden things. Most of the user defined
> functions seem pretty reasonable, though some seem to be doing some pretty
> inefficient SQL and unnecessary type casting etc. Privileges and roles are
> a mess - seems to be a bit of the 'make everyone a super user' approach
> rather than work out what is required, but I expected that. There are a
> couple of databases with considerable size, but many of them are quite
> small (I will likely be consolidating a number of servers as their size and
> load is low and it will be easier to manage fewer servers). All in all, it
> isn't as bad as it could be or as bad as I've seen before, so it shouldn't
> bee too bad. Establishing some standards and change control will help.
>
> thanks again,
>
> tim
>
>
> On 16 February 2018 at 14:11, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 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.
>>
>
>
>
> --
> regards,
>
> Tim
>
> --
> Tim Cross
>
>
Tim,

FYI, the policy in this list is to avoid top posting and bottom post
instead.

Before you do anything else, * check the postgresql.conf* for *shared_buffers,
work_mem & maintenance_work_mem* values.
Quite often the developers have no clue on how to tune a database.

Roles and privileges can always be fixed. At least you don't have to deal
with the case of where they created indexes for every column
in every table, then copied the schema for each client....yeech!

Here's a couple more you may find helpful.

current_queries.sql
triggers.sql

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.