Database Technologies

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

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