Friday, October 3, 2014

Postgre SQL Commands

Following are some of the commands I though very useful for newbie developers.
  • Connect to the DB
/usr/local/pgsql/bin/psql -U postgres [DB]
  • Execute postgres functions
\i /test/query/run.sql
  • How to get differecne between two schems
             Here I have have used apgdiff    
/usr/java/[JDK Verssion]/bin/java -jar apgdiff-xxxxx.jar target_db.sql source_db.sql > diff.sql
  • How to get the dump
/usr/local/pgsql/bin/pg_dump --file=/home/uploads/schema_backup/[file name].sql --schema=[schema name] -U [user name] [DB Name]
  • How to restore a schema 
-U [user name] --pset pager -f /home/uploads/pgdump/[file anme].sql [DB Name]
  • How to connect to the Database
/usr/local/pgsql/bin/psql  -U [user name]  [schema name]
  • How to backup only the selected table
pg_dump --file=/rezsystem/backup/[file name].sql --host=[connect IP] --table=[Table name] --username=[user name] --schema=[schema name] [DB Name]
  • How to backup only the table structure
pg_dump --file=$1 --host=$2 -U $3 --schema=$4 --schema-only $5 > $6 2>&1
           ex. pg_dump --file=/rezsystem/rm_upload/[file name].sql --host=localhost -U [user name] --schema=[schema name] --schema-only [DB Name] > /rezsystem/rm_upload/[file name].sql 2>&1
  • How to update bulk DDL statement in a one SQL file
psql --file=/rezrelmgmt/uploads/Lsi/[file name].sql --host=localhost --username=postgres --quiet --tuples-only [DB Name]
  • How to check check the running datbase processors with the status
ps auxww | grep ^postgres
  • How to kill the exact process
kill -INT prodessId
  • How to check the total connections
ps ax |grep post |wc -l