Re: Database health check/auditing
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@gmail.com> wrote:
Tim,On Thu, Feb 15, 2018 at 9:22 PM, Tim Cross <theophilusx@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
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.
regards,
Tim
--
Tim CrossВ списке pgsql-general по дате отправления: