postgresql小试 – 箱猫日和

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

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

sudo apt-get update
sudo apt-get install postgresql

1 2	sudo apt–get update sudo apt–get install postgresql

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

sudo -u postgres psql -l

1	sudo –u postgres psql –l

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

sudo -u postgres createdb mydb

1	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

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

1	sudo netstat –anp \| fgrep postgres

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

listen_addresses = ‘*’

1	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

1	host all all host_ip/32 trust

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

sudo gem install pg

1	sudo gem install pg

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

sudo apt-get install libpq-dev

1	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’)

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基本完成。