Search docs.molo17.com
    Configuration guide Connectors

    Set up a Redis target for snapshots and CDC

    The Redis target keeps a current copy of your tables in Redis. Each source row becomes one Redis hash, the key of the hash is built from the table and the primary key, and a delete removes the key. This guide shows you how to give Gluesync a Redis account with only the commands it needs, how to create the target, how to copy a PostgreSQL table and follow its live changes, how the keys and the values look in Redis, and how to fix the errors that you are most likely to meet. To append every change to a log instead, use Set up a Redis Streams target for CDC events.

    In this guide

    You will restrict the Redis default user to three commands and to the keys that Gluesync writes, create the Redis target in the Control Plane, and map two PostgreSQL tables to hashes with the default key and with a custom key. You will run a snapshot, follow an insert, an update, a delete and a truncate, and read the hashes with redis-cli. You will see how each data type is written, which settings change the write speed, and the errors that were reproduced for this guide.

    Quick information

    Goal

    Replicate PostgreSQL tables into Redis hashes with a snapshot, keep them in sync with change data capture (CDC), and read the keys

    Audience

    Data engineers and Redis administrators who use Redis as a cache or a fast lookup copy of a database

    Level

    Intermediate

    Time required

    About 35 minutes

    Area

    Redis → ACL; Control Plane → Pipelines → Redis target → Base settings; entity → Field mapping → Document key

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have a Redis server that Core Hub can reach, and you can run redis-cli on it as an administrator. The Control Plane shows 7.4 as the minimum supported version, for Community and Enterprise editions. This guide was tested with Redis 7.4. Redis is a target only: you need another database as the source;

    2. You have a source that Core Hub can read with CDC. This guide uses PostgreSQL 16 with wal_level = logical, set up as in Set up a PostgreSQL WAL source;

    3. You can sign in to the Control Plane with a role that can create pipelines;

    4. You have source tables with a primary key. This guide uses public.customers (id integer primary key and the text columns name, surname, address, gender, phone and email) with 20 rows, and a small table public.drivers with an integer key id.

    The statements and the messages quoted in this guide were run and captured against Redis 7.4 and the Control Plane that ships with Gluesync 2.3.

    Guide steps

    Restrict the Redis default user

    CLI

    The target signs in with the password only. The Username field of the form is required, but the agent does not send it, so Redis always authenticates the connection as the default user. A named user with its own password does not work for this target. Instead, give the default user a password and take away everything that Gluesync does not need.

    Do this in the right order, because the next command locks the default user and your own tools use it too. First create an administrator for yourself and for the other applications of this Redis, then restrict default:

    1. Create an administrator. Replace the password:

      redis-cli ACL SETUSER admin on '>change-me-admin' '~*' '&*' +@all

    2. Restrict the default user, with your own password and the key patterns of your tables. This guide writes the keys public::customers::<id> and driver-<id>, so it allows public::* and driver-*. The reset keyword starts from a user with no commands, no keys and no password:

      redis-cli --user admin --pass change-me-admin --no-auth-warning \
  ACL SETUSER default reset on '>change-me-strong' '~public::*' '~driver-*' +hset +hsetnx +del

    3. Check what the user can and cannot do:

      redis-cli --user default --pass change-me-strong --no-auth-warning HSET public::probe a 1
