PostgreSQL对接SequoiaDB

@H_404_0@Postgresql是一款开源的sql 数据库，支持标准sql，用户可以通过JDBC驱动连接Postgresql进行应用程序开发。用户通过扩展Postgresql 功能，让开发者可以使用sql语句访问SequoiaDB数据库,完成SequoiaDB数据库的增、删、查、改操作。本文就针对如何扩展Postgresql 功能，实现Postgresql对接SequoiaDB进行介绍。

1. 部署Postgresql

1.1 安装Postgresql

@H_404_0@本教程为Postgresql与SequoiaDB的对接教程，所以作者建议使用者在sdbadmin用户下（SequoiaDB数据库默认用户）安装并使用Postgresql（本教程使用的Postgresql版本为9.3.4）。

@H_404_0@源码编译Postgresql
@H_404_0@下载链接：http://www.postgresql.org/ftp/source/
解压后编译安装（需要root权限）
```
$> tar -zxvf postgresql-9.3.4.tar.gz
$> cd postgresql-9.3.4/
$> ./configure && make && make install
```
@H_404_0@切换用户
```
$>su - sdbadmin
```
@H_404_0@拷贝Postgresql文件
```
$>cp -rf /usr/local/pgsql ~/
```
@H_404_0@进入Postgresql目录
```
$>cd pgsql
```
@H_404_0@环境变量添加Postgresql的lib库
```
$>export LD_LIBRARY_PATH=$(pwd)/lib:${LD_LIBRARY_PATH}
```
@H_404_0@建议用户将Postgresql的lib加到sdbadmin用户的环境变量中，否则每次登陆sdbadmin使用Postgresql，都需要手工添加Postgresql的lib 到 LD_LIBRARY_PATH中
```
$> echo "export LD_LIBRARY_PATH=$(pwd)/lib:${LD_LIBRARY_PATH}" >> ~/.bash_profile
```
@H_404_0@创建Postgresql的数据目录
```
$>mkdir pg_data
```
@H_404_0@初始化数据目录(该操作只能操作一次)
```
$>bin/initdb -D pg_data/
```

1.2 安装SequoiaDB-Postgresql 插件

@H_404_0@创建Postgresql的lib 目录
@H_404_0@获取Postgresql的libdir路径
```
$> PGLIBDIR=$(bin/pg_config --libdir)
```
@H_404_0@如果显示的libdir目录不存在，则需要用户自己手工创建目录
```
$> mkdir -p ${PGLIBDIR}
```
@H_404_0@创建Postgresql的extension目录
@H_404_0@获取Postgresql的sharedir路径
```
$> PGSHAREDIR=$(bin/pg_config --sharedir)
```
@H_404_0@在shardir目录上再创建extemsion目录
```
$> mkdir -p ${PGSHAREDIR}/extension
```
@H_404_0@从SequoiaDB的安装包中，拷贝Postgresql的扩展文件
@H_404_0@从SequoiaDB安装后的postgresql目录中拷贝sdb_fdw.so文件到Postgresql的lib目录，SequoiaDB默认安装目录为/opt/sequoiadb
```
$> cp -f /opt/sequoiadb/postgresql/sdb_fdw.so ${PGLIBDIR}
```

@H_404_0@将sdb_fdw.control和sdb_fdw--1.0.sql脚本拷贝到extension目录中，两个脚本需要用户手工编辑

$> cp -f sdb_fdw.control ${PGSHAREDIR}/extension/ ;
$> cp -f sdb_fdw--1.0.sql ${PGSHAREDIR}/extension/ ;

@H_404_0@sdb_fdw.control 脚本内容

# sdb_fdw extension
    comment = 'foreign data wrapper for SequoiaDB access'
    default_version = '1.0'
    module_pathname = '$libdir/sdb_fdw'
    relocatable = true

@H_404_0@sdb_fdw--1.0.sql 脚本内容

/* contrib/mongo_fdw/sdb_fdw--1.0.sql */
-- complain if script is sourced in psql,rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION sdb_fdw" to load this file. \quit
CREATE FUNCTION sdb_fdw_handler()
RETURNS fdw_handler
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
CREATE FUNCTION sdb_fdw_validator(text[],oid)
RETURNS void
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
CREATE FOREIGN DATA WRAPPER sdb_fdw
HANDLER sdb_fdw_handler
VALIDATOR sdb_fdw_validator;

1.3 部署Postgresql

