Database Technologies

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

While configuring Ubuntu for PostgreSQL, when I was trying to connect Ubuntu server, I got following error message: edbs-MacBook-Pro:~ vibhor$ ssh vibhor@172.16.82.130 ssh: connect to host 172.16.82.130 port 22: Connection refused To resolve this issue, I have found that installation of Ubuntu 10.10 doesn't have openssh-server installed due to which Message was coming, so I have installed openssh-server using following command as given below: root@ubuntu:~# apt-get install openssh-server Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: linux-headers-2.6.35-22 linux-headers-2.6.35-22-generic Use 'apt-get autoremove' to remove them. Suggested packages: rssh molly-guard openssh-blacklist openssh-blacklist-extra The following NEW packages will be installed: openssh-server 0 upgraded, 1 newly installed, 0 to remove and 7 not upgraded. Need to get

pg_reorg is a utility which I had mentioned in my previous Blog. This utility can be use for : 1. Online Reorganising of Tables 2. Online VACUUM FULL (i.e removing bloats) (if table is having primary key) 3. Online Clustering of tables Since, in production environment, we cannot perform VACUUM FULL/CLUSTER, because it locks the table, therefore DBAs always need a way which they can use to perform maintenance activity without locking. So, I thought to use pg_reorg and compile it against Advanced Server. Advanced Server is a prebuilt binary, so we cannot compile any module or tool with it, without modifying the makefile. Following are some steps, if someone wants can try, to compile pg_reorg with Advanced Server. Donwload pg_reorg utility from following location: http://pgfoundry.org/frs/?group_id=1000411&release_id=1721 1. Execute pg_config of PPAS to find the gcc(location) which used to build binary of Advanced Server, as given below: pg_config |grep "CC =&quo

Some people ask this, How to use the Nested tables in PPAS? Since there is no Constructor Function Available and Oracle Compatibility Documentation is based on Procedure/Functions. So, I thought to give an overview on this. We can use nested tables by creating some manual Constructor and Using those constructor function to fetch the data or inserting the data: Following is an example: create type test_type as object (col1 varchar(200)); create type nested_type as table of test_type; create table test_nested(col1 nested_type); Now, Create some constructor Functions which can convert the data types as given below: --- Constructor Function of test_type CREATE OR REPLACE FUNCTION test_type(varchar) return test_type as declare result test_type; Begin result=row($1); return result; END; And --Constructor Function of nested_type CREATE OR REPLACE FUNCTION nested_type(test_type[]) return nested_type as counter integer:=0; rec test_type; result nested_type; BEGIN for rec i

This happened to me when I was trying to migrate the Oracle Database to Advanced Server & PostgreSQL. This kind of error comes if Data in Oracle have some junk/invisible Characters which conversion is unknown. select * from junk_character; 'ORA-29275: partial multibyte character' Oracle Descritption for this error is given below: ORA-29275: partial multibyte character Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input. Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW. Which doesn't give much information. To find the the column and rows which have those junk/invisible character user can try following trick. a. Select the data column wise as given below: select col1 from tablename; select col2 from tablename; If you are sure about the columns which has those junk charac