首先要感谢Dave先生的贡献才有了redis_fdw。Dave贡献了两个FDW模块,mysql_fdw和redis_fdw分别用于PostgreSQL9.1连接到MySQL和Redis。
接下来通过redis_fdw来测试一下PostgreSQL 9.1是怎么连接到Redis的。
首先到
http://redis.io下载一个redis2.4.1的稳定版源码。
安装参考README
[root@digoal redis-2.4.1]# tar -zxvf redis-2.4.1.tar.gz[root@digoal redis-2.4.1]# cd redis-2.4.1[root@digoal redis-2.4.1]# make[root@digoal redis-2.4.1]# vi redis.conf
加上密码校验,并放置后台运行,用作测试其他参数不修改了。
daemonize yesrequirepass DIGOAL
启动redis
[root@digoal redis-2.4.1]# src/redis-server ./redis.conf
确认已启动
[root@digoal redis-2.4.1]# netstat -anp|grep 6379tcp 0 0 0.0.0.0:6379 0.0.0.0:* LISTEN 26647/redis-server
接下来在另一台PostgreSQL数据库服务器上配置redis_fdw.
首先是下载redis_fdw
http://www.pgxn.org/user/pgsnake
解压到postgresql源码包的 postgresql-9.1.0/contrib/ 目录
然后把redis-2.4.1/deps/hiredis 目录拷贝到 postgresql-9.1.0/contrib/redis_fdw-1.0.0
接下来编译hiredis
cd hiredismakemake install
确认安装OK之后。编译redis_fdw
[root@digoal redis_fdw-1.0.0]# . /home/postgres/.bash_profile(导入两个环境变量 PATH, LD_LIBRARY_PATH)PATH和LD_LIBRARY_PATH如下 : export PGHOME=/opt/pgsql91 export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib export PATH=$PGHOME/bin:$PATH:.[root@digoal redis_fdw-1.0.0]# USE_PGXS=1 make clean[root@digoal redis_fdw-1.0.0]# USE_PGXS=1 make[root@digoal redis_fdw-1.0.0]# USE_PGXS=1 make install-- 以下步骤可能可以省略, 如果没有才需要拷贝.[root@digoal redis_fdw-1.0.0]# cp redis_fdw.so /opt/pgsql/lib/[root@digoal redis_fdw-1.0.0]# cp redis_fdw--1.0.sql /opt/pgsql/share/extension/[root@digoal redis_fdw-1.0.0]# cp redis_fdw.control /opt/pgsql/share/extension/
OK,至此就算安装好了。
接下来体验一下。
先到PostgreSQL数据库里面创建redis_fdw这个extension .
psql -h 127.0.0.1 digoal postgrespsql (9.1.0)Type "help" for help.digoal=# create extension redis_fdw;
然后到redis服务端插入一些数据。
vi ins.shset visits:digoal1:totals 1set visits:digoal2:totals 2set visits:digoal3:totals 3set visits:digoal4:totals 4set visits:digoal5:totals 5set visits:digoal6:totals 6set visits:digoal7:totals 7set visits:digoal8:totals 8set visits:digoal9:totals 9set visits:digoal10:totals 10set visits:digoal11:totals 11set visits:digoal12:totals 12set visits:digoal13:totals 13set visits:digoal14:totals 14set visits:digoal15:totals 15set visits:digoal16:totals 16set visits:digoal17:totals 17set visits:digoal18:totals 18set visits:digoal19:totals 19set visits:digoal20:totals 20set visits:digoal21:totals 21set visits:digoal22:totals 22set visits:digoal23:totals 23set visits:digoal24:totals 24set visits:digoal25:totals 25set visits:digoal26:totals 26set visits:digoal27:totals 27set visits:digoal28:totals 28set visits:digoal29:totals 29set visits:digoal30:totals 30set visits:digoal31:totals 31set visits:digoal32:totals 32set visits:digoal33:totals 33set visits:digoal34:totals 34set visits:digoal35:totals 35set visits:digoal36:totals 36set visits:digoal37:totals 37set visits:digoal38:totals 38set visits:digoal39:totals 39set visits:digoal40:totals 40set visits:digoal41:totals 41set visits:digoal42:totals 42set visits:digoal43:totals 43set visits:digoal44:totals 44set visits:digoal45:totals 45set visits:digoal46:totals 46set visits:digoal47:totals 47set visits:digoal48:totals 48set visits:digoal49:totals 49set visits:digoal50:totals 50set visits:digoal51:totals 51set visits:digoal52:totals 52set visits:digoal53:totals 53set visits:digoal54:totals 54set visits:digoal55:totals 55set visits:digoal56:totals 56set visits:digoal57:totals 57set visits:digoal58:totals 58set visits:digoal59:totals 59set visits:digoal60:totals 60set visits:digoal61:totals 61set visits:digoal62:totals 62set visits:digoal63:totals 63set visits:digoal64:totals 64set visits:digoal65:totals 65set visits:digoal66:totals 66set visits:digoal67:totals 67set visits:digoal68:totals 68set visits:digoal69:totals 69set visits:digoal70:totals 70set visits:digoal71:totals 71set visits:digoal72:totals 72set visits:digoal73:totals 73set visits:digoal74:totals 74set visits:digoal75:totals 75set visits:digoal76:totals 76set visits:digoal77:totals 77set visits:digoal78:totals 78set visits:digoal79:totals 79set visits:digoal80:totals 80set visits:digoal81:totals 81set visits:digoal82:totals 82set visits:digoal83:totals 83set visits:digoal84:totals 84set visits:digoal85:totals 85set visits:digoal86:totals 86set visits:digoal87:totals 87set visits:digoal88:totals 88set visits:digoal89:totals 89set visits:digoal90:totals 90set visits:digoal91:totals 91set visits:digoal92:totals 92set visits:digoal93:totals 93set visits:digoal94:totals 94set visits:digoal95:totals 95set visits:digoal96:totals 96set visits:digoal97:totals 97set visits:digoal98:totals 98set visits:digoal99:totals 99set visits:digoal100:totals 100
导入到库0和库1
[root@digoal src]# cat ./ins.sh|./redis-cli -h 127.0.0.1 -p 6379 -a DIGOAL -n 0[root@digoal src]# cat ./ins.sh|./redis-cli -h 127.0.0.1 -p 6379 -a DIGOAL -n 1
接下来在PostgreSQL数据库创建连接REDIS的外部表:
digoal=# CREATE SERVER redis_server1FOREIGN DATA WRAPPER redis_fdwOPTIONS (address '172.xxx.xxx.xxx', port '6379');digoal=# CREATE USER MAPPING FOR digoalSERVER redis_server1OPTIONS (password 'DIGOAL');CREATE USER MAPPINGdigoal=# grant usage on FOREIGN server redis_server1 to digoal;GRANTdigoal=> CREATE FOREIGN TABLE redis_db0 (key text, value text)SERVER redis_server1OPTIONS (database '0');CREATE FOREIGN TABLEdigoal=> CREATE FOREIGN TABLE redis_db1 (key text, value text)SERVER redis_server1OPTIONS (database '1');CREATE FOREIGN TABLEdigoal=> select * from redis_db0 limit 5;key | value------------------------+-------visits:digoal13:totals | 13visits:digoal23:totals | 23visits:digoal33:totals | 33visits:digoal43:totals | 43visits:digoal17:totals | 17(5 rows)digoal=> select * from redis_db1 limit 5;key | value------------------------+-------visits:digoal13:totals | 13visits:digoal23:totals | 23visits:digoal33:totals | 33visits:digoal43:totals | 43visits:digoal17:totals | 17(5 rows)digoal=> select count(*) from redis_db0;count-------100(1 row)digoal=> select count(*) from redis_db1;count-------100(1 row)
注意事项请参考redis_fdw README.
【参考】
【其他FDW】
1. hive
2. PostgreSQL Foreign Table - pgsql_fdw
3. PostgreSQL Foreign Table - oracle_fdw 1
4. PostgreSQL Foreign Table - oracle_fdw 2
5. PostgreSQL Foreign Table - oracle_fdw 3
6. PostgreSQL Foreign Table - file_fdw
7. PostgreSQL Foreign Table - redis_fdw
8. PostgreSQL Foreign Table - mysql_fdw 1
9. PostgreSQL Foreign Table - mysql_fdw 2
转帖:http://blog.163.com/digoal@126/blog/static/16387704020119181188247/