Database Technologies

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

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

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

In my Blog on get_number_of_segments, I had used the simple query to find the number of segments. Since, from PostgreSQL 9.0, system admin function pg_relation_filepath can be use to find the location of relfilenode, therefore I thought to reduce the code of get_number_function and use the pg_relation_filepath function. Following is a modified plperl function get_number_of_segments(text) using system admin function pg_relation_filepath(relation reglcass): CREATE OR REPLACE FUNCTION get_number_of_segments(text) returns table(tablename text, segments int) as $$ my $sql = spi_prepare("select 'ls -1 '||pg_relation_filepath(\$1)||'.*' as cmd",'TEXT'); my $q = spi_query_prepared($sql,$_[0]); my $rv = spi_fetchrow($q); my $cmd = $rv->{rows}[0]; my $command = $cmd -> {location}; open(CMD, "$command |"); $count = ; close(CMD); @count=split(/[\n\r]+/,$count); return_next...

Till PostgreSQL 9.0, PostgreSQL doesn't have any feature which can be use to read the external file (a concept of External Table). External Table is a feature of Database using which, any one can read the flat files as if it were in a table in the database. So, I thought give an idea how user can implement this in Database using plperl. Let's see how you can implement it. I have a flat file, which has following data: Filename: test.data Location: /tmp/test.data 1,Check1 2,Check2 3,check3 4,check4 5a,check5 5a,check5 Since, I know this flat file is having two field (numeric and text), so,I can write a plperl function, which can read the file and can get the attribute(column values) of a line on the basis of passed delimiter, with this if there is any bad record, that will go to the bad file for verification if required. Following is a sample plperl function : CREATE OR REPLACE FUNCTION read_external(text,text) returns table(id numeric, col1 text) as $$ ## W...

Following are some of the important Process of PostgreSQL/Postgres Plus Advanced Server. vibhore@ubuntu:~/edb/edb-postgres$ ps -eaf|grep postgre 1002 1080 1 0 06:07 ? 00:00:00 /opt/PostgresPlus/8.4AS/bin/edb-postgres -D /opt/PostgresPlus/8.4AS/data (Mandatory) 1002 1101 1080 0 06:07 ? 00:00:00 postgres: logger process (Mandatory) 1002 1103 1080 0 06:07 ? 00:00:01 postgres: writer process (Mandatory) 1002 1104 1080 0 06:07 ? 00:00:01 postgres: wal writer process (Mandatory) 1002 1105 1080 0 06:07 ? 00:00:00 postgres: autovacuum launcher process (Optional if autovacuum is on) 1002 1106 1080 0 06:07 ? 00:00:00 postgres: stats collector process (Mandatory) Above is an snapshot of the processes which exist in PostgreSQL/Postgres ...

Generally DBA, has to login in system to monitor the activities by querying the pg_stat_activity veiw. However if someone wants to do continous monitor for every 2 seconds. He has to run SQL against pg_stat_activity every 2 seconds. So, For such activity, I have made following shell script which can be use with watch command on linux to monitor the activities. #!/bin/bash ## Prepared by Vibhor Kumar (EDB). Q="select procpid,now() - query_start as long, waiting, current_query from pg_stat_activity where procpid pg_backend_pid() limit 10;" while getopts "b:U:d:p:" opt; do case $opt in b) BIN=$OPTARG;; U) PGUSER=$OPTARG;; d) DB=$OPTARG;; p) port1=$OPTARG;; *) echo "Usage: $0 -b -d -U -mp -sp ";; esac done if [ -z $BIN ];then echo "Usage: $0 -b bin directory -d database name -U user -p port" exit fi if [ -z $PGUSER ];then echo "Usage: $0 -b bin directory -d database name -U user -p port" ...