Database Technologies

1. pg_describe_object:  In PostgreSQL 9.1, pg_describe_object function has been added. Using this function, user can get human readable string for understanding the pg_depend content as given below: postgres=# select classid, objid, objsubid, pg_describe_object(classid, objid, objsubid) from pg_depend where classid <> 0 and pg_describe_object(classi classid | objid | objsubid | pg_describe_object ---------+-------+----------+------------------------------------------------ 1247 | 16426 | 0 | type test_money 1247 | 16425 | 0 | type test_money[] 1259 | 16424 | 0 | table test_money 1259 | 16429 | 0 | index test_money_idx 2606 | 16431 | 0 | constraint uniq_constraint on table test_money 2. quote_all_identifiers (boolean) parameter:  With this parameter, User can force quoating of all identifiers in EXMPLAIN and in system catalog functions like pg_get_viewdef(). Example is given below: postgr

PostgreSQL 9.1 has come with new options and Improvement for Client Application. So, I thought to blog about those improvements.   Deprecated createlang/droplang: In PostgreSQL 9.1, createlang/dronlang is now deprecated commands. Since in PostgreSQL 9.1, for creating additional language, user has to use CREATE EXTENSION SYNTAX , there these binaries now issue command given below: CREATE EXTENSION plperl; CREATE EXTENSION plpyhthon; Added Features in psql: In PostgreSQL 9.1, following are the important improvement done in psql command:   1. Know about connection. Now psql has meta command \conninfo which gives detal about current connections. So, if you are connected to PostgreSQL database, then you can use command "\conninfo" to get detail about your connections, some example is given below: postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432". postgres=# \conni

Since, PostgreSQL 9.1 has already been released and there are lot of new features added in it, so, I thought to blog about each features. Today, I am discussing about Synchronous and Asynchronous Replication supported in PostgreSQL 9.1. For Asynchronous Replication, user can use following method: 1. Change Following Parameters in postgresql.conf file of Primary Database: archive_command = cp -i %p /Users/postgres/archive/%f archive_mode = on max_wal_senders = 3 # Maximum 'wal_senders', processes responsible for managing a connection with a standby server wal_keep_segments = # How many WAL segments (=files) should be kept on the primary, whatever may happen (you won't have to copy them manually on the standby if the standby gets too far behind) wal_level = hot_standby 2. Make Following changes in pg_hba.conf host replication postgres [Ipv4 address of Standby Server]/32 trust host replication postgres [Ipv4 address of Master Server]/32 tr

Today, I thought to share my Training slides on FTS (Full Text Search) in PostgreSQL 9.0. I have used this presentation on giving training. Enjoy :). Full Text Search Training

Depsez has Started a New project called Curo, which is currently made for Unix/Linux and is in initial phase. So, I thought to look at how it works and how normal user, who knows using psql, can benefit from it. Before Installing Curo, user has to make sure, he has postgreSQL client: psql and following package installed on his Linux/Unix System: 1. Bash 2. dialog If you have above required packages on your machine, then you can move for Curo installation. Curo Installation is very simple. Following are the steps which can be use to install Curo on Unix/Linux System: 1. Download the Curo from Following link: https://github.com/omniti-labs/curo Above link will give you file similar to given below: omniti-labs-curo-48a4f31.tar.gz 2. Untar the file using following command: tar -xvf omniti-labs-curo-48a4f31.tar.gz 3. Copy omniti-labs-curo-48a4f31 to users home directory as given below: cp omniti-labs-curo-48a4f31 ~/.curo 4. Now configure .inputrc and .editrc file as given

