Replicate to Redis

Create triggers that push rows to Redis

1. Install PostgreSQL Redis Extension

2. Create this replicate_to_redis() function:

CREATE OR REPLACE FUNCTION replicate_to_redis() RETURNS trigger AS
$body$
DECLARE
  redis_key TEXT;
  pk TEXT;
  pkcols CURSOR FOR
    -- determine the primary key column(s)
    SELECT c.column_name as col
    FROM information_schema.table_constraints tc 
    JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 
    JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
    WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name = TG_TABLE_NAME;
BEGIN
  redis_key = '';
  IF TG_OP = 'DELETE' THEN
    NEW = OLD;
  END IF;
  FOR pkcol IN pkcols LOOP
    IF (length(redis_key) > 0) THEN
      redis_key = redis_key || ',';
    END IF;
    EXECUTE 'SELECT (' || quote_literal(NEW) || '::' || TG_RELID::regclass || ').' || quote_ident(pkcol.col) INTO pk;
    redis_key = redis_key || pk;
  END LOOP;
  redis_key = TG_TABLE_NAME || ':' || redis_key;
  BEGIN
    PERFORM redis_connect(0); -- TODO: use redis configuration json
  EXCEPTION 
    WHEN OTHERS THEN 
    -- ignore 'already connected' error
  END;
  IF TG_OP = 'DELETE' THEN
    PERFORM redis_command(0, 'DEL %s', redis_key);
  ELSE
    PERFORM redis_command(0, 'SET %s %s', redis_key, row_to_json(NEW)::text);
  END IF;
  RETURN null;
EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'Problem sending to Redis: %', SQLERRM;
  RETURN null;
END;
$body$
LANGUAGE 'plpgsql';

3. Configure your redis connection

COMMENT ON FUNCTION public.replicate_to_redis()
IS '{
  "host": "127.0.0.1",
  "port": 6379,
  "pass": "",
  "db": 0
}';

3. Create a trigger on each table that you want to replicate to Redis:

CREATE TRIGGER redis AFTER INSERT OR UPDATE OR DELETE
ON "authors" FOR EACH ROW EXECUTE PROCEDURE replicate_to_redis();

CREATE TRIGGER redis AFTER INSERT OR UPDATE OR DELETE
ON "books" FOR EACH ROW EXECUTE PROCEDURE replicate_to_redis();  

4. That's it. Now anytime you INSERT/UPDATE/DELETE, your record(s) will sync with Redis. Take advantage of super fast reads from Redis by instantiating Oreo with the cache option set to your Redis client object!

(Tested with PostgreSQL 9.3.5)