Database Technologies

In the series of blogging about new features in 9.3, today, I thought about blogging new functions and improvements coming in PostgreSQL. Lets look whats new in 9.3, in terms of in build functions: 1. New in one array functions for one dimensional array PostgreSQL 9.3, is coming with new functions which can help users to manipulate one dimensional arrays by calling simple functions at the place of crafting their own functions and following some methods to do the modification in it. i. array_remove function This is a new function added in 9.3, which provides ability for removing the elements from array. Function takes two arguments :   a. One dimensional array from which user wants to remove elements   b. element value which user wants to remove . Syntax of this function is given below: ARRAY_REMOVE( , element) Example of array_remove is given below: postgres=# select array_remove(ARRAY['First','Second','Delete','Four'],'D

In PostgreSQL 9.2, community has added a function pg_xlog_location_diff(), which is very useful for finding the difference between two xlog location in bytes and also useful for monitoring replication. Detail of this function is given in following link: http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP However this function is not available for users of PostgreSQL/PPAS 9.0/9.1 users. So, I thought to write same function plpgsql so, that users can take benefit of same in 9.0/9.1. Before using formula and developing function, lets understand what is xlog and offset. Let's consider user has used function pg_current_xlog_location() function and he gets following information: worktest=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 1/D1012B80 (1 row) In above, first field before forward slash is the hexadecimal value of logical xlog file and second field i.e. D1012B80 is hexadecimal offset inside

PostgreSQL has some useful functions which can be use to know about the privilege of a user on a particular Database object. Those functions is available in following link: http://www.postgresql.org/docs/9.1/static/functions-info.html Functions has_*_privilege in PostgreSQL/PPAS is good to know about privilege a user has on one database objects and these function returns boolean value true or false. Since, DBAs/Users are interested in listing objects and privileges of a Database User and currently PostgreSQL doesn't have a view, which DBA can use to list users privileges on objects for a particular database. Therefore, I thought about making some functions, which can be used to list users privileges, based on what is available in PostgreSQL/PPAS 9.1. These are basic functions and can be expanded, as per need, to show more privileges like WITH GRANT OPTION. Following are functions which can use to get the privileges of a particular user: 1. Function for table privileges: C

Great News is Postgres Plus Advanced 9.1 is now available for users. So, I thought to write something about Virtual Private Database features, which is part of 9.1 Virtual Private Database (VPD) is a feature which enables Administrator to create security around actual data (i.e row/columns) so that multiple users can access data which is relevant to them. Steps which is require to create Virtual Private database is given below: 1. Create an Application Context 2. Create security policies functions 3. Apply security policies to tables Lets see how user can implement it in Advanced Server. 1. Setup an environment as given below: CREATE user merry identified by edb; CREATE user john identified by edb; CREATE TABLE public.john_merry(userid varchar2(200),val numeric); grant select,update,delete,insert on john_merry to john; grant select,update,delete,insert on john_merry to merry; 2. Now create a Policy Function as given below: CREATE OR REPLACE FUNCTION verify_user ( p_sche

PPAS 9.1 Beta 1 is released. So, I thought to write about it and New Features which are coming. Best part of PPAS 9.1 is that it has all features of PostgreSQL 9.1, which I have already discribed in my series of 9.1. Links are given below: 1. PostgreSQL Object Manipulation Feature s 2. New Replication and Recovery Feature s 3. Queries improvement of Core PostgreSQL 9.1 4. Updateable Views using INSTEADOF Trigger in PostgreSQL 9.1 5. Utility Operation Improvements 6. Upsert/Merge using Writeable CTE 7. New Functions/Improvement 8. Object Functions Improvement in PostgreSQL 9.1 9. Client Application Improvement in PostgreSQL 9. 1 10. Asynchronous/Synchronous Replication There are many other features in PostgreSQL 9.1, which requires some time to write with example. However, below is list of those Improvements: 1. Unlogged Tables 2. FOREACH IN ARRAY in PL/pgSQL 3. Pl/Perl Improvement:     a. Record type support     b. Pl/perl array argument map to Perl Argument.

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

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