Sync Schema changes automatically with Logical Replication Postgresql

Question

I know there is limitation for logical replication to publish schema change to the subscriber. And for example if you add a column in publisher you have to manually add the same column to the subscriber to sync again.

Is there a workaround to do it automatically, tool or workflow that you use?

Answer 1

You can use triggers to log DDL commands to a table and replicate the table.

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

-- create function to log ddl events
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 trigger to call ddl logger on ddl events
CREATE EVENT TRIGGER log_ddl_trigger
ON ddl_command_end
EXECUTE FUNCTION log_ddl_changes();

You will need to come up with a schema on the receiving side to apply these statements. This can also be accomplished with triggers

-- create a sql command runner function
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;

-- call the runner function when a new item is added to the ddl_log table
CREATE TRIGGER execute_ddl_after_insert
AFTER INSERT ON ddl_log
FOR EACH ROW
EXECUTE FUNCTION execute_ddl_command();

-- ensure the trigger runs since logical replication usually disables triggers
ALTER TABLE ddl_log
   ENABLE ALWAYS TRIGGER execute_ddl_after_insert;

Keep in mind there may be security risks involved depending on who can write to ddl_log on the primary (you're effectively making a remote-execution system).

Ref https://www.percona.com/blog/power-of-postgresql-event-based-triggers/

Answer 2

As you saw, logical replication does not replicate DDL. That's the first point of the documentation (see https://www.postgresql.org/docs/15/logical-replication-restrictions.html)

What I would do is:

• store my DDL in a .SQL file
• create a small program (pick your favorite language) that will connect to all your databases (You can describe that in a simple text file) and play the same SQL file on all of them using psql

To simplify the connection, I strongly advise using a service file and a password file (for security reason if you use a password-based connection).