infinite possibility

PostgreSQL Installer provided by EnterpriseDB is so handy and make your life very easy for OS X Desktop installation.

Maybe I just don’t know the handy utility but I wrote a shell script to control/display various tasks hence I wanted to share this with you.

The utility contains: start | stop | restart | stopfast | stopnow | status | vac_status | show_connections | version | restore backup

NODE_NAME=`uname -n`

NODE_NAME=${NODE_NAME%%\.*local}

NODE_NUMBER=${NODE_NAME##*SQL-}

PGBIN=/Library/PostgreSQL/9.1/bin

PGDATA=/Library/PostgreSQL/9.1/data

pidof(){

# usage: `pidof`

ps -ef | grep postgres | awk '{if ($3==1)print $2}'

}

start(){

pid=`pidof`

if [ "$pid" == "" ] ; then

echo "Starting postgres..."

su - postgres -c "${PGBIN}/pg_ctl -w start -D ${PGDATA} -l ${PGDATA}/pg_log/startup.log"

else

status

fi

}

stop(){

pid=`pidof`

if [ "$pid" != "" ] ; then

echo "Stopping postgres..."

su - postgres -c "${PGBIN}/pg_ctl stop -m smart -w -D ${PGDATA}"

else

echo "Postgres is not running"

fi

}

stopfast(){

pid=`pidof`

if [ "$pid" != "" ] ; then

echo "Stopping postgres..."

su - postgres -c "${PGBIN}/pg_ctl stop -m fast -w -D ${PGDATA}"

else

echo "Postgres is not running"

fi

}

stopnow(){

pid=`pidof`

if [ "$pid" != "" ] ; then

echo "Stopping postgres..."

su - postgres -c "${PGBIN}/pg_ctl stop -m immediate -w -D ${PGDATA}"

else

echo "Postgres is not running"

fi

}

status(){

pid=`pidof`

if [ "$pid" != "" ] ; then

echo "postgres is running"

echo `ps -ef | grep postgres | grep -v grep | awk '{if ($3==1) print "Postgres: " $8 ", Data Directory: " $10}'`

else

echo "postgres is not running"

fi

}

vac_status(){

su - postgres -c "${PGBIN}/psql -c \"select relname, last_autovacuum, last_autoanalyze from pg_stat_user_tables where last_autovacuum IS NOT NULL order by last_autovacuum desc\""

}

show_connections(){

su - postgres -c "${PGBIN}/psql -c \"SELECT datname, usename, procpid, client_addr, waiting, query_start, current_query FROM pg_stat_activity;\""

}

version(){

su - postgres -c "${PGBIN}/psql -c \"SELECT version()\""

}

restore_bak(){

echo "backup file path : "

read BACKUPFILEPATH

echo "database name : "

read DBNMAE

chmod -R 777 $BACKUPFILEPATH

cat ${BACKUPFILEPATH} | gunzip | ${PGBIN}/psql -U postgres ${DBNMAE}

${PGBIN}/psql -d ${DBNMAE} -U postgres < /users.sql

}

case "$1" in

start)

start

;;

stop)

stop

;;

status)

status

;;

restart)

stop

start

;;

stopfast)

stopfast

;;

stopnow)

stopnow

;;

vac_status)

vac_status

;;

show_connections)

show_connections

;;

restore_bak)

restore_bak

;;

version)

version

;;

*)

echo "Usage: $0 { start | stop | restart | stopfast | stopnow | status | vac_status | show_connections | version | restore_bak }"

exit 1

esac

exit 0

apgdiff (Another PostgreSQL Diff Tool) is so easy to setup and use.

# you need at least Java 1.6

$ java -version

java version “1.6.0_29″
Java(TM) SE Runtime Environment (build 1.6.0_29-b11-402-10M3527)
Java HotSpot(TM) 64-Bit Server VM (build 20.4-b02-402, mixed mode)

# download binary and that’s it.

$ curl -O http://apgdiff.startnet.biz/download/apgdiff-2.3-bin.zip
$ unzip apgdiff-2.3-bin.zip

# create SQL dump files

$ pg_dump –host HOST_IP –port 5432 -U DB_USER -s -f new.sql ALPHA_DB
$ pg_dump –host HOST_IP –port 5432 -U USER -s -f old.sql PROD_DB

