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

MySQL Commands

Important MySQL Commands.

• How to check if multiple instance are running on the Server

sudo netstat -tap | grep mysql

• Kill mySql processors

pkill mysqld

• Start , Stop or Restart MySQL

/etc/init.d/mysql start|stop|restart|reload|force-reload|status

• Backup MySQL Database

mysqldump -u root -p DB > database_dump.txt

• Restore MySQL Database

mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

• Back up MySQL Database with Compress

mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

• Restore compressed backup files

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

• Restore a database that already exists

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]