Postgresql: Postgresql Commands Cheat-Sheet
September 19th, 2010 by jeremychonePG Tools
- pg_dump dbname > backupfile: Backup as a sql file
- psql dbname < backupfile : restore
Login
su - postgres psql #or psql mydb
Create Db
su - postgres createdb DBTest
PSQL Commands
- \h : SQL help
- \? : psql help
- \q : quit
- \l : show databases
- \d : show tables
- \d tablename : show columns
- \c dbname : connect to db
- \! : switch to OS shell mode (very useful on Mac OS where you start on the SQL shell)
SQL Commands
- DROP DATABASE MYDB;
Backup & Restore Database
Backup: $ pg_dump {source_db} -f {dumpfilename.sql}
Restore: $ psql -d {desintation_db} -f {dumpfilename.sql}
Note: on Mac OS X, the command prompt need to be started from the application folder or from pgAdminIII, and then, type “\!” to switch to command shell, and then, go to /Library/PostgreSQL/8.4/bin to find the psql and pg_dump scripts
How to backup and restore postgresql