==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

<pre>
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 luna with password 'luna';"
sudo -u postgres psql -c "create database luna_development owner luna;"
or production
sudo -u postgres psql -c "create database luna_production owner luna;"
</pre>

config/database.yml:

<pre>
production:
  adapter: postgresql
  encoding: unicode
  database: <%= postgresql_database %>
  pool: 5
  username: <%= postgresql_user %>
  password: <%= postgresql_password %>
  host: <%= postgresql_host %>
</pre>

drop database:
<pre>
psql -U luna -d luna_test -W -c "drop database luna_production"
</pre>


=== Postgres数据库常用操作命令 ===

psql连接数据库:
<code>psql -d luna_production -U luna -W</code>

删除数据库:
<code>dropdb luna_production</code>  

或者： <code>sudo -u postgres psql -c "drop database luna_test"</code>

创建数据库(luna用户作为拥有者):
<code>createdb  -O luna luna_production</code>

数据库重启动:
<code>service postgresql restart</code>

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

执行表users信息:
<code>\d users</code>

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


==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


==ERROR and fix==
PANIC: error about corrupted transaction logs.

<pre>
root@lunaserver:~# sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_resetxlog /var/lib/postgresql/9.1/main/
could not change directory to "/root"
Transaction log reset
root@lunaserver:~# /etc/init.d/postgresql restart * Restarting PostgreSQL 9.1 database server 
</pre>