@H_404_0@检查端口是否被占用
@H_404_0@Postgresql默认启动端口为”5432”,检查端口是否被占用(检查操作建议使用root用户操作，只有检查端口需要root权限，其余操作还是需要在sdbadmin用户下操作)
```
$>netstat -nap | grep 5432
```
@H_404_0@如果5432端口被占用或者希望修改Postgresql的启动端口，则执行：
```
$> sed -i "s/#port = 5432/port = 11780/g" pg_data/postgresql.conf
```
@H_404_0@启动Postgresql服务进程（需要使用sdbadmin用户执行以下命令）
```
$> bin/postgres -D pg_data/ >> logfile 2>&1 &
```

@H_404_0@检查Postgresql是否启动成功

@H_404_0@执行命令：

$> netstat -nap | grep 5432

@H_404_0@结果为：

tcp   0   0 127.0.0.1:5432     0.0.0.0:*         LISTEN     20502/postgres
unix  2   [ ACC ]   STREAM    LISTENING   40776754 20502/postgres     /tmp/.s.PGsql.5432

@H_404_0@创建Postgresql的database
@H_404_0@$> bin/createdb -p 5432 foo
@H_404_0@进入Postgresql shell 环境
```
$> bin/psql -p 5432 foo
```

2. Postgresql连接SequoiaDB

@H_404_0@以下操作均在Postgresql shell 环境下执行

2.1 Postgresql与SequoiaDB建立关联

@H_404_0@加载SequoiaDB连接驱动
```
foo=# create extension sdb_fdw;
```

@H_404_0@配置与SequoiaDB连接参数

foo=# create server sdb_server foreign data wrapper sdb_fdw options(address '192.168.30.182',service '11810',user 'sdbadmin',password 'cmbc123');

@H_404_0@关联SequoiaDB的集合空间与集合
@H_404_0@注：集合空间与集合必须已经存在于SequoiaDB，否则查询出错。
默认情况下，表的字段映射到SequoiaDB中为小写字符，如果强制指定字段为大写字符，创建方式参考注意事项1
映射SequoiaDB 的数组类型，创建方式参考注意事项2
```
foo=# create foreign table test (name text,id numeric) server sdb_server options ( collectionspace 'chen',collection 'test' ) ;
```
@H_404_0@查询
```
foo=# select * from test;
```
@H_404_0@写入数据
```
foo=# insert into test values('one',3);
```

@H_404_0@更改数据

foo=# update test set id=9 where name='one';

@H_404_0@查看所有的表(show tables;)
```
foo=# \d
```
@H_404_0@查看表的描述信息
```
foo=# \d test
```
@H_404_0@删除表
```
foo=# drop foreign table test;
```
@H_404_0@退出Postgresql shell环境
```
foo=# \q
```

2.2 使用须知

2.2.1 数据类型的对应关系

2.2.2 注意事项

@H_404_0@注意字符的大小写
@H_404_0@SequoiaDB 中的集合空间、集合和字段名均对字母的大小写敏感
@H_404_0@集合空间、集合名大写
@H_404_0@假设SequoiaDB 中存在名为TEST的集合空间，CHEN的集合，在Postgresql中建立相应的映射表
```
foo=# create foreign table sdb_upcase_cs_cl (name text) server sdb_server options ( collectionspace 'TEST',collection 'CHEN' ) ;
```
@H_404_0@字段名大写
@H_404_0@假设SequoiaDB 中存在名为foo的集合空间，bar的集合，而且保存的数据为：
```
{
 "_id": {
   "$oid":"53a2a0e100e75e2c53000006"
 },"NAME": "test"
}
```
@H_404_0@在Postgresql中建立相应的映射表
```
foo=# create foreign table sdb_upcase_field (“NAME” text) server sdb_server options ( collectionspace 'foo',collection 'bar' ) ;
```
@H_404_0@执行查询命令：
```
foo=# select * from sdb_upcase_field;
```
@H_404_0@查询结果为：
```
NAME
------
 test
(1 rows)
```

@H_404_0@映射SequoiaDB中的数据类型

@H_404_0@假设SequoiaDB中存在foo集合空间，bar集合，保存记录为：

{
 "_id": {
    "$oid":"53a2de926b4715450a000001"
 },"name": [
   1,2,3
 ],"id": 123
}

@H_404_0@在Postgresql 中建立相应的映射表

foo=# create foreign table bartest (name numeric[],id numeric) server sdb_server options ( collectionspace 'foo',collection 'bar' ) ;

