DbPrompt Advanced Features and Scripts

Back to DbPrompt Main Page

Chunk Database Update or Deletion

Updating or deleting large amounts of data may require to split the operation in smaller chunks. This because the large operation may cause disk or memory issues and may lead to database locks.

  • The update example is using a nested loop with a Postgres.setResultSetHoldability.
  • The delete example is loading id values into an ArrayList.
groovy
import java.sql.ResultSet
sql.setResultSetHoldability( ResultSet.HOLD_CURSORS_OVER_COMMIT)
def i = 0
sql.eachRow( "SELECT userid FROM dom_user_ext_6089".toString() ){ r ->
  sql.execute("UPDATE dom_user_ext_6089 SET ext_string_015='T' WHERE userid=?".toString(), [ r.userid ])
  i++
  if ( i > 1000 ){
    sql.commit()
    i = 0
    print "."
  }
}
sql.commit()
Deletion example:
groovy
  List ids = new ArrayList()
  sql.eachRow( 'SELECT id FROM large_table' ) { m ->
    ids.add( m.id )
  }
  int cnt = 0;
  for ( int id : ids ) {
    sql.execute('DELETE FROM large_table WHERE id=?', [id] )
    cnt++
    if ( cnt > 1000) {
      print '.'
      sql.commit()
      cnt = 0;
    }
  }
/

Create Custom DbPrompt 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 DbPrompt 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 dbprompt_disk_usage")
sql.execute("DROP TABLE IF EXISTS dbprompt_disk_usage_logs")
sql.execute("CREATE TABLE dbprompt_disk_usage( filesystem text, blocks bigint, used bigint, free bigint, percent text, mount_point text )")
sql.execute("CREATE TABLE dbprompt_disk_usage_logs( blocks bigint, folder text )")

sql.execute("COPY dbprompt_disk_usage FROM PROGRAM 'df -k | sed \"s/  */,/g\"' WITH ( FORMAT CSV, HEADER ) ")
sql.execute("UPDATE dbprompt_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 dbprompt_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 dbprompt_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 dbprompt_disk_usage_logs").usage

sql.eachRow( "SELECT * FROM dbprompt_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 dbprompt_disk_usage_logs")
sql.execute("DROP TABLE dbprompt_disk_usage")

Save Passwords in Separate File

I used to save my DbPrompt scripts in a GIT project, including the init.sql file with the database connections. There was a problem with the passwords: I didn't want the passwords to get listed into GIT. Therefore DbPrompt allow to save a separate file, with passwords. It the file can be .pgpass in the user home directory or passwords.txt in home directory/.dbprompt. The file format is the same as Postgres pgpass

hostname:port:database:username:password
For example the password is 'secret' for all hosts, port 1521 user postgres.
*:1521:sakila:postgres:secret

Create Connections Using Suffixer

There are cases when you have to manage large database clusters where you have primaries and standby databases. I used to manage a cluster where the primary database host name was like pos1.db, pos2.db, ... and standbys were called pos1.sbydb, pos2.sbydb...

For this cases I have created the database connections like below.

groovy
import com.ecircle.dsql.connection.*

for ( int i=1;i <130; i++){
  ConnectionFactory.createConnector( "pos${i}", "postgres", "user=postgres host=pos${i}.db db=sakila${i}" )
}

ConnectionFactory.addSuffix( new Suffix("sby"){
  public Connector create( Connector con ){
   String host = con.getHost();
   if ( host.endsWith(".db") ) return duplicate( con, "host=" + host.replaceFirst(/\.db/, /.sbydb/))
   return null
  }
})
The Suffix class receive a connection. If the connection is matching the name .db, it duplicates the Connector by creating a new parameters string, where .db is replaced with .sbydb.

DbPrompt Start Parameters

DbPrompt can be started with following parameters:

  • -cron to start in cron mode, here for details.
  • A script file name or path to a script file. DbPrompt will execute the given script.

DbPrompt Support

For any issue or feature request please write us. Please write us also about any database-specific scripts you wrote, this will help us to improve the database specific management commands.

Navigation