Sync DDL changes over Postgres logical replication

ddl-triggers.sql

-- create log table (source side)
CREATE TABLE ddl_log (
    id integer PRIMARY KEY,
    object_tag TEXT,
    ddl_command TEXT,
    timestamp TIMESTAMP
);
CREATE SEQUENCE ddl_log_seq;

-- create log function (source side)
CREATE OR REPLACE FUNCTION log_ddl_changes()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO ddl_log (id, object_tag, ddl_command, timestamp)
        VALUES (nextval('ddl_log_seq'), tg_tag, current_query(), current_timestamp);
END;
$$ LANGUAGE plpgsql;

-- create ddl logging trigger (source side)
CREATE EVENT TRIGGER log_ddl_trigger
ON ddl_command_end
EXECUTE FUNCTION log_ddl_changes();


-- ddl executor function (receiving side)
CREATE OR REPLACE FUNCTION execute_ddl_command()
RETURNS TRIGGER AS $$
BEGIN
    SET search_path TO public;
    EXECUTE NEW.ddl_command;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- execute ddl when it's added to log table (receiving side)
CREATE TRIGGER execute_ddl_after_insert
AFTER INSERT ON ddl_log
FOR EACH ROW
EXECUTE FUNCTION execute_ddl_command();

-- allow trigger to run on replication connection (receiving side)
ALTER TABLE ddl_log
   ENABLE ALWAYS TRIGGER execute_ddl_after_insert;
   
-- Congrats! You've configured remote SQL execution (take care to protect ddl_log table)