Category: PostgreSQL


PostgreSQL Utility for EnterpriseDB Installation

January 30th, 2012 — 10:00pm

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

Comment » | mac, OS X, PostgreSQL, System Admin

PostgreSQL DB Version Control with apgdiff

January 29th, 2012 — 9:05pm

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

Comment » | Bash Shell, mac, OS X, PostgreSQL

PostgreSQL – Document PostgreSQL functions

January 22nd, 2012 — 12:25pm

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

Comment » | PostgreSQL

PostgreSQL – Document Table and Column Description (Comments on Table and Column)

August 3rd, 2011 — 9:08pm

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

Comment » | PostgreSQL

postgreSQL – inconsistent performance

August 2nd, 2011 — 8:55pm

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.

Comment » | PostgreSQL

PostgreSQL: Dump Schema Only or Triggers Only

July 17th, 2010 — 2:59pm

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

Comment » | Bash Shell, PostgreSQL

nightly backup (pg_dump) with shell script and cron job

July 10th, 2010 — 8:00am

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

Comment » | PostgreSQL

Back to top