postgresql小试


作为一个只用过mysql,外加在公司用oracle的苦逼程序员,今天趁着感冒差不多好的当口,学学之前就碰到过,但是始终没自己动手试过的postgresql。

首先说明一下,我是在vagrant做的虚拟机中搭建的postgresql。postgresql的具体安装方法是

sudo apt-get update
sudo apt-get install postgresql

安装好之后你可以通过下面的方式测试

sudo -u postgres psql -l

如果列出一些数据库的话,那就代表成功。基本上apt-get install方式安装的不会失败的,除非你有别的程序占据了5432端口(postgresql默认端口)之类的。
为了方便,你需要建立一个数据库自己用。

sudo -u postgres createdb mydb

连接上mydb并建立一个表增加一些数据

sudo -u postgres psql mydb
psql (9.1.10)
Type "help" for help.

mydb=# CREATE TABLE users (
  id int,
  name varchar(255)
);
CREATE TABLE
mydb=# \d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | temp    | table | postgres
 public | users   | table | postgres
 public | weather | table | postgres
(3 rows)

mydb=# INSERT INTO users VALUES(1, 'XnnYygn');
INSERT 0 1
mydb=# INSERT INTO users VALUES(2, 'XY');
INSERT 0 1
mydb=# SELECT * FROM users;
 id |  name   
----+---------
  1 | XnnYygn
  2 | XY
(2 rows)

mydb=# \q

SQL语句想必大家都很熟悉。\d表示列出所有表,\q表示退出(MySQL也是\q)。
接下来,尝试从宿主机用ruby代码访问postgresql。
首先通过下面的命令确认postgresql绑定的IP。

sudo netstat -anp | fgrep postgres

默认情况下是127.0.0.1,你需要修改配置文件postgresql.conf。我的配置文件是/etc/postgresql/9.1/main/postgresql.conf。配置项是

listen_addresses = '*'

这里原先不是星号,是localhost,你要改成*,这样就会绑定到0.0.0.0。
这时你再用netstat查看时,发现postgres绑定到了0.0.0.0:5432上,并且从外部telnet guest_ip 5432是可以通的(吐槽一下,某些高级/资深头衔的人不知道怎么确认服务器某个端口是否开启,居然想用ping……)。
接下来如果你尝试从外部连接的话是不一定能成功的,因为还有权限管理配置在另外一个文件中pg_hba.conf,我的是在/etc/postgresql/9.1/main/pg_hba.conf,增加

host all all host_ip/32 trust

host_ip就是你的宿主机IP,这里表示允许来自host_ip的请求访问所有数据库。
接下来,就是用ruby来访问了。我使用的是pg。安装方法是

sudo gem install pg

如果遇到异常,多半是没有装postgresql的开发相关的包,可以用下面的方式解决。

sudo apt-get install libpq-dev

我会告诉你我的宿主机也是ubuntu么?还有其实是PQ,而不是PG,我被搞了好长时间么?

安装好pg之后,参照pg主页的代码,在irb中

require 'pg'
conn = PG::Connection.open(:host = guest_ip, :dbname = 'mydb', :user = 'postgres')
res = conn.exec('SELECT * FROM users')

open的参数,因为是远程服务器,所以需要host,user必须,而postgres这个值是我猜的……
res默认是一个字典的集合,这样方便视图处理。当然你可以通过each_row方法获取单行值,通过fields获取headers。
记得最后conn.close关闭连接。

至此,使用ruby远程访问postgresql基本完成。