Quick reference for PG command line tools, this post serve as a reference for future use.

Postgresql command line tools:

– Dump: /PATH/TO/BIN/pg_dump -U USERNAME DATABASE_NAME > DESTINATION.sql;

– Restore: /PATH/TO/BIN/pg_restore -U USERNAME -no-owner -d DATABASE_NAME < file.sql; or /PATH/TO/BIN/psql -U USERNAME DATABASE_NAME < file.sql;
– Connect to PG command line interface : /PATH/TO/BIN/psql --U USERNAME ;
– Exit command line tool: \q

Inside psql command line tool:
– user or connect to database: \connect DATABASE_NAME
– list all databases: \list | \l
– list all tables in the current database: \dt
– describe table structure: \d+ TABLE_NAME
– list all users: \du

Sometimes we have an update of codebase many files updates also new files to be added and also removed, to overcome this issue you will need to add them or remove them without adding/removing files already exsits in the repo, the following command line script solve this issue.

add files

svn st | grep ^? | sed 's/? //' | xargs svn add

remove files

svn st | grep ^! | sed 's/! //' | xargs svn rm

Just for my reference here is a script that will drop all the tables from the database at once
and don’t forget to replace the username, password and database name 😛

for i in $(mysql -u username -ppassword databasename -e "Show tables;" | grep -v Tables_in | grep -v "+"); do mysql -u username -ppassword databasename -e "drop table $i; "; done;