Redis
Redis is an open-source in-memory storage, used as a distributed, in-memory key–value database, cache and message broker, with optional durability.
The Redis Wrapper allows you to read data from Redis within your Postgres database.
Restoring a logical backup of a database with a materialized view using a foreign table can fail. For this reason, either do not use foreign tables in materialized views or use them in databases with physical backups enabled.
Supported Redis Data Types
All Redis value will be stored as text
or jsonb
column in Postgres, below are the supported Redis data types:
Redis Type | Foreign Table Type (src_type) |
---|---|
List | list |
Set | set |
Hash | hash |
Sorted Set | zset |
Stream | stream |
Multiple List | multi_list |
Multiple Set | multi_set |
Multiple Hash | multi_hash |
Multiple Sorted Set | multi_zset |
See below for more descriptions for the Multiple *
types and src_type
foreign table option.
Preparation
Before you get started, make sure the wrappers
extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
and then create the foreign data wrapper:
_10create foreign data wrapper redis_wrapper_10 handler redis_fdw_handler_10 validator redis_fdw_validator;
Secure your credentials (optional)
By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server
in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.
_10-- Save your Redis connection URL in Vault and retrieve the `key_id`_10insert into vault.secrets (name, secret)_10values (_10 'redis_conn_url',_10 'redis://username:password@127.0.0.1:6379/db'_10)_10returning key_id;
Connecting to Redis
We need to provide Postgres with the credentials to connect to Redis. We can do this using the create server
command:
_10create server redis_server_10 foreign data wrapper redis_wrapper_10 options (_10 conn_url_id '<key_ID>' -- The Key ID from above._10 );
The connection URL format is:
_10redis://[<username>][:<password>@]<hostname>[:port][/<db>]
Creating Foreign Tables
The Redis Wrapper supports data reads from Redis.
Integration | Select | Insert | Update | Delete | Truncate |
---|---|---|---|---|---|
Redis | ✅ | ❌ | ❌ | ❌ | ❌ |
For example:
_10create foreign table redis_list (_10 element text_10)_10 server redis_server_10 options (_10 src_type 'list',_10 src_key 'my_list'_10 );
The foreign table columns names and types must be fixed for each source type, as listed below:
src_type | Column name | Column type |
---|---|---|
list, set, zset | element | text |
hash | key | text |
value | text | |
stream | id | text |
items | jsonb | |
multi_* | key | text |
items | jsonb |
See below for the full list of src_type
and descriptions.
Foreign table options
The full list of foreign table options are below:
-
src_type
- Foreign table source type in Redis, required.This can be one of below types,
Source type Description list Single list set Single set hash Single hash zset Single sorted set stream Stream multi_list Multiple lists, specified by src_key
patternmulti_set Multiple sets, specified by src_key
patternmulti_hash Multiple hashes, specified by src_key
patternmulti_zset Multiple sorted sets, specified by src_key
pattern -
src_key
- Source object key in Redis, required.This key can be a pattern for
multi_*
type of foreign table. For other types, this key must return exact one value. For example,Source Type src_key
exampleslist, set, hash, zset, stream my_list
,list:001
,hash_foo
,zset:1000
and etc.multi_list, multi_set, multi_hash, multi_zset my_list:*
,set:*
,zset:*
and etc.
Query Pushdown Support
This FDW doesn't supports pushdown.
Examples
Some examples on how to use Redis foreign tables.
Let's prepare some source data in Redis CLI first:
_18127.0.0.1:6379> RPUSH list foo bar 42_18127.0.0.1:6379> SADD set foo bar 42_18127.0.0.1:6379> HSET hash foo bar baz qux_18127.0.0.1:6379> ZADD zset 30 foo 20 bar 10 baz_18127.0.0.1:6379> XADD stream * foo bar_18127.0.0.1:6379> XADD stream * aa 42 bb 43_18_18127.0.0.1:6379> RPUSH list:100 foo bar_18127.0.0.1:6379> RPUSH list:200 baz_18_18127.0.0.1:6379> SADD set:100 foo_18127.0.0.1:6379> SADD set:200 bar_18_18127.0.0.1:6379> HSET hash:100 foo bar_18127.0.0.1:6379> HSET hash:200 baz qux_18_18127.0.0.1:6379> ZADD zset:100 10 foo 20 bar_18127.0.0.1:6379> ZADD zset:200 40 baz 30 qux
Basic example
This example will create foreign tables inside your Postgres database and query their data:
-
List
_10create foreign table redis_list (_10element text_10)_10server redis_server_10options (_10src_type 'list',_10src_key 'list'_10);_10_10select * from redis_list;Query result:
_10element_10---------_10foo_10bar_1042_10(3 rows) -
Set
_10create foreign table redis_set (_10element text_10)_10server redis_server_10options (_10src_type 'set',_10src_key 'set'_10);_10_10select * from redis_set;Query result:
_10element_10---------_1042_10foo_10bar_10(3 rows) -
Hash
_11create foreign table redis_hash (_11key text,_11value text_11)_11server redis_server_11options (_11src_type 'hash',_11src_key 'hash'_11);_11_11select * from redis_hash;Query result:
_10key | value_10-----+-------_10foo | bar_10baz | qux_10(2 rows) -
Sorted set
_10create foreign table redis_zset (_10element text_10)_10server redis_server_10options (_10src_type 'zset',_10src_key 'zset'_10);_10_10select * from redis_zset;Query result:
_10element_10---------_10baz_10bar_10foo_10(3 rows) -
Stream
_11create foreign table redis_stream (_11id text,_11items jsonb_11)_11server redis_server_11options (_11src_type 'stream',_11src_key 'stream'_11);_11_11select * from redis_stream;Query result:
_10id | items_10-----------------+--------------------------_101704343825989-0 | {"foo": "bar"}_101704343829799-0 | {"aa": "42", "bb": "43"}_10(2 rows)
Query multiple objects example
This example will create several foreign tables using pattern in key and query multiple objects from Redis:
-
List
_11create foreign table redis_multi_lists (_11key text,_11items jsonb_11)_11server redis_server_11options (_11src_type 'multi_list',_11src_key 'list:*'_11);_11_11select * from redis_multi_lists;Query result:
_10key | items_10----------+----------------_10list:100 | ["foo", "bar"]_10list:200 | ["baz"]_10(2 rows) -
Set
_11create foreign table redis_multi_sets (_11key text,_11items jsonb_11)_11server redis_server_11options (_11src_type 'multi_set',_11src_key 'set:*'_11);_11_11select * from redis_multi_sets;Query result:
_10key | items_10---------+---------_10set:100 | ["foo"]_10set:200 | ["bar"]_10(2 rows) -
Hash
_11create foreign table redis_multi_hashes (_11key text,_11items jsonb_11)_11server redis_server_11options (_11src_type 'multi_hash',_11src_key 'hash:*'_11);_11_11select * from redis_multi_hashes;Query result:
_10key | items_10----------+----------------_10hash:200 | {"baz": "qux"}_10hash:100 | {"foo": "bar"}_10(2 rows) -
Sorted set
_11create foreign table redis_multi_zsets (_11key text,_11items jsonb_11)_11server redis_server_11options (_11src_type 'multi_zset',_11src_key 'zset:*'_11);_11_11select * from redis_multi_zsets;Query result:
_10key | items_10----------+----------------_10zset:200 | ["qux", "baz"]_10zset:100 | ["foo", "bar"]_10(2 rows)