Database Technologies

As discussed in previous Blogs about Hot Standby in following links: 1. Monitoring Hot Standby 2. HotStandby in PostgreSQL 9.0. I came up with the following Monitoring Script for Hot Standby. #!/bin/bash # Filename: monitor_hotstandby # Usage is $0 -m master:port -s slave:port -b "PostgreSQL Bin Directory" # Author: Vibhor Kumar # Date: Jan 4th 2011 # E-mail: vibhor.aim@gmail.com while getopts "m:s:b:" opt;do case $opt in m) h1=`echo $OPTARG|cut -d":" -f1` p1=`echo $OPTARG|cut -d":" -f2`;; s) h2=`echo $OPTARG|cut -d":" -f1` p2=`echo $OPTARG|cut -d":" -f2`;; b) PGHOME="$OPTARG" esac done PSQL=$PGHOME/psql function usage() { if [ -z $h1 ];then echo "USAGE: " echo "$0 -m master:port -s slave:port -b pg bin directory" exit 1 fi if [ -z $h2 ];then echo "USAGE: " echo "$0 -m master:port -s slave:port -b pg bin directory" exit 1 ...

This is a Question which I had seen people generally ask, when they Move their DBs to PG or PGPlus Advanced Server and Look for Workaround of Oracle Feature of Traverse of Directed Graph in PG. Oracle Provides CONNECT BY NOCYCLE for traversing a Directed Graph. As mentioned in Following link: http://www.adp-gmbh.ch/ora/sql/connect_by_nocycle.html However, CONNECT BY NOCYCLE implementation, some times miss some paths for Traversing Graph as you can see in following Example: create table directed_graph ( node_from char(1), node_to char(1) ); insert into directed_graph values ('A', 'C'); insert into directed_graph values ('A', 'B'); insert into directed_graph values ('B', 'E'); insert into directed_graph values ('C', 'H'); insert into directed_graph values ('H', 'I'); insert into directed_graph values ('I', 'D'); insert into directed_graph values ('D', 'F'); insert...

Lot of People think that COPY is only a Server Based Command. However its not completely true. User can copy the data of a table at client side using COPY Command. The Difference is only in syntax usage. User has to use backSlash before COPY Command as given below: testdb=# select * from test; id ---- 1 2 3 (3 rows) testdb=# \copy test to '/tmp/test.copy' testdb=# \q We can see output of a file at Client side as given below: cat /tmp/test.copy 1 2 3 Similarly User can also copy the data in a file to Server using \COPY as given below: \COPY test from '/tmp/test.copy' iclive1460=# \COPY test from '/tmp/test.copy' iclive1460=# select * from test; id ---- 1 2 3 1 2 3 (6 rows) Note:: One should remember while using \COPY Command, one should not terminate command with semicolon ';'

SQL Injection is method of Hacking Data inside the Database. Using SQL Injection, Hackers do multiple Attempt of SQLs to understand the Structure of tables and Try to get the hint of data inside a table. For example, if a Hacker knows that UserName and Password of a Website are stored in a database table say member, then he can write query something like given below to fetch all the data inside the function as given below: select * from tablename where 1=1; Or select * from tablename where 'x'='x'; Similarly, suppose Hacker wants to know the structure (Columns) of any table, then he can try multiple attempts of finding the column name by using the HAVING OR GROUP BY CLAUSE and can find the Columns Names Based on Error. As given below: postgres=# select * from test_sql_injection group by 1 having 1=1; ERROR: column "test_sql_injection.col" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select * from test_sql_injection gro...

One of my colleague has asked about this. She was doing some testing and wanted to drop a user which has objects across the databases. While dropping user/role, DBA/Admin will get following error messages: postgres=# drop user test; ERROR: role "test" cannot be dropped because some objects depend on it DETAIL: 2 objects in database test Above error messages gives the sufficient information to Admin that there are some objects depend/Owned by the user exists in other database. For dropping such user, there are two methods: 1. Reassign all the objects owned by the user to some other user and then drop the user. Above is very useful, if employee, who left the company, has written some Procedure/objects, which is getting used in Application/process. Command Which can be are following: REASSIGN OWNED BY old_role to new_role; DROP USER old_role; Note:: reassign command need to be executed for all the databases under one PG instance. 2. First Drop all the objects own...

On production System, some times DBAs have to rebuild the indices, since, Rebuilding indices lock the table therefore DBA can use Option CONCURRENTLY. For normal Indices CONCURRENTLY is a best option, however Primary Key Indices Rebuild require Lock on table. To overcome this issue, I have made following function: SWAP Index with Pkey: CREATE OR REPLACE FUNCTION swap_for_pkey(text,text,text) returns integer AS $$ DECLARE cmd text; oid1 integer; oid2 integer; filenode1 integer; filenode2 integer; relation text; BEGIN select oid::integer into oid1 from pg_class where relname=$2 and relnamespace = (select oid from pg_namespace where nspname=$1); RAISE NOTICE 'PKEY OID: %',oid1; select relfilenode::integer into filenode1 from pg_class where oid=oid1; select oid::integer into oid2 from pg_class where relname=$3 and relnamespace = (select oid from pg_namespace where nspname=$1); RAISE NOTICE 'PKEY OID: %',oi...

For using these query DBA has to install the adminpack contrib module of PostgreSQL. If you are using one-click installer then user can find the adminpack.sql in /PostgreSQL Installation Directory/share/postgresql/contrib directory. Following are some SQLs Based on adminpack contrib module DBA can use to find the last logfile, size of the logfile and can ready using PG Client applications (psql,pgAdmin etc.) SELECT * FROM ( SELECT pg_ls_dir('pg_log')) AS t (filename)ORDER BY 1 DESC LIMIT 1 SELECT size FROM pg_stat_file('pg_log/postgresql-2010-05-13.log') SELECT pg_read_file('pg_log/postgresql-2010-05-13.log', 0, 21604) Sometimes, user doesn’t have the access to the Server and for reading the PG logfile user has to login on the server. Now they don;t have to. If you have adminpack.sql installed in PostgreSQL user can easily read the last logfile by creating following function: CREATE OR REPLACE FUNCTION pg_read_last_logfile() returns text AS $$ DECLARE...