Tuesday, May 24, 2011

Remote Log Reading in PostgreSQL 8.4 and 9.0.

I was going through the one of the important feature (SQL/MED) which is coming feature of PostgreSQL 9.1.

This feature enables user to access any external file, using SQL, from PostgreSQL Terminal.

Magnus hagander's has also blogged about this feature and he has shown "How to access the PostgreSQL logfile using SQL/Med".

After going through his blog, I thought to do same with PostgreSQL 8.4/9.0 using plperl program.

Following are the steps, which can be use to access the postgreSQL csv log file:
1. Change the log_destination parameter in postgresql.conf file of PG Instance Directory, to create csvlog of postgreSQL log. As given below.
log_destination = 'stderr,csvlog' 
2. Reload the changes in PostgreSQL, using following command:
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
3. Connect to PostgreSQL database using psql command and Create following data type
CREATE type pg_log_type as ( log_time   timestamp(3) with time zone ,
 user_name   text ,
 database_name   text ,
 process_id   integer ,
 connection_from   text ,
 session_id   text ,
 session_line_num   text ,
 command_tag   text ,
 session_start_time   timestamp with time zone ,
 virtual_transaction_id   text ,
 transaction_id   text ,
 error_severity   text ,
 sql_state_code   text ,
 message   text ,
 detail   text ,
 hint   text ,
 internal_query   text ,
 internal_query_pos   text ,
 context   text ,
 query   text ,
 query_pos   text ,
 location   text ,
 extra   text
 );
4. Now, Create following plperl function to read the pg_log files:
CREATE OR REPLACE FUNCTION read_pg_log(text) returns setof pg_log_type as    
$$    
## Written by Vibhor to Read external file 
use strict;
use warnings;
use CSV;
my $file = $_[0];
my $badfile = ">>"."$_[0]".".bad";
my $csvdata = "";
my $numq = 0;
my $dquote = "\"";
open (CSV, "<", $file);
while () {
       my    $pos = -1;
    while (($pos = index($_, $dquote, $pos)) > -1) {
        $pos++; # step over the double quote
        $numq++;
    };

 $csvdata = $csvdata . $_; # linefeed preserved
            if ( ! ($numq % 2) ) {
           my @line = CSVsplit($csvdata);
           return_next({log_time => $line[0] , user_name => $line[1] , database_name => $line[2] , process_id => $line[3] , connection_from => $line[4] , session_id => $line[5] , session_line_num => $line[6] , command_tag => $line[7] , session_start_time => $line[8] , virtual_transaction_id => $line[9] , transaction_id => $line[10] , error_severity => $line[11] , sql_state_code => $line[12], message => $line[13], detail => $line[14], hint => $line[15], internal_query => $line[16] , internal_query_pos => $line[17] , context => $line[18], query => $line[19] , query_pos => $line[20] , location => $line[21] , extra => $line[22] });
 # reset trans-loop iterator variables
        $csvdata = "";
        $numq = 0;
    };
    }
close CSV; 
return undef;    
$$ language plperlu; 

Please note: Before using the above function, please make sure, you have CSV Module installed in perl.

Following are some snapshots of using the function:
postgres=# select log_time, log_time-lag(log_time,1) OVER () from read_pg_log('/Library/PostgresPlus/9.0SS/data/pg_log/postgresql-2011-05-23_221143.csv') limit 10 offset 10;
           log_time            |   ?column?   
-------------------------------+--------------
 2011-05-24 01:42:29.974+05:30 | 00:00:05.006
 2011-05-24 01:42:34.982+05:30 | 00:00:05.008
 2011-05-24 01:42:39.989+05:30 | 00:00:05.007
 2011-05-24 01:42:44.995+05:30 | 00:00:05.006
 2011-05-24 01:42:50.113+05:30 | 00:00:05.118
 2011-05-24 01:42:50.145+05:30 | 00:00:00.032
 2011-05-24 01:42:55.152+05:30 | 00:00:05.007
 2011-05-24 01:43:00.16+05:30  | 00:00:05.008
 2011-05-24 01:43:05.168+05:30 | 00:00:05.008
 2011-05-24 01:43:10.175+05:30 | 00:00:05.007
(10 rows)
To view all the error logged, following command can be use:
postgres=# select error_severity,message from read_pg_log('/Library/PostgresPlus/9.0SS/data/pg_log/postgresql-2011-05-23_221143.csv') where error_severity like '%ERROR%' limit 5
postgres-# ;
 error_severity |                message                 
