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

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

– 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.

I needed to extract triggers only, sequence/index only etc when we migrated database.
I received advice from postgresql General List and thought I should post exact step/script of how to accomplish this.

Open Terminal:

1. dump schema only

pg_dump -h localhost -p 5432 –username UserName –schema-only –format c –file path_to_backup.backup –schema ‘public’ db_name

2. create list of item that you want to extract
2.1. TRIGGERS only

pg_restore –list “path_to_backup.backup” | awk ‘/[0-9]*; [0-9]* [0-9]* (TRIGGER)/ { print }’ > “path_to_triggers.list”

2.2. SEQUENCE, INDEX

pg_restore –list “path_to_backup.backup” | awk ‘/[0-9]*; [0-9]* [0-9]* (SEQUENCE|INDEX)/ { print }’ > “path_to_seq.list”

3. Create SQL script from the list
3.1. TRIGGERS only

pg_restore -L path_to_triggers.list -f dest_path_triggers.sql path_to_backup.backup

3.2. SEQUENCE, INDEX

pg_restore -L path_to_seq.list -f dest_path_seq.sql path_to_backup.backup

1. write a shell script

#!/bin/bash

#backup.sh

logfile=”/backups/logfile.log”

# Location to place backups.

backup_dir=”/backups”

touch $logfile

timeslot=`date +%y%m%d_%H%M`

databases=(“db1″ “db2″ “db3″)

# get length of an array

tLen=${#databases[@]}

for (( i=0; i<${tLen}; i++ )); do

echo “Vacuum Starting at `date` for database: ${databases[$i]} ” >> $logfile

timeinfo=`date ‘+%T %x’`

psql -c “vacuum verbose analyze” -d ${databases[$i]} -U postgres >> $logfile

echo “Vacuum completed. Backup Starting at `date` for database: ${databases[$i]} ” >> $logfile

pg_dump ${databases[$i]} -h 127.0.0.1  -U postgres | gzip > “$backup_dir/${databases[$i]}-$timeslot-backup.gz”

echo “Backup and Vacuum complete at `date` for database: ${databases[$i]} ” >> $logfile

done

2. Add an entry to crontab to execute the script above
2.1. open terminal and login as postgres: $ su – postgres
2.2. list cron job: $ crontab -l
2.3. edit cron job:
2.3.1. $ crontab -e #in terminal vi editor will open)
2.3.2. i for insert mode
2.3.3. enter entry. for instance, to run it every 1 am:

00 01 * * * path_to_backup.sh > /dev/null

2>&1
2.3.4. ESC key to exist edit mode
2.3.5. :wq to save and quit