postgres

postgres

连接数据库, 默认的用户和数据库是postgres
psql -U user -d dbname

切换数据库,相当于mysql的use dbname
\c dbname
列举数据库，相当于mysql的show databases
\l
列举表，相当于mysql的show tables
\dt
查看表结构，相当于desc tblname,show columns from tbname
\d tblname

\di 查看索引 

创建数据库： 
create database [数据库名]; 
删除数据库： 
drop database [数据库名];  
*重命名一个表： 
alter table [表名A] rename to [表名B]; 
*删除一个表： 
drop table [表名]; 

ps aux | grep postgres

psql postgres

数据还原
bunzip2 -c huafei3_development.bz2 | psql <OPTIONS> template1

备份：
pg_dump -h qa.fun-guide.mobi -U pgsql huafei_development > huafeibak.out
psql -h localhost -U postgres -d huafei_development < huafeibak.out

http://my.oschina.net/zhao/blog/55071

桥接


bjv1db tunnel


Host bjv1db
  HostName 119.90.35.166
  User root
  Port 1022
  identityfile ~/.ssh/funguide key
  ServerAliveInterval 10
  LocalForward 5435 localhost:5432
  localForward 本地端口 远端端口

超级用户密码丢失， 卸载重新安装

rm -rf /usr/local/var/postgres
rm -rf /usr/local/opt/postgresql
rm -rf /usr/local/bin/postgres 
rm -rf /usr/local/Cellar/postgresql 
rm -rf /usr/local/share/postgresql
rm -rf /usr/local/include/postgres_ext.h
brew uninstall postgresql

brew install postgresql 
brew unlink postgresql
brew link --overwrite postgresql
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
createuser -s -r postgres
psql -U postgres
ALTER USER menxu WITH PASSWORD '';
~: su menxu 系统用户和数据库用户是分开的
Password:
/Users/menxu/.rvm/scripts/initialize:44: __rvm_cleanse_variables: function definition file not found
/Users/menxu/.rvm/scripts/initialize:46: command not found: rvm_error
应该是用这种方式登陆用户不会运行shell的设置脚本