Denish Patel

Deploying Maximum HA Architecture with PostgreSQL

noreply@blogger.com (Denish Patel) — Mon, 02 Apr 2012 22:57:00 +0000

Today, I gave talk on "Deploying Maximum HA Architecture with PostgreSLQ" at PG Day New York. You could check out slides here !

2 PG Days in 2 days apart !!

noreply@blogger.com (Denish Patel) — Mon, 26 Mar 2012 12:12:00 +0000

Want to learn more about PostgreSQL? Are you at driving distance from Washington DC and/or Newyork area? If any of anwers is yes then you shound't miss this opportunity to attend 2 PostgreSQL Days within 2 days apart in your area with plenty of knowledge sharing talks and networking events!

1. Friday,March 30th,2012 PG Day in Washington DC

2. Monday,April 2nd,2012 PG day in Newyork

I will be attending both of the these days. If you are attending, will love to meet you in-person.

See you soon!!

monitor bucardo replication lag using circonus

noreply@blogger.com (Denish Patel) — Thu, 01 Mar 2012 21:06:00 +0000

I have been using circonus for monitoring, trending and alerting for any database metrics for quite a long time now. The circonus interface makes the monitoring, trending and alerting setup painless and you can see graph flowing in minutes. Another good thing about Circonus is that you can monitor anything that you can query from database ! This week, the task at my hand was to find a way to monitor bucardo replication lag. `bucardo_ctl status sync_name` provides very important information that you can rely for trending and alerting purposes.
$ bucardo_ctl status my_slave Sync name: my_sync Current state: WAIT:22s (PID = 19500) Type: pushdelta Source herd/database: slave_herd / master_herd Target database: my_slave Tables in sync: 318 Last good: 23s (time to run: 1m 21s) Last good time: Feb 29, 2012 15:27:14 Target: my_slave Ins/Upd/Del: 142 / 0 / 0 Last bad: 1h 45m 9s (time to run: 19m 57s) Last bad time: Feb 29, 2012 13:42:29 Target: my_slave Latest bad reason: MCP removing stale q entry PID file: /var/run/bucardo/bucardo.ctl.sync.my_sync.pid PID file created: Wed Feb 29 13:42:33 2012 Status: active Limitdbs: 0 Priority: 0 Checktime: none Overdue time: 00:00:00 Expired time: 00:00:00 Stayalive: yes Kidsalive: yes Rebuild index: 0 Do_listen: no Ping: yes Makedelta: no Onetimecopy: 0

All the information provided by `bucardo_ctl status` command is important but most interesting thing to monitor is "Last good:". Last good shows bucardo replication lag on slave server.

Trending in circonus:

Now, I have metric identified for the monitoring. Next step is to find the best way to put the metric into the monitoring tool. After some poking around the output and ways to monitor stuff, I decided to grab the SQL query from bucardo_ctl perl script and stick it into circonus monitoring. Most of the time spent for setting up this monitor was to grab right query from big perl script (bucardo_ctl) and mapping the metric required from the query. After that, here is the query that I plugged into Circonus in no-time.

SELECT 'bucardo_last_good' , round(extract(epoch FROM now()-ended)) FROM (SELECT * FROM bucardo.q WHERE sync = 'my_sync' AND cdate >= now() - interval '3 days' UNION ALL SELECT * FROM freezer.master_q WHERE sync = 'my_sync' AND cdate >= now() - interval '3 days') AS foo WHERE ended is NOT NULL AND aborted IS NULL ORDER BY ended DESC LIMIT 1;

Alerting in circonus:

bucardo_ctl status shows Last good status to "unknown" if replication is broken.
Name Type State PID Last_good Time I/U/D Last_bad Time ===========+=====+========+====+=========+=====+=====+========+===== my_sync| P |WAIT:35s|7620| unknown | | |36s |1m58s

In circonus, you could setup rules and relevant severity levels. The most important part is that if the query doesn't return any row it should page("unknown" condition) . Circonus provides rule for alerts in case of a metric is absent. Now, I am all set with alerts as well.

Yay! bucardo replication is under monitoring and trending without any hassle! Hopefully, this post will help you next time when you try to put bucardo replication lag under monitoring.

Half a decade with OmniTi, PostgreSQL, FOSS ...