I had seen people ask Questions about finding the edb-jdbc/postgresql driver version with Database Version. So, I thought to give one java script which can be use to find Database Details with edb-jdbc version. Following is a JAVA Code which can be use to find the Database Version and EDB-JDBC/postgresql-jdbc Version: File Name: DBinfo.java import java.sql.*; public class DBinfo { public static void main(String[] args) { try { Class.forName("com.edb.Driver"); Connection con = DriverManager.getConnection("jdbc:edb://localhost:5444/edb", "enterprisedb","edb"); // Advanced Server Database Connection Information DatabaseMetaData dbmd = con.getMetaData(); System.out.println("===== Database info ====="); System.out.println("DatabaseProductName: " + dbmd.getDatabaseProductName() ); System.out.println("DatabaseProductVersion: " + dbmd.getDatabaseProduc

Logrotator is a small C program which is well written and available on most of the UNIX based OS. It allows automatic rotation, compression of logfiles and removal of old logfiles. This small file can be use to manage applications logfiles. It can also use for managing slony logfiles. For managing logfiles using logrotator user has to make a logrotator conf file, and can be scheduled in crontab for managing logile. Let see how you can make a logrotator configuration file for slony. I have slony master/slave daemon running on my slave maching has following location for slony logfiles: /opt/PostgresPlus/9.0AS/slony_test/slony1/node1 -- For master /opt/PostgresPlus/9.0AS/slony_test/slony1/node2 -- For slave. For managing logfile, user can make logrotator configuration file similar to given below: Configuration filename: /etc/logrotate.d/slonylogfiles "/opt/PostgresPlus/9.0AS/slony_test/slony1/node[12]/*.log" { rotate 5 mail vibhor.aim@gmail.com

Today, I though to mention some of Oracle workarounds which can be use for Migration of Oracle Database to PPAS 9.0(EnterpriseDB). 1. UTL_RAW.CAST_TO_RAW This is a Oracle Packaged Function, which can be use to convert VARCHAR2 value into RAW Value. Lets see its example in Oracle: SQL> select utl_raw.cast_to_raw('TESTING TESTING') from dual; UTL_RAW.CAST_TO_RAW('TESTINGTESTING') -------------------------------------------------------------------------------- 54455354494E472054455354494E47 Since PPAS 9.0, doesn't have this package in 9.0, therefore user can use postgresql function decode() for converting of varchar datatype to RAW/bytea data. Following is an example: edb=# select decode('TESTING TESTING','escape') from dual; decode ---------------------------------- \x54455354494e472054455354494e47 (1 row) which is a same output. 2. UTL_RAW.CAST_TO_VARCHAR2 Using this packaged function user can conver

This has been asked many times, so I thought to write steps for enabling ssl: Following are steps, which can be use to enable ssl in postgreSQL: 1. Generate a passphrase protected certificate using following command: openssl req -new -text -out cert.req Snapshot is given below: Generating a 1024 bit RSA private key ....................++++++ ...................................................++++++ writing new private key to 'privkey.pem' Enter PEM pass phrase: Verifying - Enter PEM pass phrase: ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name

One of my Colleague had asked how he can use Nagios for PostgreSQL Monitoring. So, I thought of writing steps to show the blog readers about nagios Configuration for PostgreSQL/PPAS. Following are the steps: 1. Download Nagios Core wget http://nagios.sourceforge.net/download/cvs/nagios-HEAD.tar.gz 2. For Installing Nagios, following packages would be required,so make sure you have them installed on your server. gcc make binutils cpp libpq-dev libmysqlclient15-dev libssl0.9.8 libssl-dev pkg-config apache2 libgd2-xpm libgd2-xpm-dev libgd-tools libpng12-dev libjpeg62-dev perl libperl-dev libperl5.8 libnet-snmp-perl 3. Create nagios user and group as given below: [root@localhost]# useradd nagios [root@localhost]# passwd nagios [root@localhost]# groupadd nagcmd [root@localhost]# usermod -G nagcmd nagios [root@localhost]# usermod -G nagcmd apache 4. Now, untar Nagios Core/Nagios Plugin using following command: tar -xvf nagios-HEAD.tar.gz 5. Now configure Nagios

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 por

Some Body has asked over forum to show the Instance Diagram of Postgres Plus Advanced Server as given Diagram of Oracle 9i/10g:  से SomeDiagrams So, I thought to show the Postgres Plus Advanced Server Instance Diagram. This Diagram has been made by one of my Colleague Raghevendra Rao: से SomeDiagrams Detail about the above processes is available on post: PostgreSQL/Postgres Plus advanced Server Process Details

I was going through the one of user posting over EnterpriseDB Forum, on which user has asked if there is any function exists in PostgreSQL/Postgres Plus Advanced Server, which can be use for Finding the number of segments of a table/relation on server. So, I thought to write about it. Currently, PostgreSQL/Postgres Plus Advanced Server doesn't come with any such function. However, if some one wants, he can write a plperl function which can find the number of segments of a table for him, as I have done in following sample code: Perl Program to get the number of segments of a table: CREATE OR REPLACE FUNCTION get_number_of_segments(text,text) returns table(tablename text, segments int) as $$ my $sql = "select 'ls -1 '||case reltablespace when 0 then setting||'/base/'||pg_database.oid||'/'||relfilenode||'.*|grep -v vm|grep -v fsm|wc -l' else (select spclocation||'/'||pg_database.oid||'/'||relfilenode||'.*|grep -v