PostgreSQL Handyman Toolset

     A PostgreSQL handyman is a person skilled at a wide range of maintenance and repairs, typically around the PostgreSQL database system.  If you are working with PostgreSQL and you have right tools for  the job, you can easily accomplish that job efficiently and most importantly on time. In real world, if you want to dig into land and you don’t have tools then you are failed at job before you start the job!  Having tools for any job is most important factor for job success. While working at OmniTI, I tested and used variety of tools to make my work life easy and efficient.

      PostgreSQL community is thriving by people, those helps to build new tools, give it back to community for re-use and providing helping hands to test the tools. Here are some of my suggested list of tools which could help  to finish your job efficiently and on time!!

 1.  Tasty Treats for PostgreSQL project includes plenty of PostgreSQL management tools , which is one of the projects of OmniTI’s Lab repository.

Some of the tools includes:

  • zbackup
  • getddl
  • pgsniff
  • table growth report
  • quickstats
  • pg_log monitor
  • bloat reports
  • pgtruss
  • system monitoring
  • compact table
  • blocked queries
  • Curo

2.  OMNIpitr Advanced WAL File Management Tools for PostgreSQL. This tool helps to handle HA processes efficiently to manage wal shipping, restore & removal , online backup on slave and/or master.

3.  bucardo.org also includes mission critial tools:

  •  Bucardo  
    • Asynchronous PostgreSQL Replication System
  • check_postgres  
    • Nagios-Friendly PostgreSQL Database Checking Script
  • pgsi 
    • Wiki-Ready Query Reports
  • tail_n_mail 
    • Monitor log files
  • boxinfo 
    • Gather system information

4.  repmgr: it allows you to monitor and manage  high availability part of  PostgreSQL databases (9.0+) installation.

5. pg_reorg: it allows you to rebuild Bloated tables online. Review these slides to understand how it works.
 
6.  RubyRep : Components of this tool set can help you compare and sync two tables or even two databases.

I would love to know  tools used by you for managing PostgreSQL database server which are missing from my PostgreSQL handyman too-lset !!

Hopefully, these tools will make your next task easy!! If you find bug, don’t hesitate to report to send out email to relevant tool maintainer with error or suggestion or patch with bug fix.

DISCLAIMER: use any of listed tool on production at your own risk.