Installation


brew install postgresql
# first time install Postgres with Homebrew, so we need to create a database with following command 
initdb /usr/local/var/postgres -E utf8

安裝完之後基本上就可以使用Postgres了

# start postgres 
pg_ctl -D /usr/local/var/postgres -l logfile start

Useful Command of Postgres


以下列出一些常用的 postgres 指令

  • List all database in command line
psql -l
  • Create User
createuser user_name
  • Create db with owner
createdb -O owner_name db_name
  • drop db
dropdb db_name
  • pg_dump & pg_restore
pg_dump -U user -Fc db_name > /tmp/db.dump
pg_restore -C -U user -d db_name db.dump
  • login into db
psql -U user db_name
  • list all tables in database
# after login into db, use postgres command line
\dt *.*
  • list all tables in schema
# after login into db, use postgres command line
\dt schema_name.*
  • list all info of tables
# after login into db, use postgres command line
\d schema_name.table_name