September 6th, 2011 by depesz | Tags: , , | 11 comments »
Did it help? If yes - maybe you can help me?

I'd like to announce start of new project for PostgreSQL admins – Curo.

Some time ago, we talked with my colleagues at OmniTI about administration of PostgreSQL, and how nicer it would be if we'd have some common tool that shows important info at will.

The thing is, that when you have PostgreSQL installed, you get psql – which you can use to get all the info you might need, but you need to type long queries to get the info you want/need.

Of course – you can install another tools – like PGTop, or use some ready snippets from wiki.

The problem with additional tools is that not always admins are happy to install stuff for you, or there are problems with compilation. So snippets can be nicer to use.

So, I got the idea of preparing set of such snippets, ready to use, and possibly – make them easier to use.

How easier? Generally when you have snippet, you have top copy/paste it, or save in file, and then do \i file in psql. But what about making them available through a menu? Or a single key press?

This is how Curo was born.

It is in very early stage right now, but it works, and we tested it on 3 systems: Linux, Mac OS X and Solaris.

So, how it works.

You get checkout of github repository (url for github project page).

Afterwards, you save the checkout as ~/.curo directory – that is “.curo" in your home directory.

Then, if you want (and you do want) keyboard shortcuts (f1-f8 keys) to work, you install appropriate readline/libedit configuration file, and you're done.

How to know which is appropriate? If you don't know – use both.

As for the configuration files – it's all described in docs so there is not much point in repeating.

Afterwards, as long as you have psql, bash and dialog, it's done.

Dialog installation is necessary to get nice menus, and dialog is usually not installed by default. So to get it you need to use your system package manager (yum, apt-get, pkgadd, port, whatever) to get it. It shouldn't be complicated, the software is free, and it's only dependency should be ncurses.

One note – dialog is needed for menu/setup screens. All reports can be ran directly using appropriate \i ~/.curo/s/something.sql from within your psql, including actions ran by pressing f* keys.

When you'll finish installation, run psql, connect to your database of choice, and you can play with Curo.

For starters – press F1. It should show menu with all currently installed actions, plus setup screen.

In setup, you can change key bindings for f1-f8 keys.

From menu you can run the actions – like “Current Activity", but you should be able to get them also from f* keys.

At the moment we have 4 actions/snippets committed to svn:

  • Current activity – shows, refreshed automatically, every 1 second, information about activity in current database. This is subset of information from pg_stat_activity, but presented in potentially easier to read way (plus it autorefreshes!)
  • Locks information – not yet finished – information of locks taken by backends connected to current database. This will be revamped soon(ish)
  • Idle in Transaction – Information about oldest current “IDLE in transaction" backend, together with info about which relations it has locks on
  • Blocked queries – shows which queries are blocked, and by which queries

As you can see we're focusing now on locks, but that's just for now. We will add more functionality to Curo, and we'll be happy to get information from other users, about what could be useful to add there.

  1. 11 comments

  2. # Ben Kruger
    Sep 6, 2011

    Can you give us some screen shots and better examples!

  3. Sep 6, 2011

    @Ben:
    Hmm .. screen shots? The program that’s running it all is still plain psql. So, while I can show screenshots of psql, I’m not sure how informative it would be.

  4. # gj
    Sep 6, 2011

    write some sort of a ‘frontend’ in python/perl for it.Configuring screen specifically for it, is a royal pita.

  5. Sep 6, 2011

    GJ:
    Not sure I understand. What kind of frontend? What would it do?

    I’m also not entirely sure what you have in mind when you mention screen configuration.

  6. Sep 6, 2011

    Hi depesz,
    I really like the idea behind Curo. Looking forward to trying the scripts inside it.

    I would also like to support the idea of putting it on github as it can be easier to be watched, forked etc.

    I can see that it has an OmniIT licence, but that shouldn’t e a problem IMO.

  7. Sep 6, 2011

    @Dragan:
    It’s not “OmniTI” license – it’s BSD.

    As for github – migrated. It was planned to be migrated, but somehow slipped my mind, thanks for reminding 🙂

  8. Sep 6, 2011

    @DEPESZ
    Great. Please make a post when you migrate it to github.

    Great Work

    Cheers

  9. Sep 6, 2011

    @DEPESZ

    About the licence. Yes it’s BSD sorry. OmniIT is the copyright holder.

    Cheers

  10. Sep 6, 2011

    @Dragan: it’s already migrated.

  11. # tommics
    Sep 9, 2011

    if you use putty you have to set your terminal to use vt100+ emulation

  12. Sep 9, 2011

    @Tommics:
    Thanks for info – I don’t have any usable windows machine, and thus don’t know how to make it work. It’s great that it works with such a simple change.

Leave a comment