Postgresql - Foreign data wrappers - Redis

通过Postgresql读取Redis数据。

需要安装插件，redis_fdw

插件代码地址：

https://github.com/pg-redis-fdw/redis_fdw

环境：

CentOS 7 + Postgresql 10.1

CentOS 7 + Redis 4.0.10

准备：

1.

git clone https://github.com/redis/hiredis.git

cd hiredis/

make

make install

安装：

git clone https://github.com/pg-redis-fdw/redis_fdw.git

cd redis_fdw/

根据版本，checkout新的branch

9.5: git checkout REL9_5_STABLE

9.6: git checkout REL9_6_STABLE

10: git checkout REL_10_STABLE

PATH=/usr/local/pgsql/bin/:$PATH make USE_PGXS=1

PATH=/usr/local/pgsql/bin/:$PATH make USE_PGXS=1 install

使用：

1. create extension

mytest=# create extension redis_fdw ;

CREATE EXTENSION

************************************

如果创建时报错：

ERROR: could not load library "/usr/local/postgresql_10.1/lib/redis_fdw.so": libhiredis.so.0.13: cannot open shared object file: No such file or directory

可以找到libhiredis.so.0.13文件，将这个文件cp到/usr/local/postgresql_10.1/lib/文件夹。错误解决。=

************************************

2. Create server

mytest=# CREATE SERVER redis_server_17

mytest-# FOREIGN DATA WRAPPER redis_fdw

mytest-# OPTIONS (address '192.168.4.17', port '6379');

CREATE SERVER

3. Create foreign table

mytest=# CREATE FOREIGN TABLE redis_17_db0 (key text, val text)

mytest-# SERVER redis_server_17

mytest-# OPTIONS (database '0');

CREATE FOREIGN TABLE

4. user mapping

mytest=# CREATE USER MAPPING FOR mytest

mytest-# SERVER redis_server_17;

CREATE USER MAPPING

5. 测试

插入值：

mytest=# insert into redis_17_db0 values('foo101','bar101');

INSERT 0 1

mytest=# insert into redis_17_db0 values('foo102','bar102');

INSERT 0 1

查看

mytest=# select * from redis_17_db0;

key | val

--------+--------

foo101 | bar101

foo102 | bar102

(2 rows)

进入到redis 查看

192.168.4.17:6379> keys '*'

1) "foo102"

2) "foo101"

在redis中插入数据

192.168.4.17:6379> set foo201 bar201

OK

192.168.4.17:6379> set foo202 bar202

OK

回到PG中查看

mytest=# select * from redis_17_db0;

key | val

--------+--------

foo202 | bar202

foo101 | bar101

foo102 | bar102

foo201 | bar201

(4 rows)

============================

创建redis 外部表：

mytest=# CREATE FOREIGN TABLE myredishash (key text, val text[])

mytest-# SERVER redis_server_17

mytest-# OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'mytable:');

CREATE FOREIGN TABLE

mytest=# INSERT INTO myredishash (key, val) VALUES ('mytable:r1','{prop1,val1,prop2,val2}');

INSERT 0 1

查看数据

mytest=# select * from myredishash;

key | val

------------+-------------------------

mytable:r1 | {prop1,val1,prop2,val2}

(1 row)

mytest=# select * from redis_17_db0 ;

key | val

--------+--------

foo202 | bar202

foo101 | bar101

foo102 | bar102

foo201 | bar201

(4 rows)

在redis中查看

192.168.4.17:6379> keys '*'

1) "mytable:r1"

2) "foo102"

3) "foo101"

4) "foo202"

5) "foo201"

PG端更新

mytest=# UPDATE myredishash

mytest-# SET val = '{prop3,val3,prop4,val4}'

mytest-# WHERE key = 'mytable:r1';

UPDATE 1

============================

创建redis外部表，

mytest=# CREATE FOREIGN TABLE myredis_s_hash (key text, val text)

mytest-# SERVER redis_server_51

mytest-# OPTIONS (database '0', tabletype 'hash', singleton_key 'mytable');

CREATE FOREIGN TABLE

插入数据：

mytest=# INSERT INTO myredis_s_hash (key, val) VALUES ('prop1','val1'),('prop2','val2');

INSERT 0 2

PG端查询

mytest=# select * from myredis_s_hash;

key | val

-------+------

prop1 | val1

prop2 | val2

(2 rows)

redis端查询：

192.168.4.17:6379> keys '*'

1) "mytable:r1"

2) "foo102"

3) "foo101"

4) "foo202"

5) "mytable"

6) "foo201"

更新：

mytest=# UPDATE myredis_s_hash

mytest-# SET val = 'val23'

mytest-# WHERE key = 'prop1';

UPDATE 1