----------------+----------------------------------------
 ERROR          | syntax error at or near "("
 ERROR          | syntax error at line 12, near "line ["+
                | syntax error at line 35, near "}      +
                | else"                                 +
                | syntax error at line 38, near "}      +
                | }"                                    +
                | syntax error at line 42, near ";      +
                |  }"
 ERROR          | syntax error at line 12, near "line ["+
                | syntax error at line 35, near "}      +
                | else"                                 +
                | syntax error at line 38, near "}      +
                | }"                                    +
                | syntax error at line 42, near ";      +
                |  }"
 ERROR          | syntax error at line 12, near "line[" +
                | syntax error at line 13, near "}      +
                | else"                                 +
                | syntax error at line 16, near "}      +
                | }"                                    +
                | syntax error at line 20, near ";      +
                |  }"
 ERROR          | syntax error at line 12, near "line[" +
                | syntax error at line 13, near "}      +
                | else"                                 +
                | syntax error at line 16, near "}      +
                | }"                                    +
                | syntax error at line 20, near ";      +
                |  }"

Sunday, May 15, 2011

PostgreSQL Database Link to Oracle Database on Linux

I have seen question, like "How to make Database Link from PostgreSQL to Oracle?", always floats in PostgreSQL Community Forum. So, I thought to do some research on it and write a Blog.

Cybertec (One of The PostgreSQL Database Company) has released a PostgreSQL Module ODBC Link, using which user can make Database link to any other database, including Oracle, MS SQL Server, PostgreSQL etc, which have ODBC compliant Data source.

Lets see how you can make ODBC Connection from PostgreSQL to any ODBC compliant data source and fetch data.

Installation of this module is very simple. Following are the steps which user can follow:
1. Install the unixODBC driver on your linux machine. user can use following useful link for Downloading and Installing the unixODBC Driver:
http://www.unixodbc.org/
2. Download ODBC-Link from following location:
http://www.cybertec.at/download/odbc_link/ODBC-Link-1.0.4.tar.gz
3. Untar the downloaded file as given below:
tar -zxvf ODBC-Link-1.0.4.tar.gz
4. Compile the source code as given below:
make USE_PGXS=1
make USE_PGXS=1 install
Note:: Before running above command, don't forget to set pg_config binary's path in PATH Environment Variable.
5. Execute following odbclink.sql file in PostgreSQL database to create require functions to access the Other Database, as given below:
$ psql -f $PGSHARE/odbclink.sql dbname
After installing the ODBC-Link for PostgreSQL, User has to install the Oracle Instant Client which comes with Oracle ODBC Driver.
Since I have Linux System so, I have installed Oracle Instant Client for linux from following link:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
After installing the ODBC Driver, User has to configure ODBC Data source. To get the unixODBC Configuration files, user can use following command:
$ ./odbcinst -j
unixODBC 2.2.12
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini
Now update the following two UnixODBC Configuration Files:
File: /etc/odbc.ini
[XE]
Description  = Oracle ODBC
Driver   = XE
Trace   = yes
TraceFile  = /tmp/odbc_oracle.log
Database  = //ubuntu:1521/XE
UserID   = hr
Password  = hr
Port   = 1521

File: /etc/odbcinst.ini
[XE]
Description = Oracle ODBC Connection Driver
Driver  = /opt/Oracle_Client/lib/libsqora.so.10.1
Debug  = 0
CommLog  = 1

After making above entries for Oracle ODBC Data source. Now restart the PostgreSQL Instance with following environment variables:
export LD_LIBRARY_PATH=/opt/Oracle_Client/lib
export TWO_TASK=//ubuntu:1521/XE
pg_ctl -D "PostgreSQL data Directory" restart -mf

Now, we are setup for using ODBC Link from PostgreSQL to Oracle Database.

Following are some snapshots:
1. Create ODBC Link as given below:
edb=# select odbclink.connect('DSN=XE');
 connect 
---------
       1
(1 row)

edb=# 

2. Now, using the ODBC Link access the Oracle Database table:
edb=# select * from odbclink.query(1,'SELECT ename,empno FROM emp') as t( ename text, d numeric);
 ename  |  d   
--------+------
 SMITH  | 7369
 ALLEN  | 7499
 WARD   | 7521
 JONES  | 7566
 MARTIN | 7654
 BLAKE  | 7698
 CLARK  | 7782
 SCOTT  | 7788
 KING   | 7839
 TURNER | 7844
 ADAMS  | 7876
 JAMES  | 7900
 FORD   | 7902
 MILLER | 7934
