log_rep_testing.txt

--- on publisher
SELECT 'CREATE TABLE manytables_'||i||'(i int);' FROM generate_series(1, 100) g(i) \gexec
SELECT pg_create_logical_replication_slot('mysub_slot', 'pgoutput');

--- on subscriber
SELECT 'CREATE TABLE manytables_'||i||'(i int);' FROM generate_series(1, 100) g(i) \gexec

CREATE OR REPLACE PROCEDURE log_rep_test(max INTEGER) AS $$
DECLARE
    counter INTEGER := 1;
    total_duration INTERVAL := '0';
    avg_duration FLOAT := 0.0;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    WHILE counter <= max LOOP
        
        EXECUTE 'DROP SUBSCRIPTION IF EXISTS mysub;';

        start_time := clock_timestamp();
        EXECUTE 'CREATE SUBSCRIPTION mysub CONNECTION ''dbname=postgres port=5432'' PUBLICATION mypub WITH (create_slot=false, slot_name=''mysub_slot'');';
        COMMIT;

        WHILE EXISTS (SELECT 1 FROM pg_subscription_rel WHERE srsubstate != 'r') LOOP
            COMMIT;
        END LOOP;

        end_time := clock_timestamp();


        EXECUTE 'ALTER SUBSCRIPTION mysub DISABLE;';
        EXECUTE 'ALTER SUBSCRIPTION mysub SET (slot_name = none);';

        
        total_duration := total_duration + (end_time - start_time);
        
        counter := counter + 1;
    END LOOP;
    
    IF max > 0 THEN
        avg_duration := EXTRACT(EPOCH FROM total_duration) / max * 1000;
    END IF;
    
    RAISE NOTICE '%', avg_duration;
END;
$$ LANGUAGE plpgsql;


call log_rep_test(5);