PostgreSql 常用命令

Installation

https://github.com/railscasts/337-capistrano-recipes/blob/master/blog-after/config/recipes/postgresql.rb http://whatcodecraves.com/articles/2008/02/05/setup-rails-with-postgresql

sudo add-apt-repository ppa:pitti/postgresql -y
sudo apt-get -y update
sudo apt-get -y install postgresql libpq-dev

sudo -u postgres psql -c "create user xxx with password xxx;"
sudo -u postgres psql -c "create database xxx owner xxx;"

drop database:

psql -U luna -d xxx -W -c "drop database xxx"

[编辑]Postgres数据库常用操作命令

psql连接数据库: psql -d 'database name' -U 'user name' -W

删除数据库: dropdb'database name'

或者： sudo -u postgres psql -c "drop database'database name'"

创建数据库(luna用户作为拥有者): createdb -O'user name' 'database name'

查看数据库中的所有表: psql登录进去后，执行 \d

执行表users信息: \d users

修改数据库用户的密码: ALTER USER luna with password 'secure-password';

[编辑]Backup & Restore

official backup: http://www.postgresql.org/docs/9.1/static/backup-dump.html#BACKUP-DUMP-ALL

File System Level Backup can only be done while database is down, in order to ensure the integrity and up-to-date for backups. READ:http://www.postgresql.org/docs/9.1/static/backup-file.html

example crontab backup:

26 * * * * sudo -u postgres pg_dumpall --clean > /tmp/db_dump_all.pgdump

example restore:

sudo -u postgres pgsql -f /tmp/db_dump_all.pgdump

osx install ,create database and user

http://blog.willj.net/2011/05/31/setting-up-postgresql-for-ruby-on-rails-development-on-os-x/

更有封装好的shell command 

适用于mac 用户

createuser -P laoban  #创建用户带密码  Enter 后输入密码

dropuser 'xxx'   #删除用户

删除数据库

 dropdb demo

创建数据库 并分配给指定用户

 createdb -O username database_name

导入数据库

psql dbname < infile

或者指定用户

PGPASSWORD='password' psql -U 'username' database < db/xxx.sql