redis-cli --user default --pass change-me-strong --no-auth-warning HSET other::probe a 1
redis-cli --user default --pass change-me-strong --no-auth-warning DEL public::probe
    Expected result

    The first command answers (integer) 1. The second answers NOPERM No permissions to access a key, because other::probe does not match any key pattern. The third answers (integer) 1 and removes the probe key.

    The three commands are all the agent needs when the Database name is 0. If you write to another logical database, add the command SELECT to the ACL SETUSER line (step 7 shows why). Redis also refuses the optional command CLIENT SETINFO, which the client sends when it connects, and writes it to ACL LOG. The agent works without it.

    If your Redis has no password, skip this step and switch on Disable auth in the next step.

    Create the pipeline and the Redis target

    UI

    1. In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

    2. In 1. Setup source, choose the PostgreSQL CDC agent tile and fill Base settings: Host name / Address, Port, Database name, Username and Password of your source database. Select Test and continue, then Next;

    3. In 2. Setup target(s), choose the Redis agent tile and fill Base settings:

      • Host name / Address: your Redis host;

      • Port: 6379, or the port of your server;

      • Database name: the number of the logical database, 0 by default. It must be a number;

      • Username: default. The field is required, and the agent ignores the value;

      • Password: the password of step 1;

    4. Select Test and continue. The target appears with the user and the address. Select Next, enter a Pipeline name and select Finish.

    Setup target page of the Redis agent with host redis
    Figure 1. The Redis target connection. The protocol is fixed to Vendor SDK, and Disable auth is off because Redis has a password.
    Expected result

    The pipeline appears in the HEALTHY group of the pipeline list, with the PostgreSQL and the Redis icons. The connection test proves the host, the port, the password and the logical database. It does not prove the key patterns of the user: step 7 shows what a key pattern mistake looks like.

    The Advanced settings tab and the key list of Advanced connection properties show names such as Topic Prefix, Use Transactions, SASL Mechanism and Security Protocol. The Redis agent does not read them, so leave both lists empty. The Control Plane has no Enable TLS switch for this target in Gluesync 2.3. Step 7 shows what happens when Redis accepts only TLS connections.

    Add the entities and choose the key of each hash

    UI

    1. In the pipeline, select Entities. The Add entities page opens with the Object browser. Expand the schema public and select the check boxes of customers and drivers. The Selected entities list shows both;

    2. Select the tree icon of customers to open the Field mapping dialog. Target Scope is public and Target Collection is customers: they start as the source schema and table. The arrows copy every column. Select Confirm;

    3. Select the gear icon of customers, then Target, then redis. Under Document key, the Preview shows public :: customers :: id. That is the key of every hash of this entity. Leave Default key as it is;

    4. Select the gear icon of drivers, then Target, then redis. Switch on Customize document key. Enter the Prefix driver, choose the Separator -, and leave Suffix empty. The Fields list holds id, so the Preview shows driver - id;

    5. Close the dialog and select Confirm and continue.

    Field mapping dialog with the PostgreSQL customers table on the source side and the Redis NoSQL target with Target Scope public and Target Collection customers
    Figure 2. The Field mapping dialog of the Redis target. Redis has no table to create: Target Scope and Target Collection only name the key.
    Settings dialog of public.drivers with the Target tab open
    Figure 3. The custom key of the drivers entity: the prefix driver, the separator - and the field id.

    The Separator list offers ::, -, _, / and None. With the default key, Gluesync joins the scope, the collection and the values of the key columns with ::, for example public::customers::1. With a custom key, the prefix replaces the scope and the collection. Key order sets the order of the fields when the key has more than one.

    Expected result

    Both entities appear in the pipeline on Hold, as public.customers and public.drivers. Nothing was written to Redis yet.

    Run the snapshot and read the hashes

    UI + CLI

    1. On Hold, select the start options button of customers, the one next to the play button. The Snapshot (copy-only) dialog opens;

    2. Keep Keep and merge under Data already in the target. It overwrites the hashes that have the same key and leaves the other keys alone. Under Speed, select Custom to see the write settings: Write method is Standard with the label This target has no bulk load, and you can set the number of Writers during the copy;

    3. Select Start snapshot. When the entity is back on Hold, do the same for drivers;

    4. Read the data. Use the administrator of step 1:

      redis-cli --user admin --pass change-me-admin --no-auth-warning DBSIZE
redis-cli --user admin --pass change-me-admin --no-auth-warning TYPE public::customers::1
redis-cli --user admin --pass change-me-admin --no-auth-warning TTL public::customers::1
redis-cli --user admin --pass change-me-admin --no-auth-warning HGETALL public::customers::1
redis-cli --user admin --pass change-me-admin --no-auth-warning --scan --pattern 'driver-*'
    Snapshot dialog with Keep and merge selected
    Figure 4. The snapshot dialog with the Custom speed tab open. The write method is Standard because the Redis target has no bulk load.
    Expected result

    DBSIZE answers 22: 20 customers and 2 drivers. TYPE answers hash. TTL answers (integer) -1, because Gluesync never sets an expiry. HGETALL returns one pair for each column. The keys of the drivers are driver-2 and driver-4:

     1) "id"
 2) "1"
 3) "name"
 4) "Name-1"
 5) "surname"
 6) "Surname-1"
 7) "address"
 8) "1 Integration Test Street"
 9) "gender"
10) "Male"
11) "phone"
12) "+39-555-0001"
13) "email"
14) "customer-1@example.com"

    The hash holds only the columns that you mapped. Redis stores every value as a string, so the types of the Field mapping dialog (int, string) decide how Gluesync reads the value and not how Redis stores it.

    The snapshot dialog also offers Delete only some rows first. On the Redis target it deletes nothing: in the test, a key that matched the condition was still in Redis after the snapshot. Remove old keys yourself with DEL or UNLINK before a snapshot if you need a clean start.

    Follow live changes

    UI + SQL

    1. In the pipeline, select the play button of customers. The Sync changes (CDC) dialog opens. Keep Where it stopped and select Start entity. Do the same for drivers. Both become Active;

    2. On the source, insert a row with a null phone, update one phone and delete one row:

      INSERT INTO customers VALUES (21, 'Ada', 'Lovelace', '12 Analytical Way', 'Female', NULL, 'ada@example.com');
