pg_timetable against replica?

[benchub]

The discussion in issue #563 seems to imply that pg_timetable cannot be run against a replica db. That certainly seems to be what I'm observing ([INFO] [notice:Cannot obtain lock on a replica. Please, use the primary node] [severity:NOTICE]) but it's quite unfortunate if true. Is there a way to make this happen? I was hopeful that pg_timetable might be a way to run queries on all my postgres nodes, as pg_cron can only run on primary nodes by design.

[pashagolub]

You can still run remote tasks on replica. What kind of routine is it?

[benchub]

Oh? When I have pg_timetable connect, even when there are no tasks configured, I get:

[INFO] [notice:Cannot obtain lock on a replica. Please, use the primary node] [severity:NOTICE] Notice received
[ERROR] [error:Cannot obtain lock for a session] Connection failed

[pashagolub]

1. pg_timetable must connect to primary
2. On primary you need to have chain definition
3. Tasks in this chain can be remote

Example:

DO $$
DECLARE 
    v_task_id bigint;
    v_chain_id bigint;
BEGIN
    -- add a remote job on primary
    INSERT INTO timetable.chain (chain_id, chain_name, run_at, live) 
    VALUES (DEFAULT, 'remote db', '* * * * *', TRUE)
    RETURNING chain_id INTO v_chain_id;

    INSERT INTO timetable.task (chain_id, task_order, command, database_connection, ignore_error)
    VALUES (v_chain_id, 
            1,
            'INSERT INTO remote_table(remote_event, timestmp) VALUES ($1, CURRENT_TIMESTAMP)', 
            format('host=%s port=%s dbname=%I user=%I password=somestrong',                     
                    <PUT REPLICA IP HERE>, 
                    <PUT REPLICA PORT HERE>,
                    current_database(),
                    session_user
                    ), 
            TRUE)
    RETURNING
        task_id INTO v_task_id;

    --Parameter values for task
    INSERT INTO timetable.parameter (task_id, order_id, value)
    VALUES 
        (v_task_id, 1, '["Row 1 added"]'::jsonb), 
        (v_task_id, 2, '["Row 2 added"]'::jsonb);
END;
$$ LANGUAGE PLPGSQL;

[benchub]

Ooh, interesting. OK, that could work. Given that I don't want to embed a password into the job definition, could I use .pgpass where pg_timetable is running? Could I even use a chain to generate an IAM token and use that? (Looking to use this against an AWS RDS environment.)

[pashagolub]

Yeap, .pgpass should definitely work! If not, please, file a bug :)

We have an idea about implementing secrets but probably it's possible to use 3rd party vault as well.