Create Custom DbConsole Command

We create a Postgres command to compute disk usage on database. The command can be executed on multiple databases. This is using the Groovy CLI. The command has to be registered in DbConsole using the register command.
def cli = new CliBuilder(usage:'disk usage ')
cli.h(longOpt:'help', 'Show usage information and quit')
def options = cli.parse(args)

if (options.'help') {
    cli.usage()
    return
}

sql.execute("DROP TABLE IF EXISTS dbconsole_disk_usage")
sql.execute("DROP TABLE IF EXISTS dbconsole_disk_usage_logs")
sql.execute("CREATE TABLE dbconsole_disk_usage( filesystem text, blocks bigint, used bigint, free bigint, percent text, mount_point text )")
sql.execute("CREATE TABLE dbconsole_disk_usage_logs( blocks bigint, folder text )")

sql.execute("COPY dbconsole_disk_usage FROM PROGRAM 'df -k | sed \"s/  */,/g\"' WITH ( FORMAT CSV, HEADER ) ")
sql.execute("UPDATE dbconsole_disk_usage SET used=used/(1024*1024), free=free/(1024*1024), blocks=blocks/(1024*1024)")

String dbid = sql.firstRow( "SELECT current_database() as dbid").dbid
sql.execute("COPY dbconsole_disk_usage_logs FROM PROGRAM 'du -m -s /data/${dbid}/pgsystem/pg_log | sed \"s/\\s\\s*/,/g\"' WITH ( FORMAT CSV ) ".toString())
sql.execute("COPY dbconsole_disk_usage_logs FROM PROGRAM 'du -m -s /data/${dbid}/jobsystem/pg_log | sed \"s/\\s\\s*/,/g\"' WITH ( FORMAT CSV ) ".toString())

int logBlocks = sql.firstRow( "SELECT sum(blocks) as usage FROM dbconsole_disk_usage_logs").usage

sql.eachRow( "SELECT * FROM dbconsole_disk_usage"){ r->
  if ( r.filesystem.startsWith( '/dev/mapper') ){
    int freePercent = Math.round(r.free*100/(r.blocks))
    String status = ( r.free < 30 && r.blocks > 1500 ) ? 'Very Low' : (( r.free < 50 && r.blocks > 1000) || r.free < 30 ) ? 'Low' : "Ok"
    String logStatus = logBlocks > 1024 ? String.format( 'pg_logs %dM', logBlocks ) : ''
    println String.format( '%10s   Free %3d%% ( %dG out of %dG ) %s', status, freePercent, r.free, r.blocks, logStatus )
  }
}

sql.execute("DROP TABLE dbconsole_disk_usage_logs")
sql.execute("DROP TABLE dbconsole_disk_usage")

Navigation