noreply@blogger.com (Denish Patel) — Tue, 14 Feb 2012 16:46:00 +0000

By end of this month, I am completing 5 years with OmniTI. Before joining OmniTI, I was an Oracle DBA and worked mostly with closed source databases. I am grateful & fortunate that Theo and Robert provided me opportunity and guidance to work and contribute with open source technologies and most importantly an open source database PostgreSQL ! At OmniTI, if you don't have hesitation to learn new technlogy, you will have smooth learning experience alongside working with your known technology for the clients. Most importantly, you can have access to technology leaders in the office. Ironically, I never worked for single company longer than a year before joining OmniTI in my career but now I am looking forward to learn and contribute to FOSS community working with OmniTI.

Since joining, I haven't spent a day without learning a new thing . Are you bored at routine work? Come join OmniTI's adventure, here I work!

Bravo to OmniTI & PosgreSQL and FOSS community!!

What is pg_extractor ?

noreply@blogger.com (Denish Patel) — Thu, 05 Jan 2012 23:03:00 +0000

In my recent blog post, I wrote about PostgreSQL DBA Handyman toolset. In the list of tools, getddl is one of them. If you are using getddl to get DDL schema and track the daily changes in SVN for production databases, you should consider moving that process to use pg_extractor instead. pg_extractor is the more advance and robust tool for extracting schema as well data using pg_dump. Keith Fiske, an author of the tool, described tool in detail in his blog post. Thanks to Keith for making the schema extraction tool more robust and taking it to next level !

Hopefully, it will help you to have more control over your database in smarter way!

Faster & Better VACUUM FULL

noreply@blogger.com (Denish Patel) — Tue, 06 Dec 2011 17:02:00 +0000

                In presentation, I discussed in detail about Bloat issue in PostgreSQL and methods to remove Bloat from the tables/indexes. Now a days, PostgreSQL9.0 is common and the widely used version for the production use and it's vital to remind about changes in most important bloat removal tool called "VACUUM FULL". Until PostgreSQL 9.0, VACUUM FULL was tardy and DBA always stayed away from it and used CLUSTER instead. (Checkout presentation for difference between CLUSTER vs VACUUM FULL)
                The VACUUM FULL statement recovers free space from a table to reduce its size from bloated tables, mostly when VACUUM itself hasn't been run frequently enough. Before PostgreSQL 9.0 , it was tardy and slow because of the way it was executed: records were read and moved one by one from their source block to a block closer to the beginning of the table. Once the end of the table was emptied, this empty part was removed. This method was very inefficient: moving records one by one creates a lot of random IO. Additionally, during this reorganization, indexes had to be maintained, making everything even more costly, and fragmenting indexes. It was therefore advised to reindex a table just after a VACUUM FULL.
                Now, the VACUUM FULL statement, as of PostgreSQL 9.0, creates a new table from the current one, copying all the records sequentially. Once all records are copied, index are created back, and the old table is destroyed and replaced. This has the advantage of being much faster. VACUUM FULL still needs an EXCLUSIVE LOCK during entire operation. The only drawback of this method compared to the old one, is that VACUUM FULL can use as much as two times the size of the table and indexes on disk, as it is creating a new versions of it.

Let's compare run-time of VACUUM FULL on PostgreSQL 8.4 vs PostgreSQL 9.0

postgres=# create table vacuumtest(id int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "vacuumtest_pkey" for table "vacuumtest" CREATE TABLE postgres=# insert into vacuumtest select generate_series(1,10000000); INSERT 0 10000000 postgres=# delete from vacuumtest where id%4=0; DELETE 2500000 postgres=# vacuum vacuumtest; VACUUM
On 8.4:
postgres=# vacuum full vacuumtest ; VACUUM Time: 61418.197 ms postgres=# reindex table vacuumtest; REINDEX Time: 12212.815 ms

On 9.0:
postgres=# vacuum full vacuumtest ; VACUUM Time: 32640.714 ms
                Above results show that VACCUM FULL on PostgreSQL 9.0 is way faster than previous versions. Moreover, VACUUM FULL has couple of advantages over CLUSTER : it's faster than CLUSTER because it doesn't have to build new table using ORDER by clause & you can run VACUUM FULL on tables on which there isn't any index.
                If you are running any bloat removal tool on the production database, i would recommend you to revisit vacuum parameters and tighten them up a little that makes regular vacuum to run more frequent so it will help to reduce frequency of running more intrusive bloat removal tools!!

