[TOC] > https://github.com/pg-redis-fdw/redis_fdw ## 概述 - 使用 pgsql 访问redis - 实际发开中可以新建一个 redis 模式来进行调用 ## 安装 ### yum 1. 添加PGDG仓库 ``` sudo rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm ``` 2. 安装扩展 ``` sudo yum install postgresql15-contrib postgresql15-devel ``` 其中`postgresql15-devel` 可能需要 `llvm-toolset-7-clang` ``` sudo yum repolist sudo yum install centos-release-scl sudo yum install llvm-toolset-7-clang ``` 在执行 `yum install postgresql15-devel` 3. clone redis_fdw,注意当前为15,只当tag 为`REL_15_STABLE ` ``` git clone -b REL_15_STABLE https://github.com/pg-redis-fdw/redis_fdw make USE_PGXS=1 make USE_PGXS=1 install ``` 4. 重启 pgsql ``` systemctl restart postgresql-15 ``` ## 启动扩展 ``` CREATE EXTENSION redis_fdw; ``` ## 创建redis 表的语法 ``` CREATE FOREIGN TABLE myredishash (key text, val text[]) SERVER redis_server OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'mytable:'); ``` OPTIONS 选项 - database 选择 redis 的数据库,默认为0 - tabletype 可选redis类型为 hash,list,set,zset(没有string 类型) - tablekeyprefix ,singleton_key 设置表为可带前缀还是无需前缀 ## pg 连接 redis 创建pg 的 redis server,用于连接redis,注意 ``` CREATE SERVER redis_server FOREIGN DATA WRAPPER redis_fdw OPTIONS (host 'localhost', port '6379'); ``` 创建pgsql 的映射 ``` CREATE USER MAPPING FOR PUBLIC SERVER redis_server OPTIONS (password 'redis_pwd'); ``` - `password` 为 redis 的密码 进行授权 ``` grant usage on FOREIGN server redis_server to postgres ``` ## 示例 ### hash key 带前缀 使用用于存储 人员信息表, `mytable:user_id` 当作key ``` CREATE FOREIGN TABLE myredishash ( key text, val text[] ) SERVER redis_server OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'mytable:'); INSERT INTO myredishash (key, val) VALUES ('mytable:r1','{prop1,val1,prop2,val2}'); UPDATE myredishash SET val = '{prop3,val3,prop4,val4}' WHERE key = 'mytable:r1'; DELETE from myredishash WHERE key = 'mytable:r1'; ``` 等价于 ``` > hmset mytable:r1 prop1 val1 prop2 val2 // 等价于插入 > hmset mytable:r1 prop1 val1-1 prop2 val2-1 // 存在的值进行更新,等价于更新 ``` ### hash key 不带前缀 是否存储共享数据的表如服务器信息等,并且使用where 获取具体key ``` CREATE FOREIGN TABLE myredis_s_hash ( key text, val text ) SERVER redis_server OPTIONS (database '0', tabletype 'hash', singleton_key 'mytable'); INSERT INTO myredis_s_hash (key, val) VALUES ('prop1','val1'),('prop2','val2'); SELECT * from mytable where val='val23' UPDATE myredis_s_hash SET val = 'val23' WHERE key = 'prop1'; DELETE from myredis_s_hash WHERE key = 'prop2'; ``` 等级于 ``` > hmset mytable:r1 prop1 val1 prop2 val2 // 等价于插入 > hset mytable prop1 val1-1 // 更新 ``` ### list 类型 ```sql CREATE FOREIGN TABLE redis.my_list ( key text, val text[] ) SERVER redis_server OPTIONS (database '0', tabletype 'list', tablekeyprefix 'mylist:'); -- 插入 INSERT INTO redis.my_list VALUES('mylist:key1','{abc,213}'); -- 更新 UPDATE redis.my_list SET val=array_append(val, 'aaaa') where key='mylist:123' -- 查找 select val[1:3] from redis.my_list where KEY='mylist:123' // 查找第1:3 的元素 ```