nagios db fluch and vacuum script help needed

[benhank]

In my relentless pursuit of nagiosisness, I came across this article from waaaaayyyy back:

Code: Select all

http://labs.nagios.com/2011/11/15/nagios-xi-benchmarking-experiments/

I am interested in making a script that does maintenance on my dbs. But when I run the commands :

Code: Select all

psql nagiosxi nagiosxi -c "vacuum;"
psql nagiosxi nagiosxi -c "vacuum analyze;"
psql nagiosxi nagiosxi -c "vacuum full;"

i get:

Code: Select all

 psql nagiosxi nagiosxi -c "vacuum;"
WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
VACUUM
[root@LKENNAGIOST01 ~]# psql nagiosxi nagiosxi -c "vacuum analyze;"
WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
VACUUM
[root@LKENNAGIOST01 ~]# psql nagiosxi nagiosxi -c "vacuum full;"
WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
VACUUM

this is on xi5 latest versions with postgress dbs. Any ideas on what I need to do to make it work?
thanks in advance!

[ssax]

This should work for you:

Code: Select all

psql nagiosxi postgres -c "vacuum;"
psql nagiosxi postgres -c "vacuum analyze;"
psql nagiosxi postgres -c "vacuum full;"

Let us know the results.

Thank you

[benhank]

worked beautifully thanks man!

how do i force it to flush and vacuum mysql?

[ssax]

These should be handled automatically already by the settings in Admin > Performance Settings > Databases, they are the three optimize intervals.

[benhank]

thanks man lock it up!