PostgreSQL use redis_fdw connect to Redis

╰+攻爆jí腚メ 2022-08-26 00:56 20阅读 0赞

首先要感谢Dave先生的贡献才有了redis\_fdw。Dave贡献了两个FDW模块，mysql\_fdw和redis\_fdw分别用于PostgreSQL9.1连接到MySQL和Redis。

接下来通过redis\_fdw来测试一下PostgreSQL 9.1是怎么连接到Redis的。

首先到 [http://redis.io][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 yes
      
       
      
       
       
        requirepass DIGOAL

启动redis

[root@digoal redis-2.4.1]# src/redis-server ./redis.conf

确认已启动

[root@digoal redis-2.4.1]# netstat -anp|grep 6379
      
       
      
       
       
        tcp        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][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 hiredis
      
       
      
       
       
        make
      
       
      
       
       
        make 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 postgres
      
       
      
       
       
        psql (9.1.0)
      
       
      
       
       
        Type "help" for help.
      
       
      
       
       
        digoal=# create extension redis_fdw;

然后到redis服务端插入一些数据。

vi ins.sh
      
       
      
       
       
         set visits:digoal1:totals                   1 
       
         set visits:digoal2:totals                   2 
       
         set visits:digoal3:totals                   3 
       
         set visits:digoal4:totals                   4 
       
         set visits:digoal5:totals                   5 
       
         set visits:digoal6:totals                   6 
       
         set visits:digoal7:totals                   7 
       
         set visits:digoal8:totals                   8 
       
         set visits:digoal9:totals                   9 
       
         set visits:digoal10:totals                 10 
       
         set visits:digoal11:totals                 11 
       
         set visits:digoal12:totals                 12 
       
         set visits:digoal13:totals                 13 
       
         set visits:digoal14:totals                 14 
       
         set visits:digoal15:totals                 15 
       
         set visits:digoal16:totals                 16 
       
         set visits:digoal17:totals                 17 
       
         set visits:digoal18:totals                 18 
       
         set visits:digoal19:totals                 19 
       
         set visits:digoal20:totals                 20 
       
         set visits:digoal21:totals                 21 
       
         set visits:digoal22:totals                 22 
       
         set visits:digoal23:totals                 23 
       
         set visits:digoal24:totals                 24 
       
         set visits:digoal25:totals                 25 
       
         set visits:digoal26:totals                 26 
       
         set visits:digoal27:totals                 27 
       
         set visits:digoal28:totals                 28 
       
         set visits:digoal29:totals                 29 
       
         set visits:digoal30:totals                 30 
       
         set visits:digoal31:totals                 31 
       
         set visits:digoal32:totals                 32 
       
         set visits:digoal33:totals                 33 
       
         set visits:digoal34:totals                 34 
       
         set visits:digoal35:totals                 35 
       
         set visits:digoal36:totals                 36 
       
         set visits:digoal37:totals                 37 
       
         set visits:digoal38:totals                 38 
       
         set visits:digoal39:totals                 39 
       
         set visits:digoal40:totals                 40 
       
         set visits:digoal41:totals                 41 
       
         set visits:digoal42:totals                 42 
       
         set visits:digoal43:totals                 43 
       
         set visits:digoal44:totals                 44 
       
         set visits:digoal45:totals                 45 
       
         set visits:digoal46:totals                 46 
       
         set visits:digoal47:totals                 47 
       
         set visits:digoal48:totals                 48 
       
         set visits:digoal49:totals                 49 
       
         set visits:digoal50:totals                 50 
       
         set visits:digoal51:totals                 51 
       
         set visits:digoal52:totals                 52 
       
         set visits:digoal53:totals                 53 
       
         set visits:digoal54:totals                 54 
       
         set visits:digoal55:totals                 55 
       
         set visits:digoal56:totals                 56 
       
         set visits:digoal57:totals                 57 
       
         set visits:digoal58:totals                 58 
       
         set visits:digoal59:totals                 59 
       
         set visits:digoal60:totals                 60 
       
         set visits:digoal61:totals                 61 
       
         set visits:digoal62:totals                 62 
       
         set visits:digoal63:totals                 63 
       
         set visits:digoal64:totals                 64 
       
         set visits:digoal65:totals                 65 
       
         set visits:digoal66:totals                 66 
       
         set visits:digoal67:totals                 67 
       
         set visits:digoal68:totals                 68 
       
         set visits:digoal69:totals                 69 
       
         set visits:digoal70:totals                 70 
       
         set visits:digoal71:totals                 71 
       
         set visits:digoal72:totals                 72 
       
         set visits:digoal73:totals                 73 
       
         set visits:digoal74:totals                 74 
       
         set visits:digoal75:totals                 75 
       
         set visits:digoal76:totals                 76 
       
         set visits:digoal77:totals                 77 
       
         set visits:digoal78:totals                 78 
       
         set visits:digoal79:totals                 79 
       
         set visits:digoal80:totals                 80 
       
         set visits:digoal81:totals                 81 
       
         set visits:digoal82:totals                 82 
       
         set visits:digoal83:totals                 83 
       
         set visits:digoal84:totals                 84 
       
         set visits:digoal85:totals                 85 
       
         set visits:digoal86:totals                 86 
       
         set visits:digoal87:totals                 87 
       
         set visits:digoal88:totals                 88 
       
         set visits:digoal89:totals                 89 
       
         set visits:digoal90:totals                 90 
       
         set visits:digoal91:totals                 91 
       
         set visits:digoal92:totals                 92 
       
         set visits:digoal93:totals                 93 
       
         set visits:digoal94:totals                 94 
       
         set visits:digoal95:totals                 95 
       
         set visits:digoal96:totals                 96 
       
         set visits:digoal97:totals                 97 
       
         set visits:digoal98:totals                 98 
       
         set visits:digoal99:totals                 99 
       
         set 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_server1                             
       
        FOREIGN DATA WRAPPER redis_fdw  
       
        OPTIONS (address '172.xxx.xxx.xxx', port '6379'); 
      
       
      
       
       
        digoal=# CREATE USER MAPPING FOR digoal                         
       
        SERVER redis_server1            
       
        OPTIONS (password 'DIGOAL'); 
       
        CREATE USER MAPPING 
      
       
      
       
       
        digoal=# grant usage on FOREIGN server redis_server1 to digoal; 
       
        GRANT 
      
       
      
       
       
        digoal=> CREATE FOREIGN TABLE redis_db0 (key text, value text)  
       
        SERVER redis_server1 
       
        OPTIONS (database '0'); 
       
        CREATE FOREIGN TABLE 
       
        digoal=> CREATE FOREIGN TABLE redis_db1 (key text, value text)  
       
        SERVER redis_server1 
       
        OPTIONS (database '1'); 
       
        CREATE FOREIGN TABLE 
      
       
      
       
       
        digoal=> select * from redis_db0 limit 5; 
       
                  key           | value  
       
        ------------------------+------- 
       
         visits:digoal13:totals | 13 
       
         visits:digoal23:totals | 23 
       
         visits:digoal33:totals | 33 
       
         visits:digoal43:totals | 43 
       
         visits:digoal17:totals | 17 
       
        (5 rows) 
       
        
       
        digoal=> select * from redis_db1 limit 5; 
       
                  key           | value  
       
        ------------------------+------- 
       
         visits:digoal13:totals | 13 
       
         visits:digoal23:totals | 23 
       
         visits:digoal33:totals | 33 
       
         visits:digoal43:totals | 43 
       
         visits: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.

【参考】

[http://redis.io][http_redis.io]

[http://www.pgxn.org/dist/redis\_fdw/][http_www.pgxn.org_dist_redis_fdw]

【其他FDW】

1. hive

[https://github.com/youngwookim/hive-fdw-for-postgresql][https_github.com_youngwookim_hive-fdw-for-postgresql]

2. PostgreSQL Foreign Table - pgsql\_fdw

[http://blog.163.com/digoal@126/blog/static/163877040201231514057303/][http_blog.163.com_digoal_126_blog_static_163877040201231514057303]

3. PostgreSQL Foreign Table - oracle\_fdw 1

[http://blog.163.com/digoal@126/blog/static/163877040201181505331588/][http_blog.163.com_digoal_126_blog_static_163877040201181505331588]

4. PostgreSQL Foreign Table - oracle\_fdw 2

[http://blog.163.com/digoal@126/blog/static/16387704020118151162340/][http_blog.163.com_digoal_126_blog_static_16387704020118151162340]

5. PostgreSQL Foreign Table - oracle\_fdw 3

[http://blog.163.com/digoal@126/blog/static/16387704020118951953408/][http_blog.163.com_digoal_126_blog_static_16387704020118951953408]

6. PostgreSQL Foreign Table - file\_fdw

[http://blog.163.com/digoal@126/blog/static/163877040201141641148311/][http_blog.163.com_digoal_126_blog_static_163877040201141641148311]

7. PostgreSQL Foreign Table - redis\_fdw

[http://blog.163.com/digoal@126/blog/static/16387704020119181188247/][http_blog.163.com_digoal_126_blog_static_16387704020119181188247]

8. PostgreSQL Foreign Table - mysql\_fdw 1

[http://blog.163.com/digoal@126/blog/static/1638770402011111233524987/][http_blog.163.com_digoal_126_blog_static_1638770402011111233524987]

9. PostgreSQL Foreign Table - mysql\_fdw 2

[http://blog.163.com/digoal@126/blog/static/16387704020121108551698/][http_blog.163.com_digoal_126_blog_static_16387704020121108551698]

转帖：http://blog.163.com/digoal@126/blog/static/16387704020119181188247/

[http_redis.io]: http://redis.io/
[http_www.pgxn.org_user_pgsnake]: http://www.pgxn.org/user/pgsnake
[http_www.pgxn.org_dist_redis_fdw]: http://www.pgxn.org/dist/redis_fdw/
[https_github.com_youngwookim_hive-fdw-for-postgresql]: https://github.com/youngwookim/hive-fdw-for-postgresql
[http_blog.163.com_digoal_126_blog_static_163877040201231514057303]: http://blog.163.com/digoal@126/blog/static/163877040201231514057303/
[http_blog.163.com_digoal_126_blog_static_163877040201181505331588]: http://blog.163.com/digoal@126/blog/static/163877040201181505331588/
[http_blog.163.com_digoal_126_blog_static_16387704020118151162340]: http://blog.163.com/digoal@126/blog/static/16387704020118151162340/
[http_blog.163.com_digoal_126_blog_static_16387704020118951953408]: http://blog.163.com/digoal@126/blog/static/16387704020118951953408/
[http_blog.163.com_digoal_126_blog_static_163877040201141641148311]: http://blog.163.com/digoal@126/blog/static/163877040201141641148311/
[http_blog.163.com_digoal_126_blog_static_16387704020119181188247]: http://blog.163.com/digoal@126/blog/static/16387704020119181188247/
[http_blog.163.com_digoal_126_blog_static_1638770402011111233524987]: http://blog.163.com/digoal@126/blog/static/1638770402011111233524987/
[http_blog.163.com_digoal_126_blog_static_16387704020121108551698]: http://blog.163.com/digoal@126/blog/static/16387704020121108551698/