(14 rows)

Similarly, user can make the ODBC link from PostgreSQL to Other Databases too. Interesting :).

Monday, May 9, 2011

First Interaction with NOSQL Database (MongoDB)

I heard a lot about NoSQL Databases. So, I thought to try out this database. I found it simple to use and easy to configure.

To Download MongoDB, user can use following link:
http://www.mongodb.org/downloads

Installation is easy. If you are planning to use pre-built binaries, then you can untar the binaries and can start using it.

To start. First Create a data directory as given below:
mkdir -p mongodb/data
Initialize the Mongodb as given below:
"MongoDB Installation Directory"/bin/mongod --dbpath mongodb/data --logpath mongodb.log

Default port on which MonogoDB runs is 27017

To Connect with MongoDB instance, user can use following command:
./mongo 127.0.0.1:27017/foo 
Or you can use following options:
usage: ./mongo [options] [db address] [file names (ending in .js)]
db address can be:
  foo                   foo database on local machine
  192.169.0.5/foo       foo database on 192.168.0.5 machine
  192.169.0.5:9999/foo  foo database on 192.168.0.5 machine on port 9999
options:
  --shell               run the shell after executing files
  --nodb                don't connect to mongod on startup - no 'db address' 
                        arg expected
  --quiet               be less chatty
  --port arg            port to connect to
  --host arg            server to connect to
  --eval arg            evaluate javascript
  -u [ --username ] arg username for authentication
  -p [ --password ] arg password for authentication
  -h [ --help ]         show this usage information
  --version             show version information
  --verbose             increase verbosity
  --ipv6                enable IPv6 support (disabled by default)

Since, I have already created the mongodb and my mongodb instance is ready to use, so lets see how you can work with MongoDB.

As we know, its a NoSQL Database, so SQL syntax would not be working here, therefore I would be showing the SQL Statement and Respective mongodb command:

Snapshot of connecting to mongoDB is given below:
edbs-MacBook-Pro:bin vibhor$ ./mongo --shell mydb
MongoDB shell version: 1.8.1
connecting to: mydb
type "help" for help
>

Lets see the interaction with mongoDB:
SQL: CREATE TABLE test (a Number, b text)
MogoDB Syntax:
> db.createCollection("user", {capped:true, size:100000});
{ "ok" : 1 }

SQL: INSERT INTO test values(123,'Check1');
MongoDB Syntax:
db.test.insert({a:1,b:'check1'})
SQL: SELECT a,b FROM test;
MongoDB Syntax:
> db.test.find({},{a:1,b:"check1"});
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }

SQL: SELECT * From Test;
MongoDB Syntax:
> db.test.find();
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }

SQL: SELECT * FROM test WHERE a=1;
MongoDB Syntax:
> db.test.find({a:1},{});
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }

SQL: SELECT a,b FROM users WHERE a=2;
MongoDB Syntax:
> db.test.find({a:2},{a:1,b:1});
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
SQL: SELECT * FROM test order by a asc;
MongoDB Syntax:
> db.test.find().sort({a:1});
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
> 
SQL: SELECT * FROM test order by a dsc;
MongoDB Syntax:
> db.test.find().sort({a:-1});
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "check1" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "check1" }

SQL: SELECT distinct b FROM test;
MongoDB Syntax:
> db.test.distinct("b");
[ "check1", "check2" ]
SQL: UPDATE test SET b='Check2' WHERE a=1;
MongoDB Syntax:
> db.test.update({a:1}, {$set:{b:"Check2"}}, false, true);
> db.test.find({},{a:1,b:1});
{ "_id" : ObjectId("4dc8626b9d89210e59c5160c"), "a" : 1, "b" : "Check2" }
{ "_id" : ObjectId("4dc862cdc0f16496e5e0f8f1"), "a" : 1, "b" : "Check2" }
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
SQL: DELETE FROM test WHERE a=1;
MongoDB Syntax:
> db.test.remove({a:1});
> db.test.find({},{a:1,b:1});
{ "_id" : ObjectId("4dc86432c0f16496e5e0f8f2"), "a" : 2, "b" : "check2" }
SQL: SELECT COUNT(a) from text
MongoDB Syntax:
> db.test.find({a: {'$exists': true}}).count();
1
> 

Following is a chart link which contains SQL Mapping with MongoDB Commands:
http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart