All posts by Denish Patel

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. 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.

The Scalability & Performance Conference : Surge 2011

         The Scalability and Performance covers most of the web scale issues, innovations and  evolutions in technology. As per my knowledge, Surge is the only conference  that gives the coverage to the issues and solutions concerning Scalability & Performance without any bias on technology that we often notice at most other conferences. For example , if you are looking from Database perspective, it covers topic ranges from RDBMS to NoSQL and talks about problems solved by the technology and new possible problems introduced by new technology.

      I attended Surge 2010 and listed attended sessions on my last year  post. I am going to attend Surge again. Surge 2010 was the best conference attendance experience for me and others. By looking at Sponsor list  (Google is one of them!), this year (Sept 28-30 , 2011) conference is going to be far bigger and better than last year  and don’t forget to checkout industry leader Speakers list & Hack Day/Training Schedule !!  If you haven’t registered , you still have chance to do so.

Surge 2011 will be surge of the knowledge ! See you soon !

Monitoring Riak using Circonus

       One of the core responsibilities that all engineering disciplines share at OmniTi is assessing the appropriateness of the technologies at hand. Recently, I have been assessing “NoSQL” database technology called Riak. As a DBA, I would like to learn a way to monitor the database server without wasting lots of time and energy! At OmniTi, we use Circonus to monitor, trend and alert on checks for the production servers . It turns out you can plug all the critical Riak Stats metrics into Circonus with no effort and very little time.Let me introduce both the technologies before I walk you through steps to plug the checks into the Circonus monitoring system.

What is Riak?

       Riak is a key/value store “NoSQL” distributed web scale database and written primarily in Erlang.
What is Circonus?
    Circonus , a SAAS offering,  gives a cohesive view of how IT affects business. Translating monitoring and trending into business sense—this is where Circonus shines. Circonus brings network monitoring back to a business intelligence system that is capable of graphing, trending, reporting and fault detection. 

    I am not going into details for installing and setting up Riak database server but it’s pretty easy to follow instruction on the Basho Wiki Page.  In my example, I have ec2 instance fired up and running riak database already and it is connected to 2 other nodes in the ring.  It’s easy to get Riak stats  from HTTP API  access. In this example, I am going to plug in some of the important metrics from  riak status  output provided by http stats command. 
Let’s assume that we want to monitor riak running on and circonus agent can access following url to get stats from the server :
1.  If you don’t have Circonus Account you can Sign Up using easy sign up process by clicking on “Sign Up” button on the top right corner on
2.  You will get the password in your email and use it for changing your account password and login to circonus.
3. Under “Checks/Metrics” and Click on “+” Sign to add new checks. It will pop-up new screen to select check type.
4.  Web -> General -> JSON and Select the Circonus Agent to use.
5.  Set  hostname= , URI=/stats and Port=8098 and click on Test check


6.  Next screen will give you all the metrics rerurned by the stats output and you can select the import ones and hit the “Finish”
7. Choose and enable required metrics. Out of 70 metrics provided by Stats output , I “enabled” only 12 important metrics for now. Though I will be looking for advice from you guys to add more metrics for monitoring, trending and alerting purposes.
8. Once they are getting tracked , you can generate graphs like:


      Yay! you have all the important metrics are in the circonus and you can generate graphs whenever you want and create worksheet per server or data center!!
I could add all the required checks for Riak Database server  under 5 minutes into Circonus!!  Share your experience and let me know if you find difficulty working with Circonus.

When was my database started ?

In the database world, it’s fair to ask question to database that when have you started? In Oracle world, it can be easily find out by querying system level view V$INSTANCE. In PostgreSQL world, until today, I wasn’t able to answer that question without checking database log files. Today, I asked this question to OmniTi’s database operations irc channel and got answer in 20 minutes. I regret that I should have asked it earlier but anyways, now I know that in PostgreSQL it’s possible using this query.

omniti=# SELECT (pg_stat_file( '')).modification;
2010-08-13 15:37:14-04

If someone complains that database was restarted , it would be easy as DBA to query and verify it. I hope this query will be helpful to you during day-to-day operations!

Extreme Training Session at PgEast: P90X your Database!

Over-allocated space in tables and indexes is a very common problem in PostgreSQL database where explosive data growth and changes occurs. This over allocated space is called “bloat”. Bloated tables and indexes waste resources of the system and cause large performance anomaly to the applications. Vacuum process helps to avoid bloat but some cases where even vacuum can not able to recover space for reuse. To shrink tables in such cases, DBA needs special attention and tools to remove bloat from the tables and indexes.

At PgEast 2011, I will discuss PostgreSQL MVCC design (compare with Oracle and MySQL) , ways to avoid bloat and online & offline methods to remove bloat from the tables and indexes. P90X your database talk will help you to keep databases skinnier and faster. If you attending this conference, looking forward to hear your experiences and tools that helps to avoid and remove bloat.

PosgreSQL DBA commands for Beginners

PostgreSQL is world best open source database. PostgrSQL is RDBMS database like Oracle . The main advantage of PostgreSQL is that it’s open source and free. Moreover, PostgreSQL provides comparable features like Oracle. Today, I came across an useful PostgreSQL Database Administration commands series. I would highly recommend following articles for the beginners to get hands on experience quickly in the PostgreSQL database administration.

Here are the links:
I hope these articles will help you to start your career in PostgreSQL database administration.

Find MySQL table level Insert/Update/Delete Statistics

Many customers are asking for getting stats from their MySQL installation. It’s pretty easy to find stats per database from Com_ variables and graph them as counter to get the number of INSERT/UPDATE/DELETE on the database level. To get the DML stats from MySQL at table level there is only one option : mysqlbinlog . Here is the best post I found that can help you to answer your question.

Get the MySQL table stats now!

CREATE TABLE … PARTITION BY RANGE . Will it be possible in PosgreSQL?

Oracle’s easy to create and manage partition table feature is the best selling point for them to attract wide range of applications to use partitioning features easily . Oracle supports partitioning since Oracle8i but there were limited features in the first release. In Oracle8i, they supported only RANGE and HASH partitions management.

On the other hand, PostgreSQL support partitioning but it’s cumbersome to create and manage partitioned tables. At least to start with, PostgreSQL should implement commands to make RANGE partition creation and management easy for the users. .
This is an example for creating range partitioned tables in Oracle …
( user_id NUMBER NOT NULL,
registration_date DATE NOT NULL,
notes VARCHAR2(500))
PARTITION BY RANGE (registration_date)
When will I be able to CREATE range partition table and manage with ALTER TABLE commands in PostgreSQL?

Possibility of Graceful Switchover?

I have been using PITR master-slave solution for long time for “failover” to slave server when there is a problem with the master server. It has proven a valuable solution for most of clients needs. The problem with “failover” solution is that we have to rebuild old-master as slave from completely new base backup from newly promoted master server. Oracle provides nice feature called “Graceful Switchover“. In this method, you can switch duty between master and slave servers and don’t have to rebuild old-master as slave from newly promoted master server. It could be very useful during server or data center move as we can shutdown old database and can have total control over xlogs to be processed before we start traffic on newly promoted master database.

I couldn’t find any graceful switchover document or blog for the PostgreSQL . Today, I spent some time on testing same theory as Oracle uses for the switchover. Oracle controls switchover using controlfile, Why can’t I use pg_control to do the same in totally controlled environment?
For PostgreSQL graceful switchover, I tested following scenario on very small test database server with pre configured setup of master and PITR slave database:
  • Step-1 : Shutdown current master database
  • Step-2 : Apply all wal logs including the one created during shutdown process to slave database server
  • Step-3: Promote slave as master in read/write mode using triggered failover file
  • Step-4: Before pointing production traffic to newly promoted master server, take backup of $PGDATA/global/pg_control and $PGDATA/global/pgstat.stat files. These two files differ between master and slave after failing over slave database into production mode.
  • Step-5: Setup old – master server for slave for newly promoted master sever and replace $PGDATA/global/pg_control and $PGDATA/global/pgstat.stat with the files backed up in Step-4
  • Step-6: Check log files on old-master to make sure that it’s recovering copied wal logs from newly promoted master server(old-slave server)
Yay! I could successfully switched over slave to master and vice versa !
At this stage, old-master(new slave) started playing wal logs coming from new-master(old-slave) server. After that, I failed over new slave server to make sure we can actually bring the database in production mode. It worked like a charm!! I ran database wide vacuum after failover and it ran fine without any error.
I have done limited testing as I have tested this with very tiny setup of “pagila” database on test server and I don’t recommend you to use on the “production database” servers as of now.
Please test this in your test or dev environment and let me know your results and concerns!!