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