UPDATE customers SET phone = '+39-555-9999' WHERE id = 2;
DELETE FROM customers WHERE id = 3;

    3. Truncate the drivers table:

      TRUNCATE drivers;

    4. After a few seconds, read the keys:

      redis-cli --user admin --pass change-me-admin --no-auth-warning HGETALL public::customers::21
redis-cli --user admin --pass change-me-admin --no-auth-warning HGET public::customers::2 phone
redis-cli --user admin --pass change-me-admin --no-auth-warning EXISTS public::customers::3
redis-cli --user admin --pass change-me-admin --no-auth-warning EXISTS driver-2
    Expected result

    The hash public::customers::21 exists and holds an empty string for phone: a null column is written as an empty string, and the field stays in the hash. HGET answers +39-555-9999. EXISTS public::customers::3 answers (integer) 0, because a delete runs DEL on the key. EXISTS driver-2 answers (integer) 1.

    In short:

    • an insert and an update both run HSET with the whole row, so a missing key is created and an existing key is overwritten field by field;

    • a delete runs DEL for the key of the row;

    • a truncate on the source is skipped. Core Hub logs the warning Received a transaction message with a truncate operation. This operation is not supported by Redis. Skipped. and no key is removed.

    Tune the writes

    UI

    The Redis target has no bulk load. Each batch is a set of HSET commands sent together, and Gluesync waits for all of them before it takes the next batch. You can tune two things, both in the entity:

    1. Writers. In the Snapshot (copy-only) dialog, under Speed and Custom, set Writers during the copy (1 to 10, 2 by default). The entity tab Fine tuning has the same setting under the name Snapshot writing concurrency;

    2. Batch size at the source. In the entity settings, open Fine tuning. Max number of items per iteration on fetch and Max snapshot page size (KB) set how many rows Core Hub reads for one batch. The Polling interval (ms) sets how often CDC asks the source for changes.

    Expected result

    Measured on one laptop with Core Hub, PostgreSQL and Redis 7.4 in containers, a snapshot of 200,000 rows of a table with five columns took 1.6 seconds with 1 writer, 1.9 seconds with 2 writers and 1.5 seconds with 8 writers, from Starting snapshot to Snapshot write completed in the Core Hub log. At this size more writers made no difference, because the source read and Core Hub were the limit. Change one setting at a time and measure on your own system.

    Fix the errors you are most likely to meet

    CLI + UI

    Each error below was reproduced by changing one setting.

    1. Start a snapshot of an entity whose keys do not match the key patterns of the user. In this guide, remove ~driver-* from the default user and start a snapshot of drivers. The entity goes to Error and the notification Redis command execution error shows NOPERM No permissions to access a key. Redis shows the key in its log: ACL LOG lists reason key and the object driver-2. Fix: add the pattern with ACL SETUSER default '~driver-*'. The entity retries by itself and finishes the snapshot, with no restart;

    2. Save the target with a wrong password. The page shows Failed to save: The connection was saved, but agent <id> of pipeline <id> did not accept the new credentials: Unable to connect to redis/<unresolved>:6379. The message does not say that the password is wrong. ACL LOG shows reason auth and the object HELLO. The wrong values are saved, and the pipeline shows On error with Error: Unknown error. Please check agent redis (<id>) logs in order to find more details. Fix: save the right values with Test and continue, and the pipeline becomes healthy again;

    3. Save a Database name other than 0 for a user that has no SELECT command. The message is the same Unable to connect error, and ACL LOG shows reason command and the object select. Fix: ACL SETUSER default +select. With it, the keys go to that logical database;

    4. Switch on Disable auth while Redis has a password. The agent sends no password and Redis refuses it, so the message is again Unable to connect to <host>/<unresolved>:<port>. Fix: switch the setting off and enter the password;

    5. Use the port of a Redis that accepts only TLS connections. The Control Plane sends no TLS setting for this target, even when you upload a certificate store file, so the agent connects in clear text. The message is Unable to connect to <host>/<unresolved>:<port>, and the Core Hub log shows java.net.SocketException: Connection reset. Keep the Redis server on a private network that Core Hub reaches, or contact support to plan TLS.

    Pipeline page with the entity public.drivers in Error and a Redis command execution error notification with the text NOPERM No permissions to access a key
    Figure 5. A key pattern mistake. The entity public.drivers is in Error and the notification names the Redis answer.
    Expected result

    After each fix, the entity or the pipeline works again without a restart of Core Hub.

    Data types

    Redis stores strings, so Gluesync converts each value to text before it runs HSET. These are the values as they were written to the hash and read back with HGETALL, from a PostgreSQL table with one column of each type, copied with a snapshot, and a session time zone of UTC:

    Source type Value in the hash Note

    integer, smallint, bigint

    127, 9007199254740991

    Plain digits

    numeric(p,s)

    23.50, 123456789012345678901234567890

    Exact. The scale of the source is kept, and a 30-digit value is not rounded

    real, double precision

    1.5, 2.25

    Plain digits

    boolean

    true

    true or false

    varchar, text

    The text

    Written as is

    json, jsonb

    {"test3":"test3"}, {"color": "red", "count": "two"}

    The JSON text that PostgreSQL returns, with its own spacing

    date

    2024-06-10

    ISO date

    timestamp

    2023-12-23T13:38:35

    ISO local date and time

    timestamptz

    2024-06-10T12:44:33Z

    ISO date and time with the offset of the session

    time, timetz

    12:44:45, 12:44:45Z

    ISO time

    uuid, inet, bit(4)

    a2fbcd2f-1ef0-416d-bd8b-4a829c4f09ad, 192.168.1.1, 1010

    Text

    NULL

    An empty string

    The field is in the hash with no characters

    bytea

    [B@de32bc9

    Not usable. Clear the column in the Field mapping dialog

    integer[]

    [Ljava.lang.Integer;@1c4f3875

    Not usable. Clear the column in the Field mapping dialog

    Numbers, booleans, text, dates and times arrive as readable text. Binary columns and arrays are the exception: they arrive as the text of a Java object, which carries no data and changes from one run to the next. Clear the check box of such a column in the Field mapping dialog, so the hash does not hold a meaningless value.

    The Redis target reference lists the connection fields and the REST API setup.

    Bulk and throughput settings

    Setting Default When to change it

    Writers during the copy (snapshot)

    2

    Try more writers when the source and Core Hub are not the limit. In the test, 1, 2 and 8 writers copied 200,000 rows in about the same time

    Max number of items per iteration on fetch (entity)

    Core Hub default

    Raise it for narrow rows, lower it for wide rows

    Max snapshot page size (KB) (entity)

    Core Hub default

    Lower it when a batch is too large for your memory

    Database name

    0

    Use another logical database to keep the keys apart. The user then needs the command SELECT

    Write method

    Standard

    Fixed. The Redis target has no bulk load

    Read Tune replication throughput for the settings that apply to every target.

    Verify the result

    You have set up the Redis target if:

    • The default user has a password and only the commands HSET, HSETNX and DEL (and SELECT for a logical database other than 0), on the key patterns that Gluesync writes;

    • The pipeline is HEALTHY and the Username of the form is default;

    • After the snapshot, TYPE of a key is hash, TTL is -1, and DBSIZE matches the number of source rows;

    • A CDC insert and update change the hash, and a delete removes the key;

    • You know that a truncate and Delete only some rows first remove nothing in Redis;

    • Binary and array columns are cleared in the Field mapping dialog.

    Troubleshooting

    Problem Possible cause What to do

    Entity in Error, notification Redis command execution error with NOPERM No permissions to access a key

    The user has no key pattern that matches the keys of the entity

    ACL LOG shows the object that was refused. Add the pattern with ACL SETUSER default '~<pattern>'. The entity retries by itself

    Failed to save with Unable to connect to <host>/<unresolved>:<port>

    Wrong host or port, a wrong password, Disable auth on a Redis that has a password, a Database name without the command SELECT for the user, or a TLS-only port

    Check ACL LOG. reason auth is a wrong password, reason command with select is the missing command. With no entry, check the host, the port and the network. The agent has no TLS setting in this release

    Pipeline On error with Error: Unknown error. Please check agent redis (<id>) logs in order to find more details

    The saved connection values are wrong. They are saved even when the test fails

    Enter the right values and select Test and continue

    A named Redis user with a password cannot sign in

    The agent sends the password only, so Redis authenticates it as the default user

    Give the default user the password and the restrictions, as in step 1

    The Username field shows Required

    The form needs a value, but the agent ignores it

    Enter default

    A hash value looks like [B@…​ or [Ljava.lang.Integer;@…​

    A bytea or an array column is written as the text of a Java object

    Clear the column in the Field mapping dialog

    A field of a hash is empty although the source column is not

    The source value is null

    A null column is written as an empty string. The field has no characters

    Keys stay in Redis after TRUNCATE on the source

    The target skips a truncate

    Delete the keys yourself with DEL or UNLINK

    Old keys stay after a snapshot with Delete only some rows first

    The Redis target does not run that deletion

    Delete the keys yourself before the snapshot

    Related guides

    Contact MOLO17 Support if you need assistance with a Redis target.