# generate upgrade statement

$ PATH_TO_APGDIFF=/tmp/apgdiff
$ java -jar $PATH_TO_APGDIFF/apgdiff-2.3.jar –ignore-start-with old.sql new.sql > upgrade.sql

1. Open Web Developer Tools
(Option + Command + i) or (Menu > View > Developer > Developer Tools

2. Disable Cache

2.1. Go to Settings

2.1.1. Click Gear icon on lower bottom

2.1.2. Check Disable Cache

#!/usr/local/bin/python
# how to call this
# $ python /Users/nreeves/Desktop/test.py greeting “How are you?”
# >> Hello greeting
# $ python /Users/nreeves/Desktop/test.py food “Rice” “Potato”
# >>I want to eat food and food
# $ python /Users/nreeves/Desktop/test.py foo “bar”
# >>programmer error!

import sys

def greeting(arg1):
    print‘Hello%s‘% (arg1)

def food(arg1, arg2):
    print‘I want to eat%sand%s‘% (arg1, arg2)

def main():
    if len(sys.argv) > 1: function=sys.argv[1]
    if len(sys.argv) > 2: arg1=sys.argv[1]
    if len(sys.argv) > 3: arg2=sys.argv[1]

    if function ==“greeting”:
        greeting(arg1)
    elif function ==“food”:
        food(arg1, arg2)
    else:
        print“programmer error!”

if __name__ == “__main__”:
    main()

Yep, documentation is critical. Everyone knows that Search-ability of the documentation is critical as documentation.

We started to document postgreSQL trigger/functions and created view to list function name, definition and comments etc… It is pretty handy and would like to share this with you.

– Add Comments

COMMENT ON FUNCTION my_function(text) IS ‘function description’;

– Search by Comments

SELECT * from pg_catalog.pg_description WHERE description = ‘function description’; — Search for Specific description

– List All Functions along with Comments

SELECT p.proname
,p.proargnames
,obj_description(p.oid)
,pg_get_functiondef(p.oid)
,CASE WHEN p.prorettype=2279 THEN ‘Trigger Function’ ELSE ‘Function’ END as type
FROM pg_proc p
JOIN pg_namespace nspance ON nspance.oid = p.pronamespace
WHERE
nspname=’public’ AND proowner <> 10
– AND proname = ‘my_function’ — Search for Specific function
– AND p.prorettype=2279 — Triggere Function Only
ORDER BY 5, 1

My initial attept failed miserably as you can see my post from here…

After bunch of help from many intelligent people I successfully setup Python2.7.2, apache, wsgi for Ubunut 10.04 LTS

++++++++++++++++++++++++++++++
+ References:
+ 1. How to install 2.7 comment made by Alen here
+ 2. How to install wsgi
++++

# ==== Setup Ubuntu 10.04 on VMWare # on server:
# install basic stuff…
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install openssh-server # only if you want to use ssh
sudo apt-get install vim # only if you like vim….
sudo apt-get install libpq-dev
sudo apt-get install curl # only if you like over wget.. I am just so used to curl over wget since os-x (my dev machine) doesn’t have wget
sudo apt-get install build-essential

##### # Install Python 2.7.2 #####

sudo vim /etc/apt/sources.list

# add the following 2 lines at the bottom of the file:

deb http://ppa.launchpad.net/fkrull/deadsnakes/ubuntu lucid main
deb-src http://ppa.launchpad.net/fkrull/deadsnakes/ubuntu lucid main

# issue the following command:

sudo apt-key adv –keyserver keyserver.ubuntu.com –recv-keys DB82666C
sudo apt-get update
sudo apt-get install python2.7 python2.7-dev

# verify instllation

which python >>/usr/local/bin/python
python -V >> Python 2.7.2

# verify the linkage

ldd /usr/local/bin/python2.7

##### # Install Apache #####
# you need to install apache2-dev for wsgi installation

sudo apt-get install apache2 apache2-dev

# then add domain.

sudo vim /etc/apache2/conf.d/fqdn
# add: “ServerName localhost” without quotes

################################
# install wsgi
################################
# if wsgi is installed via apt-get then purge it:

sudo apt-get purge libapache2-mod-wsgi

mkdir tmp # or wherever you want to compile
cd tmp
curl -O http://modwsgi.googlecode.com/files/mod_wsgi-3.3.tar.gz tar xzf mod_wsgi-3.3.tar.gz
rm mod_wsgi-3.3.tar.gz
cd mod_wsgi-3.3 ./configure –with-python=/usr/local/bin/python make sudo make install
# the load declaration for the module needs to go wsgi.load
sudo vim /etc/apache2/mods-available/wsgi.load
# then add the following line: “LoadModule wsgi_module /usr/lib/apache2/modules/mod_wsgi.so” without quotes
# Then you have to activate the wsgi module with:
sudo a2enmod wsgi

# That’s it!

So Fedora 15 with GNOME 3 is very beautiful. I really like it. Mac’s spotlight like search is +1.

I had a bit hard time accessing my macbook to Fedora 15 via SSH so I thought I should share this with you.

1. Modify sshd_conf file (/etc/ssh/sshd_config): Find Line “Port 22″ and uncomment the line

2. Restart sshd service by issuing 

$ service sshd stop$ service sshd restart

3. Let’s check to see if sshd is running

$ service sshd status# or you could$ ps aux|grep sshd

3. Okay, let’s test it. Go to client (my case is my macbook) and issued $ ssh username@ip_address Connect then I got “Connection Refused” Error. So looks like I need to mess with firewall (iptables).

3.1 Edit file /etc/sysconfig/iptables and add the following line. Note that this should go above the first instance of -A INPUT otherwise other rule might block you as rule will will be read from top down and stops as soon as it meets the rule.

# to open ssh
-A INPUT -m state –state NEW -m tcp -p tcp –dport 22 -j ACCEPT

$ service iptables restart

– Add comment to table

COMMENT ON TABLE fnl is ‘Customer Information’

– Add comment to column

COMMENT ON COLUMN customer.id is ‘customer primary key’

 

– Retrieve Info Individually

SELECT col_description((select c.oid from pg_catalog.pg_class c where c.relname = ‘customer’), 1)

SELECT obj_description((select c.oid from pg_catalog.pg_class c where c.relname = ‘customer’))

 

– Retrieve Info for one table

SELECT

cols.column_name

,(select pg_catalog.obj_description(oid) from pg_catalog.pg_class c where c.relname=cols.table_name) as table_comment

,(select pg_catalog.col_description(oid,cols.ordinal_position::int) from pg_catalog.pg_class c where c.relname=cols.table_name) as column_comment

FROM information_schema.columns cols

WHERE cols.table_catalog=’database_name’ and cols.table_name=’customer’

We have this query to join 4 tables.

For some reason, the performance of this query was inconsistent on Server A. First run took 6 sec, 2nd run took 300 ms then 3rd run took 6 sec again etc…

We dump the data to other server and run the same query, very consistent result – about 100 ms almost all the time.

We even got “could not read from hash-join temporary file” error and crashed Server A. We end up finding out that work_mem of the Server A is set to very low compare to other servers.

So followed this: http://wiki.postgresql.org/wiki/Performance_Optimization and we changed 2 settings:

work_mem to 16MB – default is 1MB and it was the default value

effective_cache_size – this one was also default of 128MB… so I set it to 3GB (machie has 6 GB and according to the wiki, half of the size of RAM would be good)

Then restart the server and yes, the performance was so consistent and was producing the same result as other server.

This will give you ethernet one

ifconfig en0 | grep ‘inet ‘ | cut -d ‘ ‘ -f2

This will give you airport one

ifconfig en1 | grep ‘inet ‘ | cut -d ‘ ‘ -f2

so if you are writing script to get your IP in either case you could:

MYIP=`ifconfig en0 | grep ‘inet ‘ | cut -d ‘ ‘ -f2`

if [$MYIP -eq '']; then

MYIP=`ifconfig en1 | grep ‘inet ‘ | cut -d ‘ ‘ -f2`

fi

MYIP=`ifconfig en0 | grep ‘inet ‘ | cut -d ‘ ‘ -f2`if [$MYIP -eq '']; then MYIP=`ifconfig en1 | grep ‘inet ‘ | cut -d ‘ ‘ -f2`fi

echo $MYIP