[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 的元素 ```