@H_404_0@执行查询命令：

foo=# select * from bartest;

@H_404_0@查询结果：

name  | id
---------+-----
{1,3} | 123

@H_404_0@连接SequoiaDB 协调节点错误
@H_404_0@如果Postgresql连接的SequoiaDB 协调节点重启，在查询时报错
```
ERROR: Unable to get collection "chen.test",rc = -15
HINT: Make sure the collectionspace and collection exist on the remote database
```
@H_404_0@解决方法：
@H_404_0@退出Postgresql shell
```
foo=# \q
```
@H_404_0@重新进入Postgresql shell
```
$> bin/psql -p 5432 foo
```

2.2.3 调整Postgresql@R_301_382@

@H_404_0@查看pg_shell中默认的配置

@H_404_0@执行命令：

foo=#\set

@H_404_0@结果为：

AUTOCOMMIT = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
VERSION = 'Postgresql 9.3.4 on x86_64-unknown-linux-gnu,compiled by gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973],64-bit'
DBNAME = 'foo'
USER = 'sdbadmin'
PORT = '5432'
ENCODING = 'UTF8'

@H_404_0@调整pg_shell查询时，每次获取记录数
```
foo=#\set FETCH_COUNT 100
```
@H_404_0@调整为每次ps_shell每次获取100 条记录立即返回记录，然后再继续获取。
@H_404_0@直接在pg_shell中修改@R_301_382@，只能在当前pg_shell中生效，重新登录pg_shell需要重新设置。
@H_404_0@修改@R_301_382@，调整pg_shell查询时，每次获取记录数
@H_404_0@执行命令：
```
$> ${PG_HOME}/bin/pg_config -sysconfdir
```
@H_404_0@结果为：
```
/opt/sequoiadb/pgsql/etc
```
@H_404_0@如果显示目录不存在，自己手动创建即可
```
$> mkdir -p /opt/sequoiadb/pgsql/etc
```
@H_404_0@将需要修改的参数写入@R_301_382@中
```
$>echo "\\set FETCH_COUNT 100" >> /opt/sequoiadb/pgsql/etc/psqlrc
```

@H_404_0@调整pg_shell的日志级别

$>sed -i 's/#client_min_messages = notice/client_min_messages = debug1/g' pg_data/postgresql.conf

@H_404_0@调整pg引擎的日志级别

$>sed -i 's/#log_min_messages = warning/log_min_messages = debug1/g' pg_data/postgresql.conf

3. 使用java客户端连接Postgresql

3.1 修改Postgresql的连接配置

@H_404_0@修改Postgresql的监听地址

$>sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '0.0.0.0'/g" pg_data/postgresql.conf

@H_404_0@修改信任的机器列表

$>linenum=$(cat -n pg_data/pg_hba.conf | grep "# IPv4 local connections:" | awk '{print $1}'); \
let "linenum=linenum+1";varStr="host   all            all            0.0.0.0/0              trust"; \
sed -i "${linenum} a${varStr}" pg_data/pg_hba.conf;

@H_404_0@重启Postgresql

$>bin/pg_ctl stop -s -D pg_data/ -m fast; bin/postgres -D pg_data/ >> logfile 2>&1 &

3.2 JDBC连接程序

```
package com.sequoiadb.sample;
import java.sql.*;
public class postgresql_sample {
   static{
       try {
           Class.forName"org.postgresql.Driver");
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       }
   }
   public static void main( String[] args ) throws sqlException{
      String pghost = "192.168.30.182";
      String port = "5432";
      String databaseName = "foo";
      // postgresql process is running in which user
      String pgUser = "sdbadmin";
      String url = "jdbc:postgresql://"+pghost+":"+port+"/" + databaseName;
      Connection conn = DriverManager.getConnection(url,pgUser,null);
      Statement stmt = conn.createStatement();
      String sql = "select * from sdb_upcase_field ";
      ResultSet rs = stmt.executeQuery(sql);
      boolean isHeaderPrint = false;
      while (rs.next()) {
           ResultSetMetaData md = rs.getMetaData();
           int col_num = md.getColumnCount();
           if (isHeaderPrint){
               for (int i = 1; i <= col_num; i++) {
                   System.out.print(md.getColumnName(i) + "|");
                   isHeaderPrint = true;
               }
           }
           for (i = 1; i <= col_num; i++) {
               System.out.print(rs.getString(i) + "|");
           }
           System.out.println();
       }
       stmt.close();
       conn.close();
   }
}
```