PostgreSQL Handyman Toolset

noreply@blogger.com (Denish Patel) — Fri, 11 Nov 2011 20:20:00 +0000

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.

The Scalability & Performance Conference : Surge 2011

noreply@blogger.com (Denish Patel) — Wed, 14 Sep 2011 19:39:00 +0000

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 !

When was my database started ?

noreply@blogger.com (Denish Patel) — Thu, 05 May 2011 16:31:00 +0000

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( 'postmaster.pid')).modification;
      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!

P90X your database!! presentation slides

noreply@blogger.com (Denish Patel) — Fri, 25 Mar 2011 19:08:00 +0000

I just gave talk on "P9ox your database!!" .. here are the slides..

Extreme Training Session at PgEast: P90X your Database!

noreply@blogger.com (Denish Patel) — Mon, 14 Mar 2011 12:00:00 +0000

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.

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

noreply@blogger.com (Denish Patel) — Mon, 17 Jan 2011 21:19:00 +0000

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

CREATE TABLE users
( user_id    NUMBER NOT NULL,
registration_date  DATE   NOT NULL,
notes      VARCHAR2(500))
PARTITION BY RANGE (registration_date)
(PARTITION users_q1 VALUES LESS THAN (TO_DATE('01/04/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION users_q2 VALUES LESS THAN (TO_DATE('01/07/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION users_q3 VALUES LESS THAN (TO_DATE('01/09/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION users_q4 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')) TABLESPACE users);

When will I be able to CREATE range partition table and manage with ALTER TABLE commands in PostgreSQL?

Possibility of Graceful Switchover?

noreply@blogger.com (Denish Patel) — Mon, 22 Nov 2010 03:23:00 +0000

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

PostgreSQL : Track functions to tune

noreply@blogger.com (Denish Patel) — Tue, 09 Nov 2010 02:22:00 +0000

Starting from PostgreSQL 8.4, there is a valuable addition in PostgreSQL features list: Per function statistics. Prior to this feature , there wasn't an easy way to find out most time consuming functions in the database.

For time spent on single call, it's easy to find out by executing function manually but it was too cumbersome and in some cases impossible to find out total time spent by function for the given time period. Enabling full logging and aggregating duration for each call by reading large log files was the only way to get that statistic. Now, you can find this details by querying a single view pg_stat_user_functions ! Awesome!

To enable function tracking you need to enable "track_functions" postgresql.conf parameter. The default is none, which disables function statistics tracking. Specify pl to track only procedural-language functions, all to also track SQL and C language functions.

#track_functions = none # none, pl, all
Once you have track_functions enabled in your database, you can get details by querying pg_stat_user_functions view:
select * from pg_stat_user_functions;

It provides following details:

funcid schemaname funcname calls total_time

self_time

Self time is the amount of time spent in the function itself, total time includes the time spent in functions it called. Time values are in milliseconds.

Here is the easy solution to track and find candidate that requires tuning!!

What's blocking your way?

noreply@blogger.com (Denish Patel) — Mon, 08 Nov 2010 17:45:00 +0000

If you've ever gotten a page about database connections are blocked, or phone call or email from an annoyed user whose transaction just won't go through, or from a developer who can't understand why application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked.

Omniti's labs project Tasty Treats for PostgreSQL provides useful tools for proactive database monitoring . email_locked_queries is the new addition to this toolset . This tool can be handy for proactive lock monitoring in your database to find the blocked and blocking sessions and it sends out email notification if the there is a connection waiting for lock(s) for more than 10 minutes. It's trivial to change the query if you want to be notified at different threshold.

Let's test the query tool and see the results by creating locking scenario:

postgres=# create table testlock(foo text,bar text); CREATE TABLE postgres=# insert into testlock values ('lock','me'); INSERT 0 1 postgres=# insert into testlock values ('find','me'); INSERT 0 1 postgres=# select * from testlock; foo | bar ------+----- find | me

lock | me

(2 rows)
Session 1:

postgres=# begin;
BEGIN
postgres=# update testlock set foo='hang' where foo='lock';
UPDATE 1

And Session 2, try to update same row:

postgres=# begin;
BEGIN
postgres=# update testlock set foo='escapeme' where foo='lock';

This statement will hang, blocked by the lock that Session 1 is holding on the row.

I ran the query tool and I got this in my email:

-[ RECORD 1 ]
--------+-----------------------------------------------------
locked_relation      |
locked_mode          | ShareLock
blocked_pid          | 9468
blocked_user         | postgres
blocked_statement    | update testlock set foo='escapeme' where foo='lock';
blocked_client_addr  |
blocked_query_age    | 00:10:28.98475
blocking_pid         | 8056
blocking_user        | postgres
blocking_statement   | IDLE in transaction
blocking_client_addr |
blocking_query_age   | 00:10:24.403767
blocking_xact_age    | 00:10:58.529881

Let me know if you have suggestions to make it more better.

Achieving PCI Compliance with PostgreSQL

noreply@blogger.com (Denish Patel) — Wed, 03 Nov 2010 21:58:00 +0000

Today, I gave Security talk on Achieving PCI Compliance at PgWest 2010, San Francisco. Slides are available to download.

Security & PgWest2010

noreply@blogger.com (Denish Patel) — Sun, 24 Oct 2010 15:35:00 +0000

Open Source databases still needs solid presence in enterprise world. One of the myths about open source databases is that they aren't secure. To eliminate this myth we have to share our experience related to security aspect of PostgreSQL database. At OmniTi , we help businesses to achieve PCI compliance or provide payment processor:PayPI . PayPI runs on PostgreSQL database.

I feel that Security aspect of the PostgreSQL database isn't marketed as it should be. Who doesn't want secured applications and databases ? The answer is everyone cares about security but we can't able to find many talks about security in the open source conferences. PgWest 2010 has wide variety of talks. Thanks to conference committee to include number of talks about security aspects of application and database. One of them is my session that will talk about achieving PCI compliance with PostgreSQL. If your system transmit, process or store credit card data then it should be PCI compliance system. Please don't miss this opportunity to share your knowledge & experience during the talk. Even if your system doesn't need PCI compliance, we can still discuss about security in general and share knowledge about features related to security.

If you are planning to attend PgWest 2010 at San Francisco from Nov 2, 2010 to Nov 4, 2010, looking forward to see you in-person.

Outlining Surge Conference Sessions

noreply@blogger.com (Denish Patel) — Wed, 01 Sep 2010 01:58:00 +0000

OmniTi's Surge conference is around the corner (Sept 30th) and there are plenty of industry leading speakers and most advance technology sessions to choose from list. Most probably I will be attending second day of two days conference. I spent a lot of time to decide on most interesting sessions to attend from wide variety of sessions. In the past, for other conferences, I found easy to choose from two parallel sessions but it's not the case at Surge. It's one of the most difficult task to choose one from two parallel talks.

I hope my list will help you to outline your sessions or at least encourage you to think now!

This is my outline of sessions to attend at Surge Conference on Day -2:

Enterprise solutions from commodity components: The Promise and the Peril - Bryan Cantrill
Don't bet the farm on your cache - Brian Akins
Top 10 Lessons Learned from Deploying Hadoop in a Private Cloud - Rod Cope
Design for Scale - Patterns, Anti-Patterns, Successes and Failures - Christopher Brown
From disaster to stability: scaling challenges of my.opera.com -Cosimo Streppone
Availability, the Cloud and Everything -Joe Williams

PostgreSQL Recovery Manager - PG_RMAN

noreply@blogger.com (Denish Patel) — Thu, 01 Apr 2010 14:42:00 +0000

During PgEast, I heard a lot about making PostgreSQL acceptable at more enterprise shops. I firmly believe that PostgreSQL has potential but lack of marketing perspective during the promotion of product and tools. Anyways, let's start creating/testing/using and telling new tools those can be attracted by C-Level people!!

If you have Oracle background , you must know Oracle Recovery Manager - RMAN. Oracle has RMAN since Oracle7i (may be longer ). It has been enhanced during each release. It is widely used program at most of the Oracle shops. Obviously, Oracle puts lots of marketing efforts around RMAN features per release.

I came across a Google Code project called pg_rman, which is developed by NTT Open Source Software Center. It looks promising by looking at features list. If i compare these features with Oracle, it includes features comparable to Oracle9i RMAN feature list.

pg_rman features:

Ease of use. Backup and restore can be done with just one command.
Online full backup, incremental backup, and archive backup.
Backup compression. Unused area in pages are removed and only actual data are compressed with gzip.
Automatic backup maintenance. Backup and archive WAL files older than specified days are deleted automatically.
Backup validation. Backup can be validated with CRC checks
No transaction lost in restore. Configuration file generator for point-in-time recovery is supported.

Let's put hands together to test a new tool and help making PostgreSQL more advanced with adding these rich tools!!

I am going to test the pg_rman pretty soon!!

RubyRep Presentation Slides

noreply@blogger.com (Denish Patel) — Sat, 27 Mar 2010 02:06:00 +0000

Guys,

Today I gave presentation @ PgEast2010 in Philadelphia. You can download slides Here

Thanks for attending or following it!!

Heading to PgEast

noreply@blogger.com (Denish Patel) — Thu, 11 Mar 2010 21:57:00 +0000

Guys,

I will be in Philadelphia on 25th and 26th March 2010 to attend PgEast PostgreSQL conference. Moreover, I will be speaking on RubyRep on 26th March 2010. If you are attending this conference , see you there!

Secure your web from SQL Injections

noreply@blogger.com (Denish Patel) — Wed, 20 Jan 2010 17:40:00 +0000

Last couple of months, I came across two incidents of SQL injections due to most common errors by IT professionals.

Both incidents are stunning and they were exploited by hackers with minimal effort. Little care during web application development could avoid both incidents.

If you are IT professional , you must know about SQL injections' cause and remedy.

wikipedia provides detailed definition :

"SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks."

Most common SQL injections are:

1. Incorrectly filtered escape characters

Most common is Quote or Special character handling

2. Incorrect type handling

Brazil incident fall into this category

3. Vulnerabilities inside the database server

WSJ incident fall into this category : BAD password!

4. Blind SQL injection

Result of this attack is not visible to hacker but it can help to exploit more SQL injections.
conditional responses
conditional errors
execute long running queries

You can avoid these catastrophic SQL injections by little care during the web application coding such as using bind variables, verify input values and take care of escaping characters. Database level security such as password and access control also help to avoid SQL injections.

I hope this will save you from future SQL injection :-)

Postgres 8.4 - Replace regular index with Text Based Index

noreply@blogger.com (Denish Patel) — Fri, 09 Oct 2009 14:43:00 +0000

I came across a nice post by Peter Eisentraut that shows how text based index can be used to do direct lookup on the text fields.

Quick test proves that..


postgres=# \d employees
  Table "public.employees"
Column |  Type   | Modifiers
--------+---------+-----------
emp_id | integer | not null
name   | text    |
Indexes:
   "employees_pkey" PRIMARY KEY, btree (emp_id)

postgres=# create index idx_employees_name on employees(name);
CREATE INDEX
postgres=# explain select * from employees where name like 'Denish%';
                       QUERY PLAN                        
-----------------------------------------------------------
Seq Scan on employees  (cost=0.00..25.38 rows=6 width=36)
  Filter: (name ~~ 'Denish%'::text)
(2 rows)

postgres=# explain select * from employees where name='Denish';
                                  QUERY PLAN                                   
---------------------------------------------------------------------------------
Bitmap Heap Scan on employees  (cost=4.30..13.76 rows=6 width=36)
  Recheck Cond: (name = 'Denish'::text)
  ->  Bitmap Index Scan on idx_employees_name  (cost=0.00..4.30 rows=6 width=0)
        Index Cond: (name = 'Denish'::text)
(4 rows)

postgres=# create index idx_employees_name_like on employees (name text_pattern_ops);
CREATE INDEX
postgres=# explain select * from employees where name like 'Denish%';
                                     QUERY PLAN                                     
--------------------------------------------------------------------------------------
Bitmap Heap Scan on employees  (cost=4.31..13.78 rows=6 width=36)
  Filter: (name ~~ 'Denish%'::text)
  ->  Bitmap Index Scan on idx_employees_name_like  (cost=0.00..4.31 rows=6 width=0)
        Index Cond: ((name ~>=~ 'Denish'::text) AND (name ~<~ 'Denisi'::text)) (4 rows)  postgres=# drop index idx_employees_name; DROP INDEX postgres=# explain select * from employees where name='Denish';                                       QUERY PLAN                                       --------------------------------------------------------------------------------------  Bitmap Heap Scan on employees  (cost=4.30..13.76 rows=6 width=36)    Recheck Cond: (name = 'Denish'::text)    ->  Bitmap Index Scan on idx_employees_name_like  (cost=0.00..4.30 rows=6 width=0)
        Index Cond: (name = 'Denish'::text)
(4 rows)

Above example shows that we don't need regular index on name field as text based index (idx_employees_name_like) fulfill the gap. However, if we do range query i.e select * from employees where name > 'denish' and name<'patel' then text based index will not be used. I don't think we ever run a range query on text based fields!.

xargs foo!

noreply@blogger.com (Denish Patel) — Tue, 01 Sep 2009 18:04:00 +0000

xargs foo helps a lot for running parallel operation..

This is the example , how we can rebuild indexes parallel on postgres

cat create_index.sql | xargs -d "\n" -L1 -P10 -I _CREATE_ psql -qAt -U postgres -p 5432 -d pagila -c "_CREATE_"

Yet Another PostgreSQL Replication Tool - RubyRep

noreply@blogger.com (Denish Patel) — Thu, 20 Aug 2009 15:22:00 +0000

One of the key features any enterprise considers when choosing a database technology for their architecture solution stack is that of replication. Oracle and MySQL both have built in replication solutions, but as of yet PostgreSQL doesn't support a built in replication solution. There are many replication solutions available however, and different companies are using different solutions customized for their needs.

Among all of the solutions, Slony is probably the most widely tested and deployed within organizations, although it does have the following limitations:

Replicated tables must have a unique or primary key

It does not support replication of large objects

Schema changes are not propagated (though they can be coordinated)

It does not support synchronizing databases outside of replication

There are limitations on version compatability; you can not replicate from PostgreSQL 8.2 to PostgreSQL 8.4 for example

It is more difficult to set up than many other replication solutions

One new alternative to Slony is a project known as RubyRep, which is designed to avoid some of the limitations of Slony. RubyRep provides both master-slave and master-master replication, and it works for PostgreSQL as well as MySQL. It is currently developed by Arndt Lehmann, a German who has been living since 2001 in Tokyo, Japan. He also provides great support to the RubyRep mailing list, especially for adding new features or fixing bugs.

RubyRep always operates on two databases. To make it simple to understand, the databases are referred to as "left" and "right" database respectively.

RubyRep's key features includes:

Simple configuration, complete setup can be done via single configuration file.

Simple Installation, if you have a JVM installed, then you just have to download and extract the files.

Platform Independent, it runs on Unix and Windows platform.

Table Design Independent, meaning that all commands work on tables no matter if they have a simple primary key (all data types acceptable), a combined primary key, or no primary key at all. It successfully processes multi-byte texts and "big" data types
It replicates tsvector datatype

In addition to the above, RubyRep actually provides three tools in one; a Compare, Sync, and Replication tools.

Compare

This tool scans corresponding tables of left and right database, looking for diverging data. Key features of the comparison tool are:

Different output modes, from a count of differences to full row dumps.

Low bandwidth mode available, reducing the number of round-trips so only actual differences go through the network.

A progress bar with estimated remaining amount of work.

Server load is targeted toward only the "right" database server.

In one test we ran, we compared two 50 million row tables in around 3 hours, without affecting production server load. This is accomplished by comparing rows in batches, and you can adjust the batch size in the configuration file.

Sync

The sync tool is used to synchronize data in corresponding tables of a left and right pair of databases. Key features of the sync tool are:

All features of the Compare tool also apply to syncs

Automatically orders table syncs to avoid foreign key conflicts.

You can configure the Sync policy to ignore deletes in left database, or to ignore creating records in right database, and other such combinations

Provides two prebuilt conflict resolution methods, either left db wins or right db wins

Custom conflict resolution methods specifiable via ruby code snippets

Merge decisions can optionally be logged in the rubyrep event log table.

Replicate

Of course RubyRep also provides a replication tool. Some of the key features of the replication tool include:

Automatically sets up all necessary triggers, log tables, etc.

Automatically discovers newly added tables and synchronizes the table content

Automatically reconfigures sequences to avoid duplicate key conflicts

Tracks changes to primary key columns

Can implement either master-slave or master-master replication

Prebuilt conflict resolution methods available include left or right wins, or earlier, later change wins

Custom conflict resolution specifiable via ruby code snippets

Replication decisions can optionally be logged in the rubyrep event log table

One of the problems common to replication solutions is that of setting up new nodes. With Slony, there are always some headaches caused by high load on master database server, as a result of the TRUNCATE/COPY cycle Slony goes through. In the case of RubyRep, most of the CPU load is on the slave server, and you can use the Sync command in advance before you start replicating database. RubyRep also provides some flexibility to ignore the Sync commands if you don't want to sync the database again.

RubyRep in action...

Help

 
; ./rubyrep --help
Usage: ./bin/rubyrep [general options] command [parameters, ...]
Asynchronous master-master replication of relational databases.
Available options:
       --verbose                    Show errors with full stack trace
   -v, --version                    Show version information.
       --help                       Show this message
Available commands:
 generate        Generates a configuration file template
 help            Shows detailed help for the specified command
 proxy           Proxies connections from rubyrep commands to the database
 replicate       Starts a replication process
 scan            Scans for differing records between databases
 sync            Syncs records between databases
 uninstall       Removes all rubyrep tables, triggers, etc. from "left" and "right" database

Generate configuration file

 
; ./rubyrep generate pagila.conf

Compare/Sync Example:

 
; cat pagila.conf
 RR::Initializer::run do |config|
 config.left = {
   :adapter  => 'postgresql', # or 'mysql'
   :database => 'pagila,
   :username => 'rubyrep',
   :password => 'rubyrep',
   :host     => '192.168.0.1',
   :port =>'5432'
 }

 config.right = {
   :adapter  => 'postgresql',
   :database => 'pagila',
   :username => 'rubyrep',
   :password => 'rubyrep',
   :host     => '127,0.0.1',
   :port     => '5483'
 }

 config.include_tables 'users'
 # config.include_tables /^e/ # regexp matching all tables starting with e
 # config.include_tables /./ # regexp matching all tables in the database
end

; ./rubyrep scan -d=keys -b -c pagila.conf  > users_diff.log
; cat users_diff.log
users             users .........................  1
---
:conflict:
- lastname: patel
 zipcode: "2096"
 ipaddress: 192.168.0.126
userid: 48212620
address: columbia

; ./rubyrep sync -c pagila.conf

Replication example:

By default, RubyRep runs in master-master replication mode, but you can adjust the following configuration setting to make it master-slave replication:


; cat pagila_replicate.conf
RR::Initializer::run do |config|
 config.left = {
   :adapter  => 'postgresql',
   :database => 'pagila,
   :username => 'rubyrep',
   :password => 'rubyrep',
   :host     => '192.168.0.1',
   :port =>'5432',
   :schema_search_path => 'public,pagila'
 }
 config.right = {
   :adapter  => 'postgresql',
   :database => 'pagila',
   :username => 'rubyrep',
   :password => 'rubyrep',
   :host     => '127,0.0.1',
   :port     => '5483',
   :schema_search_path => 'public,pagila'
 }
  config.include_tables /./ # regexp matching all tables in the database
  config.options[:auto_key_limit] = 60
  config.options[:adjust_sequences] = false
  config.options[:sequence_increment] = 1
  #Sync Policy: Changes in the right database will not be applied to the left database.
  config.options[:right_record_handling] = :ignore
  config.options[:sync_conflict_handling] = :left_wins
 # Additional logging
 config.options[:logged_replication_events] = [
                                        :ignored_changes,
                                        :ignored_conflicts
                                               ]
#ignore history tables
config.exclude_tables /_history/
config.exclude_tables 'test1'
config.exclude_tables 'pagila'
end

Detailed information for the each configuration setting can be found in the RubyRep documentation. There are also sample configuration files and a tutorial available too for getting familiar with each of the features RubyRep offers. Based on our initial testing, it should also be possible to upgrade some older PostgreSQL databases from 8.2 to 8.4.